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

Notice that we don't need to grant secadm_role to secadm ; rather, if the user meets all the
requirements of the procedure ( p_check_secadm_access ), then that IDENTIFIED USING procedure will set
his role to secadm_role . There is an analogous restriction here: either you grant a role or you grant the
ability to execute a procedure, which will set the role. In both cases you are limiting access to the role by
requiring a specific grant. However, by using the procedure to set the role, you can place further
restrictions on access.
Notice that in this case, we are granting a privilege (to execute the procedure) to a user directly,
instead of, as we recommended previously, granting permissions to a role and granting the role to a user.
Every rule has exceptions! The reason for this specific exception is that we are protecting a role with a
procedure. We do not need to further protect the procedure with a role.
The Security Administrator, secadm user can acquire secadm_role by executing the procedure as
shown below. In fact, every time secadm connects to Oracle Database, he will need to call that
procedure to get the secadm_role role.
EXEC sys.p_check_secadm_access;
System Privileges Granted to the Security Administrator Role
The bulk of privileges that we want our Security Administrator to have will be granted by SYS :
GRANT
CREATE USER
,ALTER USER
,CREATE ROLE
,GRANT ANY OBJECT PRIVILEGE
,GRANT ANY PRIVILEGE
,GRANT ANY ROLE
,CREATE ANY PROCEDURE
,CREATE ANY TRIGGER
,CREATE ANY CONTEXT
,CREATE PROFILE
,AUDIT SYSTEM
,AUDIT ANY
TO secadm_role;
Most of these system privileges that we are granting to secadm_role permit us to do some of what we
have been doing already as SYS . We will allow our security administrator to create and modify users and
to create roles and grant them to users. secadm will grant privileges for roles to work with Oracle objects
(structures) in other schemas. He will grant certain system privileges to roles. He will also create
procedures and triggers, which are like procedures, but execute when certain events occur (we'll discuss
this more in a bit.) We will also create an application context when we get to Chapter 10. And the security
administrator will create profiles, which we will see when we get to Chapter 8. For now, we rely on the
default profile.
We will also be setting up some auditing as security administrator. We will audit a variety of system
events, and we are going to audit access to tables and other structures in the HR schema—hence the
AUDIT ANY privilege. Any time you see ANY in an object privilege grant, you can read that as “in any
schema.” Generally, a user already has those privileges in his own schema.
These are not all the system and schema object privileges that our security administrator will need
to do his job, but they will get us started. We will come back as SYS and give the security administrator a
bit more privileges later.