Database Reference
In-Depth Information
Verify HR View Role by a Procedure
Just as we did previously for the
secadm_role
, we will create a procedure to protect access to the
hrview_role
. At the end of the procedure, if the
CURRENT_USER
meets the requirements encoded in this
procedure, we will
SET ROLE
to the
hrview_role
.
Create the procedure by executing the code in Listing 2-2 as
secadm
. Here, we are creating a
procedure in another schema. Notice the schema name, appsec. prepended on the procedure name. To
do this requires
secadm
to have the
CREATE ANY PROCEDURE
system privilege.
Listing 2-2.
p_check_hrview_access
Procedure for Secure App Role
CREATE OR REPLACE PROCEDURE appsec.p_check_hrview_access
AUTHID CURRENT_USER
AS
BEGIN
IF( ( SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) LIKE '192.168.%' OR
SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) = '127.0.0.1' )
AND
TO_CHAR( SYSDATE, 'HH24' ) BETWEEN 7 AND 18
)
THEN
EXECUTE IMMEDIATE 'SET ROLE hrview_role';
END IF;
END;
/
Notice that, here again, we have the statement
AUTHID CURRENT_USER
. We are going to execute with
invoker's rights (IR) instead of the default definer's rights (DR). In this procedure we have encoded two
tests in the
IF
statement. And for those sessions that pass both tests, we set the role to
hrview_role
.
Test for Subnet
The first test in
p_check_hrview_access
, shown in Listing 2-2, gets the IP Address of the client from the
user environment and tests that it begins with the string “192.168.” The
LIKE
syntax indicates that the
address should consist of the specified characters followed by zero or more characters (the “%” symbol
is a wildcard). The test assures that the client exists on our internal corporate subnet. (“192.168” is a
non-routed subnet, and can be used behind firewalls or on test subnets. It is typically the subnet used
for a home network that attaches to the Internet through a DSL or cable modem.) Your corporate subnet
is most likely different from this subnet, so modify the code for the procedure before you create it. At the
command prompt, you can find the IP address and other networking information of your Windows
workstation by issuing a command:
C:\>ipconfig /all
Probably the first two octets (sets of three or fewer digits separated by dots, or sets of eight bits in
binary) of your workstation's IP address are representative of your corporate subnet. Contact your
networking support personnel to determine exactly what to use.
This procedure also allows you to connect to Oracle from the Oracle database server itself. If the IP
address is 127.0.0.1, the localhost address (that is, the Oracle database server itself), then the procedure
also succeeds.
