Generate script for Database securities : Role and System Privilege on Oracle Databases 10g onward
Database security
Each database product like Oracle has a particular security model and the DBA should be well aware in order to control access to data. This is part of authorization, Authorization is granting permission on various parts of the database.
System privileges
A system privilege is a right to perform a particular action or to perform an action on any object of a particular type. Objects include tables, views, materialized views, synonyms, indexes, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages. Only the instance administrator or a user with the admin privilege can grant or revoke system privileges.
| ***note***
You have to use all of the options as below, that I wrote because they cover direct and indirect grants. |
1.
— GRANT ROLE TO USER
— ADMIN OPTION = NO
SELECT 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||';'
FROM DBA_ROLE_PRIVS
WHERE ADMIN_OPTION='NO'
AND GRANTEE NOT IN ('DBSNMP',
'SYSMAN',
'MGMT_VIEW',
'SYS',
'SYSTEM',
'FLOWS_FILES',
'MDSYS',
'ORDSYS',
'EXFSYS',
'SCOTT',
'WMSYS',
'ORACLE_OCM',
'APPQOSSYS',
'XS$NULL',
'APEX_030200',
'OWBSYS_AUDIT',
'MDDATA',
'ORDDATA',
'CTXSYS',
'ANONYMOUS',
'OUTLN',
'DIP',
'APEX_PUBLIC_USER',
'XDB',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'ORDPLUGINS',
'OWBSYS',
'SI_INFORMTN_SCHEMA',
'OLAPSYS',
'PUBLIC','TSMSYS','DBA')
AND GRANTEE NOT LIKE 'MGMT%'
ORDER BY GRANTEE;
2.
— GRANT ROLE TO USER
— ADMIN OPTION = YES
SELECT 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||' WITH ADMIN OPTION;'
FROM DBA_ROLE_PRIVS
WHERE ADMIN_OPTION='YES'
AND GRANTEE NOT IN ('DBSNMP',
'SYSMAN',
'MGMT_VIEW',
'SYS',
'SYSTEM',
'FLOWS_FILES',
'MDSYS',
'ORDSYS',
'EXFSYS',
'SCOTT',
'WMSYS',
'ORACLE_OCM',
'APPQOSSYS',
'XS$NULL',
'APEX_030200',
'OWBSYS_AUDIT',
'MDDATA',
'ORDDATA',
'CTXSYS',
'ANONYMOUS',
'OUTLN',
'DIP',
'APEX_PUBLIC_USER',
'XDB',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'ORDPLUGINS',
'OWBSYS',
'SI_INFORMTN_SCHEMA',
'OLAPSYS',
'PUBLIC','TSMSYS','DBA')
AND GRANTEE NOT LIKE 'MGMT%'ORDER BY GRANTEE;
3.
— GRANT SYS PRIVS TO USER
— ADMIN OPTION = NO
SELECT 'GRANT '||PRIVILEGE||' TO '||GRANTEE||';'
FROM DBA_SYS_PRIVS
WHERE ADMIN_OPTION='NO'
AND GRANTEE NOT IN ('DBSNMP',
'SYSMAN',
'MGMT_VIEW',
'SYS',
'SYSTEM',
'FLOWS_FILES',
'MDSYS',
'ORDSYS',
'EXFSYS',
'SCOTT',
'WMSYS',
'ORACLE_OCM',
'APPQOSSYS',
'XS$NULL',
'APEX_030200',
'OWBSYS_AUDIT',
'MDDATA',
'ORDDATA',
'CTXSYS',
'ANONYMOUS',
'OUTLN',
'DIP',
'APEX_PUBLIC_USER',
'XDB',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'ORDPLUGINS',
'OWBSYS',
'SI_INFORMTN_SCHEMA',
'OLAPSYS',
'PUBLIC','TSMSYS','DBA')
AND GRANTEE NOT LIKE 'MGMT%'
ORDER BY GRANTEE;
4.
— GRANT SYS PRIVS TO USER
— ADMIN OPTION = YES
SELECT 'GRANT '||PRIVILEGE||' TO '||GRANTEE||' WITH ADMIN OPTION;'
FROM DBA_SYS_PRIVS
WHERE ADMIN_OPTION='YES'
AND GRANTEE NOT IN ('DBSNMP',
'SYSMAN',
'MGMT_VIEW',
'SYS',
'SYSTEM',
'FLOWS_FILES',
'MDSYS',
'ORDSYS',
'EXFSYS',
'SCOTT',
'WMSYS',
'ORACLE_OCM',
'APPQOSSYS',
'XS$NULL',
'APEX_030200',
'OWBSYS_AUDIT',
'MDDATA',
'ORDDATA',
'CTXSYS',
'ANONYMOUS',
'OUTLN',
'DIP',
'APEX_PUBLIC_USER',
'XDB',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'ORDPLUGINS',
'OWBSYS',
'SI_INFORMTN_SCHEMA',
'OLAPSYS',
'PUBLIC','TSMSYS','DBA')
AND GRANTEE NOT LIKE 'MGMT%'
ORDER BY GRANTEE;
5.
—ALTER USER FOR QUOTA TABLESPACE
—IF MAXBYTES = -1 OR -XX = UNLIMITED
SELECT 'ALTER USER '||USERNAME ||' QUOTA UNLIMITED ON '|| TABLESPACE_NAME||';' FROM DBA_TS_QUOTAS WHERE MAX_BYTES >-1 AND USERNAME NOT IN ( 'DBSNMP', 'SYSMAN', 'MGMT_VIEW', 'SYS', 'SYSTEM', 'FLOWS_FILES', 'MDSYS', 'ORDSYS', 'EXFSYS', 'SCOTT', 'WMSYS', 'ORACLE_OCM', 'APPQOSSYS', 'XS$NULL', 'APEX_030200', 'OWBSYS_AUDIT', 'MDDATA', 'ORDDATA', 'CTXSYS', 'ANONYMOUS', 'OUTLN', 'DIP', 'APEX_PUBLIC_USER', 'XDB', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'ORDPLUGINS', 'OWBSYS', 'SI_INFORMTN_SCHEMA', 'OLAPSYS', 'PUBLIC','TSMSYS') ORDER BY TABLESPACE_NAME,USERNAME;
6.
—ALTER USER FOR QUOTA TABLESPACE
—IF MAXBYTES > -1 OR -XX = NOT UNLIMITED
SELECT 'ALTER USER '||USERNAME||' QUOTA '||ROUND(MAX_BYTES/1024/1024)||'M ON '||TABLESPACE_NAME||';' FROM DBA_TS_QUOTAS WHERE MAX_BYTES >-1 AND USERNAME NOT IN ( 'DBSNMP', 'SYSMAN', 'MGMT_VIEW', 'SYS', 'SYSTEM', 'FLOWS_FILES', 'MDSYS', 'ORDSYS', 'EXFSYS', 'SCOTT', 'WMSYS', 'ORACLE_OCM', 'APPQOSSYS', 'XS$NULL', 'APEX_030200', 'OWBSYS_AUDIT', 'MDDATA', 'ORDDATA', 'CTXSYS', 'ANONYMOUS', 'OUTLN', 'DIP', 'APEX_PUBLIC_USER', 'XDB', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'ORDPLUGINS', 'OWBSYS', 'SI_INFORMTN_SCHEMA', 'OLAPSYS', 'PUBLIC','TSMSYS') ORDER BY TABLESPACE_NAME,USERNAME;