GRANT / REVOKE

Grants a privilege to a user.

GRANT privilege ON database_name.table_name TO 'user_name'@'host_name';

Grants a privilege along with the ability to delegate it to other users.

GRANT privilege ON database_name.table_name TO 'user_name'@'host_name' WITH GRANT OPTION;

Revokes a privilege from a user.

REVOKE privilege ON database_name.table_name FROM 'user_name'@'host_name';

Shows the privileges granted to a user (MySQL).

SHOW GRANTS FOR 'user_name'@'host_name';

The following three users are used in the examples. The database name is 'shop_db'.

Grants SELECT, INSERT, UPDATE, and DELETE to the application user.

sample_grant_revoke.sql

GRANT SELECT, INSERT, UPDATE, DELETE
    ON shop_db.*
    TO 'app_user'@'localhost';

Query OK, 0 rows affected

Creates a read-only report user.

sample_grant_revoke.sql

GRANT SELECT
    ON shop_db.*
    TO 'report_user'@'%';

Query OK, 0 rows affected

Grants access to a specific table only.

sample_grant_revoke.sql

GRANT SELECT
    ON shop_db.products
    TO 'public_user'@'%';

Query OK, 0 rows affected

Grants all privileges to the admin user.

sample_grant_revoke.sql

GRANT ALL PRIVILEGES
    ON shop_db.*
    TO 'admin_user'@'localhost'
    WITH GRANT OPTION;

Query OK, 0 rows affected

Revokes the SELECT privilege from the report user.

sample_grant_revoke.sql

REVOKE SELECT
    ON shop_db.*
    FROM 'report_user'@'%';

Query OK, 0 rows affected

Shows the privileges granted to a user (MySQL).

sample_grant_revoke.sql

SHOW GRANTS FOR 'app_user'@'localhost';

+-------------------------------------------------------------------------------+
| Grants for app_user@localhost                                                 |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost`                                  |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `shop_db`.* TO `app_user`@`localhost` |
+-------------------------------------------------------------------------------+
2 rows in set

Applies privilege changes immediately (MySQL). This command is not needed when you run GRANT directly, but is required to apply changes made by manually editing the privilege tables.

sample_grant_revoke.sql

FLUSH PRIVILEGES;

Query OK, 0 rows affected

PostgreSQL uses a similar syntax to MySQL, but does not use the @host_name notation for specifying users. It supports role-based privilege management.

-- Grants privileges to a user (PostgreSQL).
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Shows the privileges granted to a user (PostgreSQL).
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'app_user';

In Oracle, each user maps one-to-one with a schema. There are two types of privileges: system privileges and object privileges.

-- Grants object privileges (Oracle).
GRANT SELECT, INSERT ON shop_db.products TO app_user;

-- Grants system privileges (Oracle).
GRANT CREATE SESSION, CREATE TABLE TO app_user;

-- Shows the privileges granted to a user (Oracle).
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'APP_USER';

SQL Server uses schema-level privilege management, and role-based management with built-in roles such as db_datareader and db_datawriter is common.

-- Grants privileges on a table (SQL Server).
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.products TO app_user;

-- Adds a user to a built-in role (SQL Server).
ALTER ROLE db_datareader ADD MEMBER report_user;

-- Shows the privileges granted to a user (SQL Server).
EXEC sp_helpuser 'app_user';

SQLite is a file-based database and does not support the GRANT or REVOKE commands. Access control is managed through the file system's permissions.

GRANT and REVOKE are classified as DCL (Data Control Language). In production environments, follow the Principle of Least Privilege: grant only the permissions an application actually needs. For example, grant only SELECT to an API user that reads data, and only SELECT, INSERT, UPDATE, and DELETE to a user that writes data.

In MySQL, users are identified in the format user_name@host_name. % matches any host, while localhost allows local connections only. From a security standpoint, grant the minimum necessary privileges to any user accessible from outside.

For transaction control, see 'TRANSACTION / COMMIT / ROLLBACK'.