# Oracle and PostgreSQL security Security This section includes pages about Oracle and PostgreSQL security-related topics. **Topics** + [ # Oracle transparent data encryption and PostgreSQL encryption ](chap-oracle-aurora-pg.security.encryption.md) + [ # Oracle and PostgreSQL roles ](chap-oracle-aurora-pg.security.roles.md) + [ # Oracle database users and PostgreSQL users ](chap-oracle-aurora-pg.security.users.md) # Oracle transparent data encryption and PostgreSQL encryption With AWS DMS, you can securely migrate databases by encrypting data at rest using Oracle transparent data encryption or PostgreSQL encryption. Oracle transparent data encryption and PostgreSQL encryption are data-at-rest encryption solutions that protect sensitive data by encrypting database files, backups, and replicas. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-2.png) | N/A | N/A | Use [Amazon Aurora Encryption](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html) | ## Oracle usage Oracle data encryption is called Transparent Data Encryption (TDE). TDE encrypts the data that is saved in the tables or tablespaces and protects data stored on media (also called data at rest) in case this media or data files are stolen. Oracle uses authentication, authorization, and auditing mechanisms to secure data in the database but TDE is working on the operating system level. You don’t need to change from the application or client when encrypting data with TDE; the database manages it automatically. TDE doesn’t protect data in transit. Use the network encryption solutions discussed. + The user who wants to configure TDE needs `ADMINISTER KEY MANAGEMENT` system privilege. + Data can be encrypted at column level or tablespace level. + Key of encryption managed in external module is called TDE root encryption. + There is one root key store for each database. **Examples** To store the root encryption key, you can configure Oracle software keystore. Define at `sqlnet.ora` the `ENCRYPTION_WALLET_LOCATION` parameter to define where the keystore is. You can put to key file in: + Regular filesystem. + Multiple DBs shared the same file. + ASM filesystem. + ASM disk group. Register in `sqlinit.ora` to put key file in ASM disk group. ``` ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA= (DIRECTORY=+ASM_file_path_of_the_diskgroup))) ``` Create software keystores. Use one of the following types. + Password-based. + Auto-login. + Local auto-login. To create password-based software keystore, connect to a database with user that have `ADMINISTER KEY MANAGEMENT` or `SYSKM` privilege and then create the keystore. ``` sqlplus c##sec_admin as syskm Enter password: password Connected. ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password; keystore altered. ``` When you use the password-based software keystore, open the keystore before any TDE root encryption keys can be created or accessed in the keystore, auto-login and local auto-login are automatically opened (you can close them). Use the following query to open the keystore. ``` sqlplus c##sec_admin as syskm Enter password: password Connected. ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password; keystore altered. ``` Set the software root encryption key, the key is stored in the keystore, this key protects the TDE table keys and tablespace encryption keys. By default, the TDE root encryption key is a key that the TDE generates. To set the software root encryption key: + Make sure that the database is open in `READ WRITE` mode. + Connect with the user that has the right privileges and create the root key. ``` sqlplus c##sec_admin as syskm Enter password: password Connected. ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP USING 'emp_key_backup'; keystore altered. ``` Encrypt the data. The following data types support encryption: `BINARY_DOUBLE`, `BINARY_FLOAT`, `CHAR`, `DATE`, `INTERVAL DAY TO SECOND`, `INTERVAL YEAR TO MONTH`, `NCHAR`, `NUMBER`, `NVARCHAR2`, `RAW` (legacy or extended), `TIMESTAMP` (includes `TIMESTAMP WITH TIME ZONE` and `TIMESTAMP WITH LOCAL TIME ZONE`), `VARCHAR2` (legacy or extended). You can’t use column encryption with the following features: + Index types other than B-tree. + Range scan search through an index. + Synchronous change data capture. + Transportable tablespaces. + Columns used in foreign key constraints. To create table with encrypted column, use the following query. ``` CREATE TABLE employee ( FIRST_NAME VARCHAR2(128), LAST_NAME VARCHAR2(128), EMP_ID NUMBER, SALARY NUMBER(6) ENCRYPT); ``` You can change the algorithm that encrypts the data. The `NO SALT` option encrypts without the algorithm. The `USING` clause defines the algorithm that is used to encrypt data. ``` CREATE TABLE EMPLOYEE ( FIRST_NAME VARCHAR2(128), LAST_NAME VARCHAR2(128), EMP_ID NUMBER ENCRYPT NO SALT, SALARY NUMBER(6) ENCRYPT USING '3DES168'); ``` To change the algorithm, use the following query. ``` ALTER TABLE EMPLOYEE REKEY USING 'SHA-1'; ``` Stop encrypting column. ``` ALTER TABLE employee MODIFY (SALARY DECRYPT); ``` When you encrypt a tablespace, the TDE encrypts in the SQL layer so all the data types and indexes restrictions aren’t applied for tablespace encryption. + Make sure that COMPATIBLE initialization parameter is set to 11.2.0.0 (minimum). + Login to the database. + Create the tablespace, you can’t modify existing tablespace, only to create new one. In this example, the first TS created with AES256 algorithm and the second TS created with default algorithm. ``` sqlplus sec_admin@hrpdb Enter password: password Connected. CREATE TABLESPACE encrypt_ts DATAFILE '$ORACLE_HOME/dbs/encrypt_df.dbf' SIZE 1M ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT); CREATE TABLESPACE securespace_2 DATAFILE '/home/user/oradata/secure01.dbf' SIZE 150M ENCRYPTION DEFAULT STORAGE(ENCRYPT); ``` For more information, see [Introduction to Transparent Data Encryption](https://docs.oracle.com/en/database/oracle/oracle-database/19/asoag/introduction-to-transparent-data-encryption.html#GUID-62AA9447-FDCD-4A4C-B563-32DE04D55952) in the *Oracle documentation*. ## PostgreSQL usage Amazon provides the ability to encrypt data at rest (data stored in persistent storage). When you enable data encryption, it will automatically encrypt the database server storage, its automated backups, its read replicas and snapshots by using the AES-256 encryption algorithm. This encryption will be done by using [AWS KMS](http://docs.aws.amazon.com/kms/latest/developerguide/overview.html). Once enabled, Amazon will transparently encrypt/decrypt the data without any impact on performances or any user intervention, and there will be no need to set any additional modifications to your clients to support this encryption. ### Enable encryption As part of the database settings you will be asked to enable encryption and choose a root key. ![\[Enable Encryption\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-enable-encryption.png) You can choose the default key provided for the account or define a specific key based on an IAM AWS KMS ARN from your account or a different account. ### Create an encryption key **To create your own key** 1. Go to the AWS Key Management Service (KMS) console, choose **Customer managed keys** and create a new key. 1. Choose relevant options and then choose **Next**. 1. Enter **Alias** as the name of the key and choose **Next**. ![\[Enter Alias\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-enter-alias.png) 1. Skip **Define Key Administrative Permissions** and choose **Next**. 1. Assign the key to the relevant users who will need to interact with Aurora. 1. On the last step you can see the ARN of the key and its account. ![\[ARN of the key\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-key-arn.png) 1. Choose **Finish** and the key will be listed in under customer managed keys. Now you can set the root encryption key by using the ARN of the key that you have created or picking it from the list. Proceed with this operation and finish the instance launch. ### SSE-S3 encryption feature overview Server-side encryption (SSE) with Amazon S3-managed encryption keys (SSE-S3) uses a multi-factor encryption. Amazon S3 encrypts its objects with a unique key and in addition it also encrypts the key itself with a root key that rotates periodically. SSE-S3 uses AES-256 as its encryption standard. After the Amazon S3 bucket was enabled with Server-side encryption, the data will be encrypted at rest, meaning that from this stage, any API call will have to include the special `x-amz-server-side-encryption` header. Additionally, the AWS command line tool will also need to be added with the `--sse` switch. For more information, see [Specifying Amazon S3 encryption](https://docs.aws.amazon.com/AmazonS3/latest/userguide/specifying-s3-encryption.html) in the *Amazon Simple Storage Service user guide* and [s3](https://docs.aws.amazon.com/cli/latest/reference/s3/) in the *CLI Command Reference*. **Enable SSE-S3** 1. Sign in to the AWS Glue console. 1. Create an AWS Glue job. 1. Define the role, bucket, and the script to use. 1. Enable Server-Side Encryption. 1. Submit the job and run it. From this point, you will notice that the only way to access the files will be by using AWS CLI Amazon S3 along with the `--sse` switch, or by adding `x-amz-server-side-encryption` to your API calls. # Oracle and PostgreSQL roles With AWS 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. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | N/A | N/A | Syntax and option differences, similar functionality. There are no users, only roles in PostgreSQL. | ## 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 roles** — Roles created at the container database (CDB) level. A common role is a database role that exists in the root and in every existing and future pluggable database (PDB). Common roles are useful for cross-container operations such as ensuring a common user has a role in every container. + **Local roles** — Roles created in a specific pluggable database (PDB). A local role exists only in a single pluggable database and can only contain roles and privileges that apply within the pluggable database in which the role exists. Common role names must start with a c\$1\$1 prefix. Starting with Oracle 12.1.0.2, these prefixes can be changed using the COMMON\$1USER\$1PREFIX 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-privilege-and-role-authorization.html#GUID-89CE989D-C97F-4CFD-941F-18203090A1AC) 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 are defined at the database cluster level and are valid in all databases in the PostgreSQL cluster. In terms of database scope, roles in PostgreSQL can be compared to common roles in Oracle 12c as they are global for all the databases and are not created in the individual scope of each database. + The `CREATE USER` command in PostgreSQL is an alias for the `CREATE ROLE` command with one important difference: when using `CREATE USER` command, it automatically adds `LOGIN` so the role can access to the database as a database user. As such, for creating PostgreSQL roles that are similar in function to Oracle roles, be sure to use the `CREATE ROLE` command. Roles with connect permissions are essentially database users. + A role is a database entity that can own objects and have database privileges. + A role can be considered a user, a group, or both depending on how it is used. + Roles are defined at the root level and are valid in all databases in the Amazon Aurora cluster. In terms of database scope, roles in PostgreSQL can be compared to common users in Oracle 12c as they are global for all the databases and are not created in the individual scope of a specific database. + Schemas are created separately from roles/users in PostgreSQL. | Oracle | PostgreSQL | | --- | --- | | Common database user (12c) | Database role with Login | | Local database user (12c) | N/A | | Database user (11g) | Database role with Login | | Database role | Database role without Login | | Database users are identical to schema | Database users and schemas are created separately | 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 | Description | Oracle | PostgreSQL | | --- | --- | --- | | List all roles | 
SELECT * FROM dba_roles;
| 
SELECT * FROM pg_roles;
| | Create a new role | 
CREATE ROLE c##common_role;
or
CREATE ROLE local_role1;
| 
CREATE ROLE test_role;
| | Grant one role privilege to another database role | 
GRANT local_role1 TO local_role2;
| 
grant myrole1 to myrole2;
| | Grant privileges on a database object to a database role | 
GRANT CREATE TABLE
  TO local_role;
