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

Note that when secadm connects to Oracle, he does not automatically acquire secadm_role . Because
it is a secure application (verified) role, and not directly granted to secadm , it cannot be a default role.
Every time the secadm user connects, he will have to execute the procedure to gain his Security
Administrator role and privileges.
This is in contrast to roles that are granted directly to a user, which are default roles initially. That
status of default role can be unset.
Acquire secadm_role from a SQL*Plus Local Connection
There are always gotcha's, and here's one that will probably get you a few times if you use SQL*Plus as
your primary client: when you are sitting at a command prompt on the Oracle database, you can
connect locally to the default instance. Do that by executing SQL*Plus without any arguments, like this:
sqlplus
You can connect then as secadm user by entering the username and the password. If you then
attempt to execute the procedure, sys.p_check_secadm_access that sets secadm_role , it will not succeed.
Why does it not succeed? Our address should be that of localhost, which should be okay. Well, when
connecting locally, SQL*Plus doesn't use the network at all—it just talks directly to the database. You can
see the lack of IP address information by executing this command:
SELECT SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) FROM DUAL;
This has some implications for security. When connected locally like this, the commands you enter
do not traverse the network adapter and have no possibility of leaking out on the network to snooping
devices.
So how, you might ask, are we supposed to connect as secadm and run sys.p_check_secadm_access
from SQL*Plus on the Oracle Database? There is a way, and it only requires that you act like you're not
connecting locally by adding the arguments for user and instance name ( orcl in this example) on the
command line. Actually orcl in this context is a TNS alias with the same name as the instance. We will
discuss TNS aliases in Chapter 11.
sqlplus secadm@orcl
At that point, you have an IP address in the session context, and you can successfully set role
through the procedure:
SELECT SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) FROM DUAL;
EXEC sys.p_check_secadm_access;
Toggle Between Roles
You can see the effects of running SET ROLE by observing your current session roles as you toggle back
and forth between create_session_role and secadm_role . As secadm user, do this and observe the list of
roles when you do each SELECT query:
SELECT * FROM sys.session_roles;
SET ROLE create_session_role;
SELECT * FROM sys.session_roles;
EXECUTE sys.p_check_secadm_access;
SELECT * FROM sys.session_roles;
You will see three roles when you execute the procedure and then select from SESSION_ROLES . The
procedure itself sets your role to secadm_role . To that role, we granted SELECT_CATALOG_ROLE . And the