Oracle Roles

Check

To list all Roles in the CDB and all PDBs (Multi-Tenant)...

COLUMN role FORMAT a30

SELECT role, common, con_id

FROM cdb_roles

ORDER BY role;

To list all Roles in the current container...

COLUMN role FORMAT a30

SELECT role, common

FROM dba_roles

ORDER BY role;

To list Users who have been Granted a Role...

SELECT grantee

FROM dba_role_privs

WHERE granted_role=UPPER('&role');

CREATE ROLE

To Create a Role...

CREATE ROLE &role;

GRANT ROLE

To GRANT a Role to a User...

GRANT &role TO &user;

DROP ROLE

To drop a Role...

DROP ROLE &role;

Generate Role DDL

@/u01/dba/usr/dbroleinfo1

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