cancel
Showing results for 
Search instead for 
Did you mean: 

What is the EDB equivalent of DBA_TAB_PRIVS?

SOLVED
Highlighted
Level 2 Adventurer

What is the EDB equivalent of DBA_TAB_PRIVS?

What table would  I need to view to see what role has access to a database, schema and table? 

1 ACCEPTED SOLUTION

Accepted Solutions
Level 2 Adventurer

Re: What is the EDB equivalent of DBA_TAB_PRIVS?

No, that did not answer the question. The answer is  

 

information_schema.table_privileges .

 

DBA_TAB_PRIVS tells you the kinds of access you have on database objects. Privileges are a seperate catagory. 

2 REPLIES 2
Moderator

Re: What is the EDB equivalent of DBA_TAB_PRIVS?

Hi Mediis,

 

The Postgres-way to check database, schema, and table privileges is to use the ACL (Access Contro List) column of the system views pg_database (databases), pg_namespace (schemas), and pg_class (tables, indexes, etc.).  Therein you will find information about privileges granted to users in a codified format--a mapping can be found in the documentation.  For your reference, it's also below:

 

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

You may also need to cross-reference with pg_user/pg_group.  Hope this helps!

Level 2 Adventurer

Re: What is the EDB equivalent of DBA_TAB_PRIVS?

No, that did not answer the question. The answer is  

 

information_schema.table_privileges .

 

DBA_TAB_PRIVS tells you the kinds of access you have on database objects. Privileges are a seperate catagory.