Oracle Roles

REM ...............................................................REMREM Name dbroleinfo1.sqlREMREM Purpose Information about roleREMREM Usage Provide the role name along with the scriptnameREMREM Parameters rolenameREMREM HISTORYREM Date Name CommentsREM ----------- -------- --------------------REM 02-SEP-2013 MPG CreatedREMREM ...............................................................
SET SERVEROUTPUT ON SIZE 1000000SET FEEDBACK OFFSET VERIFY OFFSET PAGESIZE 0
SPOOL /tmp/roleinfo.lst
DECLARE   wuser VARCHAR2 (30) := '&1';
   /* Users */   CURSOR cusr IS      SELECT role AS username      FROM dba_roles      WHERE role LIKE UPPER(wuser);
   /* Quotas */   CURSOR cquota (u IN VARCHAR2) IS      SELECT tablespace_name, bytes,             DECODE(max_bytes,-1,'unlimited',max_bytes) max_bytes      FROM dba_ts_quotas      WHERE username LIKE UPPER(u);
   /* Roles granted */   CURSOR crole (u IN VARCHAR2) IS      SELECT granted_role, admin_option, default_role      FROM dba_role_privs      WHERE grantee = UPPER(u)      ORDER BY granted_role;
   /* System privileges granted */   CURSOR csys (u IN VARCHAR2) IS      SELECT privilege, admin_option      FROM dba_sys_privs      WHERE grantee = UPPER(u)      ORDER BY privilege;
   /* Object privileges granted */   CURSOR cobj (u IN VARCHAR2) IS      SELECT (owner ||'.'|| table_name) object, privilege      FROM dba_tab_privs      WHERE grantee = UPPER(u)      ORDER BY owner, table_name;
   /* Column privileges granted */   CURSOR ccol (u IN VARCHAR2) IS      SELECT (owner ||'.'|| table_name ||'.'|| column_name) wcolumn, privilege      FROM dba_col_privs      WHERE grantee = UPPER(u)      ORDER BY owner, table_name, column_name;
   wcount NUMBER := 0;   wdate VARCHAR2(25) := TO_CHAR(SYSDATE,'Mon DD, YYYY HH:MI AM');   w5space CHAR(5) := '. ';   wdum1 VARCHAR2(255);   wdum2 VARCHAR2(255);   wdum3 VARCHAR2(255);   wdum4 VARCHAR2(255);   wdum5 VARCHAR2(255);   wdum6 VARCHAR2(255);   wdum7 VARCHAR2(255);
