Skip to content

Manage Users and Privileges in CDB and PDB

    In multitenant environments there are two types of user.

    • Common User : The user is present in all containers (root and all PDBs).
    • Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.

    The following example shows how to create common users with and without the CONTAINER clause from the root container.

    CONN / AS SYSDBA
    
    -- Create the common user using the CONTAINER clause.
    CREATE USER c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;
    GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;
    
    -- Create the common user using the default CONTAINER setting.
    CREATE USER c##test_user2 IDENTIFIED BY password1;
    GRANT CREATE SESSION TO c##test_user2;

    The following example shows how to create local users with and without the CONTAINER clause from the root container.

    CONN / AS SYSDBA
    
    -- Switch container while connected to a common user.
    ALTER SESSION SET CONTAINER = pdb1;
    
    -- Create the local user using the CONTAINER clause.
    CREATE USER test_user3 IDENTIFIED BY password1 CONTAINER=CURRENT;
    GRANT CREATE SESSION TO test_user3 CONTAINER=CURRENT;
    
    -- Connect to a privileged user in the PDB.
    CONN system/[email protected]
    
    -- Create the local user using the default CONTAINER setting.
    CREATE USER test_user4 IDENTIFIED BY password1;
    GRANT CREATE SESSION TO test_user4;

    The following example shows how to create a common role and grant it to a common and local user.

    CONN / AS SYSDBA
    
    -- Create the common role.
    CREATE ROLE c##test_role1;
    GRANT CREATE SESSION TO c##test_role1;
    
    -- Grant it to a common user.
    GRANT c##test_role1 TO c##test_user1 CONTAINER=ALL;
    
    -- Grant it to a local user.
    ALTER SESSION SET CONTAINER = pdb1;
    GRANT c##test_role1 TO test_user3;

    The following example shows how to create local a role and grant it to a common user and a local user.

    CONN / AS SYSDBA
    
    -- Switch container.
    ALTER SESSION SET CONTAINER = pdb1;
    
    -- Alternatively, connect to a local or common user
    -- with the PDB service.
    -- CONN system/[email protected]
    
    -- Create the common role.
    CREATE ROLE test_role1;
    GRANT CREATE SESSION TO test_role1;
    
    -- Grant it to a common user.
    GRANT test_role1 TO c##test_user1;
    
    -- Grant it to a local user.
    GRANT test_role1 TO test_user3;

    The basic difference between a local and common grant is the value used by the CONTAINER clause.

    -- Common grants.
    CONN / AS SYSDBA
    
    GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;
    GRANT CREATE SESSION TO c##test_role1 CONTAINER=ALL;
    GRANT c##test_role1 TO c##test_user1 CONTAINER=ALL;
    
    -- Local grants.
    CONN system/[email protected]
    GRANT CREATE SESSION TO test_user3;
    GRANT CREATE SESSION TO test_role1;
    GRANT test_role1 TO test_user3;