Close Menu

    Manage users and privileges in Amazon RDS Custom for Oracle with Multitenant option

    Amazon Relational Database Service (Amazon RDS) Custom for Oracle is a managed database service for legacy, custom, and packaged applications that require access to the underlying operating system and database environment. Amazon RDS Custom for Oracle supports the Oracle Multitenant option on Oracle Database version 19c using Enterprise Edition and Standard Editions. With this release, an RDS Custom for Oracle database (DB) instance on Enterprise edition can operate as a multitenant container database (CDB) hosting one or more pluggable databases (PDBs). However, Oracle allows a maximum of three PDBs in Standard Edition. A PDB is a set of schemas, schema objects, and non-schema objects that logically appears to a client as a regular database.

    Oracle Multitenant feature is available in Oracle database from 12cR1 (12.1.0.1) and later. This enables customers to use multiple PDBs in a single Oracle database, facilitating better manageability and consolidation of environments. In Oracle Multitenant architecture, there are various user management approaches available that can be used to create and manage user accounts in the container database (CDB) and PDBs. In this post we discuss the options for managing users and how they can be set up and used for different scenarios.

    Common user accounts

    Users who exist in all current and future containers can navigate across them. Common users are created from the root and are automatically replicated in each PDB except the seed PDB. Common users can connect to any PDB, provided they’re given the appropriate privileges in each PDB. The name assigned to a common user begins with the characters specified by the COMMON_USER_PREFIX initialization parameter, which is C## by default.

    When working with multi-tenant databases in AWS, it’s crucial to be aware of two AWS-owned accounts: C##RDSADMIN and C##RDS_DATAGUARD. These accounts play vital roles in database management.

    C##RDSADMIN Account

    This account is used by Amazon RDS for administrative purposes in Oracle databases. It’s a common user account that RDS creates and manages automatically. The main purposes of this account include:

    • Performing database management tasks
    • Executing maintenance operations
    • Applying patches and updates
    • Monitoring database health and performance
    • Managing backups and restores

    C##RDS_DATAGUARD Account

    This account is specifically related to Oracle Data Guard functionality in Amazon RDS. Oracle Data Guard is a feature that provides high availability, data protection, and disaster recovery for Oracle databases. The main purposes of this account include:

    • Managing Data Guard configurations
    • Facilitating standby database creation and maintenance
    • Handling switchover and failover operations
    • Monitoring and maintaining Data Guard-related processes

    It’s important to note that this account has elevated privileges necessary for RDS to manage the database effectively and should be handled with care:

    • Do not modify privileges: Refrain from altering the privileges associated with these accounts.
    • Do not drop accounts: Under no circumstances should these accounts be dropped from the database.
    • Logon trigger considerations: If implementing logon triggers, make sure to exclude these AWS-owned accounts from the trigger’s scope.
      Code snippet for logon trigger 
      CREATE OR REPLACE TRIGGER user_logon_trigger
AFTER LOGON ON DATABASE
BEGIN
  -- Check if the connecting user is not one of the RDS managed accounts
  IF USER NOT IN ('-C##RDSADMIN-', '-C##RDS_DATAGUARD-') THEN
    -- Your custom logon logic goes here
    -- For example, you might want to:
    --   - Log connection attempts
    --   - Restrict connections based on time of day
    --   - Set session parameters
    --   - Perform any other custom checks or actions

    -- Example: Log connection attempts
    INSERT INTO connection_log (username, login_time, ip_address)
    VALUES (USER, SYSTIMESTAMP, SYS_CONTEXT('USERENV', 'IP_ADDRESS'));

    -- Example: Restrict connections outside business hours
    IF TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN '09' AND '17' THEN
      RAISE_APPLICATION_ERROR(-20000, 'Connections are only allowed during business hours (9 AM to 5 PM)');
    END IF;

    -- Add any other custom logic here

  END IF;