BEGIN   DBMS_OUTPUT.ENABLE(1000000);
   FOR rusr IN cusr   LOOP      DBMS_OUTPUT.PUT_LINE('********** USER INFORMATION ********** ' || wdate);      DBMS_OUTPUT.PUT_LINE('*--------------------------------------------------------------------------*');      wcount := wcount + 1;      DBMS_OUTPUT.PUT_LINE(w5space);
      OPEN cquota (rusr.username);      FETCH cquota INTO wdum1, wdum2, wdum3;
      IF cquota%NOTFOUND      THEN       DBMS_OUTPUT.PUT_LINE('********** ' || rusr.username || ' - NO QUOTA GRANTED *********');         CLOSE cquota;      ELSE         CLOSE cquota;
         FOR rquota IN cquota (rusr.username)         LOOP            DBMS_OUTPUT.PUT_LINE('ALTER USER '||rusr.username||' QUOTA '||rquota.max_bytes||' ON '||rquota.tablespace_name||';');         END LOOP;
      END IF;
      OPEN crole (rusr.username);      FETCH crole INTO wdum1, wdum2, wdum3;
      IF crole%NOTFOUND      THEN         DBMS_OUTPUT.PUT_LINE('********** ' || rusr.username || ' - NO ROLES GRANTED *********');         CLOSE crole;      ELSE         CLOSE crole;
         FOR rrole IN crole (rusr.username)         LOOP            DBMS_OUTPUT.PUT_LINE('GRANT '||rrole.granted_role||' TO '||rusr.username||';'||' (Admin:'||rrole.admin_option||' Default:'||rrole.default_role||')');         END LOOP;
      END IF;
      OPEN csys (rusr.username);      FETCH csys INTO wdum1, wdum2;
      IF csys%NOTFOUND      THEN         DBMS_OUTPUT.PUT_LINE('********** ' || rusr.username || ' - NO SYSTEM PRIVILEGES GRANTED *********');         CLOSE csys;      ELSE         CLOSE csys;
         FOR rsys IN csys (rusr.username)         LOOP            DBMS_OUTPUT.PUT_LINE('GRANT '||rsys.privilege||' TO '||rusr.username||';'||' (Admin:'||rsys.admin_option||')');         END LOOP;
      END IF;
      OPEN cobj (rusr.username);      FETCH cobj INTO wdum1, wdum2;
      IF cobj%NOTFOUND      THEN         DBMS_OUTPUT.PUT_LINE('********** ' || rusr.username || ' - NO OBJECT PRIVILEGES GRANTED *********');         CLOSE cobj;      ELSE         CLOSE cobj;
         FOR robj IN cobj (rusr.username)         LOOP            DBMS_OUTPUT.PUT_LINE('GRANT '||robj.privilege||' ON '||robj.object||' TO '||rusr.username||';');         END LOOP;
      END IF;
      OPEN ccol (rusr.username);      FETCH ccol INTO wdum1, wdum2;
      IF ccol%NOTFOUND      THEN         DBMS_OUTPUT.PUT_LINE('********** ' || rusr.username || ' - NO COLUMN PRIVILEGES GRANTED *********');         CLOSE ccol;      ELSE         CLOSE ccol;
         FOR rcol IN ccol (rusr.username)         LOOP            DBMS_OUTPUT.PUT_LINE(w5space||RPAD(rcol.wcolumn,50)||                                          RPAD(rcol.privilege,20));         END LOOP;
         DBMS_OUTPUT.PUT_LINE(w5space);      END IF;
      DBMS_OUTPUT.PUT_LINE('*--------------------------------------------------------------------------*');   END LOOP;
   IF wcount =0   THEN      DBMS_OUTPUT.PUT_LINE('******************************************************');      DBMS_OUTPUT.PUT_LINE('* *');      DBMS_OUTPUT.PUT_LINE('* Plese Verify Input Parameters... No Matches Found! *');      DBMS_OUTPUT.PUT_LINE('* *');      DBMS_OUTPUT.PUT_LINE('******************************************************');   END IF;END;/
SET SERVEROUTPUT OFFSET FEEDBACK ONSET VERIFY ONSET PAGESIZE 999SPOOL OFFPROMPTPROMPT Output saved at /tmp/roleinfo.lst

OS_ROLES

This is a CIS Benchmark requirement

Recommended setting is FALSE.
If TRUE it allows the OS to use external groups within the database instance.

Check

SELECT UPPER(VALUE)

FROM V$SYSTEM_PARAMETER

WHERE UPPER(NAME)='OS_ROLES'

Mutli-Tenant version...

SELECT DISTINCT UPPER(V.VALUE),

DECODE (V.CON_ID,0,(SELECT NAME FROM V$DATABASE),

1,(SELECT NAME FROM V$DATABASE),

(SELECT NAME FROM V$PDBS B WHERE V.CON_ID = B.CON_ID))

FROM V$SYSTEM_PARAMETER V

WHERE UPPER(NAME) = 'OS_ROLES';

Change

ALTER SYSTEM SET OS_ROLES = FALSE SCOPE = SPFILE;

Change takes effect on instance restart

REMOTE_OS_ROLES

This is a CIS Benchmark requirement

Recommended setting is FALSE.
If TRUE it allows the OS to use external groups from the remote client within the database instance.

Check

SELECT UPPER(VALUE)

FROM V$SYSTEM_PARAMETER

WHERE UPPER(NAME)='REMOTE_OS_ROLES';

Change

ALTER SYSTEM SET REMOTE_OS_ROLES = FALSE SCOPE = SPFILE;

Bibliography

https://stackoverflow.com/questions/15066408/how-to-find-the-privileges-and-roles-granted-to-a-user-in-oracle/15066503
Built-in Roleshttps://hemantoracledba.blogspot.com/2014/02/the-difference-between-select-any.htmlhttp://arup.blogspot.com/2011/07/difference-between-select-any.htmlhttp://www.petefinnigan.com/weblog/archives/00001461.htmhttps://mikedietrichde.com/2015/03/02/new-behaviour-in-oracle-database-12c-and-11-2-0-4-select-any-dictionary-with-reduced-privilege-set/
REMOTE_OS_ROLEShttps://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/REMOTE_OS_ROLES.html