Skip to content

Query to find permissions granted directly at to specific objects for a specific user – Netezza

You can run below query to find permissions granted directly at to specific objects for a specific user. This query will also report the object name, object type and the database name where this access is granted.
Here we will pull data from various system table and system views in system database.

select u.usename as "User Name", D.DATABASE, o.objname as "Object Name", oc.classname as "Object Type" from _t_object o, _t_user u,  
_t_object_classes oc, _v_database D where u.usename='TEST_ID' and                     
u.usesysid=o.objowner and o.objclass=oc.objclass and o.OBJDB=D.objid; 

Comments

  1. Andrew says:

    this grabs ownership only not permissions. I beleive you would want to run /dpu from a nzsql command line for seeing real permissions (ex. select/execute/truncate)

  2. James George says:

    Right, this query only grabs ownership.
    \dpu gives the privileges of an user. How do we find all the privileges that are granted to a certain object? Please let me know.

    1. Rais says:

      Please use nz_get_acl script from NZ Toolkit.