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.
db2 connect
db2 get authorizations
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
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.
"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
