Managing permissions and access control in Babelfish for Aurora PostgreSQL - Amazon Aurora
Supported use casesUnsupported use cases with the workaroundsUnsupported use cases

Managing permissions and access control in Babelfish for Aurora PostgreSQL

In Babelfish for Aurora PostgreSQL, you can manage permissions and access control for databases, schemas, and objects. The following tables will outline the specific SQL commands for granting permissions in Babelfish to achieve various access control scenarios. It will cover supported use cases that can be implemented as well as workarounds for currently unsupported cases. This will allow you to configure appropriate permissions to meet your security and compliance requirements when working with Babelfish databases.

Supported use cases

The following table explains the use cases that are supported in Babelfish. For each use case, the table shows the action needed to achieve it and sample SQL commands.

Use case Action SQL commands Comments

Allow login to do SELECTs/DMLs/DDLs in any database

Add login to sysadmin server role

ALTER SERVER ROLE sysadmin ADD MEMBER login

None

Allow login to do SELECTs/DMLs/DDLs in a database

Make login the owner of the database

ALTER AUTHORIZATION ON DATABASE::database TO login

A database can have only one owner

Allow database user to do SELECTs/DMLs on a schema

Grant permission to database user on schema

GRANT SELECT/EXECUTE/INSERT/UPDATE/DELETE ON SCHEMA::schema TO user

None

Allow database user to do SELECTs/DMLs on a schema

Make database user owner of schema at schema creation time

CREATE SCHEMA schema AUTHORIZATION user

Changing schema ownership after creation isn't currently supported

Allow database user to do SELECTs/DMLs on an object

Grant permission to database user on object

GRANT SELECT/EXECUTE/INSERT/UPDATE/DELETE ON SCHEMA::object TO user

None

Unsupported use cases with the workarounds

The following table explains the use cases that aren't supported in Babelfish, but which can be achieved using a workaround.

Use case Action SQL commands Comments

Allow database user to do SELECTs/DMLs/DDLs in a database including dropping database

Make login owner of database

ALTER AUTHORIZATION ON DATABASE database TO login

Adding database users/roles to db_owner role isn't currently supported

Allow database user to do only SELECTs in a database

Grant SELECT to database user on all schemas in database

GRANT SELECT ON SCHEMA::schema TO user

Database role db_datareader isn't currently supported

Allow database user to do DML on all objects in a database

Grant INSERT, UPDATE, DELETE to database user on all schemas in database

GRANT INSERT, UPDATE, DELETE ON SCHEMA::schema TO user

Database role db_datawriter isn't currently supported

Unsupported use cases

The following table explains the use cases that aren't supported in Babelfish.

Use case Comments

Allow database user to do only DDLs in a database

Database role db_ddladmin isn't currently supported

Allow database user to only CREATE/ALTER/DROP any database role

Database role db_securityadmin isn't currently supported

Allow database user to only GRANT/REVOKE permissions on objects in a database

Database role db_securityadmin isn't currently supported

Allow database user to only CREATE/ALTER/DROP any user

Database role db_accessadmin isn't currently supported

Allow database user to only grant and revoke database access and alias user accounts to logins

Database role db_accessadmin isn't currently supported

Allow login to only CREATE/DROP any database

Server role dbcreator isn't currently supported

Allow login to only ALTER any login

Server role securityadmin isn't currently supported