Oracle Configuration

3 min
Purpose 
In Oracle, the objective is to create a user who can: 
Connect to the database (CREATE SESSION), 
View metadata and dictionary information,
Perform SELECT operations on any table. 
Grant Permissions 
Oracle
-- Session creation privilege
GRANT CREATE SESSION TO [ username ];

-- Metadata view permissions
GRANT SELECT_CATALOG_ROLE TO [ username ];

 -- Select permission on all tables
GRANT SELECT ANY TABLE TO [ username ];

-- Select permission on all dictionary objects
GRANT SELECT ANY DICTIONARY TO [ username ];
-- Session creation privilege
GRANT CREATE SESSION TO [ username ];

-- Metadata view permissions
GRANT SELECT_CATALOG_ROLE TO [ username ];

 -- Select permission on all tables
GRANT SELECT ANY TABLE TO [ username ];

-- Select permission on all dictionary objects
GRANT SELECT ANY DICTIONARY TO [ username ];
﻿
Verify System Privileges 
Oracle
1 SELECT
s . PRIVILEGE AS SYS_PRIV ,
r . GRANTED_ROLE AS ROLE_PRIV ,
t . PRIVILEGE AS TAB_PRIV
FROM
( SELECT PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE = ’[ username ]’) s
LEFT JOIN
( SELECT GRANTED_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = ’[ username ]’) r
ON 1=1
LEFT JOIN
( SELECT PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE = ’[ username ]’) t
ON 1=1;
1 SELECT
s . PRIVILEGE AS SYS_PRIV ,
r . GRANTED_ROLE AS ROLE_PRIV ,
t . PRIVILEGE AS TAB_PRIV
FROM
( SELECT PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE = ’[ username ]’) s
LEFT JOIN
( SELECT GRANTED_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = ’[ username ]’) r
ON 1=1
LEFT JOIN
( SELECT PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE = ’[ username ]’) t
ON 1=1;
﻿
﻿
Masking Permissions
PostgreSQL Configuration