List users in Db2 database

Query below returns list of users in current database with few of their authorities.

Query

select authid as username,
       case bindaddauth when 'Y' then 1 else 0 end as bindadd,
       case connectauth  when 'Y' then 1 else 0 end as connect,
       case createtabauth when 'Y' then 1 else 0 end as create_tab,
       case dbadmauth when 'Y' then 1 else 0 end as dbadm,
       case externalroutineauth  when 'Y' then 1 else 0 end as ext_routine,
       case implschemaauth when 'Y' then 1 else 0 end as implschema,  
       case loadauth when 'Y' then 1 else 0 end as load,
       case nofenceauth when 'Y' then 1 else 0 end as nofence,
       case quiesceconnectauth when 'Y' then 1 else 0 end as quiesceconn,
       case securityadmauth when 'Y' then 1 else 0 end as securityadm,
       case sqladmauth when 'Y' then 1 else 0 end as sqladm,
       case wlmadmauth when 'Y' then 1 else 0 end as wlmadm,
       case explainauth when 'Y' then 1 else 0 end as explain,
       case dataaccessauth when 'Y' then 1 else 0 end as dataaccess,
       case accessctrlauth when 'Y' then 1 else 0 end as accessctrl,
       case createsecureauth when 'Y' then 1 else 0 end as createsecure 
from sysibmadm.authorizationids a
left join syscat.dbauth d
          on d.grantee = a.authid
where authidtype = 'U'
order by username;

Columns

  • usename - user name
  • bindadd - indicate if user held privilage to create new packages in the database
  • connect - indicate if user held privilage to connect to the database
  • create_tab - indicate if user held privilage to create tables
  • dbadm - indicate if user held DBADM authority
  • ext_routine - indicate if user held authority to create external routines
  • implschema - indicate if user held authority to implicitly create schemas by creating objects in non-existent schemas.
  • load - indicate if user held authority to use the database load utility.
  • nofence - indicate if user held authority to create non-fenced user-defined functions
  • quiesceconn - indicate if user held authority to access the database when it is quiesced
  • securityadm - indicate if user held authority to administer database security
  • sqladm - indicate if user held authority to monitor and tune SQL statements
  • wlmadm - indicate if user held authority to manage WLM objects
  • explain - indicate if user held authority to explain SQL statements without requiring actual privileges on the objects in the statement
  • dataaccess - indicate if user held authority to access data
  • accessctrl - indicate if user held privilage to grant and revoke privilages
  • createsecure - indicate if user held authority to create secure objects

Rows

  • One row represents one user in the database
  • Scope of rows: all users in the database
  • Ordered by user name

Sample results

sample results

IBM Data Studio

To see users in Data Studio expand User branch in Data Source Explorer. To view privilages of user right-click on specific user (1) and then select Properties (2).

After it's done in the bottom of Data Studio will appear Properties tab. Click Privilages (3) and there will be displayed privilages to the database.

Notes

If you want to know more about IBM DB2 user authorities visit official documentation: IBM DB2 authorities

0
There are no comments. Click here to write the first comment.