Database Reference
In-Depth Information
Note
You can find this script in the file named
Chapter8/SecAdm.sql
.
Proxy Sessions
The idea behind proxy sessions is that we can connect as our application user but do work as an
identified person user. This allows us to securely connect as our application user (no individual person
passwords are used, nor indeed are they needed), and to audit the activities of individual persons. These
goals do not differ radically from what we accomplish with setting our client identifier to the OS user
identity, as in the previous section. The primary differences (in our efforts) are:
•
With proxy sessions, we are not doing work as our application user, but as our
individual user.
•
With proxy sessions, an Oracle user must exist for each individual person who is
going to connect.
Create Individual Person Users in Oracle
Why would we want to go to the effort of setting up individual person users in Oracle database when our
application account is all we need to do the work? That is a good question, and the answer may give you
reasons to choose to take either of the two routes we are describing in this section:
1.
Using standard connections and placing the OS user identity in the Oracle
client identifier.
2.
Using proxy sessions.
Individual (human) users require some administrative activity; however, it can be minimal. Let's
create one example user named
OSUSER
(feel free to substitute your user ID for
OSUSER
in these
commands):
CREATE USER osuser
IDENTIFIED EXTERNALLY
;
GRANT create_session_role TO osuser;
ALTER USER
osuser GRANT CONNECT THROUGH appusr
;
For each individual user, you only need to execute these commands. Each user must have the
Create Session system privilege because proxying requires creation of an additional session. You will
have one connection (as the application user) and two sessions—one as the application user and one as
the proxying user.
You could script user creation from a list of all the users in your organization and quickly create an
Oracle user for each person. The ease with which these users can be created is not an argument in their
favor, as there is still an effort required. There is also an administrative effort required to remove or
disable users when employees are no longer around.
Another argument against individual users in Oracle database is that some access and allocation is
provided for each user. Each user will have an associated schema. This schema will not consist of much,
but it will exist. And when you scroll through the list of schemas (in an IDE) to get to the one you want,
you may be scrolling through dozens, hundreds, or thousands belonging to individual persons.
