Creating Users in Oracle 12c: Understanding Common and Local Users

Introduction

Oracle Database 12c introduced a revolutionary architecture called multitenant, which includes Container Databases (CDBs) and Pluggable Databases (PDBs). This architecture allows for easier management of multiple databases within a single instance. A critical aspect of working with Oracle 12c is understanding how to create and manage users in this new environment. Specifically, it’s important to distinguish between common users and local users.

Understanding CDB and PDB

Container Database (CDB)

• Definition: The root database that holds metadata for all pluggable databases.
• Components: Includes a seed PDB (PDB$SEED) which serves as the template for creating new PDBs, along with additional PDBs like ORCLPDB.
• Purpose: Manages and facilitates resource allocation across all pluggable databases.

Pluggable Database (PDB)

• Definition: An independent database within a CDB that can be plugged in or unplugged.
• Characteristics: Acts as a separate instance with its own users, schema, and security settings.
• Usage: Ideal for development, testing, or isolating applications.

User Types in Oracle 12c

Common Users

• Definition: Also known as container users, these belong to the CDB and are available across all PDBs.
• Creation Syntax: Use a double pound sign ## before the username (e.g., create user c##username identified by password;).
• Use Case: Useful for tasks that need to span multiple PDBs or manage configurations at the container level.

Local Users

• Definition: Users specific to a single PDB, isolated from other databases in the CDB.
• Creation Syntax: Standard user creation command (e.g., create user username identified by password;) executed within a session connected to a specific PDB.
• Use Case: Suitable for application-specific tasks and data encapsulation within individual PDBs.

Creating Users

Setting Up Your Environment

1. Check Database Version:

   • Use the query SELECT banner FROM v$version WHERE ROWNUM = 1; to ensure you are working with Oracle 12c or above.

2. Connect to a Pluggable Database:

   alter session set container = ORCLPDB;
   

3. Create Local User:

   • Connect as SYSDBA and switch to the desired PDB.
   • Use the following SQL statement:

     create user scott identified by tiger default tablespace users quota unlimited on users;
     

4. Unlock and Grant Privileges (if necessary):

   • Unlock the user if it exists but is locked:

     alter user scott account unlock;
     

   • Grant required privileges:

     grant connect, create table to scott;
     

Common User Creation

1. Connect to CDB$ROOT.
2. Use Special Syntax for Common Users:

   create user c##common_user identified by password default tablespace users quota unlimited on users;
   

3. Grant Necessary Privileges.

Best Practices and Considerations

• Avoid using undocumented parameters like _ORACLE_SCRIPT to bypass restrictions, as this can pose risks and potentially void support contracts.
• Always prefer local user creation for application-specific tasks unless a common user is necessary for cross-PDB operations.
• Regularly check Oracle documentation for updates on best practices in user management within CDBs and PDBs.

Conclusion

Understanding the distinction between common and local users is crucial when managing Oracle 12c environments. By following best practices, administrators can effectively manage security, resource allocation, and application isolation within a multitenant architecture.