The REVOKE command revokes previously granted privileges from one or more roles Syntax-for grant option:
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
SIMPLE PRACTICAL:
-- connect as u1 user and view the buy table
postgres=> select * from benz2.buy;
item_id | volume
---------+--------
10 | 1000
30 | 300
(2 rows)
--connect as u2 superuser and revoke privileges from u1 user
postgres=# revoke all privileges on benz2.buy from u1;
REVOKE
--after revoking privilege u1 user con't view the buy table
postgres=> select * from benz2.buy;
ERROR: permission denied for relation buy
Granting options are two methods:
I.WITH GRANT OPTION
II.WITH ADMIN OPTION
II.WITH ADMIN OPTION
I.WITH GRANT OPTION:
If GRANT OPTION FOR is specified, only the grant option for the privilege is revoked, not the privilege itself. Otherwise, both the privilege and the grant option are revoked.
If a user holds a privilege with grant option and has granted it to other users then the privileges held by those other users are called dependent privileges. If the privilege or the grant option held by the first user is being revoked and dependent privileges exist, those dependent privileges are also revoked if CASCADE is specified; if it is not, the revoke action will fail. This recursive revocation only affects privileges that were granted through a chain of users that is traceable to the user that is the subject of this REVOKE command. Thus, the affected users might effectively keep the privilege if it was also granted through other users.
--list down the user for which user do u want to grant & revoke,Now we will choose u3 & u5 user
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
john | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication | {}
u1 | | {}
u10 | | {}
u11 | | {}
u2 | Superuser | {}
u3 | | {}
u5 | | {}
--grant select privilegewith grant option as u2 superuser to u3
postgres=# GRANT SELECT ON benz2.demo TO u3 with grant option;
GRANT
--view the demo table as u3 user if you try to insert any data on demo table means it will throw "permission denied" error becouse u2 user grant "select"(read) permission only to u3 user
postgres=> select * from benz2.demo;
id | name | place | pincode
----+---------+---------+---------
1 | anbu | pulicat | 601205
2 | nijam | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
9 | mohad | pulicat | 601202
7 | kadahar | pulicat | 601201
(8 rows)
postgres=> insert into benz2.demo values(8,'nijam','jamilabath',601208);
ERROR: permission denied for relation demo
--connect as u3 user & grant given select privilege to u5
postgres=> GRANT SELECT ON benz2.demo TO u5 with grant option;
GRANT
--connect as u5 user & view the demo table
[postgres@r1 ~]$ psql -U u5
Password for user u5:
postgres=> select * from benz2.demo;
id | name | place | pincode
----+---------+---------+---------
1 | anbu | pulicat | 601205
2 | nijam | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
9 | mohad | pulicat | 601202
7 | kadahar | pulicat | 601201
(8 rows)
you can revoke privileges using two method from u2 superuser
1.Revoke cascade method
2.Revoke chain method
1.REVOKE CASCADE METHOD:
if you try to revoke without cascade method it will throw following error so you need to use cascade method from u2 superuser
--connect as u2 user and issue the following command
postgres=# revoke SELECT ON benz2.demo from u3;
ERROR: dependent privileges exist
HINT: Use CASCADE to revoke them too.
solution:
postgres=# revoke SELECT ON benz2.demo from u3 cascade;
REVOKE
2.REVOKE CHAIN METHOD:
this system is first you need to revoke privileges from u5 user after that revoke from u3 user without using cascade option
--connect as u3 user & issue the following command
postgres=> revoke SELECT ON benz2.demo from u5;
REVOKE
--connect the u2 user & issue the following command
postgres=# revoke SELECT ON benz2.demo from u3;
REVOKE
II.WITH ADMIN OPTION:
When revoking membership in a role, GRANT OPTION is instead called ADMIN OPTION, but the behavior is similar. Note also that this form of the command does not allow the noise word GROUP.
syntax- for with admin option:
REVOKE [ ADMIN OPTION FOR ]
role_name [, ...] FROM role_name [, ...]
[ CASCADE | RESTRICT ]
--Following command will show that u2 user what are the privileges having to access postgres objects
postgres=> \dp+
Access privileges
Schema | Name | Type | Access privileges | Column access privil
eges
--------+-------------+-------+---------------------------+---------------------
-----
benz2 | buy | table | u2=arwdDxt/u2 |
benz2 | comment_log | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | contable | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | demo | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 +|
| | | u3=r/u2 |
benz2 | dept | table | postgres=arwdDxt/postgres+|
| | | u1=arwdDxt/postgres |
benz2 | item | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | mytab | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | mytab2 | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | orders | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | practis | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | prod | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | product | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | products | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | sale | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | stock | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | t1 | table | u7=arwdDxt/u7 +|
| | | u1=arwdDxt/u7 |
benz2 | t10 | table | u3=arwdDxt/u3 +|
| | | u1=arwdDxt/u3 |
benz2 | t11 | table | u5=arwdDxt/u5 +|
| | | u1=arwdDxt/u5 |
benz2 | t6 | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
benz2 | t9_renamed | table | u2=arwdDxt/u2 +|
| | | u1=arwdDxt/u2 |
(20 rows)
--connect as u2 superuser grant u3 user privileges to u6 user "with admin" option
syntax:
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
postgres=# GRANT U3 TO U6 WITH ADMIN OPTION;
GRANT ROLE
--Now you can access u3 user privileges as u6 user and also u6 user grant u3 user privileges to another user becouse u6 user having "with admin option" following command is accessing u3 objects as u6 user for checking purposes
postgres=> select * from benz2.demo;
id | name | place | pincode
----+---------+---------+---------
1 | anbu | pulicat | 601205
2 | nijam | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
9 | mohad | pulicat | 601202
7 | kadahar | pulicat | 601201
(8 rows)
--connect the u6 user, grant u6 user privileges to u7 user
postgres=> grant u6 TO u7;
GRANT ROLE
--connect as u7 user and fetch the u6 user demo table
[postgres@r1 ~]$ psql -U u7
Password for user u7:
psql.bin (9.3.14)
Type "help" for help.
No entry for terminal type "xterm";
using dumb terminal settings.
postgres=> select * from benz2.demo;
id | name | place | pincode
----+---------+---------+---------
1 | anbu | pulicat | 601205
2 | nijam | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
3 | umar | pulicat | 601205
4 | junaith | pulicat | 601206
9 | mohad | pulicat | 601202
7 | kadahar | pulicat | 601201
(8 rows)
--connect as u2 user and revoke all (u3) privileges from (u6&u7 user)
postgres=# revoke U3 from U6;
REVOKE ROLE
postgres=# revoke U3 from U7;
REVOKE ROLE
--If you try to feth demo table from u7 user it will show error by following
postgres=> select * from benz2.demo;
ERROR: permission denied for relation demo
No comments:
Post a Comment