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

running on the same computer as the Oracle database server (the IP address 127.0.0.1 is also known as
the localhost or the loopback address). This requirement may not be appropriate for your system; if not,
you can still create the procedure, but comment out the three lines that start with IF , THEN , and END IF by
placing two dashes (minus signs) in front of it. You can execute this command as SYS , and the procedure
will be created.
Listing 2-1. p_check_secadm_access Procedure for Secure App Role
CREATE OR REPLACE PROCEDURE sys.p_check_secadm_access
AUTHID CURRENT_USER
AS
BEGIN
-- This is a comment
IF( SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) = '127.0.0.1' )
THEN
EXECUTE IMMEDIATE 'SET ROLE secadm_role';
END IF;
END;
/
We will want to come back and visit (replace) this procedure later, adding whatever additional
security constraints we deem appropriate. For now, our Security Administrator must be connected
directly to the Oracle Database (running SQL*Plus on the server.)
As a programmer, you will already understand the IF / THEN / END IF syntax and realize there are other
PL/SQL grammar requirements like the BEGIN / END and the semicolons.
Look at the code between the BEGIN flag and the last END flag. Between there it says, in English, “if the
user's environment has an Internet Protocol (IP) address of the local host, then immediately set his role
to secadm_role .” We are using the SYS_CONTEXT context to get the IP address from the user environment ,
and we are determining if the address is equal to 127.0.0.1 (localhost). If that test is true, then we
immediately set the role for the current session to secadm_role .
INVOKER'S RIGHTS VERSUS DEFINER'S RIGHTS
In the procedure we defined above, we say AUTHID CURRENT_USER so that this procedure is executed as
the current user, not as SYS (the one who defined the procedure.) So when executed, this procedure will
use what is called “Invoker's Rights” ( CURRENT_USER ) as opposed to “Definer's Rights” (the default or
owner, which in this case is SYS ). If we didn't do this, the environment and identity would appear to be SYS
when we execute the procedure, but we want to be able to determine the acceptability of (authorize) the
specific, current user. Another reason we need to execute with invoker's rights is that otherwise we are
not allowed to set a role from a procedure-we can only set the role for the authenticated (current) user.
Security Administrator Role Acquisition
We are not nearly done defining our Security Administrator yet. We need to permit the Security
Administrator to execute the procedure we created. We do that by issuing the following command:
GRANT EXECUTE ON sys.p_check_secadm_access TO secadm;