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:
REVOKE privilege | ALL
ON TABLE tbl_name | ALL TABLES IN SCHEMA schema_name
Let’s analyze the above syntax:
- First, specify the privileges that is to be revoked. Use the ALL option to revoke all previously granted privileges.
- Then, set the name of the table after the ON keyword.
- Finally, specify the name of the role whose privileges 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
Now grat all privileges on the film table to the role ‘abhishek’ as shown below:
Now provide the SELECT privilege on the actor table to the role ‘abhishek’ as shown below:
Here we will revoke the SELECT privilege on the actor table from the role ‘abhishek’, as shown below:
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:
Share your thoughts in the comments
Please Login to comment...