Transcript s10_SecuringOracleDB

Securing the Oracle Database
中原大學應用數學系
劉立民
Introducation

Passwords and Users

Oracle Password Management Features

Default Oracle Users

Privileges, Grants, Roles, and Views

Oracle9i Security Products and Features
Password features
Password enhancements

User PROFILE


Default profile
Create/alter profile
SQL> describe DBA_PROFILES;
名稱
空值?
類型
----------------------------------------------------------------- -------- ----------------------PROFILE
NOT NULL VARCHAR2(30)
RESOURCE_NAME
NOT NULL VARCHAR2(32)
RESOURCE_TYPE
VARCHAR2(8)
LIMIT
VARCHAR2(40)
SQL> select * from DBA_PROFILES;
PROFILE
-----------------------------DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
RESOURCE_NAME
-------------------------------COMPOSITE_LIMIT
FAILED_LOGIN_ATTEMPTS
SESSIONS_PER_USER
PASSWORD_LIFE_TIME
CPU_PER_SESSION
PASSWORD_REUSE_TIME
CPU_PER_CALL
PASSWORD_REUSE_MAX
LOGICAL_READS_PER_SESSION
PASSWORD_VERIFY_FUNCTION
RESOURCE
-------KERNEL
PASSWORD
KERNEL
PASSWORD
KERNEL
PASSWORD
KERNEL
PASSWORD
KERNEL
PASSWORD
LIMIT
----------UNLIMITED
UNLIMITED
UNLIMITED
UNLIMITED
UNLIMITED
UNLIMITED
UNLIMITED
UNLIMITED
UNLIMITED
NULL
Password enhancements (Con’t)

Resource_type


Password
Kernel
SQL> select * from dba_profiles where resource_type = 'PASSWORD'
PROFILE
RESOURCE_NAME
RESOURCE
------------------------------ -------------------------------- -------DEFAULT
FAILED_LOGIN_ATTEMPTS
PASSWORD
DEFAULT
PASSWORD_LIFE_TIME
PASSWORD
DEFAULT
PASSWORD_REUSE_TIME
PASSWORD
DEFAULT
PASSWORD_REUSE_MAX
PASSWORD
DEFAULT
PASSWORD_VERIFY_FUNCTION
PASSWORD
DEFAULT
PASSWORD_LOCK_TIME
PASSWORD
DEFAULT
PASSWORD_GRACE_TIME
PASSWORD
LIMIT
--------UNLIMITED
UNLIMITED
UNLIMITED
UNLIMITED
NULL
UNLIMITED
UNLIMITED
已選取 7 個資料列.

Execute ultpwdmg.sql at $ORACLE_HOME/rdbms/admin
@”D:\Oracle\ora92\rdbms\admin\utlpwdmg.sql”
Password enhancements (Con’t)
SQL> select * from dba_profiles where resource_type = 'PASSWORD'
PROFILE
RESOURCE_NAME
RESOURCE
------------------------------ -------------------------------- -------DEFAULT
FAILED_LOGIN_ATTEMPTS
PASSWORD
DEFAULT
PASSWORD_LIFE_TIME
PASSWORD
DEFAULT
PASSWORD_REUSE_TIME
PASSWORD
DEFAULT
PASSWORD_REUSE_MAX
PASSWORD
DEFAULT
PASSWORD_VERIFY_FUNCTION
PASSWORD
DEFAULT
PASSWORD_LOCK_TIME
PASSWORD
DEFAULT
PASSWORD_GRACE_TIME
PASSWORD
已選取 7 個資料列.
LIMIT
--------3
60
1800
UNLIMITED
VERIFY_FUNCTION
.0006
10
Aging and expiration

Password_life_time


Number of days the same password can be used
for authentication
Password_grace_time

Ex:
Number of days in the grace period
Password_life_time = 90
Password_grace_time = 15
After day 90+15, the account will be locked.
Password History


Parameters prevents users from avoiding
password expiration by changing a password
and changing it back to the original password.
Password_reuse_time


Password_reuse_max


Number of days until a password cannot be
resued.
Number of password changes required before the
current password can be reused.
Setting both to UNLIMITED allows passwords
to be reused immediately.
Account locking

Failed_login_attempts


Number of attempts to log in that can fail
before the user account is locked
Password_lock_time


