Sybex OCA Oracle 10g Administration I Study Guide phần 7 ppsx

Auditing Database Activity 345

Identifying Enabled Object Auditing Options

The object auditing options that are enabled in the database are recorded in the DBA_OBJ_

AUDIT_OPTS data dictionary view. Unlike the statement and privilege _AUDIT_OPTS views, the

DBA_OBJ_AUDIT_OPTS always has one row for each auditable object in the database. There are

columns for each object privilege that auditing can be enabled on, and in each of these columns,

a code is reported that shows the auditing options. For example, the following report on the

HR.EMPLOYEES table shows that no auditing is enabled for the INSERT object privilege and that

the SELECT object privilege has auditing enabled with one audit entry for each access when the

access is successful and one audit entry for each session when the access is not successful:

SELECT owner, object_name, object_type, ins, sel

FROM dba_obj_audit_opts

WHERE owner='HR'

AND object_name='EMPLOYEE_SALARY';

OWNER OBJECT_NAME OBJECT_TY INS SEL

------------ ------------------------- --------- --- ---

HR EMPLOYEE_SALARY TABLE -/- A/S

The coding for the object privilege columns contains one of three possible values: a dash (-) to

indicate no auditing is enabled), an A to indicate BY ACCESS, or an S to indicate BY SESSION. The

first code (preceding the slash) denotes the action for successful statements, and the second code

(after the slash) denotes the action for unsuccessful statements.

Disabling Object Auditing

To disable object auditing, use a NOAUDIT statement, which allows the same WHENEVER options

as the AUDIT statement. For example, to disable auditing of unsuccessful SELECT statements

against the HR.EMPLOYEES table, execute the following:

NOAUDIT select ON hr.employee_salary WHENEVER NOT SUCCESSFUL;

Purging the Audit Trail

Database audit records for statement, privilege, and object auditing are stored in the table

SYS.AUD$. Depending on how extensive your auditing and retention policies are, you will need

to periodically delete old audit records from this table. The database does not provide an inter￾face to assist in deleting rows from the audit table, so you will need to do so yourself. To purge

audit records older than 90 days, execute the following as user SYS:

DELETE FROM sys.aud$ WHERE timestamp# < SYSDATE -90;

4367.book Page 345 Wednesday, October 13, 2004 1:18 PM

346 Chapter 6 User Administration and Security

You might want to copy the audit records into a different table for historical retention or

export them to an operating system file before removing them. It is a good practice to audit

changes to the AUD$ table so that you can identify when changes were made.

The audit table does not have a self-managing purge job and will grow without

bounds. To keep your SYSTEM tablespace from getting too large, you should

regularly delete old entries from the sys.aud$ table.

Managing Fine-Grained Auditing

Fine-grained auditing (FGA) lets you monitor and record data access based on the content of the

data. With FGA, you define an audit policy on a table and optionally a column. When the specified

condition evaluates to TRUE, an audit record is created, and an optional event-handler program is

called. You use the PL/SQL package DBMS_FGA to configure and manage FGA.

In the following sections, you will learn how to create, drop, enable, and disable fine-grained

auditing policies.

Creating an FGA Policy

To create a new FGA policy, use the packaged procedure DBMS_FGA.ADD_POLICY. This proce￾dure has the following parameters:

object_schema This is the owner of the object to be audited. The default is NULL, which tells

the database to use the current user.

object_name This is the name of the object to be monitored.

policy_name This is a unique name for the new policy.

audit_condition This is a SQL expression that evaluates to a Boolean. When this condition eval￾uates to either TRUE or NULL (the default), an audit record can be created. This condition cannot

directly use the SYSDATE, UID, USER, or USERENV functions, it cannot use subqueries or sequences,

nor can it reference the pseudocolumns LEVEL, PRIOR, or ROWNUM.

audit_column This is a comma-delimited list of columns that the database will look to access.

If a column in audit_column is referenced in the SQL statement and the audit_condition is

not FALSE, an audit record is created. Columns appearing in audit_column do not have to also

appear in the audit_condition expression. The default value is NULL, which tells the database

that any column being referenced should trigger the audit record.

handler_schema This is the owner of the event-handler procedure. The default is NULL, which

tells the database to use the current schema.

handler_module This is the name of the event-handler procedure. The default NULL tells the