END;
/
    • Do not change the password for management accounts: Passwords are rotated periodically for accounts such as SYS, SYSTEM, RDSADMIN, and RDS_DATAGUARD.
    • Do not drop DBA roles: It’s critical to maintain the DBA roles associated with these accounts, as they are essential for proper database management and operations.

    Adhering to these guidelines helps maintain the integrity of your multi-tenant database and prevents potential disruptions to AWS-managed functionalities.
    Each container in a CDB holds common and local users. Any user, common or local, can only exercise the granted privileges inside the specific container to which the user is connected.

    The purpose of the following code block is to illustrate how to create a common user with broad privileges across all containers in a multitenant database. It demonstrates the process of user creation, privilege granting, and verification of the user’s presence across different containers. This example is particularly useful for understanding how common users are managed in Oracle’s multitenant architecture.

    SQL> create user c##cdbuser1 identified by Amazon123 container=all;
User created.
SQL> grant create session to c##cdbuser1 container=all;
Grant succeeded.
SQL> grant dba to c##cdbuser1 container=all;
Grant succeeded.
SQL> grant select any dictionary to c##cdbuser1 container=all;
Grant succeeded.
SQL> select username, common, con_id from cdb_users where username like 'C##CDBUSER1%';
 USERNAME                  COM     CON_ID
