Oracle Roles
Check
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
CREATE ROLE
To Create a Role...
CREATE ROLE &role;
GRANT ROLE
GRANT ROLE
To GRANT a Role to a User...
GRANT &role TO &user;
DROP ROLE
DROP ROLE
To drop a Role...
DROP ROLE &role;
Generate Role DDL
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
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 requirementOS_ROLES
Recommended setting is FALSE.
If TRUE it allows the OS to use external groups within the database instance.
Check
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
Change
ALTER SYSTEM SET OS_ROLES = FALSE SCOPE = SPFILE;
Change takes effect on instance restartREMOTE_OS_ROLES
This is a CIS Benchmark requirementREMOTE_OS_ROLES
Recommended setting is FALSE.
If TRUE it allows the OS to use external groups from the remote client within the database instance.
Check
Check
SELECT UPPER(VALUE)
FROM V$SYSTEM_PARAMETER
WHERE UPPER(NAME)='REMOTE_OS_ROLES';
Change
Change
ALTER SYSTEM SET REMOTE_OS_ROLES = FALSE SCOPE = SPFILE;
Bibliography
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
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