Oracle: REVOKE statement

REVOKE Statement in Oracle is used to revoke privileges for existing users. In easy terms, we can say that this statement is used to fetch some rights from the users in the database. 

To check all the session privileges, you can use the following code:

select * from session_privs;

For instance, You have a user as NEW_USER, and you what to check all the privileges of that user.

some of the Privileges we can REVOKE from users is shown below:

1. Create Session
2. Alter Session
3. Create Table
4. Alter Table
5. Delete Table
6. Create Views
7. Create Sequence
8. Create Synonyms

Along with the above-mentioned privileges, there are many more.

Syntax

REVOKE PRIVILEGE_TYPE from USER_NAME;

Example

Let's try to create a table from user NEW_USER:

create table Stu(Name Varchar(20));

You found the User named NEW_USER is using a lot more memory space for creating Tables. So, you want to revoke a particular user for creating new tables. 

NOTE: To do so, we will revoke CREATE TABLE and CREATE ANY TABLE privileges from the user.

REVOKE CREATE TABLE from NEW_USER;
REVOKE CREATE ANY TABLE from NEW_USER;

Output

Now, If you try to create a table using NEW_USER, It will display the following output: