Give All Permissions to a User in SQL

Last updated: January 17, 2025

Reviewed by: Michal Aibin
Baeldung Pro – SQL – NPI EA (cat = Baeldung on SQL)
announcement - icon

Learn through the super-clean Baeldung Pro experience:

>> Membership and Baeldung Pro.

No ads, dark-mode and 6 months free of IntelliJ Idea Ultimate to start with.

1. Introduction

We grant permissions to database users to ensure proper access control while maintaining security. Granting permission to a user may initially seem like a single action. However, it often involves multiple steps depending on the database management system and the required access level.

For instance, in PostgreSQL, granting all permissions to a user might include multiple actions. These include providing access to connect to the database, use schemas, and perform operations on tables, views, and sequences. Similarly, in MySQL and SQL Server, we combine commands or predefined roles to achieve comprehensive permissions.

In this tutorial, we’ll explore how to grant all necessary permissions to a user in SQL, focusing on PostgreSQL, MySQL, and SQL Server. Each database system has its unique methods, and understanding these ensures proper access control while adhering to security best practices.

2. Implementation in PostgreSQL

PostgreSQL offers fine-grained control over user permissions. Furthermore, these permissions enable database administrators to grant specific or all privileges on databases, schemas, and objects. Let’s break down the steps to provide comprehensive permissions to a user in PostgreSQL.

For illustration purposes, let’s use the Baeldung University database. First, let’s create a user in the University database:

CREATE USER dev_user WITH PASSWORD 'password123';
CREATE ROLE
Time: 0.026s

Here, we created a new user in the database called dev_user. By default, the user has no permission to interact with the database or schema. To confirm the permissions, let’s query the information_schema.role_table_grants view to see what access dev_user has on the database table:

SELECT grantee, privilege_type, table_schema, table_name 
FROM information_schema.role_table_grants 
WHERE grantee = 'dev_user';
+---------+----------------+--------------+------------+
| grantee | privilege_type | table_schema | table_name |
|---------+----------------+--------------+------------|
+---------+----------------+--------------+------------+
SELECT 0
Time: 0.009s

In the output, the query returns no rows, indicating dev_user has no permissions.

2.1. Granting Connection Privileges on the Database

To start, a user must have the ability to connect to a database. By default, this is granted to all users through the PUBLIC role. Let’s grant permission for the created user to have access to the database:

GRANT CONNECT ON DATABASE university TO dev_user;
GRANT
Time: 0.008s

The query allows dev_user to connect to the University database but doesn’t grant any additional permissions.

2.2. Granting Schema-Level Privileges

Furthermore, we grant usage and creation privileges for the user to access objects within the schema. Let’s assume we’re working with the default PUBLIC schema:

GRANT USAGE ON SCHEMA public TO dev_user;
GRANT CREATE ON SCHEMA public TO dev_user;
GRANT
GRANT
Time: 0.006s

In the query, the USAGE allows the user to access the schema and see its structure. Additionally, the CREATE permits the user to create new objects such as tables within the schema.

These assigned privileges ensure that dev_user can interact with the schema but can’t perform operations on existing objects.

2.3. Granting Table-Level Privileges

Next, to grant comprehensive access to existing tables in the schema, we need to assign privileges such as SELECT, INSERT, UPDATE, and DELETE:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dev_user;
GRANT
Time: 0.005s

Now, the dev_user can query, insert, modify, and delete data in all current tables in the PUBLIC schema. Additionally, let’s also configure default privileges to automatically grant these permissions on any tables created in the future:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dev_user;
You're about to run a destructive command.
Do you want to proceed? (y/n): Y
Your call!
ALTER DEFAULT PRIVILEGES
Time: 0.011s

This query makes sure any new table created in the PUBLIC schema will automatically inherit these permissions.

2.4. Granting Sequence-Level Privileges

Moving forward, we need to also grant sequence-level privileges to both the existing and future sequences:

GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO dev_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO dev_user;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
GRANT
ALTER DEFAULT PRIVILEGES
Time: 0.005s

The query grants access to all existing sequences. Additionally, it also sets default privileges for future sequences.

2.5. Verifying the Permissions

Now that we’ve granted all permissions to the dev_user. Let’s confirm these permissions assigned by querying the information_schema.role_table_grants view again:

