REVOKE

 In Oracle, the REVOKE statement is used to remove previously granted privileges from a user, role, or public. This is essential for maintaining control over who has access to database resources and managing security within the system. It’s the opposite of the GRANT statement, which is used to give privileges to a user or role.

Syntax of REVOKE

The basic syntax for the REVOKE statement is:

REVOKE privilege [, privilege...]

    ON object

    FROM user [, user...]

    [CASCADE CONSTRAINTS];

Key Elements of the Syntax:

1. privilege: This specifies the privilege(s) to be revoked, such as SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc.
• You can specify multiple privileges separated by commas.
2. object: This refers to the object on which the privileges are granted, such as a table, view, procedure, etc.
3. user: This is the name of the user, role, or public from whom you are revoking the privilege. It can also be a role or PUBLIC (to revoke the privilege from all users).
4. CASCADE CONSTRAINTS: If you are revoking a privilege that has been granted through a foreign key relationship, you can specify CASCADE CONSTRAINTS to automatically drop dependent constraints.

Types of Privileges in Oracle:

• System Privileges: These allow a user to perform actions such as creating objects or managing database configurations. For example:
• CREATE SESSION (connect to the database)
• CREATE TABLE (create tables)
• ALTER USER (modify user settings)
• DROP USER (delete a user)
• Object Privileges: These allow a user to perform specific actions on database objects such as tables, views, or procedures. For example:
• SELECT (retrieve data from a table)
• INSERT (add rows to a table)
• UPDATE (modify existing data in a table)
• DELETE (remove rows from a table)
• Role Privileges: You can grant or revoke roles that group together system and object privileges.

Examples of REVOKE Statements:

Revoke object privilege from a user:

REVOKE SELECT ON employees FROM john_doe;

This removes the SELECT privilege on the employees table from the user john_doe.

Revoke multiple privileges from a user:

REVOKE INSERT, DELETE ON orders FROM jane_doe;

This removes both the INSERT and DELETE privileges on the orders table from jane_doe.

Revoke a system privilege from a user:

REVOKE CREATE SESSION FROM guest_user;

This revokes the CREATE SESSION privilege from the guest_user, meaning they can no longer log into the database.

Revoke privileges from a role:

REVOKE SELECT, UPDATE ON products FROM sales_role;

This revokes the SELECT and UPDATE privileges on the products table from the sales_role role.

Revoke a role from a user:

REVOKE manager_role FROM employee_123;

This removes the manager_role role from the user employee_123, which means they no longer have any privileges granted through that role.

Revoke a privilege from PUBLIC:

REVOKE SELECT ON customers FROM PUBLIC;

This removes the SELECT privilege on the customers table from all users, as PUBLIC represents all users in the system.

Important Notes:

• Cascading Effects: When revoking a privilege granted through a role, any user who has the role will also lose the privilege. If a user has the same privilege granted both directly and through a role, you need to revoke it from both places.
• REVOKE on System Privileges: When revoking system privileges, you can prevent a user from performing administrative actions, such as creating objects, creating tables, or even connecting to the database.
• Dependents of Revoked Privileges: If you revoke privileges that are granted as part of a foreign key relationship, you might need to use the CASCADE CONSTRAINTS option to drop dependent constraints.
• GRANT OPTION: When you grant a privilege with the WITH GRANT OPTION, the user can then pass that privilege to others. If you revoke a privilege granted with the GRANT OPTION, it will also revoke the privilege from any other users to whom the privilege was granted through that user.
• Revoke from a Role: If you revoke a privilege from a role, users who have been granted that role will no longer have those privileges.

Example with CASCADE CONSTRAINTS:

REVOKE CREATE TABLE FROM user1 CASCADE CONSTRAINTS;

This revokes the CREATE TABLE privilege from user1 and automatically drops any dependent constraints associated with this privilege.