Oracle and PostgreSQL roles

With Amazon DMS, you can manage database user roles and permissions when migrating data from Oracle or PostgreSQL databases. Database roles define the privileges and access control for database users, specifying which operations they can perform on database objects like tables, views, and stored procedures.

Oracle usage

Oracle roles are groups of privileges granted to database users. A database role can contain individual system and object permissions as well as other roles. Database roles enable you to grant multiple database privileges to users in one operation. It is convenient to group permissions together to ease the management of privileges.

Oracle 12c introduces a new multi-tenant database architecture that supports the creation of both common and local roles:

Common role names must start with a c## prefix. Starting with Oracle 12.1.0.2, these prefixes can be changed using the COMMON_USER_PREFIX parameter.

A CONTAINER clause can be added to CREATE ROLE statement to choose the container applicable for the role.

Examples

Create a common role.

show con_name

CON_NAME
CDB$ROOT

CREATE ROLE c##common_role;

Role created.

Create a local role.

show con_name

CON_NAME
ORCLPDB

CREATE ROLE local_role;

Role created.

Grant privileges and roles to the local_role database role.

GRANT RESOURCE, ALTER SYSTEM, SELECT ANY DICTIONARY TO local_role;

Database users to which the local_role role is granted now have all privileges that were granted to the role.

Revoke privileges and roles from the local_role database role.

REVOKE RESOURCE, ALTER SYSTEM, SELECT ANY DICTIONARY FROM local_role;

For more information, see Configuring Privilege and Role Authorization in the Oracle documentation.

PostgreSQL usage

In PostgreSQL, roles without login permissions are similar to database roles in Oracle. PostgreSQL roles are most similar to common roles in Oracle 12c as they are global in scope for all the databases in the instance.

Roles with connect permissions are essentially database users.

The CREATE USER command in PostgreSQL is an alias for the CREATE ROLE command with one important difference: the CREATE USER command it automatically adds the LOGIN argument so that the role can access the database and act as a database user.

Examples

Create a new database role called myrole1 that will allow users (to which the role is assigned) to create new databases in the PostgreSQL cluster. Note that this role will not be able to login to the database and act as a database user. In addition, grant SELECT, INSERT, and DELETE privileges on the hr.employees table to the role.

CREATE ROLE hr_role;
GRANT SELECT, INSERT,DELETE on hr.employees to hr_role;

Typically, a role being used as a group of permissions would not have the LOGIN attribute, as with the preceding example.

Create a role that can log in to the database and specify a password.

CREATE USER test_user1 WITH PASSWORD 'password';

CREATE ROLE test_user2 WITH LOGIN PASSWORD 'password';

CREATE USER is identical to CREATE ROLE, except that it implies a login to the database.

When you provision a new Amazon Aurora cluster, a root user is created as the most powerful user in the database.

Create a role that can log in to the database and assign a password that has an expiration date.

CREATE ROLE test_user3 WITH LOGIN PASSWORD 'password' VALID UNTIL '2018-01-01';

Create a powerful role db_admin that provides users with the ability to create new databases. This role will not be able to log in to the database. Assign this role to the test_user1 database user.

CREATE ROLE db_admin WITH CREATEDB;

GRANT db_admin TO test_user1;

Create a new hello_world schema and create a new table inside that schema.

CREATE SCHEMA hello_world;

CREATE TABLE hello_world.test_table1 (a int);

Summary

SELECT * FROM dba_roles;

SELECT * FROM pg_roles;

CREATE ROLE c##common_role;
or
CREATE ROLE local_role1;

CREATE ROLE test_role;

GRANT local_role1 TO local_role2;

grant myrole1 to myrole2;

GRANT CREATE TABLE
  TO local_role;

GRANT create
  ON DATABASE postgresdb
  to test_user;

hr.employees to myrole1;

GRANT INSERT, DELETE
  ON hr.employees
  to myrole1;

SELECT * FROM dba_users;

SELECT * FROM pg_user;

CREATE USER c##test_user
IDENTIFIED BY test_user;

CREATE ROLE test_user
WITH LOGIN
PASSWORD 'test_user';

ALTER USER c##test_user
IDENTIFIED BY test_user;

ALTER ROLE test_user
WITH LOGIN
PASSWORD 'test_user';

Alter User c##test_user QUOTA
UNLIMITED ON TABLESPACE users;

GRANT my_role TO c##test_user;

GRANT my_role TO test_user;

ALTER USER c##test_user ACCOUNT
LOCK;

ALTER ROLE test_user WITH
NOLOGIN;

ALTER USER c##test_user ACCOUNT
UNLOCK;

ALTER ROLE test_user WITH LOGIN;

GRANT CREATE TABLE TO c##test_user;

GRANT create
  ON DATABASE postgres
  to test_user;

ALTER USER C##test_user default
tablespace users;

ALTER ROLE test_user SET default_
tablespace = 'pg_global';

GRANT SELECT
  ON hr.employees
  to c##test_user;

GRANT SELECT
  ON hr.employees
  to test_user;

GRANT INSERT,DELETE
  ON hr.employees
  to c##test_user;

GRANT INSERT,DELETE
  ON hr.employees
  to test_user;

GRANT EXECUTE
  ON hr.procedure_name
  to c##test_user;

grant execute
  on function "newdate"()
  to test_user;

CREATE PROFILE app_users
  LIMIT SESSIONS_PER_USER 5;
ALTER USER C##TEST_USER
  PROFILE app_users;

ALTER ROLE test_user WITH
CONNECTION LIMIT 5;

CREATE USER my_app_schema
  IDENTIFIED BY password;

CREATE SCHEMA my_app_schema;

For more information, see CREATE ROLE in the PostgreSQL documentation.