SELECT grantee, privilege_type, table_schema, table_name 
FROM information_schema.role_table_grants 
WHERE grantee = 'dev_user';
+----------+----------------+--------------+------------+
| grantee  | privilege_type | table_schema | table_name |
|----------+----------------+--------------+------------|
| dev_user | INSERT         | public       | student    |
| dev_user | SELECT         | public       | student    |
| dev_user | UPDATE         | public       | student    |
| dev_user | DELETE         | public       | student    |
| dev_user | INSERT         | public       | department |
| dev_user | SELECT         | public       | department |
| dev_user | UPDATE         | public       | department |
| dev_user | DELETE         | public       | department |
| dev_user | INSERT         | public       | course     |
| dev_user | SELECT         | public       | course     |
| dev_user | UPDATE         | public       | course     |
| dev_user | DELETE         | public       | course     |
+----------+----------------+--------------+------------+
SELECT 12
Time: 0.012s

As seen, the output returns rows showing that dev_user has full access to the tables in the PUBLIC schema.

Finally, let’s also login to the University database with the dev_user and test querying the Student table:

select * from student limit 3;
+------+-----------------+-------------+------------+-----------------+-----------------+-----+
| id   | name            | national_id | birth_date | enrollment_date | graduation_date | gpa |
|------+-----------------+-------------+------------+-----------------+-----------------+-----|
| 1003 | Rita Ora        | 132345166   | 2001-01-14 | 2020-01-15      | 2024-06-15      | 4.2 |
| 1007 | Philip Lose     | 321345566   | 2001-06-15 | 2020-01-15      | 2024-06-15      | 3.8 |
| 1010 | Samantha Prabhu | 3217165566  | 2001-03-21 | 2020-01-15      | 2024-06-15      | 4.9 |
+------+-----------------+-------------+------------+-----------------+-----------------+-----+
SELECT 3
Time: 0.012s

The query was executed successfully, which confirms that the dev_user user has been granted the necessary permissions.

3. Implementation in MySQL

MySQL provides a straightforward and flexible mechanism for managing user permissions. Like PostgreSQL, we can grant permissions at the database, table, or even column level for granular access control.

To begin, we need to create a user to give permissions. In MySQL, user accounts are defined with a username and host. Let’s create a user named dev_user who can connect from any host (‘%’):

CREATE USER 'dev_user'@'%' IDENTIFIED BY 'password123';
Query OK, 0 rows affected (0.049 sec)

This query creates a user dev_user with the password password123 allowing connection from any host. By default, the new user has no privilege.

3.1. Granting Privileges at the Database Level

To give the user full access to the University database, we use the GRANT statement:

GRANT ALL PRIVILEGES ON University.* TO 'dev_user'@'%';
Query OK, 0 rows affected (0.006 sec)

This statement grants all permissions on all tables within the University database to dev_user. Additionally, in MySQL, schemas are equivalent to databases. By granting ALL PRIVILEGES on the database (University.*), we have effectively given the user access to all objects within the schema.

To confirm the permissions, let’s query the information_schema.user_privilege table:

SELECT GRANTEE, PRIVILEGE_TYPE 
FROM information_schema.user_privileges 
WHERE GRANTEE = "'dev_user'@'%'";
+----------------+----------------+
| GRANTEE        | PRIVILEGE_TYPE |
+----------------+----------------+
| 'dev_user'@'%' | USAGE          |
+----------------+----------------+
1 row in set (0.005 sec)

The output confirms that the dev_user has the necessary privileges at the schema level.

3.2. Verifying the Permissions

Finally, let’s confirm the assigned permission with the use of the SHOW GRANTS command:

