Granting and Revoking Permissions
Granting and revoking permissions to database users in SQL database management systems is an essential process for managing access and control of database users to database tables and securing or fixing the security of existing databases. The GRANT and REVOKE command statements control the permissions or access of database users, allowing database users or administrator roles to perform tasks and actions on database operation objects such as tables, views, and database procedures.
Here, we will learn how to grant and revoke permissions in popular database systems including MySQL, PostgreSQL, and SQL Server.
Granting and Revoking Permissions in MySQL Database Software.
In MySQL database software, the GRANT and REVOKE command statements are used to provide permissions to database users or control administrator privileges.
Granting Permissions in MySQL Database Software.
To provide permissions to database users in MySQL database software, the GRANT command statement is used. Here, user privileges such as database or table modification, update, delete, and insert permissions are provided to the database user using the GRANT command. All these features provide special rights to the user receiving the privileges.
MySQL GRANT command syntax.
GRANT privilege_type ON database_name.table_name TO ‘username’@’host’;
Elements of the MySQL GRANT command.
- privilege_type – This is the type of special privilege provided to the database user. For example, SELECT, INSERT, UPDATE, etc., are database operations.
- database_name.table_name – This is the database and table, or the wildcard * for the entire table.
- ‘username’@’host’ – This is the username and password details in the database of the connecting host or system, allowing connection from that host. For example, localhost, server location, etc.
Example of the MySQL GRANT command.
Here, we are granting SELECT and INSERT table privileges on the employee table in the database table named customer_db. GRANT SELECT, INSERT ON customer_db.employee TO ‘siddhi_deora’@’localhost’;
To provide all privileges on a large volume of data in a complete database (including privileges to modify or delete database tables).
GRANT ALL PRIVILEGES ON customer_db.* TO ‘siddhi_deora’@’localhost’;
Granting administrative privileges to a database user.
To provide administrative privileges such as GRANT OPTION to a database user in a database table (this allows the database user to grant privileges to other users).
GRANT ALL PRIVILEGES ON customer_db.* TO ‘siddhi_deora’@’localhost’ WITH GRANT OPTION;
This grants siddhi_deora the permission to grant the same privileges to other database users in the database table.
Revoking permissions from a database user.
The REVOKE command or statement is used to revoke special administrator privilege permissions granted to a database user in a database table.
Syntax for revoking permissions.
REVOKE privilege_type ON database_name.table_name FROM ‘username’@’host’;
Example of revoking permissions.
Here, to revoke the INSERT privilege of the user on the employee database table.
REVOKE INSERT ON customer_db.employee FROM ‘siddhi_deora’@’localhost’;
To revoke all administrator privileges granted to a specific database user in a database table.
REVOKE ALL PRIVILEGES ON customer_db.* FROM
‘siddhi_deora’@’localhost’; After applying the revoke statement, the database user may need to run this:
FLUSH PRIVILEGES;
Granting and revoking permissions in PostgreSQL database software.
In PostgreSQL database software, special user privilege permissions are granted and revoked by applying GRANT and REVOKE command statements, similar to MySQL software. For example, PostgreSQL database software also supports special user roles. This makes it easy to manage and control special permissions for multiple database users.
Syntax of the GRANT statement in PostgreSQL.
To grant permissions on a specific user object in a database table:
GRANT privilege_type ON object TO username;
Elements of the PostgreSQL GRANT statement.
- privilege_type – This is the type of user permission in the PostgreSQL software, such as (SELECT, INSERT, UPDATE, DELETE, etc.) operations.
- object – This is the user database object (table, schema, database, etc.).
- username – This is the specific role or user in the database table to whom the administrator wants to grant privilege permissions.
Example of GRANT in PostgreSQL database software.
Here, to grant special permissions like SELECT and INSERT on the employee table in the customer_db database.
GRANT SELECT, INSERT ON employee TO siddhi_deora;
To grant all privilege permissions on a database table.
GRANT ALL PRIVILEGES ON DATABASE customer_db TO siddhi_deora;
To provide a role to a database user (e.g., db_datareader).
GRANT db_datareader TO siddhi_deora;
Revoking permissions in PostgreSQL database software.
To revoke special privileges or permissions from a database user, apply the REVOKE command or statement.
PostgreSQL Basic syntax for revoking permissions.
REVOKE privilege_type ON object FROM username;
Example of PostgreSQL revoking permissions.
Here, to revoke INSERT permission on the employee database table.
REVOKE INSERT ON employee FROM siddhi_deora;
To revoke all privilege permissions on the database table.
REVOKE ALL PRIVILEGES ON DATABASE customer_db FROM siddhi_deora;
Granting and revoking permissions in SQL Server database software.
In SQL Server database software, database user privilege permissions are also managed and controlled by applying the GRANT and REVOKE commands. Here, SQL Server software provides more detailed control to database users. For example, providing special privileges or permissions on database table schemas, tables, views, and stored procedures, etc.
Granting permissions in SQL Server database software.
Here, to provide a special role or permission to a database user.
Syntax of the Grant command in SQL Server database software.
GRANT privilege_type ON object TO username;
Elements of the SQL Server Grant command.
- privilege_type – This is the type of permission to be provided to the database user. For example, (SELECT, INSERT, UPDATE, DELETE, etc.) are database operations.
- object – This is the database object, such as a table, schema, stored procedure, etc.
- username – This is the database user or role to whom permission is being granted in the database.
Example of the Grant command in SQL Server.
Here, to provide administrator privileges such as SELECT and INSERT on the employee database table.
GRANT SELECT, INSERT ON employee TO siddhi_deora;
To provide SELECT permission on all tables in the customer_db database.
GRANT SELECT ON SCHEMA::dbo TO siddhi_deora; Database users can also be granted permissions on specific stored procedures.
GRANT EXECUTE ON PROCEDURE::sp_add_employee TO siddhi_deora;
Revoking permissions in SQL Server database software.
To revoke specific permissions from a database user, apply the REVOKE command or statement.
Revoking permissions in SQL Server syntax.
REVOKE privilege_type ON object FROM username;
Revoking permissions in SQL Server instance.
Here, to revoke INSERT permission on the employee database table.
REVOKE INSERT ON employee FROM siddhi_deora;
To revoke all types of permissions from a database user.
REVOKE ALL PRIVILEGES ON DATABASE customer_db FROM siddhi_deora;
Permissions can also be revoked on the database table schema.
REVOKE SELECT ON SCHEMA::dbo FROM siddhi_deora;
Types of permission privileges in SQL database software.
- SELECT – SELECT provides permission to read or display data from a database table or view.
- INSERT – This provides permission to insert new table rows into a database table.
- UPDATE – This provides permission to modify or update existing table rows in a database table.
- DELETE – This provides permission to delete existing table rows from a database table.
- ALL – This provides all available permission privileges on the database table.
- EXECUTE – This provides permission to run database stored procedures or functions.
- ALTER – This provides permission to modify database objects such as tables and views.
- INDEX – This provides permission to create and delete indexes in the database.
- CREATE – This provides permission to create database objects such as tables, views, and indexes in the database.
- DROP – This provides permission to delete database objects.
Conclusion on Granting and Revoking Permissions in Database.
Granting and revoking user permissions in multiple database software is an essential aspect of database security and user management. By applying GRANT and REVOKE commands or statements, database administrators can effectively handle and manage user access control to sensitive data and database structures. Utilizing best practices for permission privileges in the database management system and managing database user roles for permissions helps in maintaining and controlling a secure and properly managed database environment.
