How to List ALL Grants of a User in SQL

1. Introduction

Listing all grants of a user in SQL is essential for several reasons, primarily related to security, auditing, and management of database resources.

To list a user’s grants in SQL, you typically need to query the system tables or views that store information about database grants. The exact way to do this can vary slightly depending on the database management system (DBMS) you are using. Below are examples for MySQL, PostgreSQL, and SQL Server.

Note that another common term for grants is “permissions”. While this article will use “grants”, remember that the terms are interchangeable.

2. Setup

For demo purposes, let’s use the Baeldung University schema. We’ll create two roles demo_user and demo_read_only_user. We’ll grant SELECT, INSERT, UPDATE, and DELETE permission to the demo_user and SELECT permission to the demo_read_only_user  on the Course table.

3. Listing Grants

3.1. MySQL

The information_schema.table_privileges view in MySQL provides information about grants assigned to the roles on the tables.

We can query the information_schema.table_privileges view to retrieve the privilege info for the demo_user and demo_read_only_user:

SELECT grantee, table_schema, table_name, privilege_type 
FROM information_schema.TABLE_PRIVILEGES 
WHERE grantee IN ("'demo_user'@'localhost'", "'demo_read_only_user'@'localhost'");

In the query above, we’re selecting the info such as grantee, table_schema, table_name, and privilege_type for demo_user and demo_read_only_user.

Now let’s verify our result by executing the query:

 grantee                           | table_schema | table_name | privilege_type  
---------------------------------------------------------------------------------
 'demo_user'@'localhost'           | university | course      | SELECT           
 'demo_user'@'localhost'           | university | course      | INSERT           
 'demo_user'@'localhost'           | university | course      | UPDATE           
 'demo_user'@'localhost'           | university | course      | DELETE           
 'demo_read_only_user'@'localhost' | university | course      | SELECT           

As we can see, our query returns privilege_type granted to the demo_user  and demo_read_only_user on the course table in the university schema.

3.2. PostgreSQL

The information_schema.role_table_grants view provides information about grants assigned to roles on the tables within a PostgreSQL database.

We can query the information_schema.role_table_grants view to retrieve privilege info for the demo_user:

SELECT grantee, table_schema, table_name, privilege_type 
FROM information_schema.role_table_grants 
WHERE grantee IN ('demo_user', 'demo_read_only_user'); 

In the query above, we’re selecting the info such as grantee, table_schema, table_name, and privilege_type for demo_user and demo_read_only_user.

Again let’s verify our result by executing the query:

  grantee            | table_schema    | table_name | privilege_type
-----------+--------------+------------+---------------------------
 demo_user           | university       | course     | INSERT
 demo_user           | university       | course     | SELECT
 demo_user           | university       | course     | UPDATE
 demo_user           | university       | course     | DELETE
 demo_read_only_user | public           | course     | SELECT
(4 rows)

As we can see, our query returns privilege_type granted to the demo_user and demo_read_only_user on the course table in the university schema.

3.3. SQL Server

The sys.database_permissions view in the SQL server provides information about the grants within the database. It contains details regarding the grants on objects such as tables, views, procedures, schemas, and other database entities.

We can query sys.database_permissions to retrieve privilege info for demo_user and demo_read_only_user:

SELECT 
    OBJECT_NAME(dp.major_id) AS object_name, 
    USER_NAME(dp.grantee_principal_id) AS grantee, 
    dp.permission_name, dp.state_desc 
FROM sys.database_permissions dp 
WHERE USER_NAME(dp.grantee_principal_id) IN ('demo_user', 'demo_read_only_user');

In the query above, we’re selecting the info such as grantee, object_name(table_name), permission_name, and state_desc for demo_user and demo_read_only_user.

Once more, let’s verify our result by executing the query:

object_name | grantee             | permission_name | state_desc
-----------------------------------------------------------------
Course       | demo_user           | INSERT         | GRANT      
Course       | demo_user           | SELECT         | GRANT      
Course       | demo_user           | UPDATE         | GRANT      
Course       | demo_user           | DELETE         | GRANT      
Course       | demo_user_read_only | SELECT         | GRANT      

As we can see, our query returns permission_name granted to the demo_user and demo_read_only_user on the Course table.

Notably, SQL Server also provides state_desc info, which indicates the status of the grant (GRANT, DENY, or REVOKE).

4. Conclusion

In this article, we discussed and implemented logic to list user grants in MySQL, PostgreSQL, and SQL Server. Listing all grants of a user is crucial for auditing and managing effectively in databases. By querying the appropriate system views or tables, we can gain insights into who can access which database objects and what actions they are allowed to perform.