System and Object Privileges in Oracle

Back to: Oracle DBA Tutorials

System and Object Privileges in Oracle

In this article, I am going to discuss System and Object Privileges in Oracle with Examples. Please read our previous article where we discussed Controlling User Access in Oracle with Examples.

System and Object Privileges in Oracle

Previously we took an overview of controlling user access. Now, let us learn about the system privileges by creating a new user and granting some system privileges and object privileges. Please take a look at the below table. We will be using these data dictionary views in the upcoming steps to check privileges.

Note: Open the Virtual Machine and start the database. Once the database is started check the listener and that all the services are up and running. Check if the pluggable databases are running or not. Once everything is fine Open SQL developer and connect to the database as sqlplus / as sysdba. We will be performing the task step-wise.

Step 1: Connect to the pluggable database.

Let us check the current container.

Command: show con_name

Now, we are connected to the root database. Let us go ahead and connect to the pluggable database because we are performing the tasks in the pluggable database.

Command: alter session set container=JAYAPDB;

Step 2: Check users and privileges in the database.

Let us go ahead and check all the users present in the pluggable database JAYAPDB.

Query: select username, account_Status, common from dba_users;

We can see these users are present in JAYAPDB. Some of the users are common users and some of the users are not common users. We can see the hr user is not a common user. This user is only dedicated to this pluggable database JAYAPDB. We have a table called SYSTEM_PRIVILEGE_MAP. This table contains all the system privileges that exist in the database.

Query: select * from SYSTEM_PRIVILEGE_MAP;

We can see many system privileges like alter system, create session, alter session, create tablespace, create user, drop user, create table, create any table.

There is a difference between creating a table and creating any table privileges. When dba grants create a table to any user, he can create a table in only his schema, when dba grants create any table to any user then the user can create a table in his schema and any other schema as well.

Step 3: Create a user demo

Let us go ahead and create a user called demo. The user that we are going to create is a local user because we are creating the user in the pluggable database JAYAPDB. Whenever we create a user in the root database then it is considered a common user.

Statement: create user demo identified by goodluck;

We can see the user demo is created. But this user does not have any privileges associated with it. Let us go ahead and grant some privileges to the user demo.

Step 4: In this step, we will grant some privileges to the user demo.

Let us first grant him the privilege to connect to the database. In order to provide a connection to the user we have to grant create session privilege.

Statement: grant create session to demo;

We have granted the create session to the demo user. Let us go ahead and grant the create table privilege to the user. So, the user can create a table in the database. Once the table is created grant unlimited tablespace privileges to the user.

We have granted the create table privilege. In order to create the table, we have to grant space access to the user. Let us go ahead and grant unlimited tablespace privilege to the user.

So, we have granted unlimited space to user demo on tablespaces.

Note: we can grant two privileges at the same time to the user. But to explain each privilege individually we are granting privileges individually.

Statement: grant create session, create table to demo;

Let us go ahead and grant some other privileges like create sequence, create view, create synonym.

Statement:

Grant create sequence to demo;
Grant create view to demo;
Grant create synonym to demo;

We can grant in this way as well. We can write multiple grant statements and execute all of them at a time. Now, that we have granted some privileges to the user demo. Let us go ahead and grant some privileges to the user demo on table employees in the hr schema. We are granting select and delete privileges to demo user on the employees table in the hr schema.

Statement: grant select, delete on hr.employees to demo;

Let us try to learn how to grant to a column in the table of schema. Let’s go ahead and grant the update privilege for the demo user to the salary column in the employees table.

Statement: grant update(salary) on hr.employees to demo;

Let us try to learn how to grant all privileges to the user demo on a table. Let us grant all privileges to the locations table in the hr schema.

Statement: grant all on hr.employees to demo;

So, the demo user is having all the privileges to the table locations. The user demo can create columns, and insert, update, and delete tables. Now, we will grant select and insert for the demo on the jobs table in the hr schema.

Statement: grant select, insert on hr. jobs to demo;

The demo user can only select and insert on the table jobs. No other privileges like delete, update cannot be allowed. Let us only grant select privileges on the countries table in hr user to the public.

Statement: grant select on hr. countries to public;

Now, we have granted all the privileges to the user demo. Let us try to check the privileges of the user and run a few queries on tables. In order to test all the privileges that we granted we have to connect to the demo user. Let us learn the privileges check using the demo user in the following steps.

Step 1: Connection to demo user.

Step 2: Check the session privileges.

In order to check the privileges of the user who has logged in, there is a table called session_privileges. We can check the privileges of users in the current session.

Query: select * from session_privs;

The user demo has created a session, create a table, create a synonym, create a view, create a sequence, and unlimited tablespace privileges. We can see create table privilege, let’s go ahead and create a table with user demo.

Step 3: Create a table in the demo user.

Let us go ahead and create table emp.

