In the CREATE USER, we used the GRANT statement to supply the user john the CREATE SESSION system privilege to enable the user to log within the Oracle database.

 GRANT CREATE SESSION TO john;

What is a privilege?

By definition, a privilege may be a right to execute an SQL statement or a right to access an object of another user.

Oracle defines two main sorts of privileges: system privileges and object privileges

System privileges

System privileges determine what a user can neutralize the database. They mainly allow a user to feature or modify schema objects within the database like creating tables, creating views, and removing tablespaces.

The most important system privileges are:

CREATE SESSION
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE
SYSDBA
SYSOPER

Object privileges

Object privileges decide how a user can access the info within the database. the thing privileges apply to rows in tables or views.

Here are some common object privileges:

INSERT
UPDATE
DELETE
INDEX
EXECUTE

To grant one or more privileges to a user, you employ the GRANT statement

Introduction to the Oracle GRANT statement

The GRANT statement assigns one or more privileges to a selected user. the subsequent illustrates the essential syntax of the GRANT statement:

GRANT {system_privileges | object_privileges }

TO user

[WITH ADMIN OPTION]

In this syntax:

First, specify the system or object privileges that you simply want to assign to a user after the GRANT keyword. If you assign quite one privilege, you employ a comma-separated list of privileges.

Second, specify the user that receives the privileges after the TO keyword.

Third, optionally use the WITH ADMIN OPTION if you would like the user to be ready to perform the following:

Grant / revoke the privilege to / from another user.
Alter the privilege to vary the authorization needed to access it.
Drop the privilege.

The user who receives the privileges via the GRANT statement is additionally referred to as a grantee.

Note that the GRANT statement also works with roles, which we’ll cover within the subsequent tutorial.

Oracle GRANT statement examples

Let’s practice with the GRANT statement to urge a far better understanding.

1) Use Oracle GRANT to grant system and object privileges to a user example

In this tutorial, we’ll launch two SQL*Plus sessions, one for the user ot which will grant privileges and another for the user john.

First, launch SQL*Plus and log in to the Oracle database using the user john. Note that we assigned the user john the CREATE SESSION system privilege, so it should be ready to log in.

In case you’re not following the CREATE USER tutorial, you’ll create the user john and grant the CREATE SESSION system privilege by using the subsequent statements:

CREATE USER john IDENTIFIED BY abcd1234;

GRANT CREATE SESSION TO john;

Second, use the user john to log in to the Oracle Database and make a replacement table:

CREATE TABLE t1(id NUMBER PRIMARY KEY);

Oracle issued the subsequent error:

ORA-01031: insufficient privileges

To allow the user john to make the table, you would like to grant the CREATE TABLE system privilege to the user as shown within the following statement:

GRANT CREATE TABLE TO john;

Now, the user john can create a replacement table:

CREATE TABLE t1(id NUMBER PRIMARY KEY);

The following statement shows the privileges of the present user:

SELECT * FROM session_privs;

Here are the privileges of the user john:

PRIVILEGE

——————

CREATE SESSION

CREATE TABLE

Third, use the user john to insert a replacement row into the t1 table:

INSERT INTO t1(id) VALUES(10);

Oracle issued the subsequent error:

ORA-01950: no privileges on tablespace ‘USERS’

This is because the user john features a quota of zero on the USERS tablespace.

To fix this, you employ the ALTER USER command to vary the quota of the user john on the USERS tablespace:

ALTER USER john QUOTA UNLIMITED ON USERS;

Now, the user john should be ready to insert a row into the t1 table:

INSERT INTO t1(id) VALUES(10);

And query data from the t1 table as well:

SELECT * FROM t1;

Here is that the output:

 ID

----------

 10

2) Use Oracle GRANT to assign privileges WITH ADMIN OPTION example

First, create a replacement user called jack and grant the user the CREATE SESSION in order that the user can log in:

CREATE USER jack IDENTIFIED BY abcd1234

 QUOTA UNLIMITED ON users;

GRANT CREATE SESSION TO jack;

Second, grant the CREATE TABLE system privilege to john, but this point , use the WITH ADMIN OPTION:

GRANT CREATE TABLE TO john WITH ADMIN OPTION;

Now, the user john can grant the CREATE TABLE system privilege to a different user e.g. jack.

