Database Reference
In-Depth Information
AUDIT EXECUTE ON appsec.p_check_hrview_access
BY ACCESS
WHENEVER NOT SUCCESSFUL;
Working as the HR Schema User
For several aspects of the endeavors put forth in this topic, we are going to use the
HR
sample schema
that can be installed with Oracle 11g. If you haven't got that schema installed, you will have had
difficulties in the preceding step that configured auditing on
HR
schema structures. Please browse to the
Oracle web site for instructions on how to install the sample schemas after the fact.
Now, at the installation of Oracle, you also had the opportunity to configure a password for the
HR
user. If the sample schemas exist but the
HR
user is not configured (or simply to reconfigure the user),
you can issue the
ALTER USER
command (as
SYS
or
secadm
):
ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY password;
Caution
Be sure to give a very complex password to this user; HR owns the data that we are going to attempt
to protect.
Connect to Oracle as the
HR
user:
CONNECT hr;
HR
has no system privileges outside his own schema, but has been granted the privileges required to
connect to the database and create a number of structures in his own schema: tables, views, indexes,
and the like.
Note
You can find a script of the following commands in the file named
Chapter2/HR.sql
.
Sensitive Data in the HR Sample Schema
In the
HR
sample schema, which can be installed with Oracle 11g, there is a table named
EMPLOYEES
. That
table has a couple columns that we should call “sensitive”:
SALARY
and
COMMISSION_PCT
. We are going to
make it our goal to grant access to that table without compromising the sensitive data.
View the contents of the
EMPLOYEES
table with this command, as
HR
:
SELECT * FROM hr.employees;
Let's pretend that it is our company's goal to publish an online personnel directory (phone book),
and that this table is the primary source data. We could export just the non-sensitive portions of the data
for use in the phone book, but it would be much better to grant secure access directly to the primary
data in this table, rather than replicating the data (assuming our database server will support the
additional load).
