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';

-- Grants SELECT, INSERT, UPDATE, and DELETE to the application user.
GRANT SELECT, INSERT, UPDATE, DELETE
    ON shop_db.*
    TO 'app_user'@'localhost';

-- Creates a read-only report user.
GRANT SELECT
    ON shop_db.*
    TO 'report_user'@'%';

-- Grants access to a specific table only.
GRANT SELECT
    ON shop_db.products
    TO 'public_user'@'%';

-- Grants all privileges to the admin user.
GRANT ALL PRIVILEGES
    ON shop_db.*
    TO 'admin_user'@'localhost'
    WITH GRANT OPTION;

-- Revokes the SELECT privilege from the report user.
REVOKE SELECT
    ON shop_db.*
    FROM 'report_user'@'%';

-- Shows the privileges granted to the application user (MySQL).
SHOW GRANTS FOR 'app_user'@'localhost';

-- Applies privilege changes immediately (MySQL).
FLUSH PRIVILEGES;

-- Example output of: 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` |
-- +-------------------------------------------------------------------------+

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'.