Page 578
Table 23.2 ContinuedObject | SQL Statement Allowed |
REFERENCES | CREATE or ALTER TABLE statement defining a FOREIGN KEY integrity constraint on object (tables only). |
SELECT | SELECT...FROM object (table, view, or snapshot). SQL statements using a sequence. |
UPDATE | UPDATE object (table or view). |
A role is a database entity that is a named group of privileges. This creates a many-to-one relationship between privileges and roles, where you have many privileges to one role. A role is a database entity that is unique within the database and not owned by a user. You can create a role with the CREATE ROLE statement, and it cannot be the same name as a user. Users can also create roles if they have CREATE ROLE privileges. When you create a role, it becomes part of your default role set.
Role Authorization by the DatabaseThe use of a role can be authenticated by a password. The SET ROLE command enables the role.
Role Authorization by the Operating SystemThe following statement creates a role named ACCTS_REC and requires that the operating system authorize its use:
CREATE ROLE role IDENTIFIED EXTERNALLY;
Operating system role authentication is possible only when the operating system can dynamically link operating system privileges (OSP) with applications. When a user starts an application, the operating system grants an OSP to the user. The granted OSP corresponds to the role associated with the application. At this point, the application can enable the application role.
If a role is authorized by the operating system, you must configure information for each user at the operating system level. This operation is operating system-dependent.
The database has a soft limit of how many roles a user can have as part of the default role set. If the user has more roles than that limit, he or she will receive an error when logging in. If this error occurs, check MAX_ENABLED_ROLES in the init.ora; it may need to be increased.
A role may also be granted to another role, allowing all the privileges of the granted role to be inherited.
Administrators should have only the authority they need to administer the database. The
primary administration tasks in a database environment can be broken into two main areas:
security administration and database administration. A
security administrator creates, alters, and
Page 579
drops users, and maintains security roles and user profiles, but does not have DBA privileges, such as starting and stopping a database.
When you grant a role to a user who will administer the privileges of the role, you must do so with the ADMIN option. This option enables users to perform administration activities, such as alter, drop the role, and grant the role to other users with the ADMIN option. This option should not be given to anyone other than administrators.
For backward compatibility, the DBA, RESOURCE, and CONNECT security roles are still around. The CONNECT role enables users to establish a session on the database and create database objects. The RESOURCE role enables users to create database entities in their own schema. The DBA role gives users complete authority in the Oracle environment, much like the root user in the UNIX operating system.
Oracle can audit and record activity that occurs on the database. You enable auditing by using the AUDIT SQL command and disabling auditing using the NOAUDIT SQL command. There are three categories of audit actions: login attempts, object accesses (specific statements on specific objects), and database actions (specific system privilege and statements without regard to object).
Any command, successful or unsuccessful, can be audited in these categories. To create the audit system views, run the CATAUDIT.SQL file script as the user SYS and set the init.ora parameter AUDIT_TRAIL. You can set up AUDIT_TRAIL to write to the database or to an operating system file: AUDIT_TRAIL=DB will write to the database, and AUDIT_TRAIL=OS will write to an operating system file. You must restart the Oracle instance in order for the new init.ora parameter to take affect. If you set the parameter AUDIT_TRAIL to DB, all audited activity will be written to the SYS.AUD$ table. You should modify the tablespace storage parameter of the SYS.AUD$ table from the system tablespace to a tablespace created for auditing purposes. You can issue all the audit commands from SQL*WORKSHEET or SQL*Plus.
The AUDIT ANY privilege is required to issue audit commands. To audit logins, use the AUDIT SESSION command. This command can audit all successful and unsuccessful attempts to establish a connection with the database. To audit only unsuccessful attempts, use the AUDIT SESSION WHENEVER NOT SUCCESSFUL command. To audit only successful attempts, use the AUDIT SESSION WHENEVER SUCCESSFUL command. You can generate audit reports if the audit data is stored in the SYS.AUD$TABLE. To report on login attempts, use the DBA_AUDIT_SESSION view:
SELECT os_username, /* O/S user name*/ username, /* Oracle user name */ to_char(timestamp,'DDMONYY HH24:MI'), /* Login time */ to_char(logoff_time, `DDMONYY HH24:MI') /* Logoff time */ FROM dba_audit_session
Page 580
ORDER BY os_username; OUTPUT TO QUERY OS_USERNAME USERNAME TIMESTAMP LOGOFF JOHNES JOHNES 24MAR97 18:00 14FEB97 18:05
By auditing database actions, you can audit at the statement and system privilege level, without regard to a specific database object. The statement AUDIT FEATURE OF AUDIT enables you to audit more than one SQL statement. For example, using the audit statement ROLE audits the CREATE ROLE, ALTER ROLE, SET ROLE, and DROP ROLE SQL statements. To audit system privileges, you must specify the privilege. The system privilege ALTER DATABASE is not included in the statement audit option, but you can still audit it because it is a system privilege. Some of the statement audit options use the same name as the system privileges. You can use the AUDIT statement for a user, session, or access. You can further define it to audit only successful or unsuccessful statements. A list of statements that can be audited are shown in Table 23.3.
Table 23.3 Statement Audit OptionsStatement | Option |
OPTION | SQL STATEMENT |
ALTER SYSTEM | ALTER SYSTEM |
CLUSTER | CREATE CLUSTER, ALTER CLUSTER, TRUNCATE CLUSTER, DROP CLUSTER |
DATABASE LINK | CREATE DATABASE LINK, DROP DATABASE LINK |
INDEX | CREATE INDEX, ALTER INDEX, DROP INDEX |
NOT EXISTS | All SQL statements that return an Oracle error because the specified structure or object does not exist |
PROCEDURE | CREATE [OR REPLACE] FUNCTION, CREATE [OR REPLACE] PACKAGE, CREATE [OR REPLACE] PACKAGE BODY, CREATE [OR REPLACE] PROCEDURE, DROP PACKAGE, DROP PROCEDURE |
PUBLIC DATABASE | CREATE PUBLIC DATABASE LINK, DROP PUBLIC |
LINK | DATABASE LINK |
PUBLIC SYNONYM | CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM |
ROLE | CREATE ROLE, ALTER ROLE, SET ROLE, DROP ROLE |
ROLLBACK SEGMENT | CREATE ROLLBACK SEGMENT, ALTER DROPBACK SEGMENT, DROP ROLLBACK SEGMENT |
SEQUENCE | CREATE SEQUENCE, DROP SEQUENCE |
SESSION | Connects and Disconnects |