Embedded Files
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/dbroleinfo
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;
Grant on Future Objects
Grant on Future Objects
NOTE: The safest solution is always to make the Grant part of the Table deployment.
CREATE ROLE myrole;
GRANT myrole TO myuser2;
CREATEs a ROLE called MYROLE and GRANTs it to MYUSER2
DECLARE
l_owner CONSTANT VARCHAR2(128) := 'MYUSER1';
l_role CONSTANT VARCHAR2(128) := 'MYROLE';
BEGIN
FOR r IN (SELECT table_name
FROM all_tables
WHERE owner = l_owner)
LOOP
EXECUTE IMMEDIATE
'GRANT SELECT ON ' ||
DBMS_ASSERT.ENQUOTE_NAME(l_owner, FALSE) || '.' ||
DBMS_ASSERT.ENQUOTE_NAME(r.table_name, FALSE) || ' TO ' ||
DBMS_ASSERT.ENQUOTE_NAME(l_role, FALSE);
END LOOP;
END;
/
GRANTs SELECT on all existing Tables owner by MYUSER1 to MYROLEMYUSER2 can now SELECT all tables owned by MYUSER1
Ideally we would then wrap this in a Trigger after create of a new table. However, we would expect execution of a GRANT via EXECUTE IMMEDIATE inside a schema DDL trigger to give an error on execution:ORA-30511: invalid DDL operation is system triggers
If you expect Tables to be created that may be missing the Grant from the Table deployment. then you could just rerun the script above periodically. But a more elegant solution may be to implement a pending Grants queue...
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
Page updated
Google Sites
Report abuse