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

giving out those raises. It would also be useful to have this view to allow Human Resources to INSERT and
DELETE folks from the database as they are hired and terminated.
However, given the sensitivity of the data (how much money you and I or our managers earn), we
are not going to grant access to this view at all. Later, we are going to provide access to this data through
much more heavily guarded and encrypted channels.
Our first audit statement on HR data was given previously in the section “Audit Failed Attempts to
Access HR Data.” Our second audit statement for HR data follows. With it, we intend to audit any direct
access to the sensitive view of the EMPLOYEES table. Later, we will look at auditing the selection of just
those fields that we consider to be sensitive, no matter what view or table they are selected from.
AUDIT SELECT ON hr.v_employees_sensitive BY ACCESS;
Test Application User Access
To test our security, we need to try things that we believe should not work (because of our security
measures) as well as the things that we permitted to succeed. To do this, you will need to connect to
Oracle as appusr user:
CONNECT appusr;
 Note You can find a script of the following commands in the file named Chapter2/AppUsr.sql .
The next three commands, we believe, will fail because the appusr user has not been directly granted
access to anything in the HR schema. He only has his default roles, which also do not have access. We
should see an error message that those tables or views don't exist.
SELECT * FROM hr.employees;
SELECT * FROM hr.v_employees_sensitive;
SELECT * FROM hr.v_employees_public;
Now we will execute the procedure that will check our validity, and then set role to hrview_role .
This should succeed if we are on our corporate subnet (or on the Oracle Database server, itself) and it is
between 7AM and 7PM:
EXEC appsec.p_check_hrview_access;
Then we will test our access to the EMPLOYEES structures in the HR schema. We do not expect the first
two commands to work; again we should see an error message that those structures don't exist. The
hrview_role does not give access to the sensitive data:
SELECT * FROM hr.employees;
SELECT * FROM hr.v_employees_sensitive;
On this last command, we should see the data from the EMPLOYEES table; however, we note that there
are two columns missing from the data: SALARY and COMMISSION_PCT .
SELECT * FROM hr.v_employees_public;