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

-- 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

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;