Oracle 12c introduces two different types of database users: LOCAL users and COMMON users. Lean to distinguish between local and common users, why common users always start with a C## prefix and the database scope for each type of user
- [Instructor] Oracle 12c introduces two types of users as part of the multitenant architecture. These are common users and local users. In previous, non-multitenant Oracle architectures, such as with Oracle 11g, we only had one type of data base users. The differences between common and local users in 12c is pretty straightforward. Let us start with local users. A local user is a user account that was created inside a specific PDB.
It is unique and local to that specific PDB, and contained inside that PDB only. So, for example, if we create a my user user, in PDB number one, that user will be stored in PBD number one, and will have scope over PDB number one only. We can also create another user, but with the same name, in PDB number two. We now have two different users, but with the same user name.
How come? Well, each is local to its own PDB. These users share nothing. And the name space for each user account is at the level of the PDB where it was created. Each user will have permissions on objects inside the PDB in which it was created. So these are local users. And as you can see, it's quite simple. However, we also have a new concept of common users in Oracle 12c container CDB data base.
When connected to the root container of the CDB data base you cannot create normal user account. The command will actually fail. That's because users created when connected to the root container are special users known as common users. And these users need to use a special naming conventions. This is important. In a multitenant Oracle environment, a common user is a data base user whose identity and password are known in the root, and in every existing and future pluggable data base inside our CDB.
Meaning, that a common user can connect to the root container and perform operations, but also, because a common user is known to all PDBs in the system, if we grant that common user the appropriate permissions in our PDBs, then that user will be able to perform operation in those PDBs as well. So why is this useful? Well, for example, let's say you want to have a DBA account called DBA underscore user that would be able to create table spaces in any of the PDBs inside our container data base.
We need our DBA user to administer multiple PDBs at the same time. To do that, we will create a new DBA user in the root container. But, we will create it using a special naming convention. For common users in Oracle 12c, that is users created in the root container, and known to all containers, all PDBs in our CDB instance, the naming format has to start with a special set of characters, c sharp sharp by default.
To create our common DBA user, creatively named DBA underscore user, we will need to log on to the root container and run a create user command when specifying the c sharp sharp DBA underscore user username. A common user, as we mentioned, can perform administrative tasks specific to the root, or the PDBs, such as plugging and unplugging PDBs or changing the PDB state.
Only common users have the appropriate privledges that navigate between the various containers that belong to a CDB. When a common user is created in the root container, that user account is actually created in all open PDBs. However, and this is extremely important, the account is not granted any privledges, and cannot even connect to any of the PDBs. After creating a common user, we'll need to explicitly grant the appropriate permissions to our common user inside each local PDB.
The default scope of all grants is limited to the PDB in which they were granted. So, for example, if we grant the c sharp sharp DBA underscore user the create session privileges in PDB one so that this user can connect to PDB one, the grant will only effect PDB one. That user will have no permissions in PDB two. If you want our common user to have privileges on PDB two as well, we have to connect to PDB two and issue the same grant as we did in PDB one.
There is also a trick that allows us to grant permissions to a common user inside all of our PDBs. We'll see that trick in the next video when we see a hands-on demo of how you can create common and local users in an Oracle container data base environment.
- Pluggable databases (PDB)
- Enabling the new multitenant container database (CDB) architecture
- Connecting to the CDB
- Creating a new PDB
- Unplugging and cloning a PDB
- Backing up and restoring a PDB using RMAN
- Using the new In-Memory Column Store
- Using improved default column values
- Enabling and using extended 32K VARCHARs
- Exploring improved Top-N queries
- Using Oracle temporary undo
- New database partitioning features
- Using adaptive execution plans
- Restoring a table to a specific point in time using RMAN