Create Secure Application Role

Create Secure Application Role

                  One of the applications of secure application role is to restrict privilege based on client IP

11.     Create database security manager user sec_mgr

SQL> create user sec_mgr identified by sec_mgr;

22.     Create a procedure which will conditionally set role at session level

SQL> CREATE OR REPLACE PROCEDURE sec_mgr.priv_mgr
                               AUTHID CURRENT_USER
                               AS
                               BEGIN
                                              IF (SYS_CONTEXT ('userenv', 'ip_address') = '10.10.10.11')

                                                              THEN
                                                             DBMS_SESSION.set_role ('sec_app_role');
                                               END IF;
                                END;
                          /

                –- replace 10.10.10.11 with your client machine IP

33.     The following creates a role appropriately named sec_app_role that will be enabled by the PRIV_MGR program in the SEC_MGR schema:

SQL> CREATE ROLE sec_app_role IDENTIFIED USING sec_mgr.priv_mgr;

44.     Create table t in schema oe to verify access privilege

SQL> create table oe.t (id char);

55.     Grant object level privileges on table ‘t’ to secure application role ‘sec_app_role’ created earlier

SQL> grant all on oe.t to sec_app_role;

66.     Connect via scott user and try to access table ‘t’ of ‘oe’ schema

Select * from oe.t;

It raised error

ERROR at line 1:
ORA-00942: table or view does not exist    

77.     Now select role at session level

SQL> SELECT ROLE FROM session_roles;

ROLE  
------------------------------  
CONNECT

RESOURCE

88.     Grant execute privilege on procedure ‘priv_mgr’ to user ‘scott’

SQL> GRANT EXECUTE ON priv_mgr TO scott;

99.     Execute procedure ‘priv_mgr’ while connected at user ‘scott’

SQL> EXEC sec_mgr.priv_mgr ;

110.  Now select role at session level

SQL> SELECT ROLE FROM session_roles;

ROLE  
------------------------------  
SEC_APP_ROLE

Role is changed at connected session of scott user, go back to step 7 to verify.

111.  Keep connected via scott user and try to access table ‘t’ of ‘oe’ schema

Select * from oe.t;

Here its important to mention that steps 10 & 11 came up with above mentioned results, when I did all above activities via client with IP 10.10.10.11, if I try to do it all other then this IP it will neither assign ‘sec_app_role’ nor can fetch rows from oe.t while connected as scott user.