-1

I need to get all possible privilege kinds for all types of objects, e.g.

TYPE KINDS
TABLE {SELECT, INSERT, UPDATE, DELETE, TRUNCATE...}
SCHEMA {CREATE, USAGE}
... ...

I need not specific grants on object or role, but possible grants like enter image description here

In the end, I need to check if a role has ALL grants on an object. E.g. this answer says how to find which grants the grantee has, but I don't know how to check if there are no more other grants.

4
  • Welcome to the DBA.SE community. Could you possible be a bit more specific? Are you searching for a list of possible privileges/permissions that PostgreSQL provides? Or are you looking for a script that will retrieve the privileges/permissions for a specific PostgreSQL user/role? Please edit your question and provide more details and possibly an example. Thank you. With your support, the community should be able to provide you with a solution.
    – John K. N.
    Jan 4, 2022 at 15:54
  • @JohnK.N. I need all possible. added some more info to the question Jan 4, 2022 at 16:03
  • @AlexTonkonozhenko just check if it has the "ALL" privilege. Jan 5, 2022 at 6:57
  • @GerardH.Pille how can I do it? I see many privileges granted, but not ALL permission Jan 5, 2022 at 8:46

1 Answer 1

0

Create a user for the sole purpose of this exercise:

create user ulysses@'%' identified by 'HariM';

Grant all privileges on an object of the type you wish to investigate, eg. a table.

grant all on ghp.test to ulysses;

Now you can query the mysql.tables_priv, the table_priv column contains a comma separated list of all possible privileges. A user or role that doesn't have the same values, doesn't have all privileges on that type of object.

MariaDB [ghp]> select table_priv from mysql.tables_priv where user = 'ulysses' and db = 'ghp' and table_name = 'test';
+---------------------------------------------------------------------------------------------------------------------+
| table_priv                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------+
| Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger,Delete versioning rows |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.