-- #############################################################################################
--
-- %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;
/
