In PostgreSQL, the REVOKE statement is used to revoke previously granted privileges on database objects through a role.
The following shows the syntax of the REVOKE statement:
Syntax: REVOKE privilege | ALL ON TABLE tbl_name | ALL TABLES IN SCHEMA schema_name FROM role_name;
Let’s analyze the above syntax:
- First, specify the privilages that is to be revoked. Use the ALL option to revoke all previously granted privilages.
- Then, set the name of the table after the ON keyword.
- Finally, specify the name of the role whose privelages is to be revoked.
First,log into the dvdrental sample database as Postgres:
psql -U postgres -d dvdrental
Now initialize a role called ‘abhishek’ with the LOGIN and PASSWORD attributes as shown below:
CREATE ROLE abhishek LOGIN PASSWORD 'geeks12345';
Now grat all previlages on the film table to the role ‘abhishek’ as shown below:
GRANT ALL ON film TO abhishek;
Now provide the SELECT privilege on the actor table to the role ‘abhishek’ as shown below:
GRANT SELECT ON actor TO abhishek;
Here we will revoke the SELECT privilege on the actor table from the role ‘abhishek’, as shown below:
REVOKE SELECT ON actor FROM abhishek;
If you wish to revoke all privileges on the film table from the role ‘abhishek’, make use of theREVOKE statement with the ALL option as shown below:
REVOKE ALL ON film FROM abhishek;
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.