------------------------- --- ----------
C##CDBUSER1                  YES          4
C##CDBUSER1                  YES          1
C##CDBUSER1                  YES          3
3 rows selected.

    Local user accounts

    Local users are users that exist only within a single PDB, which is the one where they are created. Local users are created in a PDB they need to access and can only connect to that PDB They’re not visible to other PDBs of the same CDB. When you create a user in a pluggable database, it exists only in that specific PDB.

    When creating a local user, the following requirements must all be met.

    • You must be connected to a user with the CREATE USER privilege.
    • The username for the local user must not be prefixed with “C##” or “c##“.
    • The username must be unique within the PDB.
    • You can either specify the CONTAINER=CURRENT clause, or omit it, as this is the default setting when the current container is a PDB.
    • A local user must use Oracle Net service while connecting to the PDB. This only applies to Amazon Elastic Compute Cloud (Amazon EC2) instances because RDS Oracle provides the Oracle DB instance endpoint for connectivity using a SQL interface. While self-managed Oracle databases require the use of Oracle Net services for local connections to PDBs, both RDS and RDS Custom for Oracle typically allow connections through the provided endpoint without requiring manual Oracle Net configuration. However, the flexibility of RDS Custom means that in some highly customized scenarios, Oracle Net services might come into play.

    To create a local user in RDS Custom for Oracle:

    1. Connect to the EC2 instance
    2. Connect to the corresponding PDB using either of the following methods:
      1. Connect directly to the PDB 
        $ sqlplus sys/*********@localhost:1521/pdb1.ec2.internal as sysdba
SQL> show con_name
CON_NAME
------------------------------
PDB1


$ sqlplus / as sysdba
SQL> show pdbs                                                      
    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
 
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> connect sys/*****@localhost: 1521/pdb1.ec2.internal as sysdba
Connected.
      2. Connect to the root and run ALTER SESSION 
        ALTER SESSION SET CONTAINER = pdb1;

SQL> show con_name
CON_NAME
------------------------------
PDB1
    3. Create the user using CREATE USER syntax 
      SQL> create user PDB1AKS identified by Oracle;
User created.
SQL> grant DBA to PDB1AKS;
Grant succeeded.
SQL> select username from dba_users where username like 'PDB%';
USERNAME
--------------------------------------------------------------------------------
PDB1AKS
PDBADMIN
[oracle@ip-10-1-1-31 ~]$ sqlplus PDB1AKS/Oracle@localhost:1521/pdb1.ec2.internal
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> create user PDB1AKS2 identified by Oracle;
User created.
SQL> grant DBA to PDB1AKS2;
Grant succeeded.

      You cannot use local user from one PDB to connect to another PDB. The user is recognized in the PDB where it was created but not in another PDB. The following query lists usernames, the ID of the PDB they were created in, the PDB name, and the date when the user was created. We are filtering usernames starting with the letters PDB. Observe that both PDB1 and PDB2 have a local user named PDB2AKS and that those users are distinct from each other.

      SQL> select USERNAME,a.CON_ID,PDB_NAME,CREATED from CDB_USERS a ,dba_pdbs b where b.PDB_ID=a.CON_ID and username like 'PDB%';
USERNAME CON_ID PDB_NAME CREATED
------------------------- ---------- --------------- -------------------
PDB1AKS 3 PDB1 06-06-2021 22:11:18
PDB1AKS2 3 PDB1 06-06-2021 22:20:09
PDBADMIN 3 PDB1 25-05-2021 07:44:58
PDB2AKS 3 PDB1 06-06-2021 22:30:03
PDB2 4 PDB2 26-05-2021 04:50:37
PDB2AKS 4 PDB2 25-05-2021 19:56:28
6 rows selected.

    Managing common and local Roles

    Each container in a CDB holds common and local roles.

    • Common roles are created from the root and are automatically replicated in each PDB except the seed PDB. The name assigned to a common role must start with c##.
    • Common roles can be granted commonly: The grant operation is replicated in each PDB except the seed PDB.
    • Common roles can be granted locally: The grant operation is performed in the container where the operation takes place.

    Common roles

    To create a common role in RDS Custom for Oracle use the following code.

    SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create role c##r1 container=all;
Role created.
SQL> grant create session to c##r1;
Grant succeeded.
 
SQL> col ROLE format a9
set lines 100
col NAME format a20
select a.role, a.common, a.con_id , b.name from cdb_roles a , v$containers b where a.con_id=b.con_id and role='C##R1';
ROLE      COM     CON_ID NAME
--------- --- ---------- --------------------
C##R1     YES          3 PDB1
C##R1     YES          4 PDB2
C##R1     YES          1 CDB$ROOT

    Local roles

    Local roles are created in a PDB they need to access. Local roles can be granted locally only in the PDB where they are created. They are not visible to the other PDBs of the same CDB.

    Create a local role in a PDB using the following code.

    SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL>
SQL> create role pdb1_test_role;
Role created.
SQL> grant connect to pdb1_test_role;
Grant succeeded.
SQL> grant pdb1_test_role to C##CDBUSER1;
Grant succeeded.
You cannot create Common Role inside a PDB
SQL> create role C##pdb1_test ;
create role C##pdb1_test          *
ERROR at line 1:
ORA-65094: invalid local user or role name

    Managing common and local Privileges

    Common privilege is required for administrative tasks such as creating new PDBs or managing backups that need to be consistent across all PDBs. Local privilege is needed for regular database operations, such as managing schema objects, executing procedures, or querying data within a specific PDB. The basic difference between a local and common grant is the value used by the CONTAINER clause. You can grant common and local privileges to common and local users and roles. The privileges become common or local based on how they’re granted. Privileges are common when they’re granted with the CONTAINER=ALL clause. When granting permissions in a multi-tenant database, the permissions are always granted locally, by default. This means that, when you’re connected to the root container, if you don’t specify the CONTAINER clause, CONTAINER=CURRENT is the default behavior. In the root container, the CONTAINER clause can be CURRENT or ALL, with default being CURRENT.

    Common privileges are automatically granted to the common grantee (user or role) in each PDB except the seed PDB. Local privileges are granted to a grantee (user or role) in a specific PDB.

    The following SQL code shows how Oracle’s multitenant architecture handles privileges for common users across different containers, demonstrating both common and local privilege assignments in a multitenant database environment.

    SQL> col grantee format a15
SQL> select grantee, privilege, common, con_id from cdb_sys_privs where privilege='CREATE SESSION' and grantee like 'C##%';
GRANTEE         PRIVILEGE                                COM     CON_ID
--------------- ---------------------------------------- --- ----------
C##R1           CREATE SESSION                           NO           1
C##CDBUSER1        CREATE SESSION                           YES          1
C##CDBUSER1        CREATE SESSION                           YES          3
C##CDBUSER1       CREATE SESSION                           YES          4

    Granting a privilege as a common privilege to a local user is not allowed.

    The following SQL code illustrates the separation and management of local users and privileges within Oracle’s multitenant architecture, emphasizing the distinction between local and common privileges in PDBs.

    SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> create user HR identified by Amazon123;
User created.
SQL> grant create session to HR container=ALL;
grant create session to HR container=ALL
ERROR at line 1:
ORA-65029: a Local User may not grant or revoke a Common Privilege or Role
 
SQL> grant create session to HR;
Grant succeeded.
 
SQL> select grantee, privilege, common, con_id from cdb_sys_privs where privilege='CREATE SESSION' and grantee='HR';
GR PRIVILEGE      COM     CON_ID
-- -------------- --- ----------
HR CREATE SESSION NO           3

    Kerberos authentication

    Kerberos authentication can be added to provide a secure and efficient way of authenticating user accounts in a multitenant architecture. Kerberos authentication works by using a centralized authentication server to issue and validate tickets that are used for authentication.

    To enable Kerberos authentication for the preceding scenarios, you can follow the steps in Enable Kerberos authentication with Amazon RDS Custom for Oracle .

    This will show you how to enable Kerberos authentication with RDS Custom for Oracle.

    Best practices for user management

    When it comes to user management in Oracle Multitenant architecture, there are several best practices to follow to ensure proper security, access control, and efficient administration. The following recommended best practices for common users.

    1. Limit the number of common users: Keep the number of common users to a minimum, because common users have access to all PDBs in the CDB. Only create common users for administrative and system-level tasks.
    2. Assign appropriate common privileges: Grant common users only the privileges they need to perform their administrative duties. Avoid granting unnecessary common privileges.
    3. Secure common user accounts: Ensure that common user accounts have strong passwords and follow security best practices, such as regular password changes and account monitoring.
    4. Audit common user activities: Implement thorough auditing and monitoring for all activities performed by common users to ensure accountability and detect any suspicious activities.

    Best practices for local users

    In an Oracle Multitenant architecture, effective management of local users is crucial for maintaining security, performance, and operational efficiency. Local users, which exist only within specific PDBs, require careful consideration in terms of their creation, privilege assignment, and ongoing administration. These best practices provide a comprehensive framework for managing local users in a way that balances security requirements with operational needs, while adhering to the principle of least privilege. Following these guidelines helps organizations maintain robust security controls, ensure compliance, and optimize database operations within their PDB environments.

    The following best practices outline key considerations and recommendations for managing local users effectively:

    1. Create local users for PDB-specific tasks: Use local users for all regular database operations and tasks that are specific to a particular PDB.
    2. Assign appropriate local privileges: Grant local users only the privileges they need to perform their tasks within the PDB. Avoid granting unnecessary local privileges.
    3. Separate duties between local users: Establish clear separation of duties between local users to implement the principle of least privilege and reduce the risk of unauthorized access or data manipulation.
    4. Manage local user accounts: Regularly review and manage local user accounts, including disabling or removing accounts that are no longer needed.
    5. Implement password policies: Enforce strong password policies for local user accounts, including regular password changes and account lockout mechanisms.
    6. Audit local user activities: Implement auditing and monitoring for local user activities to ensure accountability and detect any suspicious behavior.
    7. Use application users instead of direct logins: Whenever possible, use application users instead of direct user logins to access the database. This helps maintain better control and separation of concerns.
    8. Use PDB-level roles: Consider creating PDB-level roles to group and manage privileges for specific tasks or user groups within a PDB.
    9. Use PDB-level resource plans: Use PDB-level resource plans to manage and control resource usage by local users within a PDB.

    Conclusion

    User accounts and privileges play a crucial role in helping to ensure the data security and access control in a multitenant architecture in Oracle Database. Depending on the specific scenario, different approaches can be used to create user accounts and grant privileges. As always, it’s essential to follow the best practices and guidelines to help ensure security and compliance.

    If you have any comments or questions, leave them in the comment section.

    About the authors

    Arnab Saha is a Senior Database Specialist Solutions Architect at AWS, based in Seattle, USA. Arnab specializes in Amazon RDS, Amazon Aurora, AWS Database Migration Service (DMS), and Amazon Elastic Block Store (EBS). He provides expert guidance and technical assistance to customers, helping them build scalable, highly available, and secure solutions in the AWS Cloud. Arnab also supports AWS partners and customers in their data modernization and cloud migration journeys.

    Balaji Salem Balasundram is a Technical Account Manager Based in Salt Lake City, USA. He works with AWS customers to migrate and achieve higher flexibility, scale, and resiliency with database services in AWS cloud.

    Sharath Chandra Kampili is a Database Specialist Solutions Architect with Amazon Web Services. He works with AWS RDS team, focusing on commercial database engines like Oracle. Sharath works directly with AWS customers to provide guidance and technical assistance on the database projects, helping them improve the value of their solutions when using AWS.

    Source: Read More

    Related Posts

    Leave A Reply