Amount of time an account remains locked
after the specified number of consecutive
failed login attempts is reached
If set to UNLIMITED, the account will no
unlock automatically.
Password profile parameters
Password profile parameters (Con’t)
Enabling password parameters

Create a user-defined profile
create profile MY_PROFILE limit
failed_login_attempts 5
password_lock_time .5
password_reuse_max UNLIMITED
password_life_time
90
password_reuse_time 60
password_verify_function MY_PASSWORD_FUNCT
password_grace_time 15;

Create users with specific profile
create user NELSON
identified by nel_123
default tablespace USERS
temporary tablespace TMP
password expire
profile MY_PROFILE;
Default Oracle Users


Several default users are created when you create
your database.
Check these users via DBA_USERS
describe DBA_USERS


Lock those unused users
Verify users’ granted_role, and privileges
select * from dba_role_privs where grantee = 'HR';
select * from dba_sys_privs where grantee = 'HR';
select * from dba_tab_privs where grantee = 'HR‘;
DBA_ views that display user privileges
VIEW
Description
DBA_SYS_PRIVS
DBA_ROLE_PRIVS
DBA_TAB_PRIVS
Show the system privileges assigned to user and roles
Show the privileges assigned to a role
Show the users who have been granted access to insert,
update, select, or delete form a table or view
Show what roles exist and whether or not they are
default
DBA_ROLES
Database objects
Object type
Description
Table
Basic unit of data storage. Table data is stored in row
comprising column.
An optional database structure used to quickly locate a row
in a table. The three kinds of indexes are cluster indexes,
table indexes, and bit-mapped indexes.
A virtual table that does not have physical storage.
A serial list of unique numbers used with in a numeric
column that can be used to generate unique values.
Optional groups of one or more tables stored together
because they are commonly used together.
Index
View
Sequence
Cluster
:
Object privileges
Privilege
Action
Select
Insert
Update
View information within a table or view.
Insert new rows of information into a table or view.
Modify one or more columns of information within a table
or view.
Remove one or more columns of information within a table
or view.
Change an object’s definition.
Compile, execute, or access a procedure or function
referenced in a program
Read files in a directory
Create a constraint that refers to a table
Create an index on a table
Delete
Alter
Execute
Read
Reference
Index
Controlling user access

Product-level Security



Users access can be restricted based on the SQL*Plus
product usage.
DBAs can use PRODUCT_USER_PROFILE to disable certain
SQL and SQL*Plus commands in the SQL*Plus environment
on a per-user basis.
PRODUCT_USER_PROFILE table consists of the following
columns:
PRODUCT
USERID
ATTRIBUTE
SCOPE
NUMERIC_VALUE
CHAR_VALUE DATE_VALUE
LONG_VALUE
NOT NULL CHAR (30)
CHAR(30)
CHAR(240)
CHAR(240)
NUMBER(15,2)
CHAR(240)
DATE LONG
PRODUCT_USER_PROFILE
PRODUCT_USER_PROFILE table is owned by SYSTEM

Commands that can be applied in PRODUCT_USER_PROFILE table includes:
ALTER, AUDIT, ANALYZE, CREATE, DELETE, DROP, INSERT, LOCK, NOAUDIT,
RENAME, SELECT, UPDATE, VALIDATE, etc.

Creating the PRODUCT_USER_PROFILE table by Execute pupbld.sql at
$ORACLE_HOME/sqlplus/admin
@”D:\Oracle\ora92\sqlplus\admin\pupbld.sql”

Example:
insert into product_user_profile(product, userid, attribute, char_value)
values('SQL*Plus', 'APPS', 'CREATE', 'DISABLED');
insert into product_user_profile(product, userid, attribute, char_value)
values('SQL*Plus', 'APPS', 'UPDATE', 'DISABLED');
insert into product_user_profile(product, userid, attribute, char_value)
values('SQL*Plus', 'APPS', 'DELETE', 'DISABLED');
insert into product_user_profile(product, userid, attribute, char_value)
values('SQL*Plus', 'APPS', 'INSERT', 'DISABLED');
insert into product_user_profile(product, userid, attribute, char_value)
values('SQL*Plus', 'APPS', 'SELECT', 'DISABLED');

Preventing access using PL/SQL


