Oracle

Mastering GRANT & REVOKE in Oracle Database (DCL) — Complete Guide with Examples

Introduction

In Oracle Database, DCL (Data Control Language) commands are essential for managing user access and database security.

The two primary DCL commands are:

  • GRANT — To assign privileges to users or roles.
  • REVOKE — To remove privileges from users or roles.

These commands control access to:

  • Tables
  • Views
  • Procedures
  • Other database objects

1. What Is GRANT in Oracle?

The GRANT command is used to give users or roles specific privileges on database objects.

 Common Privileges:

  • SELECT — Read data from tables.
  • INSERT — Add new records.
  • UPDATE — Modify existing records.
  • DELETE — Remove records.
  • CONNECT — Allow user login.
  • RESOURCE — Allow creation of tables, views, etc.

2. What Is REVOKE in Oracle?

The REVOKE command removes previously granted privileges from users or roles.

3. How to Create a User and Grant Privileges (Step-by-Step)

Step 1: Create a New User

CREATE USER c##SANDHIYA IDENTIFIED BY Admin;

Step 2: Grant Basic Access (Login & Resource Use)

GRANT CONNECT, RESOURCE TO c##SANDHIYA;
  • CONNECT → Allows user to log in.
  • RESOURCE → Allows user to create tables, views, etc.

Step 3: Grant Privileges on Specific Table

GRANT SELECT ON EMP_IND TO c##SANDHIYA;

GRANT INSERT, UPDATE, DELETE ON EMP_IND TO c##SANDHIYA;

Tip: Always grant the least amount of privileges necessary for the task.

4. REVOKE Privileges from a User

Revoke SELECT Privilege Only

REVOKE SELECT ON EMP_IND FROM c##SANDHIYA;

Revoke All Privileges on a Table

REVOKE ALL ON EMP_IND FROM c##SANDHIYA;

5. Granting Public Access (All Users)

Sometimes, companies need to allow all users to view a table.

Grant SELECT to All Users

GRANT SELECT ON EMP_IND TO PUBLIC;

Revoke SELECT from All Users

REVOKE SELECT ON EMP_IND FROM PUBLIC;

Note: PUBLIC means every current and future database user.

6. Role-Based Access Control (Efficient Privilege Management)

Roles work like permission groups (similar to WhatsApp groups for users).

Step 1: Create a Role

CREATE ROLE company_reader;

Step 2: Grant Privileges to Role

GRANT SELECT ON EMP_IND TO company_reader;

Step 3: Assign Role to Users

GRANT company_reader TO c##SANDHIYA, c##ARUN, c##DIVYA;

Step 4: Revoke Role from a User

REVOKE company_reader FROM c##DIVYA;

Tip: Roles can also have privileges like INSERT, UPDATE, and DELETE:

GRANT INSERT, UPDATE, DELETE ON EMP_IND TO company_reader;

 Quick Reference — Privilege Keywords

Keyword Meaning
SELECT Read/view data
INSERT Add new rows
UPDATE Modify existing records
DELETE Remove records
REFERENCES Define foreign key constraints
ALTER Change table structure
INDEX Create indexes

Security Tip:

Always follow the Principle of Least Privilege:

Grant users only the permissions they absolutely need to perform their duties.

This reduces security risks and prevents unauthorized changes.

Conclusion

By effectively using GRANT and REVOKE commands, you can:

  • Control user access to database objects
  • Enhance security
  • Manage large teams easily through roles

 
Membership not found