Introduction: –Database users are the one who really uses and takes the benefits of the database. There will be different types of users depending on their need and way of accessing the database.

  1. Application Programmers – They are the developers who concur with the database by means of DML queries. These DML queries are written in the application programs like C, C++, JAVA, Pascal etc. These queries are transfigured into object code to communicate with the database. For example, writing a C program to generate the report of employees who are working in particular department will involve a query to fetch the data from the database. It will include an embedded SQL query in the C Program.
  2. Sophisticated Users – They are database developers, who write SQL queries to select/insert/delete/update data. They do not use any application or programs to request the database. They precisely interact with the database by means of the query language like SQL. These users will be scientists, engineers, analysts who thoroughly study SQL and DBMS to apply the concepts in their requirement. In short, we can say this category includes designers and developers of DBMS and SQL.
  3. Specialized Users – These are also sophisticated users, but they write special database application programs. They are the developers who develop the complex programs to the requirement.
  4. Stand-alone Users – These users will have stand –alone database for their personal use. These kinds of the database will have ready-made database packages which will have menus and graphical interfaces.
  5. Native Users – these are the users who use the existing application to interact with the database. For example, online library system, ticket booking systems, ATMs etc which has the existing application and users use them to interact with the database to fulfill their requests.

In the world of 12c  database, we can find some new terminology about the user. With Oracle 12c Multitenant architecture, New terminologies for users has been introduced. LOCAL USER and COMMON USER.

COMMON USER:

  1. A common user is a DB user, which work perform an activity in all the containers including root container of the CDB.
  2. A common user can only be created in root container.
  3. Common username must start with C##.
  4. While creating a common user, we can’t mention container=CURRENT. Either mention container=ALL or don’t use container keyword.
  5. It is not recommended to create objects under common user

Create a common user:

If you are creating the user, by explicitly mentioning the default tablespace, then that tablespace should be present in all the containers i.e root container and PDBS.
If the tablespace is not present in any of the PDBs, then it will throw an error as below.

GRANTING PRIVILEGE TO COMMON USER:

Now let’s try to connect to a PDB:

Let’s check the privileges for the user in each PDB.

We can see only root container has this privilege, despite it is a common user.

Now I tried to grant privilege with the container=ALL option.

— TRY TO CONNECT TO THE PDB:

So, for a common user, we need to grant privilege with current=ALL, else PDBS will not inherit that privilege.

LOCAL USER:

1.Local users are the normal database user, which can be created only in PDBs. it is dedicated for that PDB. ( Means this user can’t be created in other PDBS).
2.With the appropriate privileges, a local user can access object in a common user’s schema

Oracle-help is always available for help to solve the issue. Thank you, readers, for the kind attention on the article.

Leave a Reply