First, specify the system or object privileges that you simply want to revoke from the user.
Second, specify the user from which you would like to revoke the privileges.

In order to revoke a system privilege from a user, you want to are granted the system privilege with the ADMIN OPTION.

To revoke an object privilege from a user, you want to previously granted the thing privilege to the user otherwise you must have the GRANT ANY OBJECT PRIVILEGE system privilege.

On top of this, you’ll use the REVOKE statement to revoke only privileges that were granted directly with a GRANT statement. In other words, you can’t use the REVOKE statement to revoke privileges that were granted through the OS or roles.

To revoke all system privileges from a user, you’ll use the subsequent statement:

REVOKE ALL PRIVILEGES FROM user;

Oracle REVOKE statement example

First, create a user names bob and grant him the CREATE SESSION system privilege in order that he can log within the Oracle Database:

CREATE USER bob IDENTIFIED BY abcd1234;

GRANT CREATE SESSION TO bob;

Second, grant the CREATE TABLE system privilege to bob:

GRANT CREATE TABLE TO bob;

Third, grant the SELECT, INSERT, UPDATE and DELETE object privileges to bob on ot.customers table:

GRANT SELECT, INSERT, UPDATE, DELETE ON ot.customers

TO bob;

Now, bob can create a replacement table in his own schema and manipulate data within the ot.customers table.

Fourth, log in to the Oracle Database as bob and execute the subsequent statements:

CREATE TABLE t1(id int);

SELECT

name

FROM

customers

ORDER BY

name

FETCH FIRST 5 ROWS ONLY;

Both queries executed successfully because the user bob has sufficient privileges.

Fifth, revoke the thing privileges from bob:

REVOKE SELECT, INSERT, UPDATE, DELETE ON ot.customers

FROM bob;

Sixth, attend the bob‘s session and choose data from the ot.customers table:

SELECT

name

FROM

customers

ORDER BY

name

FETCH FIRST 5 ROWS ONLY;

Oracle issued the subsequent error:

ORA-00942: table or view doesn’t exist

This is correct because bob is not any longer has the SELECT object privilege on the ot.customers table.

Seventh, revoke the CREATE TABLE system privilege from bob:

REVOKE CREATE TABLE FROM bob;

Eighth, attend the bob’s session and plan to create a replacement table:

CREATE TABLE t2(id INT);

Oracle issued the subsequent error, which is what we expected.

ORA-01031: insufficient privileges

If you don’t want bob to log in, you’ll revoke the CREATE SESSION system privilege as shown within the following statement:

REVOKE CREATE SESSION FROM bob;

Next time, bob won’t be ready to log within the Oracle Database anymore.

So, this brings us to the end of blog. This Tecklearn ‘Oracle REVOKE statement’ 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

468