| 
GRANT create
  ON DATABASE postgresdb
  to test_user;
| | Grant DML permissions on a database object to a role | 
hr.employees to myrole1;
| 
GRANT INSERT, DELETE
  ON hr.employees
  to myrole1;
| | List all database users | 
SELECT * FROM dba_users;
| 
SELECT * FROM pg_user;
| | Create a database user | 
CREATE USER c##test_user
IDENTIFIED BY test_user;
| 
CREATE ROLE test_user
WITH LOGIN
PASSWORD 'test_user';
| | Change the password for a database user | 
ALTER USER c##test_user
IDENTIFIED BY test_user;
| 
ALTER ROLE test_user
WITH LOGIN
PASSWORD 'test_user';
| | External authentication | Supported via Externally Identified Users | Currently not supported; future support for AWS Identity and Access Management (IAM) users is possible | | Tablespace quotas | 
Alter User c##test_user QUOTA
UNLIMITED ON TABLESPACE users;
| Not supported | | Grant role to user | 
GRANT my_role TO c##test_user;
| 
GRANT my_role TO test_user;
| | Lock user | 
ALTER USER c##test_user ACCOUNT
LOCK;
| 
ALTER ROLE test_user WITH
NOLOGIN;
| | Unlock user | 
ALTER USER c##test_user ACCOUNT
UNLOCK;
| 
ALTER ROLE test_user WITH LOGIN;
| | Grant privileges | 
GRANT CREATE TABLE TO c##test_user;
| 
GRANT create
  ON DATABASE postgres
  to test_user;
