How to check DB2 SYSADM or SYSCTRL authorization f

Question

How to check DB2 SYSADM or SYSCTRL authorization for a user.

I need to verify user/password by means of DB2 authority to create/drop database. Is there any specific command for this?

I need to verify user/password with a command that tells whether the user has the authority to create and drop database (means attempt to validate before firing database Create command) or not.

Answer 1

Try this; It's implied that the ID has SELECT on the views referenced in the query below;

select 1 from 
sysibmadm.dbmcfg t1 join 
sysibmadm.authorizationids t2 on t1.value=t2.authid 
where t1.name='sysadm_group' fetch first row only
with ur

A SQL0551 on the above query would also be an indication that the connected user does not have SYSADM.

Answer 2

"GET AUTHORIZATIONS" command is discontinued in Version 9.7. "AUTH_LIST_AUTHORITIES_FOR_AUTHID" table function should be used instead.

db2 connect 
db2 SELECT VARCHAR(AUTHORITY, 30) AS AUTHORITY,
   D_USER,
   D_GROUP,
   D_PUBLIC,
   ROLE_USER,
   ROLE_GROUP,
   ROLE_PUBLIC,
   D_ROLE
  FROM TABLE(SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID('ALICE', 'U'))
 WHERE AUTHORITY = 'SYSADM' OR AUTHORITY = 'DBADM'

Link: GET AUTHORIZATIONS command has been discontinued

Answer 3

This is how I would do it in from unix shell, it shouldn't be to hard to adapt to the java equivalent;

createdb=$(db2 get authorizations|egrep 'SYSADM|SYSCTRL' | grep -c ' = YES')
if [[ $createdb ]]; then
   echo 'Do it'
fi

Answer 4

db2 connect
db2 get authorizations