database to not use an event handler. If the event handler is a packaged procedure, the handler_

module must reference both the package name and program, using dot notation, like this:

UTL_MAIL.SEND_ATTACH_RAW

4367.book Page 346 Wednesday, October 13, 2004 1:18 PM

Auditing Database Activity 347

enable This is a Boolean that tells the database if this policy should be in effect. The default

is TRUE.

statement_types This tells the database which types of statements to monitor. Valid values are

a comma-delimited list of SELECT, INSERT, UPDATE, and DELETE. The default is SELECT.

audit_trail This parameter tells the database whether to record the SQL statement and bind

variables for the triggering SQL in the audit trail. The default value DBMS_FGA.DB_EXTENDED

indicates that the SQL statement and bind variables should be recorded in the audit trail. Set this

parameter to DBMS_FGA.DB to save space by not recording the SQL statement or bind variables

in the audit trail.

audit_column_ops This parameter has only two valid values: DBMS_FGA.ALL_COLUMNS and

DBMS_FGA.ANY_COLUMNS. When set to DBMS_FGA.ALL_COLUMNS, this parameter tells the data￾base that all columns appearing in the audit_column parameter must be referenced in order to

trigger an audit record. The default is DBMS_FGA.ANY_COLUMNS, which tells the database that if

any column appearing in the audit_column also appears in the SQL statement, an audit record

should be created.

To create a new disabled audit policy named COMPENSATION_AUD that looks for SELECT

statements that access the HR.EMPLOYEES table and references either SALARY or COMMISSION_

PCT, execute the following:

DBMS_FGA.ADD_POLICY(object_schema=>’HR’

,object_name=>’EMPLOYEES’

,policy_name=>’COMPENSATION_AUD’

,audit_column=>’SALARY, COMMISSION_PCT’

,enable=>FALSE

,statement_types=>’SELECT’);

Enabling an FGA Policy

Use the procedure DBMS_FGA.ENABLE_POLICY to enable an FGA policy. This procedure will

not raise an exception if the policy is already enabled. For example, you can enable the

COMPENSATION_AUD policy added in the previous section like this:

DBMS_FGA.ENABLE_POLICY(object_schema=>'HR'

,object_name=>'EMPLOYEES'

,policy_name=>'COMPENSATION_AUD');

If you use direct path inserts, be careful with FGA auditing. If an FGA policy

is enabled on a table participating in a direct path insert, the auditing over￾rides the hint, disabling the direct path access and causing conventional

inserts. As with all hints, the database does not directly tell you that your hint

is being ignored.

4367.book Page 347 Wednesday, October 13, 2004 1:18 PM

348 Chapter 6 User Administration and Security

Disabling an FGA Policy

To turn off a fine-grained access policy, use the DBMS_FGA.DISABLE_POLICY procedure. Here

is an example:

DBMS_FGA.DISABLE_POLICY(object_schema=>'HR'

,object_name=>'EMPLOYEES'

,policy_name=>'COMPENSATION_AUD');

Dropping an FGA Policy

To remove an FGA audit policy, use the DBMS_FGA.DROP_POLICY procedure. For example, to

drop the COMPENSATION_AUD policy used in this section, run this:

DBMS_FGA.DROP_POLICY(object_schema=>’HR’

,object_name=>’EMPLOYEES’

,policy_name=>’COMPENSATION_AUD’);

Identifying FGA Policies in the Database

Query the DBA_AUDIT_POLICIES data dictionary view to report on the FGA policies defined in

your database. For example, the following report shows that the policy named COMPENSATION_

AUD on the column SALARY in the table HR.EMPLOYEES is defined, but not enabled:

SELECT policy_name ,object_schema||'.'||

object_name object_name

,policy_column

,enabled ,audit_trail

FROM dba_audit_policies;

POLICY_NAME OBJECT_NAME POLICY ENABLED AUDIT_TRAIL

---------------- ------------ ------ ------- -----------

COMPENSATION_AUD HR.EMPLOYEES SALARY NO DB_EXTENDED

Audit records from this policy, when enabled, capture the standard auditing information as

well as the text of the SQL statement that triggered the auditing (DB_EXTENDED).

Reporting on the FGA Audit Trail Entries