| | Default tablespace | 
ALTER USER C##test_user default
tablespace users;
| 
ALTER ROLE test_user SET default_
tablespace = 'pg_global';
| | Grant select privilege on a table | 
GRANT SELECT
  ON hr.employees
  to c##test_user;
| 
GRANT SELECT
  ON hr.employees
  to test_user;
| | Grant DML privileges on a table | 
GRANT INSERT,DELETE
  ON hr.employees
  to c##test_user;
| 
GRANT INSERT,DELETE
  ON hr.employees
  to test_user;
| | Grant execute | 
GRANT EXECUTE
  ON hr.procedure_name
  to c##test_user;
| 
grant execute
  on function "newdate"()
  to test_user;
Specify the arguments types for the function inside the brackets. | | Limits user connection | 
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 a new database schema | 
CREATE USER my_app_schema
  IDENTIFIED BY password;
| 
CREATE SCHEMA my_app_schema;
| For more information, see [CREATE ROLE](https://www.postgresql.org/docs/13/sql-createrole.html) in the *PostgreSQL documentation*. # Oracle database users and PostgreSQL users With AWS DMS, you can migrate data from Oracle and PostgreSQL databases to Amazon Aurora. Database users are accounts that control authentication and authorization for a specific database instance. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | N/A | N/A | N/A | ## Oracle usage Database user accounts are used for authenticating connecting sessions and authorizing access for individual users to specific database objects. Database Administrators grant privileges to user accounts, and applications use user accounts to access database objects. ### Steps for providing database access to applications 1. Create a user account in the database. User accounts are typically authenticated using a password. Additional methods of authenticating users also exist. 1. Assign permissions to the database user account enabling access to certain database objects and system permissions. 1. Connecting applications authenticate using the database username and password. ### Oracle database users common properties + Granting privileges or roles (collection of privileges) to the database user. + Defining the default database tablespace for the user. + Assigning tablespace quotas for the user. + Configuring password policy, password complexity, lock, or unlock the account. ### Authentication mechanisms + **Username and Password** — Used by default. + **External** — Using the operating system or third-party software (such as Kerberos). + **Global** — Enterprise directory service (such as Active Directory or Oracle Internet Directory). ### Oracle schemas compared to users In an Oracle database, a user equals a schema. This relationship is special because users and schemas are essentially the same thing. Consider an Oracle database user as the account you use to connect to a database while a database schema is the set of objects (tables, views, and so on) that belong to that account. + You can’t create schemas and users separately. When you create a database user, you also create a database schema with the same name. + When you run the `CREATE USER` command in Oracle, you create a user for login and a schema in which to store database objects. + Newly created schemas are empty, but objects such as tables can be created within them. ### Database users in Oracle 12c Two types of users exist in the Oracle 12c database: + **Common Users** — Created in all database containers, root, and Pluggable Databases (PDB). Common users must have the C\$1\$1 prefix in the username. + **Local Users** — Created only in a specific PDB. Different database users with identical usernames can be created in multiple PDBs. **Examples** The following example demonstrates the following operations: + Create a common database user using the default tablespace. + Grant privileges and roles to the user. + Assign a profile to the user, unlock the account, and force the user to change the password (`PASSWORD EXPIRE`). + Create a local database user in the `my_pdb1` pluggable database. ``` CREATE USER c##test_user IDENTIFIED BY password DEFAULT TABLESPACE USERS; GRANT CREATE SESSION TO c##test_user; GRANT RESOURCE TO c##test_user; ALTER USER c##test_user ACCOUNT UNLOCK; ALTER USER c##test_user PASSWORD EXPIRE; ALTER USER c##test_user PROFILE ORA_STIG_PROFILE; ALTER SESSION SET CONTAINER = my_pdb1; CREATE USER app_user1 IDENTIFIED BY password DEFAULT TABLESPACE USERS; ``` For more information, see [Managing Security for Oracle Database Users](https://docs.oracle.com/database/121/DBSEG/users.htm) in the *Oracle documentation*. ## PostgreSQL usage In PostgreSQL there are no users, only roles, role with connect privilege can be considered as a user. For more information, see [PostgreSQL Roles](chap-oracle-aurora-pg.security.roles.md#chap-oracle-aurora-pg.security.roles.pg).