SHOW GRANTS FOR 'dev_user'@'%';
+----------------------------------------------------------+
| Grants for dev_user@%                                    |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev_user`@`%`                     |
| GRANT ALL PRIVILEGES ON `University`.* TO `dev_user`@`%` |
+----------------------------------------------------------+
2 rows in set (0.002 sec)

This output confirms that the dev_user has full access to the University database.

4. Implementation in SQL Server

SQL Server offers robust options for managing user permissions at various levels, such as database and object levels. Furthermore, in SQL Server, we can control user access for specific operations, ensuring secure and appropriate use of database resources. Let’s explore how to grant all necessary permissions to a user in SQL Server.

First, let’s create a user named dev_user and give it login capabilities:

CREATE LOGIN Dev_login WITH PASSWORD = 'Password123!';
CREATE USER dev_user FOR LOGIN Dev_login; 
Server: Msg 0000, Level 11, State 0, Line 1
Login created.
Server: Msg 0000, Level 11, State 0, Line 1
User created.

The first query creates a login named Dev_login in the SQL Server database. Then, the CREATE USER statement associates the user dev_user with the login Dev_login. The user created doesn’t have any permission by default.

4.1. Granting Database-Level Permissions

Further, to provide comprehensive access at the database level, let’s grant SELECT, INSERT, UPDATE, and DELETE permissions for the user:

GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE::University TO dev_user;
Server: Msg 0000, Level 11, State 0, Line 1
Command(s) completed successfully.

This query gives the dev_user permissions to perform all CRUD (Create, Read, Update, Delete) operations across the entire database. However, this doesn’t grant control over specific objects such as tables or views.

4.2. Granting Object-Level Permissions

Additionally, to grant permissions to specific objects, such as tables, we use the GRANT statement with the OBJECT keyword. Let’s grant dev_user access to perform CRUD operations on all tables in the current database:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN DATABASE TO dev_user; 
Server: Msg 0000, Level 11, State 0, Line 1
Command(s) completed successfully.

This statement grants the specified permissions to the user dev_user on all tables that exist within the University database.

4.3. Verifying the Permissions

Let’s confirm permissions have been granted successfully by querying the inspect permissions at the database and object levels:

SELECT grantee_principal_id, class, class_desc, permission_name, state_desc
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID('dev_user');
+--------------------+-------+-----------------+-----------------+------------+  
|grantee_principal_id| class | class_desc      | permission_name | state_desc |  
+--------------------+-------+-----------------+-----------------+------------+  
| 5                  | 0     | DATABASE        | SELECT          | GRANT      |  
| 5                  | 0     | DATABASE        | INSERT          | GRANT      |  
| 5                  | 0     | DATABASE        | UPDATE          | GRANT      |  
| 5                  | 0     | DATABASE        | DELETE          | GRANT      |  
| 5                  | 1     | OBJECT_OR_COLUMN| SELECT          | GRANT      |  
| 5                  | 1     | OBJECT_OR_COLUMN| INSERT          | GRANT      |  
| 5                  | 1     | OBJECT_OR_COLUMN| UPDATE          | GRANT      |  
| 5                  | 1     | OBJECT_OR_COLUMN| DELETE          | GRANT      |  
+--------------------+-------+-----------------+-----------------+------------+  
Server: Msg 0000, Level 11, State 0, Line 1
Command(s) completed successfully.

This output confirms that dev_user has been granted the necessary permissions at both the database and object levels.

Additionally, let’s login with the dev_user and query the Student table:

SELECT * FROM student;
+------+-----------------+-------------+------------+-----------------+-----------------+-----+  
| id   | name            | national_id | birth_date | enrollment_date | graduation_date | gpa |  
+------+-----------------+-------------+------------+-----------------+-----------------+-----+  
| 1003 | Rita Ora        | 132345166   | 2001-01-14 | 2020-01-15      | 2024-06-15      | 4.2 |  
| 1007 | Philip Lose     | 321345566   | 2001-06-15 | 2020-01-15      | 2024-06-15      | 3.8 |  
| 1010 | Samantha Prabhu | 3217165566  | 2001-03-21 | 2020-01-15      | 2024-06-15      | 4.9 |  
...
Server: Msg 0000, Level 11, State 0, Line 1
Command(s) completed successfully.

The successful query confirms that dev_user can access and interact with the Student table as intended.

5. Conclusion

In this article, we’ve explored how to grant all necessary permissions to a user in PostgreSQL, MySQL, and SQL Server. Further, we demonstrated the key steps to ensure proper access control. PostgreSQL offers fine-grained privilege management, while MySQL provides a more straightforward approach. Finally, SQL Server combines flexibility and precision, enabling administrators to assign permissions at both database and object levels.

Understanding these helps administrators maintain secure and efficient database environments.