I'm Top Oracle DBA

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.

Object privileges

An object privilege is a right to perform a particular action on an object or to access another user’s object. Objects include tables, views, materialized views, indexes, synonyms, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages.

An object’s owner has all object privileges for that object, and those privileges cannot be revoked. The object’s owner can grant object privileges for that object to other database users. A user with the admin privilege can grant and revoke object privileges from users who do not own the objects on which the privileges are granted.

1.

—GRANT OBJECT PRIVILEGE
—GRANTABLE=YES

SELECT 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||' WITH GRANT OPTION;'
FROM DBA_TAB_PRIVS
WHERE GRANTABLE='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')
AND GRANTEE NOT LIKE 'MGMT%'
ORDER BY GRANTEE;

2.

—GRANT OBJECT PRIVILEGE
—GRANTABLE = NO

SELECT 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';'
FROM DBA_TAB_PRIVS
WHERE GRANTABLE='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')
AND GRANTEE NOT LIKE 'MGMT%'
ORDER BY GRANTEE;

3.

—CREATE SYNONYM
—WITHOUT DB_LINK

SELECT 'CREATE OR REPLACE SYNONYM '||OWNER||'.'||SYNONYM_NAME||' FOR '||TABLE_OWNER||'.'||TABLE_NAME||';'
FROM DBA_SYNONYMS
WHERE DB_LINK IS NULL
AND OWNER 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')
AND OWNER NOT LIKE 'MGMT%'
ORDER BY OWNER,SYNONYM_NAME;

4.

—CREATE SYNONYM
—WITH DB_LINK AND TABLE_OWNER IS NOT NULL

SELECT 'CREATE OR REPLACE SYNONYM '||OWNER||'.'||SYNONYM_NAME||' FOR '||TABLE_OWNER||'.'||TABLE_NAME||'@'||DB_LINK||';'
FROM DBA_SYNONYMS
WHERE DB_LINK IS NOT NULL
AND TABLE_OWNER IS NOT NULL
AND OWNER 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')
AND OWNER NOT LIKE 'MGMT%'
ORDER BY OWNER,SYNONYM_NAME;

5.

— CREATE SYNONYM
— WITH DB_LINK AND WITHOUT TABLE_OWNER

SELECT 'CREATE OR REPLACE SYNONYM '||OWNER||'.'||SYNONYM_NAME||' FOR '||TABLE_NAME||'@'||DB_LINK||';'
FROM DBA_SYNONYMS
WHERE TABLE_OWNER IS NULL
AND DB_LINK IS NOT NULL
AND OWNER 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')
AND OWNER NOT LIKE 'MGMT%'
ORDER BY OWNER,SYNONYM_NAME;