Which two statements are true about a common user?

And of course this is wrong…

Answer is B and D

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

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.

http://dbasolved.com/2013/06/29/common-user-vs-local-user-12c-edition/

A COMMON USER can perform administrative tasks that are specific to the CDB or PDB, such as plugging and unplugging a PDB. COMMON USERs are the only ones that can navigate between containers that belong to a CDB.

When creating a COMMON USER account there are specific rules that must be followed:

Must be connected to the root and have the commonly granted CREATE USER system privilege

The session’s current container must be CDB$ROOT

The user name must start with C## or c##

Explicitly designate a user account as a common user, specify the CONTAINER=ALL as part of the CREATE USER statement

Do not create objects in the schemas of common users.

If specifying the DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA… ON, and PROFILE clauses as part of CREATE USER; then you must ensure that those objects existing in all containers (PDB) below the CDB

User-created schema objects owned by COMMON USERS cannot be shared across PDB boundaries

B it depends if CREATE USER PRIVILEGE was granted common or not. I mean, if privilege is common, user can create users in local PDB.

I understood that. But all other options are wrong, except D. So they dont specify how create user was granted to common user. Its Oracle tricks for us to fail 🙂

BD

B. Confirm.

SQL> CREATE USER c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;
SQL> GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;

Grant succeeded.

SQL> grant create user to c##test_user1 CONTAINER=ALL;

Grant succeeded.

— connect to root and create commond user
SQL> conn c##test_user1/password1;
Connected.
SQL> CREATE USER c##test_user2 IDENTIFIED BY password1 CONTAINER=ALL;

User created.
— connect to pdb and create local user
SQL> conn c##test_user1/password1@pdborcl;
Connected.
SQL>
SQL> create user test identified by test;

User created.

SQL>