Admin Option in Oracle

Back to: Oracle DBA Tutorials

Admin Option in Oracle with Examples

In this article, I am going to discuss Admin Option in Oracle with Examples. Please read our previous article where we discussed PDB_DBA in Oracle with Examples.

Admin Option in Oracle

Now, we will be learning about a simple option called with admin option. Let us go ahead and connect to the database as “Sqlplus / as sysdba”. Once connected let us check the container name.

So, we are connected to the CDB$ROOT database. Let us move to the pluggable database JAYAPDB.

Command: alter session set container=jayapdb;

We are connected to the pluggable database JAYAPDB. Let us go ahead and create a user named TEST_USER. We are creating this user to test the admin option in the grant statement. This user is a local user.

Statement: create user TEST_USER identified by test_user;

Let’s go ahead and grant some basic privileges to create a session for the user TEST_USER with the admin option.

Statement: grant create session to TEST_USER with admin option;

So, this statement defines that the user TEST_USER has the admin privilege on the privilege CREATE SESSION. So, the user TEST_USER can grant create a session with any other user. This option is not for real-world usage. In order to prove this option let’s connect to the TEST_USER.

Now, that we are connected to TEST_USER. Let’s try to grant create session privilege to another user called hr. The user hr is present in the JAYAPDB database.

Statement: grant create session to hr;

So, the user TEST_USER can grant create session to the hr user. So, if any user has granted with admin option, then that user can assign that privilege to any other user.

In the next article, I am going to discuss User Profiles in Oracle with Examples. Here, in this article, I try to explain Admin Option in Oracle with Examples. I hope you enjoy this Admin Option in Oracle article.