Search code examples
db2db2-luw

how to list the specific user acces type for the database in db2

The user 'XX' present under group 'YY'. I want to find the access type of the user 'xx' for a database. Which query can I use?

I am using a Linux environment with Db2 v10.5

Solution

  • Below query returns all privileges for the specified USERID:

    SELECT distinct p.AUTHID, p.PRIVILEGE, p.OBJECTNAME, p.OBJECTSCHEMA, p.OBJECTTYPE
FROM SYSIBMADM.PRIVILEGES P, SYSIBMADM.AUTHORIZATIONIDS A,
TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('HLOESER')) as U 
WHERE p.privilege='CREATEIN' and a.authidtype='G' and a.authid=p.authid
AND (u.group=a.authid or a.authid='PUBLIC')
UNION
SELECT distinct p.AUTHID, p.PRIVILEGE, p.OBJECTNAME, p.OBJECTSCHEMA, p.OBJECTTYPE
FROM SYSIBMADM.PRIVILEGES P
WHERE p.authid='USERID'

    The query is taken from this blog post on using the Db2 administrative views and routines. If you leave out the WHERE clause, it should give you a table for all users.