What are the essential SQL commands for managing users and permissions?

1 Creating Users

To create a new user in SQL, you need to use the CREATE USER command, followed by the user name and optionally some attributes, such as password, host, role, or default schema. The syntax and options may vary depending on the database system you are using. For example, in MySQL, you can create a user with a password and a host name like this: CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password123'; In PostgreSQL, you can create a user with a password and a role like this: CREATE USER bob WITH PASSWORD 'password123' IN ROLE admin;

2 Modifying Users

To modify an existing user in SQL, you need to use the ALTER USER command, followed by the user name and the changes you want to apply. For example, you can change the password, rename the user, or grant or revoke roles. Again, the syntax and options may differ depending on the database system you are using. For example, in Oracle, you can change the password and rename a user like this: ALTER USER bob IDENTIFIED BY 'newpassword456' RENAME TO alice; In SQL Server, you can grant or revoke a role to a user like this: ALTER USER bob ADD ROLE reader; ALTER USER bob DROP ROLE writer;

3 Deleting Users

To delete a user in SQL, you need to use the DROP USER command, followed by the user name and optionally some options, such as CASCADE or RESTRICT. The CASCADE option will also delete any objects owned by the user, such as tables or views. The RESTRICT option will prevent the deletion of the user if they own any objects. For example, in MySQL, you can delete a user with the CASCADE option like this: DROP USER 'bob'@'localhost' CASCADE; In PostgreSQL, you can delete a user with the RESTRICT option like this: DROP USER bob RESTRICT;

4 Granting Permissions

To grant permissions to a user in SQL, you need to use the GRANT command, followed by the privileges you want to grant, the object you want to grant them on, and the user you want to grant them to. For example, you can grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called customers to a user called bob like this: GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO bob; You can also use the ALL keyword to grant all privileges on an object, or the * symbol to grant privileges on all objects in a schema or database. For example, you can grant all privileges on all tables in the sales schema to a user called alice like this: GRANT ALL ON sales.* TO alice;

5 Revoking Permissions

To revoke permissions from a user in SQL, you need to use the REVOKE command, followed by the privileges you want to revoke, the object you want to revoke them from, and the user you want to revoke them from. For example, you can revoke UPDATE and DELETE privileges on a table called customers from a user called bob like this: REVOKE UPDATE, DELETE ON customers FROM bob; You can also use the ALL keyword to revoke all privileges on an object, or the * symbol to revoke privileges on all objects in a schema or database. For example, you can revoke all privileges on all tables in the sales schema from a user called alice like this: REVOKE ALL ON sales.* FROM alice;