The DELETE privilege disabled for user APPS
can easily be executed through a PL/SQL
block.
This can be avoided by removing the PL/SQL
block creation access itself.
insert into product_profile (product, userid, attribute, char_value) values
('SQL*Plus', 'APPS', 'DECLARE', 'DISABLED');
insert into product_profile (product, userid, attribute, char_value) values
('SQL*Plus', 'APPS', 'BEGIN', 'DISABLED');

PRODUCT_PROFILE is a synonym of PRODUCT_USER_PROFILE
exists to ensure backwards compatibility.
With Grant Option

DBAs can grant different privileges to different users.
SQL> CREATE USER TOM IDENTIFIED BY ICE;
User created.
SQL> GRANT CREATE SESSION TO TOM;
Grant succeeded.
SQL> GRANT CREATE TABLE TO TOM;
Grant succeeded.
SQL> GRANT CREATE SEQUENCE TO TOM;
Grant succeeded.

DBAs can also grant users “with grant option” to allow them to
grant access to other users without any action on the DBA parts.
SWL> CREATE TABLE TEST (COL NUMBER);
Table created.
SQL> GRANT SELECT, UPDATE ON TEST TO TOM WITH GRANT OPTION;
Grant succeeded.
With Admin Option

DBA can grant users administration privileges
using “with admin option”
SQL> GRANT CREATE TABLE TO TOM WITH ADMIN OPTION;
Grant succeeded.

Revoke uses’ administration privileges
SQL> REVOKE CREATE TABLE FROM TOM;
Revoke succeeded.

Privileges granted by TOM still retain after
DBA revoke TOM’s administration privileges.
Using Role

Why using roles



Grant privileges to individual directly can be very
tedious.
Example, 100 objects with 100 users needs to
make 10,000 grants; one people leave the
company needs to make 100 revokes.
Steps of using roles




Group users into categories
Define one or more roles for each category
Grant proper privileges to each role
Assign roles to users in each category
Create roles

Assume table EMP has two kinds of
users: query EMP and update EMP.
SQL> create role report_writer identified by rep123;
Role created.
SQL> create role data_changer identified by your_pwd;
Role created.

Grant privileges to roles
SQL> GRANT SELECT ON EMP TO REPORT_WRITER;
Grant succeeded.
SQL> GRANT UPDATE, DELETE, INSERT ON EMP TO DATA_CHANGER;
Grant succeeded.
Assign roles to users

Once we create roles with proper privileges,
we can assign roles to users.
SQL> GRANT REPORT_WRITER TO TOM;
Grant succeeded.
SQL> GRANT DATA_CHANGER TO HENRY;
Grant succeeded.

Revoke roles from users to maintain the
proper privileges
SQL> REVOLE DATA_CHANGE FROM HENRY;
Revoke succeeded.

DBA_ROLES
SQL> select * from dba_roles;
Oracle-supplied roles

CONNECT


RESOURCE


Gives the user the ability to access the database
Gives the user the ability to create objects and use
space in the database
DBA

Gives the user administrative privileges in the
database
Querying roles
VIEW
Description
USER_ROLE_PRIVS
ROLE_ROLE_PRIVS
ROLE_TAB_PRIVS
ROLE_SYS_PRIVS
SESSION_ROLES
Show
Show
Show
Show
Show
the
the
the
the
the
roles granted to current user
roles which are granted to roles
table privileges granted to roles
system privileges granted to roles
roles which the user currently has enabled
Set roles


Default roles will be enabled
automatically and accessible when
users connect to the database
For roles requiring password, users
need to use set role to enable the role
privileges.
SQL> set role DATA_CHANGE identified by your_pwd;
Using Views to Manage
Privileges


Rather than granting users privileges on a particular
table, you can give them access to a view of the
table.
Views add two more levels of security:



A view can limit access to only selected columns of the base
table.
A view can provide value-based security for the information
in a table. Thus a WHERE clause in the definition of a view
can display only selected rows of a base table.
The user need not be given privileges on base
objects underlying the view.
An Example of a View
View privileges

View creation
SQL> CREATE VIEW STAFF AS (
2
SELECT EMPNO, ENAME, JOB, MGR, DEPTNO FROM EMP
3
);
View created.

Grant privileges to roles
SQL> GRANT SELECT ON STAFF TO REPORT_WRITER;
Grant succeeded.
Row Level Security

