Database Governance Cheat Sheet

PostgreSQL

MySQL

Mariadb

SQLServer

Create User

CREATE USER <newUser> identified by encrypted password '<safe password>'

CREATE USER 'newUser'@'%' IDENTIFIED BY PASSWORD 'safe password'

CREATE USER newUser WITH PASSWORD 'safe password'

List Users

select usename from pg_catalog.pg_user;

select host, user from mysql.user;

SELECT name FROM sys.database_principals WHERE type_desc = 'SQL_USER' AND default_schema_name = 'dbo'

Drop User

DROP USER IF EXISTS <user name>

Read Table

GRANT SELECT ON TABLE <TABLE NAME> TO <USER NAME>

Read Write Table

GRANT SELECT, INSERT, UPDATE ON TABLE <TABLE NAME> TO <USER NAME>

Table Admin Privileges

GRANT ALL PRIVILEGES ON TABLE <TABLE NAME> TO <USER NAME>

AWS Redshift

Snowflake

Create User

CREATE USER <newUser> password '<safe password>'

CREATE USER <newUser> IF NOT EXISTS PASSWORD = 'safe password'

List Users

select usename from SVL_USER_INFO

SHOW USERS

Drop User

DROP USER IF EXISTS <user name>

Read Table

GRANT SELECT ON TABLE <TABLE NAME> TO <USER NAME>

Read Write Table

GRANT SELECT, INSERT, UPDATE ON TABLE <TABLE NAME> TO <USER NAME>

Table Admin Privileges

GRANT ALL PRIVILEGES ON TABLE <TABLE NAME> TO <USER NAME>

AWS Lake Formation

Google BigQuery

Read Table

boto3.grant_permissions(CatalogId: '<account_id>', Resource='Table': {'DatabaseName': ',<schema name>', 'Name': ',<Table Name>'}, Permissions='SELECT', Principal={ 'DataLakePrincipalIdentifier': 'arn:aws:iam::<account id>:<user name>'}

entry = bigquery.AccessEntry( role="READER", entity_type="userByEmail", entity_id="[email protected]")

Read Write Table

boto3.grant_permissions(CatalogId: ',<account_id>', Resource='Table': { 'DatabaseName': ',<schema name>', 'Name': ',<Table Name>' },  Permissions='SELECT, INSERT', Principal={ 'DataLakePrincipalIdentifier': 'arn:aws:iam::<account id>:<user name>'}

entry = bigquery.AccessEntry( role="WRITER", entity_type="userByEmail", entity_id="[email protected]")

Table Admin Privileges

boto3.grant_permissions(CatalogId: ',<account_id>', Resource='Table': {'DatabaseName': ',<schema name>', 'Name': ',<Table Name>',},  Permissions='SELECT, INSERT, UPDATE', Principal={ 'DataLakePrincipalIdentifier': 'arn:aws:iam::<account id>:<user name>' }

entry = bigquery.AccessEntry( role="OWNER", entity_type="userByEmail", entity_id="[email protected]")

Do you find this cheat sheet useful ? Give feedback using the widget in right bottom corner and we will make the cheat sheet better!