Secure application roles can be used to grant roles selectively based on the specific needs of the application users. The main advantage is that secure application roles do not require hardcoded passwords in the application code, and can be enabled in the background using a stored procedure. In this way, you can develop some strict rules to allow users to receive certain privileges only while the application is in use. Also in this recipe we will create two users vw_america and vw_europe that will also be used in the further recipes.
The application role will be enabled by using the default context sys_context. A detailed coverage on contexts can be found in Chapter 5, Beyond Privileges: Oracle Virtual Private Database.
- Connect as the user
system. Create two usersvw_americaandvw_europeand grant create session privilege to each of them as follows:SQL> create user vw_america identified by vw_america; User created. SQL> create user vw_europe identified by vw_europe; User created. SQL> grant connect to vw_america,vw_europe; Grant succeeded. SQL> Grant succeeded.
- Connect as the user
systemand create the secure roles_app_role, as follows:SQL> create role s_app_role identified using s_app_role_proc; Role created.
- Connect as the user
HRand usegrant selectonemp_details_viewtos_app_role, as follows:SQL> conn HR Password: Connected. SQL> grant select on emp_details_view to s_app_role; Grant succeeded. SQL>
- Connect as the user
systemand creates_app_role_proc, which will enables_app_rolebased on the values returned bysys_context, as follows:SQL> conn system Enter password: Connected. 1 create or replace procedure s_app_role_proc 2 authid current_user as 3 begin 4 if (sys_context('userenv','session_user')='vm_america' or sys_context('userenv','session_user')='vm_europe') 5 then 6 dbms_session.set_role('s_app_role'), 7 end if; 8* end; SQL> / Procedure created.
- Apply
grant executeon the procedures_app_role_proctovw_americaandvw_europe, as follows:SQL> grant execute on s_app_role_proc to vw_america,vw_europe; Grant succeeded.
- Grant the secure application role
s_app_roleto the usersvw_americaandvw_europe, as follows:SQL> grant s_app_role to vw_america,vw_europe; Grant succeeded. SQL>
- Connect as the user
vw_americaand execute thes_app_role_procprocedure to enable the secure application role, as follows:SQL> conn vw_america Enter password: Connected. SQL> exec security_adm.s_app_role_proc; PL/SQL procedure successfully completed.
- In this moment,
vw_americahass_app_roleenabled and should be able to select fromhr.emp_details_view:SQL> select count(*) from hr.emp_details_view 2 ; COUNT(*) ---------- 106 SQL>
- Get the current enabled roles within the current session as follows:
SQL> select role from session_roles; ROLE ------------------------------ S_APP_ROLE
Secure application roles are enabled only when the context returns the appropriate value. It is a very good method to use for privilege segregation.
Secure application roles can be used to implement security together with technologies, such as Oracle Vault. See Chapter 7, Beyond Privileges: Oracle Database Vault.
- The Using session-based application contexts recipe in Chapter 5, Beyond Privileges: Oracle Virtual Private Database