Third, login as john and grant the CREATE TABLE system privilege to jack:

GRANT CREATE TABLE TO jack;

Finally, login as jack and make a replacement table:

CREATE TABLE t2(id NUMBER PRIMARY KEY);

The user jack can create the table.

3) Using Oracle GRANT to assign privileges which has ANY option example

Some system privileges have the keyword ANY that permits a user to perform the corresponding action on any objects within the database.

For example, SELECT ANY TABLE allows a user to pick data from any table in any schema within the database.

Consider the subsequent example.

First, log in as jack and choose the info from t1 table within the john‘s schema:

SELECT * FROM john.t1;

Oracle issued the subsequent error:

ORA-00942: table or view doesn’t exist

Second, login as ot and grant the SELECT ANY TABLE system privilege to jack:

GRANT SELECT ANY TABLE TO jack;

Third, from the session of john, execute the SELECT statement:

SELECT * FROM john.t1;

Here is that the output:

 ID

----------

 10

Now the user jack can select data from any table in any schema within the Oracle database.

4) Using Oracle GRANT to grant object privileges to a user example

First, launch the primary SQL*Plus session, log in as ot user and make a replacement table named t2:

CREATE TABLE t2(id INT);

Second, insert some values into the t2 table:

INSERT INTO t2(id) VALUES(1);

INSERT INTO t2(id) VALUES(2);

Third, launch the second SQL*Plus session, log in as john, and query data from the ot.t2 table:

SELECT * FROM ot.t2;

Oracle issued the subsequent error:

ORA-00942: table or view doesn’t exist

This is because the user john doesn’t have a privilege to question data from the ot.t2 table.

Fourth, return to the primary SQL*Plus session and grant the SELECT object privilege on ot.t2 to john:

GRANT SELECT ON ot.t2 TO john;

Fifth, go the second session SQL*Plus and query data from the ot.t2 table:

SELECT * FROM ot.t2

Now, john should be ready to query data from the ot.t2 table.

Sixth, attempt to insert some rows into the ot.t2 table:

INSERT INTO ot.t2(id) VALUES(3)

Oracle issued the subsequent error:

ORA-01031: insufficient privileges

To allow john to insert and update data within the ot.t2 table, you would like to grant the INSERT and UPDATE object privilege to john:

GRANT INSERT, UPDATE ON ot.t2 TO john;

Now, john should be ready to insert and update data within the ot.t2 table.

So, this brings us to the end of blog. This Tecklearn ‘Overview of Oracle privileges’ blog helps you with commonly asked questions if you are looking out for a job in Oracle DB. If you wish to learn Oracle DB and build a career in Database domain, then check out our interactive, Oracle 12c DBA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

What is the Oracle Database
Pre-requisites for oracle database
Oracle database 12c installation
Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

Explain the Memory Structures
Describe the Process Structures
Overview of Storage Structures

Preparing the Database Environment

Identify the tools for Administering an Oracle Database
Plan an Oracle Database Installation
Install the Oracle Software by using Oracle Universal Installer (OUI)
Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

Setting database initialization parameters
Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

Managing Database Storage Structures
Overview of table space and data files
Overview of Primary Components
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

Describe the stages of database startup and shutdown
Using alert log and trace files
Configure and Manage the Oracle Network
pFile and spFile
Tablespace Management
Storage and Relationship Structure

Oracle Backup & Recovery

Redo log File Management
Performing Database Recovery
Overview of Data Recovery Advisor
Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

Using the RMAN Recovery Catalog
Identify situations that require RMAN recovery catalog
Create and configure a recovery catalog
Synchronize the recovery catalog
Create and Use RMAN stored scripts
Back up the recovery catalog
Create and use a virtual private catalog

Configuring Backup Specifications

Using RMAN to Create Backups
Create image file backups
Create a whole database backup
Enable fast incremental backup
Using RMAN to Perform Recovery
Perform complete recovery from a critical or noncritical data file loss using RMAN
Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

Moving Data
Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

Oracle Data Dictionary contains information related to database privileges
Data dictionary views
Media failure
Flashback CDB

Database Tuning

What is database tuning
How to tune the database
Tuning the data dictionary

Got a question for us? Please mention it in the comments section and we will get back to you.

647