How can I list ALL grants a user received?

sql

database-management

oracle-database

sql-queries

byAlex Kataev·Nov 30, 2024

The privileges assigned to a MySQL user can be procured using:

SELECT privilege_type FROM information_schema.user_privileges WHERE grantee = 'user@host';

Here, replace user@host with the respective user credentials. In PostgreSQL or Oracle, this can be done by querying their corresponding security catalogs or dictionary views.

In Oracle, you can access system privileges by querying dba_sys_privs:

SELECT privilege 
FROM dba_sys_privs 
WHERE grantee = 'USERNAME';

Without fail, swap 'USERNAME' with the genuine username. Account for direct object privileges, role-based privileges, and column privileges for an extensive list.

The "show me everything" approach

Direct system and object permits

System privileges of a user can be retrieved by querying dba_sys_privs:

-- As little bear said, 'I want more!'
SELECT * 
FROM dba_sys_privs 
WHERE grantee = 'USERNAME';

Direct grants to tables or views are displayed with the dba_tab_privs view:

-- Direct privilege access hotter than Tabasco
SELECT * 
FROM dba_tab_privs 
WHERE grantee = 'USERNAME';

Role-based privileges rock!

Roles also house permissions assigned through them. Thus, fetch the roles linked to a user with:

-- Role call, anyone?
SELECT * 
FROM dba_role_privs 
WHERE grantee = 'USERNAME';

To know the system privileges related to these roles, use:

-- Roles have privileges too, know?
SELECT rp.grantee, sp.privilege
FROM dba_role_privs rp
JOIN role_sys_privs sp ON rp.granted_role = sp.role
WHERE rp.grantee = 'USERNAME';

Bring it all together

In order to render a composite list of all object grants, bring all_tab_privs_recd into play:

-- All board this query ride!
SELECT * 
FROM all_tab_privs_recd 
WHERE grantee = 'USERNAME';

Do remember, all_tab_privs_recd might omit temporary table grants.

DBMS_METADATA, my new best friend

Oracle's DBMS_METADATA package - it's like showing up at a buffet and being handed everything:

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','USERNAME') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','USERNAME') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','USERNAME') FROM DUAL;

Ensure you replace 'USERNAME' with the actual username (vital note - case-sensitivity!)

Transformation time!

When constructing these queries, mind the duplicate entries. It's like finding clones at a party; ensuring clear, organized results will make everyone happier (and less confused!)

Navigating uncertainties

Drilling down role hierarchy

While exploring role grants, ponder upon the role hierarchy. You can fetch each role's privileges in a recursive manner:

-- Recursive query, because sometimes the journey is the destination
WITH RECURSIVE role_privs (role, privilege) AS (
  SELECT granted_role, privilege
  FROM role_sys_privs
  WHERE role IN (SELECT granted_role FROM dba_role_privs WHERE grantee = 'USERNAME')
  UNION ALL
  SELECT rsp.granted_role, rsp.privilege 
  FROM role_sys_privs rsp, role_privs rp 
  WHERE rsp.role = rp.role
)
SELECT * FROM role_privs;

Temporary tables

General views might miss out on temporary table grants. So tune-in to specific views that list these privileges.

Column-specific liberties

For more granularity, DBA_TAB_PRIVS reveals column-specific privileges:

-- Column or not column, that is the question
SELECT grantee, table_name, column_name, privilege 
FROM dba_tab_privs 
WHERE grantee = 'USERNAME'
AND column_name IS NOT NULL;

Ignoring default schemas

To focus more on specific user permissions, eliminate defaults:

-- Ignoring SYS and SYSTEM, because we are rebels
SELECT * 
FROM dba_tab_privs 
WHERE grantee != 'SYS' AND grantee != 'SYSTEM' AND grantee = 'USERNAME';