0612-0614

Previous Table of Contents Next

Page 612

CAUTION

Using operating system authenticated roles should be considered only at sites where the DBA can make changes to the appropriate file. If the DBA lacks this authority, the entire process entails too much overhead to be useful. Grants should be performed at the database level instead.

One of the first steps in creating an operating system authenticated role is to make certain that the parameter OS_ROLES in the INIT.ORA parameter file is set to TRUE. This parameter enables the DBA to enable or disable the use of operating system authenticated roles.

CAUTION

To prevent possible breaches of security, a default role cannot be authenticated at the operating system level when Oracle's MultiThread Server is running. If breaches of security are not a concern, you can enable them for multithread sites by setting the value of REMOTE_OS_ROLES to TRUE. Consider this carefully .

Operating system authenticated roles, like operating system authenticated accounts, must be created at the database level. As with user accounts, this is done by identifying them externally. For example:

 % sqlplus system Password: ........ Connected. SQL> create role manager identified externally; Role created. 

Each role to be used by the Oracle database instance must be defined as being identified externally in the database. It must also be defined at the operating system. The role always has the prefix ora_, following by the Oracle SID of the instance and the name of the role. It can also have the suffix d (if it is a default role) or a (if the user has ADMIN OPTION on the role).

In the following example, a role named manager is set up in the norm instance:

 ora_norm_manager_ad:*:512:larry,daryl,o_daryl 

The users larry, daryl, and o_daryl have manager as their default role, with ADMIN OPTION on it. Assuming that the role has been created ”that is, identified externally ”within the database and that the database has been restarted with OS_ROLES set to TRUE, this is all that is required to authenticate an account at the operating system level. Modifying this role is a matter of simply adding a user to the group at the UNIX level. Note that operating system authentication is not available on all platforms.

Page 613

System Privilege Roles

Oracle6 has only three system privileges. The privileges are

• connect, which enables the user to connect to the database
• resource, which enables the user to create objects in database tablespaces
• dba, which gives the user full system rights on the database

Oracle7 and Oracle8 provide for backward compatibility by giving combination privileges that simulate the same functionality as their Oracle6 counterparts. It uses roles of system privileges to accomplish this task.

The Oracle7 and Oracle8 connect privileges that are assigned to a user by means of system privilege roles are

 ALTER SESSION CREATE CLUSTER CREATE DATABASE LINK CREATE SEQUENCE CREATE SESSION CREATE SYNONYM CREATE TABLE CREATE VIEW 

The Oracle7 and Oracle8 RESOURCE privileges that are assigned to a user by means of system privilege roles are

 CREATE CLUSTER CREATE PROCEDURE CREATE SEQUENCE CREATE TABLE CREATE TRIGGER UNLIMITED TABLESPACE 

UNLIMITED TABLESPACE is normally not available as part of a role. Oracle enables it specifically to deal with backward compatibility.

The Oracle7 and Oracle8 EXP_FULL_DATABASE privileges that are assigned to a user by means of system privilege roles are

 SELECT ANY TABLE BACKUP ANY TABLE INSERT, UPDATE, and DELETE on SYS.INCEXP 

Page 614

 INSERT, UPDATE, and DELETE on SYS.INCVID INSERT, UPDATE, and DELETE on SYS.INCFIL 

The Oracle7 and Oracle8 IMP_FULL_DATABASE privilege that is assigned to a user by means of system privilege role is

 BECOME USER 

The Oracle7 and Oracle8 DBA privileges that are assigned to a user by means of system privilege roles are

 ALL PRIVILEGES WITH ADMIN OPTION EXP_FULL_DATABASE IMP_FULL_DATABASE 

There have been rumors that these system roles have been provided only for backward compatibility with previous versions of Oracle and that future releases will not support them. Although this seems unlikely , given the amount of software that relies on these privileges, you should be aware of it. After all, Oracle undertook drastic changes when it moved from Oracle6 to Oracle7.

Database Auditing

This section on database auditing is the one that really causes the Mission: Impossible soundtrack to play louder. Auditing gives the DBA the ability to track information within the database. It provides information on who performed a certain operation and when it was performed. This is a powerful security feature of the Oracle RDBMS, but it comes with a price.

Auditing is a reactive function. It gives the DBA information about an activity only after it has already occurred. This reactive information provides a snapshot of what occurred, depending on the level of detail being audited . It gives the DBA a basis for tracking changes within the database.

Because auditing causes additional rows to be added to the database for each operation, it is important to balance the auditing being done against constraints such as performance overhead and physical storage requirements. Unless site-specific reasons require otherwise , the DBA should limit the amount of information being audited. It is not uncommon for DBAs to run continuous high-level audit trails that track which users are connecting to the database, for example. It is much more uncommon for the database to track all SQL statements being issued by all users at all times. As a rule of thumb, the DBA should introduce only lower levels of auditing when he suspects inappropriate activity, and he should be specific about whom the audit is directed against.

To activate auditing for a database instance, the DBA must make certain that the AUDIT_TRAIL parameter of the INIT.ORA parameter file is set to DB or OS to indicate where the audit trail should be written. The default value for this parameter is NONE.

Previous Table of Contents Next