# Create a role
CREATE ROLE myrole; # MySQL, Postgres and SQL Server
# See all available roles
SELECT rolname FROM pg_roles; # Postgres
SELECT name FROM sys.database_principals WHERE type = 'R'; # SQL Server
SELECT * FROM mysql.user WHERE Host='%'; # MySQL
# Delete a role
DROP ROLE myrole; # Postgres and SQL Server
DROP USER myrole; # MySQL
# Grant permission to a role
GRANT SELECT, INSERT, UPDATE ON mydatabase.mytable TO myrole; # MySQL, Postgres and SQL Server
# Revoke permission from a role
REVOKE SELECT, INSERT, UPDATE ON mydatabase.mytable FROM myrole; # MySQL, Postgres and SQL Server
# Create a user
CREATE USER new_user WITH PASSWORD 'password'; # Postgres
CREATE LOGIN new_user WITH PASSWORD = 'password'; # SQL Server
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password'; # MySQL
# Change user password
ALTER USER existing_user WITH PASSWORD 'new_password'; # Postgres
ALTER LOGIN existing_user WITH PASSWORD = 'new_password'; # SQL Server
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('newpassword'); # MySQL
# See all available users
SELECT usename FROM pg_user; # Postgres
SELECT name FROM sys.sql_logins; # SQL Server
SELECT User FROM mysql.user; # MySQL
# Delete a user
DROP USER existing_user; # Postgres
DROP LOGIN existing_user; # SQL Server
DROP USER 'existing_user'@'localhost'; # MySQL
# Add a user to a role
ALTER ROLE myuser SET ROLE myrole; # Postgres
ALTER ROLE myrole ADD MEMBER myuser; # SQL Server
GRANT myrole TO myuser WITH GRANT OPTION; # MySQL
# Remove a user from a role
ALTER ROLE myuser DROP ROLE myrole; # Postgres
ALTER ROLE myrole DROP MEMBER myuser; # SQL Server
REVOKE myrole FROM myuser; # MySQL
# Grant permission to a user
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.mytable TO 'myuser'@'localhost'; # MySQL
GRANT SELECT, INSERT, UPDATE, DELETE ON mytable TO myuser; # Postgres and SQL Server
# Revoke permission from a user
REVOKE SELECT, INSERT, UPDATE, DELETE ON mydatabase.mytable FROM 'myuser'@'localhost'; # MySQL
REVOKE SELECT, INSERT, UPDATE, DELETE ON mytable FROM myuser; # Postgres and SQL Server
# Note : There is also the concept of group that is not supported by MySQL
# Always make sure to grant permission to connect to database and schema
GRANT CONNECT ON DATABASE db_name TO role;
GRANT USAGE ON SCHEMA schema_name TO role;