TECHNOLOGY: Inside OCP

 

Multitenant Database Management

By Puneet Sangar

 

Manage users, roles, privileges, and resources in container databases and pluggable databases.

Oracle Database 12c consolidates multiple databases as pluggable databases (PDBs) in a container database (CDB). This column focuses on users, roles, privileges, and the Resource Manager feature of Oracle Database in the context of CDBs and PDBs. It presents sample questions on these topics similar to those you might encounter in the Upgrade to Oracle Database 12c and Oracle Database 12c: Advanced Administration exams. You can earn the Oracle Database 12c Administrator Certified Professional certificate by successfully completing either exam.

Users, Privileges, and Roles in PDBs and a CDB

A multitenant CDB with PDBs has two types of users: common and local. A common user is a user whose identity and password are known in the root container—the collection of schemas, schema objects, and nonschema objects to which all PDBs belong—and in every existing and future PDB. A local user is a database user that exists only in a single PDB.

A common user can log in to the root and any PDB in which it has privileges. The operations a common user can perform depend on the privileges granted to that user. Privileges themselves are neither common nor local. How privileges are applied depends on whether the privilege is granted commonly—that is, with the CONTAINER = ALL clause—or granted locally in a PDB. Common users can have different privileges in different PDBs. Some administrative tasks, such as creating or unplugging a PDB, can be performed only by a common user. All Oracle-supplied administrative user accounts, such as SYS and SYSTEM, are common users and can navigate throughout the CDB.

Multiple PDBs can have local users with the same name and credentials, but each local user is distinct. Local users can have administrative privileges, but those privileges apply only to the PDB in which the local user account was created.

A common role, like a common user, exists in the root container and in every existing and future PDB. Common roles are useful for cross-container operations. A common role can be either user-created or Oracle-supplied. User-created common roles must have names that start with C## or c##, and the names may contain only ASCII or EBCDIC characters. Common users can both create and grant common roles to other common users and to local users. A common role can be granted to a common user either commonly or locally in a PDB. If you grant a common role to a local user, the privileges of that common role will apply only to the local user’s PDB. A local user cannot create common roles.

Privileges granted commonly to a common role are applied in the root and in all PDBs to which the grantor can connect—including any PDBs that are added later—if the following conditions are met:

• Both the grantor and the grantee are common users.
• The grantor possesses the ADMIN option for the common role that was granted.
• The GRANT statement contains the CONTAINER = ALL clause.

For example, a SELECT ANY TABLE privilege granted commonly with the CONTAINER = ALL clause to the c##dba common user applies to this user in all PDBs.

A common user can be granted a common role, and this role can contain locally granted privileges. For example, the common role C##ADMIN can be granted a SELECT ANY TABLE privilege that is local to a PDB. If a common role contains locally granted privileges, those privileges will apply only within the PDB in which they were granted.

A local role cannot be granted commonly. PDBs in the same CDB may contain local roles with the same name. These roles are independent of each other, just as they would be in separate non-CDBs.

Which two statements are true about a common user in a multitenant database?

      a. A common user can be granted only common roles.

      b. A common user can own schema objects in PDBs.

      c. A common user can create objects in a PDB and make them accessible in all PDBs.

      d. A common user can be granted local roles in a PDB.

      e. A common user can own schema objects only in CDB$ROOT.

The correct answers are B and D. Answer B is correct because common users can create schema objects in a PDB if they have privileges to create schema objects in that PDB; those objects are created in that PDB only. Answer D is correct (and answer A is incorrect) because a common user can be granted local roles in a PDB. Answer C is incorrect because objects created by a common user in a PDB are available only in that PDB. Answer E is incorrect because common users can own schema objects in PDBs.

Examine the following commands, which create a user for an Oracle Database 12c multitenant CDB that contains multiple PDBs. The command is executed as user SYS connected to CDB$ROOT:

SQL> CREATE USER C##USER1 IDENTIFIED BY Orcl123;
SQL> CREATE ROLE c##role1 CONTAINER = ALL;

SQL> GRANT connect, resource to C##ROLE1;

SQL> GRANT C##ROLE1 to C##USER1;

Which three statements are correct in this scenario?

      a. The C##ROLE1 role is granted to user C##USER1 only in CDB$ROOT.

      b. The C##ROLE1 role is granted to user C##USER1 in all PDBs in the CDB.

      c. The C##ROLE1 role is created only in CD$ROOT.

      d. The C##ROLE1 role is created in all the PDBs in the CDB.

      e. The C##USER1 user can be granted only common roles.

      f. The C##USER1 user can be granted local roles in a PDB.

Answers A, D, and F are correct. Answer A is correct because the C##ROLE1 role is granted to the C##USER1 user only in CDB#ROOT—because the CONTAINER = ALL clause is missing in the GRANT. Answer D is correct because a common role is created in all the PDBs in a CDB. Answer F is correct (and answer E is incorrect) because a common user can be granted a local role, and that role is applicable in that PDB only. Answer B is incorrect because the CONTAINER = ALL clause is missing when the role is granted to the C##USER1 user.

Examine the following commands, which create a user for an Oracle Database 12c multitenant CDB that contains multiple PDBs. The command is executed as user SYS connected to CDB$ROOT:

SQL> CREATE USER C##ADMIN IDENTIFIED BY Orcl123;

SQL> GRANT connect, resource to C##ADMIN; 

