Managing permissions and access control in Babelfish for Aurora PostgreSQL - Amazon Aurora
Supported use casesUnsupported use cases with the workaroundsUnsupported use cases
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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 Babelfish version compatibility

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

Add login to sysadmin server role

ALTER SERVER ROLE sysadmin ADD MEMBER login

None

All versions

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.

Version 3.4 and higher

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

Version 3.6 and higher, 4.2 and higher

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.

Version 1.2 and higher

Allow database user to do SELECTs/DMLs on an object

Grant permission to database user on object

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

None

All versions

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

Add user to db_owner fixed database role

ALTER ROLE db_owner ADD MEMBER user

Only users can be added db_owner fixed database role. Adding roles to db_owner role is not yet supported.

Version 4.5 and higher, 5.1 and higher

Allow user or members of a custom database role to do only SELECTs in a database

Add user or role to db_datareader fixed database role

ALTER ROLE db_datareader ADD MEMBER user / role

None

Version 4.5 and higher, 5.1 and higher

Allow user or members of a custom database role to do only DMLs in a database

Add user or role to db_datawriter fixed database role

ALTER ROLE db_datawriter ADD MEMBER user / role

None

Version 4.5 and higher, 5.1 and higher

Allow user or members of a custom database role to do only DDLs in a database

Add user or role to db_accessadmin fixed database role

ALTER ROLE db_accessadmin ADD MEMBER user / role

None

Version 4.5 and higher, 5.1 and higher

Allow user or members of a custom database role to only CREATE/ALTER/DROP custom roles, GRANT/REVOKE permissions on objects in a database and/or CREATE SCHEMA in a database

Add user or role to db_securityadmin fixed database role

ALTER ROLE db_securityadmin ADD MEMBER user / role

None

Version 4.5 and higher, 5.1 and higher

Allow user or members of a custom database role to only CREATE/ALTER/DROP any user, grant and revoke database access and map user accounts to logins and/or CREATE SCHEMA in a database

Add user or role to db_accessadmin fixed database role

ALTER ROLE db_accessadmin ADD MEMBER user / role

None

Version 4.5 and higher, 5.1 and higher

Allow login to only CREATE/DROP/ALTER any database

Add login to dbcreator fixed server role

ALTER SERVER ROLE dbcreator ADD MEMBER login

Only those databases can be altered on which dbcreator login has access.

Version 4.5 and higher, 5.1 and higher

Allow login to only CREATE/ALTER/DROP any login

Add login to securityadmin fixed server role

ALTER SERVER ROLE securityadmin ADD MEMBER login

None

Version 4.5 and higher, 5.1 and higher

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 Babelfish version compatibility for workarounds

Allow user to do SELECTs/DMLs on an object/schema along with option to GRANT these permissions to other users

GRANT the permissions to all the other users directly

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

GRANT ... WITH GRANT OPTION isn't currently supported.

Version 3.6 and higher, 4.2 and higher

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

Add members of role to db_owner fixed database role

ALTER ROLE db_owner ADD MEMBER user

Adding roles to db_owner role isn't currently supported.

Version 4.5 and higher, 5.1 and higher

Unsupported use cases

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

Use case Comments

Deny user or members of a custom database role to do SELECTs in a database

db_denydatareader fixed database role is not yet supported

Deny user or members of a custom database role to do DMLs in a database

db_denydatawriter fixed database role isn't currently supported.

Allow login to only KILL any database connection

processadmin fixed server role isn't currently supported.

Allow login to only add or remove linked servers

setupadmin fixed server role isn't currently supported.