Controlling User Access in Oracle

In this article, I am going to discuss Controlling User Access in Oracle. This is a chapter and, in this chapter, we will be learning about Administering User Security. The prerequisites in this chapter are PL/SQL.

Controlling User Access in Oracle:

We have a person called a database administrator. Database Administrator will create users by creating a username and password. The administrator will provide privileges to users in order to connect to the database and perform any task. Here comes the database security.

Oracle Database Security:

In database security, we have two categories

System Security: System security is the system privileges that include performing a particular action within the database. For example, a user containing privileges like, creating tables, creating indexes, and creating procedures comes under system privileges.
Data Security: Object privileges come under data security. It is manipulating the content of the database objects. If the user hr has privileges on the table employees, then it is mentioned as object privileges.

What is the difference between Users and Schemas in Oracle?

Schemas: Schemas are collections of objects such as tables, views, and sequences. Users and schemas are database users, but when the user has objects, we call them schema. Let us go ahead and learn about individual privileges.

System Privileges in Oracle:

We have more than 200 privileges that are available in the database. The database administrator has high-level system privileges for tasks such as

Creating new users
Removing users
Removing tables
Backing up tables

All these system privileges can be viewed in a table called SYSTEM_PRIVILEGE_MAP based on the release version.

Examples to Understand System Privileges in Oracle:

System Privileges:

CREATE SESSION
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE

Creating Users in Oracle:

The database administrator creates a user using the create statement. The statement will be as follows.

Statement:

Create user username identified by password;
Create user demo identified by goodluck123;
Once the user is created the database administrator can grant specific system privileges to that user.

Syntax: grant privilege1, privilege2, to user [user, role, public]

Example:

If we want to grant create a table to the hr user then the grant statement will be as follows

Statement: Grant CREATE TABLE to hr;

If we want to grant to a group then the statement is as follows:

Statement: Grant CREATE VIEW to PUBLIC;

An application developer may require the following system privileges.

System Privileges in Oracle:

CREATE SESSION
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE

To grant these privileges to the user demo, we have to use the following grant statement.

Statement: Grant CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW to demo;

What is a Role in Oracle?

A role is a named group of related privileges that can be granted to the user. This method makes it easier to revoke and maintain privileges. A user can have access to several roles and several users can be assigned to the same role. Roles are typically created for a database application.

From the above image, we can see the left image. We have assigned the privileges directory to the user. The structure looks so clumsy. On the right-hand side, we can see that there is a role called manager that is created where all the privileges are assigned and the role manager is granted to all the users. The role is mentioned like a basket.

We can create a role for the privileges like CREATE SESSION, CREATE TABLE, CREATE VIEW, and CREATE SEQUENCE. Let’s go ahead and try that with an example

Creating and Granting privileges to a role:

Create role: CREATE ROLE MANAGER;

Grant privileges to a role: GRANT CREATE TABLE, CREATE VIEW to manager;

Grant a role to users: GRANT manager to demo;

Changing user password: The DBA creates the user account and initializes the password. A user can change the password by using the alter user statement.

Statement: alter user demo identified by goodluck123;

Object Privileges in Oracle:

An object privilege is a privilege or right to perform a particular action on a specific table, view, sequence, or procedure. Object privileges vary from object to object. An owner has all the privileges on the object. An owner can give specific privileges to that owner’s object. For example, we have a user called hr and the hr has a table called employees, then the user hr has all the privileges on the table employees. The user hr can grant privileges on the table employees to another user and another role.

Syntax:
GRANT object_priv {(columns)]
ON object
TO {USER | ROLE | public};

Example to Understand Object Privileges in Oracle:

Grant select privileges to the user demo on table employees.

Statement: GRANT SELECT ON EMPLOYEES TO DEMO;

Grant privileges to update specific columns to users and roles

Grant update (department_id, location_id) on departments to demo, manager;

Here we are granting update privileges to the user demo and role called manager where they can update only two columns department_id and location_id in the table called departments. The user demo cannot update any other columns present inside the departments table.

Allow all users on the system to query data from the DEPARTMENTS table:

Grant select on departments to public;

When we see public that indicates we are granting a privilege to all the users in the database.

Revoke Object Privileges:

We can use the revoke statement to revoke the privileges granted to the other users. Privileges granted to others through with GRANT option clause are also revoked.

Syntax: Revoke privilege1, privilege2 on object from user |role |public;

Example to Revoke Object Privileges:

Revoke the select and insert privileges from the user demo on table departments

Statement: revoke select, insert on departments from demo;

We have many dictionary tables related to privileges. Below is the table which determines the data dictionary view and the description of the data dictionary views.

This is the basic information that is required to learn about user administration. In the next article, I am going to discuss System and Object Privileges in Oracle with Examples. Here, in this article, I try to explain Controlling User Access in Oracle with Examples and I hope you enjoy this Controlling User Access in Oracle article.