oracle give privileges user code example

Example: oracle user privileges

-- List privileges, including roles, for MY_USER
SELECT GRANTEE,
       PRIVILEGE    AS PRIVILEGE_ROLE,
       NULL         AS OWNER,
       NULL         AS TABLE_NAME,
       NULL         AS COLUMN_NAME,
       ADMIN_OPTION AS PRIVILEGE,
       NULL         AS GRANTABLE
FROM all_SYS_PRIVS
WHERE GRANTEE = 'MY_USER'
UNION ALL
SELECT R.GRANTEE, R.GRANTED_ROLE AS PRIVILEGE_ROLE, P.OWNER, P.TABLE_NAME, 
	P.COLUMN_NAME, PRIVILEGE, P.GRANTABLE
FROM all_ROLE_PRIVS R
         LEFT JOIN ROLE_TAB_PRIVS P ON P.ROLE = R.GRANTED_ROLE
WHERE R.GRANTEE = 'MY_USER';

Tags:

Sql Example