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

Security Administrator User
Let's go ahead and start defining our security administrator. The security administrator is going to be a
separate non-person user; that is, an account and password that can be delegated to various people who
rotate into and out of the job responsibility. The security administrator will be doing tasks that would
typically be done by a DBA or even by SYS , but we are going to limit the privileges we give to the security
administrator to just those aspects related to application security.
First, as SYS we will create the user secadm and grant him the create_session_role , in one step.
Substitute a real password for “password” in this command:
GRANT create_session_role TO secadm IDENTIFIED BY password;
 Caution Be sure to give a very complex password to this user; it will be powerful enough to be dangerous in
the wrong hands. This warning goes for the SYS and SYSTEM accounts as well.
Security Administrator Role
Next, we will create the security administrator role, secadm_role . It will be the role that is granted all the
privileges needed to do security administration. We can then grant this role to any user, but we will limit
it to just one user, secadm .
At the outset, I want to set some requirements regarding how and when this role can be used, so I
will not use the NOT IDENTIFIED keywords that we saw with create_session_role . Rather, I will have it be
identified (verified) by a procedure. You can see the name of the verification procedure,
sys.p_check_secadm_access in this command:
CREATE ROLE secadm_role IDENTIFIED USING sys.p_check_secadm_access;
A role that is identified by a procedure is called a secure application role.
Security Administration Role Verification
As I mentioned, all the privileges that the security administrator needs will be granted to secadm_role , so
we are going out of our way to protect it. The keywords IDENTIFIED USING sys.p_check_secadm_access
indicate that when a user attempts to acquire secadm_role , he will have to get it from the procedure
named p_check_secadm_access , which exists in the SYS schema.
A stored procedure (procedure) is a named block of Procedural Language/Structured Query
Language (PL/SQL) code that is stored and run (executed) on the Oracle database. Generally, a
procedure takes parameters and does work. It can also return information through its parameters. In
Oracle there are also stored functions (functions), which are very similar to procedures except that they
usually take values; do research or calculations; and then return a single value as the result. We will be
using both procedures and functions.
The specific procedure used for verifying secadm_role , p_check_secadm_access does not take any
parameters (arguments or values passed to the procedure for evaluation), and does not return any
results. Most procedures do take parameters, but they don't have to.
In the Listing 2-1, we are creating the procedure to be used for acquiring the security administrator
role. Note that the simple goal I have for this procedure is to require that the security administrator be