DB2 SYSADM Windows - NO LUCK

Question

I have installed DB2 10.5 Enterprise Edition on a Windows 2012 server but can't get a SYSADM access with anything. I also have restored a DB2 database taken from a similar server to the new one but can't gain SYSADM permisisons to browse data. The new installation was installed with a domain account as the instance owner and the 2 Local groups (DB2ADMNS and DB2USERS) were created with the instance owner and Administration accounts added to DB2ADMNS group. WHat ever I did, can't browse the data on the database I restored with any of the members of DB2ADMNS group (Local or domain). I have setup SYSADM_GROUP to DB2ADMNS and global registry to DB2_GRP_LOOKUP=local but still no luck. I restarted DB2 everytime I made a change or log out and in but still no luck.

What am I missing to gain SYSADM access to DB2 at least via the instance owner account ?

Thanks.

Db2 implements the policy of "Separation of Duties" by default, which means that SYSADM authority does not provide access to read data in a database. Are you able to connect to the database? Are you able to query the system catalog? What IDs have DBADM and/or DATAACCESS authority? — Ian Bjorhovde, Jan 02 '19 at 19:12

mao · Answer 1 · 2019-01-02T19:31:45.787

0

To recover, drop the restored database(s), then try the below.

Before you perform the RESTORE of the database, you must first do (in the db2cmd administrator window) these commands:

db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
db2stop force
db2start

Ensure all commands complete successfully. Now retry the restore and check what rights you have.

This is documented.

If you still get problems after following the above, you need to EDIT your question to show exactly all the commands and the results, the old and new instance-names, the syscat.dbauth entries for both of them, and additionally show the output of db2set -all and also the exact messages that appear in db2diag.log when the instance-owner account attempts to access data.

In many cases, simply studying the contents of syscat.dbauth will indicate the problem, once you've connected to the database and that view has PUBLIC select. Users that have DATAACCESS need to be able to connect the the database on the new environment, so in a domain environment DB2_GRP_LOOKUP should be same as the originating Db2-server. If there are large numbers of rows in syscat.dbauth you can filter with SQL like this SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID ('YOUR_ACCOUNT')) AS T

edited Jan 02 '19 at 19:31

answered Jan 02 '19 at 18:37

mao

11,321
2
13
29

Still the same. No Luck. – Kevin D Jan 02 '19 at 18:47
What I meant that my used ID gained access to the database after following what you posted but how do I gain SYSADM access for me, db2 instance account and a bunch of local server accounts which all are in local admin group ? – Kevin D Jan 02 '19 at 19:19
What's the result of the following queries (change `YOUR_ACCOUNT` to your user name)?. `select group from table(auth_list_groups_for_authid('YOUR_ACCOUNT'));` `select * from table(auth_list_authorities_for_authid('YOUR_ACCOUNT', 'U')) where authority in ('SYSADM', 'DBADM', 'DATAACCESS');` – Mark Barinstein Jan 02 '19 at 19:40
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE ---------- ------ ------- -------- --------- ---------- ----------- ------ SYSADM * Y * * * * * DBADM N N N N N N * DATAACCESS N N N N N N * – Kevin D Jan 02 '19 at 19:50
Posting format is terrible but I hope you can see what they are. – Kevin D Jan 02 '19 at 19:54

score 0 · Answer 2 · answered Jan 02 '19 at 20:44

SYSADM authority (starting from 9.7) in DB2 doesn't necessarily allows user to 'browse data' as it was noted by @Ian Bjorhovde.

select * 
from table(auth_list_authorities_for_authid('YOUR_ACCOUNT', 'U')) 
where authority in ('SYSADM', 'DBADM', 'DATAACCESS');

AUTHORITY  D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE 
---------- ------ ------- -------- --------- ---------- ----------- ------ 
SYSADM          *       Y        *         *          *           *      * 
DBADM           N       N        N         N          N           N      * 
DATAACCESS      N       N        N         N          N           N      *

According to the results of this query, your YOUR_ACCOUNT user does have SYSADM authority, but doesn't have DBADM or DATAACCESS authorities in the database. This probably means, that you get SQL0551N errors on various data access attempts. If you want to get DBADM / SECADM access for this user, you can either:

use DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable before the RESTORE DATABASE command
grant these authorities to your user from some another user which has SECADM authority (query the SYSCAT.DBAUTH table to get it)

This is DB2 10.5 and I thought being a SYSADM would automatically grant you the 3 permissions (DBADM / SECADM / DATAACCESS) per DB2 documentation. — Kevin D, Jan 02 '19 at 21:08
DBADM/SECADM is automatically granted to the database creator, but not to the user restored the database from a backup, unless the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable is set. — Mark Barinstein, Jan 02 '19 at 21:40
Even if the user (The one who is restoring the database) is a SYSADM on the instance ? — Kevin D, Jan 02 '19 at 21:44

DB2 SYSADM Windows - NO LUCK

2 Answers2