Secure Application Role

Step 1: Create separate security user

SQL> create user sec identified by sec;

User created.

SQL> grant dba to sec;

Step 2: Connect with security user and developed these objects

1. package and package body
2. create role
3. add some objects privileges to above role
4. grant execute privileges on package to a user.


sql> conn sec/sec

create or replace package sec_role_pkg authid current_user is
  procedure sec_role;
end ;

 create or replace package body sec_role_pkg is
 procedure sec_role is
 begin
  if sys_context('userenv','session_user')='HR' then
   dbms_session.set_role('MYROLE');
   end if;
  end ;
 end ;


SQL> create role Myrole identified using sec_role_pkg;

Role created.

SQL> grant select on scott.emp to myrole;

Grant succeeded.

SQL> grant execute on sec.sec_role_pkg to hr;

Grant succeeded.

Step 3: Now Connect form HR user to test role

SQL> conn hr/hr
Connected.

SQL> select * from session_roles;

ROLE
------------------------------
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE

ROLE
------------------------------
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA

16 rows selected.

Execute the procedure and check what role now HR have

SQL> exec sec.sec_role_pkg.sec_role;

PL/SQL procedure successfully completed.

SQL>  select * from session_roles;

ROLE
------------------------------
MYROLE

SQL>

SOME TEST OF HR USER
SQL> create table test1 (id number);
create table test1 (id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        17


SQL> select count(*) from employees;

  COUNT(*)
----------
       107


SQL> select count(*) from scott.dept;
select count(*) from scott.dept
                           *
ERROR at line 1:
ORA-00942: table or view does not exist