# Concept of group in privilege does not exist in mysql.
# However, They exist in PostgreSQL and SQL Server
# Create group:
CREATE ROLE mygroup [LOGIN FALSE]; # Postgres and SQL Server
# Delete a group
DROP ROLE mygroup; # Postgres and SQL Server
# Add user in a group
ALTER GROUP mygroup ADD USER myuser; # Postgres and SQL Server
# Delete user from a group
ALTER GROUP mygroup DROP USER myuser; # Postgres and SQL Server
# See all available groups
SELECT rolname FROM pg_roles WHERE rollogin = FALSE; # Postgres
SELECT name FROM sys.database_principals WHERE type = 'R' AND is_fixed_role = 0; # SQL Server
# Grant permission to a group
GRANT SELECT, INSERT, UPDATE, DELETE ON mytable TO mygroup; # Postgres and SQL Server
# Revoke permission from a group
REVOKE SELECT, INSERT, UPDATE, DELETE ON mytable FROM mygroup; # Postgres and SQL Server
# Add group to a role
ALTER ROLE myrole SET GROUP mygroup; # Postgres
ALTER ROLE myrole ADD MEMBER mygroup; # SQL Server
# Revoke group from a role
ALTER ROLE myrole DROP GROUP mygroup; # Postgres
ALTER ROLE myrole DROP MEMBER mygroup; # SQL Server
# Enable expiration time on role for a user
ALTER USER myuser VALID UNTIL 'now' + INTERVAL '90 days'; # Postgres
ALTER LOGIN myuser WITH PASSWORD EXPIRATION ON, EXPIRATION_DATE = '2030-12-31'; # SQL Server
# Enable expiration time on role for a group
ALTER GROUP mygroup VALID UNTIL 'now' + INTERVAL '90 days'; # Postgres
ALTER ROLE mygroup WITH PASSWORD EXPIRATION ON, EXPIRATION_DATE = '2030-12-31'; # SQL Server
# Grant schema access to group
GRANT USAGE ON SCHEMA schema_name TO user_or_group_name; # Postgres
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO user_or_group_name;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::schema_name TO user_or_group_name; # SQL Server
# Grant database access to group
GRANT CONNECT ON DATABASE database_name TO user_or_group_name; # Postgres
GRANT CONNECT SQL TO user_name; # SQL Server
# 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;
# Note: They become very powerful when handling with ROLE and USER