Statement:
create table emp
(
      empid number constraint emp_pk primary key,
      Ename varchar2(100)
);

We have created table emp and columns empid, ename. The column empid is a constraint named emp_pk. The user demo has full access to the table that is created under demo user. The user can insert, update, alter, and delete the table emp. Let’s try to insert a value to the emp table.

Statement: insert into emp values ( 1, ‘jaya’);

As we have inserted a column, let us try to check the table.

Query: select * from emp;

Let us try to alter the table with a new column called salary.

Statement: alter table emp add (salary number);

Let us check if the column is added or not.

Query: select * from emp;

We played with the table emp. Let us try to test the other privileges as well.

Step 4: Test the system privileges.

Let us try to create a sequence in the demo user.

Statement: create sequence emp_s;

We have created a sequence that is executed successfully. Let us try to create an index as well.

Statement: create index ename_ind on emp(ename);

We can create every object because the demo is the owner of the table emp. Let us try to create a view as well.

Statement:

Create or replace view emp_v As select empid, name from emp;

So, the view is also created.

Step 5: Change the password for the user.

Whenever the user is created by dba, the dba creates the default password for the user. In order to improve security, we have to change the password manually.

Statement: alter user demo identified by goodluck123;

Note: Once you changed the password, you have to reconnect to the user.

Step 6: Test the object privileges

Now, that we have checked the system privileges, Let us try to check the object privileges. We have granted select and delete privileges to demo users on the employees table in the hr schema. Let’s try to check the select privilege on the employees table.

Query: select * from hr.employees;

We can select employees without hr. including when there is a public synonym created. Let us try to check if there are any synonyms created.

Query: select * from all_synonyms where table_name=’EMPLOYEES’;

We can see there are no results found because the user hr did not create any synonyms under the schema hr. We have not granted update privileges to any column except the salary column. Let’s try to update the other columns in the employees table.

Statement:

Update hr.employees Set department_id=null Where employee_id=1;

We can see we got an error indicating insufficient privileges. Let us now try to update the column that we have granted. We have granted to update the salary column in employees table.

Statement: Update hr.employees Set salary=500 Where employee_id=1;

We can see 0 rows updated because we do not have any employee_id as 1.

Step 7: Test Dictionary Tables

Let us check with a table called session_privs.

Query: select * from session_privs;

We can see create a session, unlimited tablespace, create a table, create a synonym, create a view, and create sequence privileges in the table. We have another table called user_sys_privs. Let us go ahead and check that table as well.

Query: select * from user_sys_privs;

We can see all the privileges that were present in the session_privs are present in the user_sys_privs. Both are the same as the privileges belonging to the user who logged in. In session_privs the user contains all the privileges direct and indirect. Indirect indicates the roles that are granted to the user.

Now, let us check with table user_tab_privs_recd. This table indicates object privileges that are assigned to the user demo. Let us try to check all the object privileges.

Query: select * from user_tab_privs_recd order by 2;

We can see all the above privileges that are granted to the user demo. Let us take the first privilege and explain that. We have a delete privilege on the table called employees where hr is the owner of the table and the grantor who granted that privilege is hr. So, the hr user has granted delete privileges on the table employees to the demo user.

Let us take another example with the table name jobs and privilege insert. We can see the insert privilege that is granted on the table jobs and the grantor who granted this privilege is hr to the demo user. We have another user_col_privs_recd. This table is defined as object privileges granted to the user on specific columns. Let us try to query that table.

Query: select * from user_col_privs_recd;

We can see the user demo has update privilege on the column salary which is present in the employees table where hr is the owner of the table employees and the grantor who granted this privilege is hr. Our user is the demo. We have created a table called emp. Let us grant select privilege to hr user on table emp which is present in demo user.

Statement: grant select on emp to hr;

Now, that we have granted a privilege to another user. There is a table to check these kinds of privileges called user_tab_privs_made.

Query: select * from user_tab_privs_made;

We can see the user demo is the grantor and the demo has granted select privilege on the table emp to the user hr. The second row is a default row that contains inherit privileges where the user demo can grant any privileges to the public role. Any object that is created by the demo user like a table, index, or view can inherit by another database or another user. Let us try to grant update privilege to the hr user on the column ename which is present in the table emp.

Statement: grant update (ename) on emp to hr;

So, that we have granted an update privilege to the column in the emp table. There is a dictionary table to check this grant. The table is called user_col_privs_made. Let’s query that table to check the privileges.

Query: select * from user_col_privs_made;

From the above result, we can see the user demo has granted update privilege on the column ename which is present in the table emp of the hr user. So, this is the information about system privileges and object privileges. We have learned how to grant privileges and test those privileges.

In the next article, I am going to discuss Creating Roles in Oracle with Examples. Here, in this article, I try to explain System and Object Privileges in Oracle with Examples and I hope you enjoy this System and Object Privileges in Oracle article.