The DBA_FGA_AUDIT_TRAIL data dictionary view is used in reporting on the FGA audit entries

that have been recorded in the database. The following example shows audit trail entries for the

COMPENSATION_AUD policy, listing the database username and the timestamp of the audit record

and computer from which the database connection was made.

SELECT db_user, timestamp, userhost

FROM dba_fga_audit_trail

4367.book Page 348 Wednesday, October 13, 2004 1:18 PM

Exam Essentials 349

WHERE policy_name='COMPENSATION_AUD'

DB_USER TIMESTAMP USERHOST

------------ -------------------- --------------------

CHIPD 10-Jun-2004 09:48:14 XYZcorp\CHIPNOTEBOOK

JUANITA 19-Jun-2004 14:50:47 XYZcorp\HR_PC2

Summary

Oracle 10g gives you a well-stocked toolkit for managing your users and securing your data￾base. You create and manage user accounts with the CREATE, ALTER, and DROP USER state￾ments. You can assign tablespace resources to be used for sorting that are different than those

for tables or indexes. You can limit the disk, CPU, and memory resources that your users con￾sume by employing tablespace quotas and kernel resource limits in user profiles.

To protect your data from unwanted access or manipulation, you can employee object and

system privileges. You can create and use roles to make managing these database privileges eas￾ier. You can enable object, statement, privilege and fine-grained auditing to help you monitor

and record sensitive database activity.

Your Oracle 10g database has several powerful features (user accounts and packages) that

will need to be locked down in your production systems, and in this chapter you learned which

user accounts need to be locked, as well as which standard packages should be locked down to

better protect your company’s data.

Exam Essentials

Be familiar with the authentication methods. Database accounts can be authenticated by the

database (identified by password), by the operating system (identified externally), or by an

enterprise security service (identified globally).

Know how to assign default and temporary tablespaces to users. Assign default and tempo￾rary tablespaces with either a CREATE USER or an ALTER USER statement.

Be able to identify and grant object, system, and role privileges. Know the difference

between these types of privileges and when to use each type.

Know the differences between the WITH ADMIN OPTION and the WITH GRANT OPTION

keywords. The ADMIN option applies to role or system privileges, but the GRANT option applies

to object privileges

Know how to enable roles. Know when a role needs to be enabled and how to enable it.

Be able to secure your database. Make sure you know how to lock down you database.

Know which packages should be secured and how to secure them.

4367.book Page 349 Wednesday, October 13, 2004 1:18 PM

350 Chapter 6 User Administration and Security

Know how to implement password security. An Oracle 10g database affords you several

standard password security settings. Know what is available in a profile and what needs to be

implemented in a password-verify function.

Know how to enable, disable and identify enabled auditing options. Be able to describe the

types of auditing, how to enable them, and how to report on the audit trail.

4367.book Page 350 Wednesday, October 13, 2004 1:18 PM

Review Questions 351

Review Questions

1. Which of the following statements creates an Oracle account, but lets the operating system

authenticate logons?

A. create user ops$admin identified by os;

B. create user ops$admin identified externally;

C. create user ops$admin nopassword;

D. create user ops$admin authenticated by os;

2. Which of the following types of statements can use a temporary tablespace?

A. An index creation

B. SQL statements with a GROUP BY clause

C. A hash join operation

D. All of the above

3. Which of the following statements gives user desmond the ability to alter table gl.accounts?

A. grant alter on gl.accounts to desmond;

B. grant alter to desmond on gl.accounts;

C. grant alter table to desmond;

D. allow desmond to alter table gl.accounts;

4. Which of the following statements gives user desmond the ability to alter table gl.accounts as

well as give this ability to other accounts?

A. grant alter any table with grant option to desmond;

B. grant alter on gl.accounts to desmond with admin option;

C. grant alter any table to desmond with grant option;

D. grant alter any table to desmond with admin option;

5. The following SQL statement will allow user regina to perform which operations on sequence

oe.orders_seq?

GRANT ALL ON oe.orders_seq TO regina;

A. Select the next value from oe.orders_seq.

B. Alter sequence oe.orders_seq to change the next value.

C. Change the number of sequence numbers that will be cached in memory.

D. Both A and C.

E. All of the above.

4367.book Page 351 Wednesday, October 13, 2004 1:18 PM