8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Security Enhancements In Oracle9i
There are a number of security enhancements in Oracle9i. Here I will focus on those directly related to the "Oracle9i Database: New Features For Administrators" OCP exam.
- Internal Connections Deprecated
- Secure Application Roles
- Partitioned Fine Grain Access Control
- Global Application Context
- Fine Grained Auditing (FGA)
- Other New Features
Related articles.
- Auditing in Oracle 10g Release 2
- Database Security Enhancements in Oracle Database 10g
- Auditing Enhancements (DBMS_AUDIT_MGMT) in Oracle Database 11g Release 2
- Virtual Private Databases (VPD)
- Oracle Label Security (OLS) in Oracle 9i
Internal Connections Deprecated
The CONNECT INTERNAL
syntax has finally been deprecated. Connecting as a priviledged user is now only possible using the following methods.
CONNECT / AS SYSDBA
CONNECT / AS SYSOPER
CONNECT username/password AS SYSDBA
CONNECT username/password AS SYSOPERA
Remeber also, Server Manager (SVRGMRL) has been deprecated so all administration must be done using SQLPlus.
Secure Application Roles
Secure application roles are a mechanism for checking that a user is entitled to have a role granted on them at runtime. Previous versions of Oracle allowed the role to have a password, but secure application roles have their validation done using packages assigned to them during creation.
CREATE ROLE tsh_role USING TSH.TSH_Admin;
Amongst other things the security package can use the following session information to decide if the role should be granted.
IF SYS_CONTEXT('USERENV','PROXY_USER') = '??' THEN ...
IF SYS_CONTEXT('USERENV','IP_ADDRESS') = '??' THEN ...
IF SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') = '??' THEN ...
If the role is valid for this access it can be set using the following.
DBMS_SESSION.SET_ROLE('TSH_ROLE');
Partitioned Fine Grain Access Control
In Oracle8i only a single security policy could be assigned to a table. This means that if two independent applications referred to a single table, the security policy assigned to that table would have to be developed to cope with all possibilities for both applications. In Oracle9i multiple security policies can be assigned to a single table, using an application context to decide between them. This is implemented using policy groups.
First policy groups are defined for each application. Then security policies are assigned to these policy groups. Then an application/driving context is defined which sets the policy group for the current login so when tables are accessed, only policies from the selected policy group are executed.
Security policies associated with the predefined policy group SYS_DEFAULT
are always executed along with
the application specific policies, making them global policies. All Oracle8i security policies are automatically assigned
to this policy group.
Global Application Context
When virtual private database (VPD) and connection pooling are used in conjunction, one or more application contexts can be shared between connections, improving performance as per-user context setup is avoided.
The global context is defined using the following syntax.
CREATE CONTEXT tsh USING tsh.init ACCESSED GLOBALLY;
Once created, this context is stored in the SGA, reducing initialization time for other connections. The Dbms_Session
package has been updated to include routines to assign and clear contexts and identifiers to facilitate this process. The new procedures
are listed below.
SET_CONTEXT
CLEAR_CONTEXT
SET_IDENTIFIER
CLEAR_IDENTIFIER
When the application server starts up it makes several connections to the same database user. When the a user logs into the application server he is allocated to one of the connections and given a temporary client id. The application server then sets his secific context along with his current session identifier.
DBMS_SESSION.set_context(namespace, attribute, value, username, client_id); DBMS_SESSION.set_identifier(client_id); -- eg. DBMS_SESSION.set_context('tsh', 'id', 'TIM', 'APP_SERVER_DB_USER', 12345); DBMS_SESSION.set_identifier(12345);
This way a single database user can use differing contexts depending on the validation done by the application server. Once the user disconnects the application can issue the following.
DBMS_SESSION.clear_identifier(12345);
Fine Grained Auditing (FGA)
Fine grained auditing extends Oracle existing auditing capabilities by allowing the user to audit actions based on
user-defined predicates. Assuming that finance clerks are allowed to look at the salaries of all employees except those
with salaries greater than £50,000, the following audit may be assigned to the employees table using the Dbms_FGA
package.
DBMS_FGA.add_policy( object_schema => 'SCOTT', object_name => 'EMPLOYEES', policy_name => 'SALARY_CHK_AUDIT', audit_condition => 'SALARY > 50000', audit_column => 'SALARY');
From now on, if salaries greater than £50,000 are queried the action will be logged in the FGA_LOG$
table.
If extra processing is required based on an FGA event this can be accomplished by defining a database procedure and associating this to the audit event.
DBMS_FGA.add_policy( object_schema => 'SCOTT', object_name => 'EMPLOYEES', policy_name => 'SALARY_CHK_AUDIT', audit_condition => 'SALARY > 50000', audit_column => 'SALARY', handler_schema => 'TSH', handler_module => 'FIRE_CLERK', enable => TRUE);
The DBMS_FGA
package contains the following procedures.
ADD_POLICY
DROP_POLICY
ENABLE_POLICY
DISABLE_POLICY
Other New Features
-
Default Account Security - All default accounts except SYS, SYSTEM, SCOTT, DBSNMP, OUTLN, AURORA$ORB$UNAUTHENTICATED, AURORA$JIS$UTILITY$,
OSE$HTTP$ADMIN are locked when the database is created. In future releases more users including
SYSTEM will also be locked by default. Oracle expects these accounts to be unlocked using
ALTER USER tsh ACCOUNT UNLOCK;
only during use, thereby reducing the likelyhood of unauthorised access. -
Oracle Label Security (OLS) - Built on the concept of Virtual Private Database (VPD), OLS adds a
NUMBER(10)
label column to the selected tables. This column is used to signify a security level or label. Rows can only be accessed by users with the appropriate label. The advantage of this method over normal VPD is that it is an "out-of-the-box" solution requiring no code. Label administration is performed via the Oracle Policy Manager, which is also used for VPD and Application Context management. - Single Sign-On - Using a combination of technologies including LDAP, Public Key Encryption and Secure Application Roles, this feature allows a single login to an application server to authorize access to multiple servers and databases. The passwords are configured in the LDAP server and passed between the servers/databases using assorted Encryption methods.
For more information see:
- Auditing in Oracle 10g Release 2
- Database Security Enhancements in Oracle Database 10g
- Auditing Enhancements (DBMS_AUDIT_MGMT) in Oracle Database 11g Release 2
- Virtual Private Databases (VPD)
- Oracle Label Security (OLS) in Oracle 9i
Hope this helps. Regards Tim...