-- ############################################################################################# -- -- %Purpose: Which ROLES are currently enabled for my Session ? -- -- When a user logs on, Oracle enables all privileges granted explicitly -- to the user and all privileges in the user's default roles. During the -- session, the user or an application can use the SET ROLE statement -- any number of times to change the roles currently enabled for the session. -- The number of roles that can be concurrently enabled is limited by the -- initialization parameter MAX_ENABLED_ROLES. You can see which roles are -- currently enabled by examining the SESSION_ROLES data dictionary view. -- -- ############################################################################################# -- SELECT role FROM session_roles; -- -- You can check the DB access in your application context using the following code construct. -- DECLARE HasAccess BOOLEAN := FALSE; CURSOR cur_get_role IS SELECT role FROM session_roles; BEGIN FOR role_rec IN cur_get_role LOOP IF (UPPER(role_rec.role) IN ('ADMIN','CLERK')) THEN HasAccess := TRUE; END IF; END LOOP; IF (NOT HasAccess) THEN RAISE_APPLICATION_ERROR (-20020,'Sorry, you have no access to the database'); END IF; END; /