Two more effective approaches to
implement row level security:


Virtual Private Database (VPD), in which
you create your own implementation of
row level security; and
Label-based access control, in which you
customize a ready-made VPD policy to
accomplish this.
Virtual Private Database (VPD)



Virtual Private Database is the ability to
perform query modification based on a
security policy you have defined in a package,
and associated with a table, view.
You can implement VPD by writing a stored
procedure to append a SQL predicate to each
SQL statement to control row level access for
that statement.
For example, SQL SELECT * FROM emp; will
be modified, say SELECT * FROM emp where
deptno = 10;
Virtual Private Database: Customers
see Only Their Own Orders
Example of VPD

Choose the tables or views to protect at the row level
SQL> create table employee (
2
trndate date,
3
credit_val number(12,2),
4
debit_val number(12,2),
5
trn_type varchar2(10),
6
cost_center varchar2(10) ) tablespace users;
Table created.


Define the business rules that will be followed for
accessing data in these tables
Create a security context to manage application
sessions
SQL> create or replace context vpd_test using set_vpd_context;
Context created.
Example of VPD (Con’t)

Create a procedure or function to manage setting of
the security context for users
SQL> CREATE TABLE emp (
2
3
4
5
6
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
sal NUMBER(7,2),
deptno NUMBER(2));
Table created.


Define the business rules that will be followed for
accessing data in these tables
Create a security context to manage application
sessions
CREATE OR REPLACE CONTEXT HR USING HR.Context_Package;
Example of VPD (Con’t)

Create a procedure or function to manage
setting of the security context for users
CREATE OR REPLACE PACKAGE Context_Package AS PROCEDURE Set_Context;

Write a package to generate the dynamic
access predicates for access to each table
CREATE OR REPLACE PACKAGE BODY Context_Package IS
PROCEDURE Set_Context IS
This_PERSON_ID NUMBER;
BEGIN
Dbms_Session.Set_Context('HR','SETUP','TRUE');
IF SUBSTR(SYS_CONTEXT('USERENV','SESSION_USER'),1,2)='OE'
:
Dbms_Session.Set_Context('HR','SETUP','FALSE');
END Set_Context;
END Context_Package;
Example of VPD (Con’t)

Make the packages public by executing the
following sql.
GRANT EXECUTE ON hr.Context_Package to Public;

Automate the setting of the security context
CREATE OR REPLACE TRIGGER HR.Set_Security_Context
AFTER LOGON ON DATABASE
BEGIN
HR.Context_Package.Set_Context;
END;
Label-based access control



Label-based access control allows
organizations to assign sensitivity labels to
data rows.
Control access to data based on those labels,
and ensure that data is marked with the
appropriate sensitivity label.
Oracle9i Label Security is not installed by
default with the Oracle9i Enterprise Edition.
Oracle Label Security
Oracle Label Security



Oracle Label Security is built on the Virtual
Private Database toolkit,
Requires no programming whatsoever.
Oracle Label Security delivers a data
dictionary and administrative tools you can
use to



construct valid labels,
set user label authorizations and privileges, and
apply the resulting Oracle Label Security policy to
tables and schemes.
Oracle9i Security Products

Oracle9i Standard Edition









Integrity
Authentication and Access Controls in Oracle9i
Privileges
Roles
Auditing
Views, Stored Program Units, Triggers
Data Encryption
High Availability
Proxy Authentication in Oracle9i
Oracle9i Security Products (Con’t)

Oracle9i Enterprise Edition







Internet Scale Security Features
Application Security
Virtual Private Database in Oracle9i
Secure Application Role
Fine-Grained Auditing
Oracle Auditing for Three-Tier Applications
Java Security Implementation in the Database
Other Oracle security products





Oracle Advanced Security
Oracle Label Security
Oracle Internet Directory
Oracle Net Services
Oracle9i Application Server
References






Oracle
Oracle
Oracle
Oracle
Oracle
Oracle
Security Handbook by Theriault
SQL*Plus: The Definitive Guide by Gennick
9i: Security Overview (9.2) by Oracle
9i Application Server: Security Guide (9.0.2) by Oracle
9i: Database Administration Guide (9.2) by Oracle
9i Net Services: Administrator’s Guide (9.2) by Oracle