Oracle Database Security - Expert Oracle and Java Security - page 14

The Audit Trail
Finally, as SYS , we are going to set up some initial auditing on the auditing trail itself. This will deter a
rogue database administrator from doing something wrong and then erasing their tracks by deleting
their audit records:
AUDIT SELECT, UPDATE, DELETE
ON sys.aud$
BY ACCESS;
When we designate BY ACCESS for auditing, we are saying that we want detailed information. The
other (possibly default) option is BY SESSION . This gives less detail, but still audits each occurrence,
rather than only providing a single audit record per session, as in earlier releases of Oracle.
The Data Dictionary
We want our security administrator to be able to view all data in the Data Dictionary, which is a
collection of views in the SYS schema that list structures and system data in Oracle. (A view is a defined
way to look at a table of data.) For example, we may want to list details about all the database users:
SELECT * FROM sys.dba_users;
There are many columns in the DBA_USERS view that are not available in the PUBLIC data dictionary
views: ALL_USERS (fewer details) and USER_USERS (a bit more detail, but only for the current user).
Much of the data dictionary has been granted to PUBLIC by default, and can be selected by every
user. For the most part, this is needed. But we will deal with this a bit more stringently in Chapter 11,
Enhancing Our Security . However, selecting some parts of the Data Dictionary requires the
SELECT_CATALOG_ROLE . Grant that role to the secadm_role :
GRANT select_catalog_role TO secadm_role;
Note that this is a role granted to a role. From now on, when we set the role to secadm_role , we will
also have the SELECT_CATALOG_ROLE .
Working as the Security Administrator
Now that our security administrator has been defined with the privileges he needs to do his job, we are
going to put him to work. Go ahead and connect:
CONNECT secadm;
 Note You can find a script of the following commands in the file named Chapter2/SecAdm.sql .
As you will recall from our creation of the security administrator role, secadm_role , we required that
it be validated by a procedure. We have only permitted one account, secadm to execute the procedure.
Execute it now to acquire secadm_role :
EXEC sys.p_check_secadm_access;