PostgreSQL – REVOKE
Last Updated :
17 Aug, 2021
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 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.
Example:
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 privileges 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;
Output:
Share your thoughts in the comments
Please Login to comment...