Sys User Example in Oracle

Back to: Oracle DBA Tutorials

Sys User Example in Oracle

In this article, I am going to discuss Sys User Example in Oracle. Please read our previous article where we discussed Special System Privileges for Administrators in Oracle with Examples.

Sys User Example in Oracle

Now, we will be creating a user which is the replica of the sys user.

Step 1: Open Linux virtual machine and start the listener and start the database. Once the database is started, open the SQL developer, and connect to the database as “sqlplus / as sysdba”. Let us go ahead and check the database name and the user.

Step 2: Check the session privileges.

Let us go ahead and run a query to check the session privileges for the sys user that we have logged in to.

Query: select * from session_privs;

We can see many privileges for the current session. Let us go ahead and check the count of rows.

We can see the number of privileges that are assigned to user “sys” is 253 privileges.

Step 3: Check the tablespaces

Before checking the tablespaces, let us check the container id.

Query: show con_id

So, all the tablespaces present in the CDB$ROOT database will have the con_id as 1. Now, let us check the list of tablespaces present in the CDBROOT database.

Query: select * from v$tablespaces where con_id=1;

We can see all the tablespaces in the container CDB$ROOT. We will be looking at users and temp tablespaces.

Step 4: Create a copy of the sys user.

Let us go ahead and create a copy of the sys user. We are in the CDB$ROOT database, so we have to use the common username which has the prefix c##.

Statement: create user c##syscopy identified by goodluck default tablespace users temporary tablespace temp account unlock;

So, here we are mentioning the username as c##syscopy and the default tablespace as uses, and the temporary tablespace as a temp and finally we are creating the user by unlocking the user. The user will be opened and live.

Step 5: Grant privileges to the new user.

As we are creating the user c##syscopy as the replica of the sys user. Let us go ahead and grant all the privileges that a DBA user present. We will be granting create session, dba, sysdba privileges to the c##syscopy user.

Statement: grant create session, dba, sysdba to c##syscopy container=all;

In this case, the user c##syscopy will have the same privileges as sysdba which contains dba, sysdba privileges.

Step 6: Check user existence.

Let us go ahead and check the user existence using the data dictionary view dba_users where this view contains all the information about the users in the database.

Query: select * from dba_users where username=upper(‘c##syscopy’);

We are using the upper because the usernames in the database are stored in the upper case.

From the output, we can see the username is present in the database. The default tablespace is users and the temporary tablespace and much more information. The profile of the username is the default. The profile is very important for creating users. We will discuss this later.

Step 7: Test the user c##syscopy

Let us go ahead and test the connection and privileges of the user c##syscopy.

Note: Open the terminal and connect to the database as “Sqlplus / as sysdba”. Then after connecting as sysdba connect to the user.

Here we are going to the c##syscopy user in two ways. As we have granted sysdba role to the user. Let us connect as sysdba and then we will try connecting as a c##syscopy user. Let us connect to the c##syscopy user as sysdba.

Statement: connect c##syscopy/goodluck@jayadb as sysdba;

Because we have mentioned jayadb as the tnsentry name in the tnsnames.ora file.

We can able to connect to the c##syscopy user as sysdba user. Let us check the database name and username.

We can see the user is “SYS” because we have logged in as sysdba. But, in audit files, we can see the actual username which is c##syscopy. Let us go ahead and check the privileges of the user while logging in as sysdba.

Query: select * from session_privs;

This query will list all the privileges. We can check the count of the privileges.

We can see 253 privileges. This is the same count as sysdba privileges count. Now, let us try to connect to the user as a normal c##syscopy user.

As we have connected as c##syscopy user without sysdba. Let us try to check the container name and username.

We can see the username is c##syscopy. This is because we are directly connected as c##syscopy users. We have not mentioned any roles here. Let us check the session privileges now.

Query: select * from session_privs;

We can see the row count is 237 rows because we have not logged in as a special role sysdba. So, the user will have fewer privileges. These privileges are dba privileges.

Step 8: Check the roles of sysdba.

Let us go ahead and open the SQL developer and connect as “sqlplus / as sysdba” user. Once connected run the below query which shows the session privileges. Once the query is retrieved, we will be checking the count of rows.

Query: select * from session_privs;

We can see the row count is 253. Now, let us check the row count for the role dba.

Query: select * from role_sys_privs where role=’DBA’;

The row count for the dba role is 235 as we have seen the same count while logging in as a c##syscopy user. So, this indicates the sysdba has more privileges than the dba role. Let us try to filter the roles which are present in sysdba and not part of the dba role using the below query.

Query: select * from session_privs where privilege not in (select privilege from role_Sys_privs where role=’DBA’);

We can see the difference in privileges between sysdba and dba. Let us try to check the count of these rows.

We can see the count of rows is 18. This is the difference privileges between sysdba and dba.

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