Which two statements are true about the C##ADMIN user? 

      a. The user is created in CDB$ROOT and in all PDBs.

      b. The user is created only in CDB$ROOT.

      c. The user is granted CONNECT and RESOURCE roles only in CDB$ROOT.

      d. The user cannot be granted other privileges and roles in the PDBs.

      e. The user is created in all PDBs but will share a common schema created in CDB$ROOT.

The correct answers are A and C. Users with names that have a C## prefix are created as common users, and common users are created in CDB$ROOT and all PDBs. The roles granted to a common user are specific to a PDB, and in this case, they are granted only in CDB$ROOT. Answer B is incorrect because the user is created in CDB$ROOT and all PDBs. Answer D is incorrect because a common user can be granted roles and privileges in a PDB. Answer E is incorrect because each PDB will have a schema associated with the common user.

Resource Manager and PDBs

In a CDB with multiple PDBs, multiple workloads within multiple PDBs can compete for system and CDB resources. When resource allocation decisions for a CDB are left to the operating system, the following workload-management-related issues can arise:

• Inappropriate allocation of resources among PDBs. One or more PDBs might use a significant amount of system resources, leaving the other PDBs starved for resources.
• Inappropriate allocation of resources within a single PDB. One or more sessions connected to a single PDB might use a significant amount of system resources, leaving the other sessions that are connected to the same PDB starved for resources.
• Inconsistent performance. A single PDB might perform inconsistently when other PDBs are competing for system resources at various times.
• Lack of resource usage data for PDBs. Operating system monitoring tools can gather resource usage data for a single non-CDB running on a system. However, operating system monitoring tools are not as effective in monitoring a CDB, because there are multiple PDBs running in the CDB on the system.

Resource Manager helps address these issues by giving the CDB more control over how hardware resources are allocated among and within PDBs. In a CDB with multiple PDBs, some PDBs are typically more important than others. Resource Manager enables you to prioritize and limit the resource usage of specific PDBs.

In Oracle Database 12c, Resource Manager can be used to prioritize and limit resource usage for competing workloads within a PDB and also for competing PDBs in a CDB. Resource Manager works at two levels: the CDB level and the PDB level. At the CDB level, it prioritizes resources by limiting resource utilization for a PDB or PDBs in a CDB. At the PDB level, it manages the workloads within each PDB by limiting the resource utilization for a workload.

With a CDB-level resource plan, you can

• Specify shares of system resources so that more resources are allocated to more-important PDBs (By default, all PDBs have equal shares.)
• Limit the CPU usage of a particular PDB
• Limit the number of parallel execution servers a particular PDB can use
• Limit the resource usage of different sessions connected to a single PDB
• Monitor the resource usage of PDBs

A CDB-level resource plan specifies the number of resources allocated to each PDB. A PDB-level resource plan can be used to allocate resources among the consumer groups (a maximum of eight) within a PDB. The plan cannot have subplans or a multiple-level scheduling policy. A PDB-level resource plan is similar to a resource plan for a non-CDB. A PDB-level plan allocates resources among the consumer groups within a PDB from the shares of resources allocated to a PDB by a CDB-level plan. When you create one or more PDB-level resource plans and there is no CDB resource plan, the CDB uses the DEFAULT_CDB_PLAN supplied with Oracle Database.

Which two resource limits can be implemented for competing PDBs through a CDB-level resource plan?

      a. The amount of undo generated by users in a PDB

      b. The number of parallel execution servers for a PDB

      c. CPU usage for a PDB

      d. The number of concurrent sessions by a user

      e. The idle time for a session

Answers B and C are correct. CPU usage by a PDB and the number of parallel execution servers a particular PDB can use can be limited by a CDB-level resource plan. The limits in answers A and E can be implemented with a PDB-level resource plan. You can implement the limit in answer D by creating a profile and assigning it to a user.

Which two statements are true about a PDB-level resource plan?

      a. It can be used to allocate resources across all PDBs in a CDB.

      b. It can be used to limit the resource utilization for a workload in a PDB.

      c. If a PDB-level plan is enabled, Resource Manager will use resource allocation at the
          PDB-level plan and ignore the limits set at the CDB level.

      d. If a PDB-level plan is not enabled, Resource Manager will allocate equal resources to all
          sessions for that PDB.

      e. If no PDB plan is enabled for a PDB, Resource Manager will allocate resources based on the
          CDB-level plan.

The correct answers are B and D. Answer B is correct because the PDB-level plan can be used only to limit resource utilization for consumer groups within that PDB. Option D is correct because if no PDB-level plan is enabled, all sessions will be allocated resources equally. Answer A is incorrect because the PDB-level plan prioritizes only the workload within a particular PDB and not for other PDBs in a CDB. Answer C is incorrect because if the PDB-level plan is enabled, it will be able to use only the resources allocated to a PDB by the CDB-level plan. Answer E is incorrect because if no PDB-level plan is enabled, all the sessions for a PDB will be allocated equal shares of the resources allocated to that PDB by a CDB-level plan.

Conclusion

In the Oracle Database 12c multitenant architecture, a CDB with multiple PDBs shares a common instance and operating system resources and PDBs can be managed as standalone databases. And with the common and local users, roles, and privileges for CDBs and PDBs, database management activities can be performed seamlessly and transparently. Resource Manager in Oracle Database 12c, for example, provides options for allocating resources among PDBs and also for managing workloads within a PDB.

---

Puneet Sangar is a principal certification exam developer at Oracle. He has been with the company since 2006.

 

출처 : http://www.oracle.com/technetwork/issue-archive/2014/14-nov/o64ocp12c-2349447.html