Databases Reference
In-Depth Information
ROLE OWNER TABLE_NAME PRIVILEGE
----------------- -------- -------------------- ---------
HR_EMP_DEPT HR EMPLOYEES SELECT
HR_EMP_DEPT HR DEPARTMENTS SELECT
2 rows selected.
The role HR_EMP_DEPT has SELECT privileges against two tables in the HR
schema: EMPLOYEES and DEPARTMENTS.
Auditing
Auditing
in the Oracle database stores information about database activities.
The activities to be audited are specified by the DBA. Once enabled, auditing
records the activity in the AUD$ table, owned by SYS.
Auditing can be fine-tuned in a number of ways. It can be restricted to par-
ticular objects or to specific users or based on whether the action is successful or
unsuccessful. In other words, you might not care if users who are granted rights
to a table access the table, but you might want to know when users without
rights to a table try to access that table.
The types of auditing can be divided into two broad categories: statement
auditing and object auditing. The general syntax for AUDIT is as follows:
auditing
Storing information about activities in
the database in the
SYS.AUD$
table.
Auditing is controlled by the DBA.
AUDIT {
statement_clause
|
object_clause
}
[BY SESSION | BY ACCESS]
[WHENEVER [NOT] SUCCESSFUL];
The
statement_clause
allows you to specify not only the SQL statement to
audit but also, optionally, the username that will be running the SQL statement.
The
object_clause
allows you to specify a particular object to audit.
The BY SESSION clause means that an audit record is written to SYS.AUD$ only
once in the session that triggered the audit, regardless of how many times the action
was performed. BY ACCESS will record all occurrences of the specified action.
The NOAUDIT command turns off auditing and has the same syntax as AUDIT,
except that BY SESSION or BY ACCESS is not specified when using NOAUDIT.
Statement Auditing
Statement auditing allows the DBA to trigger audit records in SYS.AUD$ when a
given SQL statement is executed, either for all users or a particular group of users.
Recently, Janice, the DBA, created a new user SCRAWFORD and granted the
CREATE TABLE privilege to SCRAWFORD. Janice is concerned that the new user is
