I had fun with the new SECADM authority introduced in DB2 9.7 recently. I wanted to restore a database on a test server from a backup of the production database taken on a production server. The production database DB2SEC had been created by the user DB2INST1 in the instance DB2INST1. The restore on the test server was being carried out by user DB2INST2 in the instance DB2INST2. The restore worked smoothly – couldn’t get much easier I thought. Next step was to give privileges to users to perform various tasks on the DB2SEC database and its objects using the GRANT statement. To my astonishment, I was greeted with:
SQL0552N “DB2INST2” does not have the privilege to perform operation “”. SQLSTATE=42502
It so happens that in DB2 9.7, SECADM authority is required for security administration and it is the only authority that provides the ability to grant and revoke all authorities and privileges. If I am the SYSADM, I should have this “God given privilege” – or so I thought! According to DB2 9.7 documentation, the SYSADM that created the database should have SECADM authority on the newly created database, DB2SEC, by default.
On the production server:
db2inst1@DB2PROD:~> db2 “select char(grantor,35) as grantor, char(grantee,35) as grantee from syscat.dbauth where securityadmauth=’Y'”
GRANTOR GRANTEE
———————————– ———
SYSIBM DB2INST1
On the test server:
db2inst2@DB2TEST:~> db2 “select char(grantor,35) as grantor, char(grantee,35) as grantee from syscat.dbauth where securityadmauth=’Y'”
GRANTOR GRANTEE
———————————– ———–
SYSIBM DB2INST1
And therein was the problem – because I had restored a database created by DB2INST1, SECADM was DB2INST1 instead of DB2INST2! To “remedy” the situation, I first created the DB2INST1 user on the test server (which was running SUSE Linux):
useradd -d /home/db2inst1 -g db2iadm1 -G dasadm1 -s /bin/bash -m -p db2inst1
Next, as user DB2INST1, I granted SECADM to user DB2INST2:
db2inst1@DB2TEST:~> db2 GRANT SECADM ON DATABASE TO USER db2inst2
DB20000I The SQL command completed successfully
db2inst1@DB2TEST:~> db2 “select char(grantor,35) as grantor, char(grantee,35) as grantee from syscat.dbauth where securityadmauth=’Y'”
GRANTOR GRANTEE
———————————– ———————-
SYSIBM DB2INST1
DB2INST1 DB2INST2
The proof of the pudding is in the eating they say. I should now be able to grant privileges using DB2INST2:
db2inst2@DB2TEST:~> db2 GRANT DBADM ON DATABASE TO user Joe
DB20000I The SQL command completed successfully.
Sweet! Finally, SECADM can be revoked from DB2INST1:
db2inst2@DB2TEST:~> db2 REVOKE SECADM ON DATABASE FROM DB2INST1
DB20000I The SQL command completed successfully
db2inst2@DB2TEST:~> db2 “select char(grantor,35) as grantor, char(grantee,35) as grantee from syscat.dbauth where securityadmauth=’Y'”
GRANTOR GRANTEE
———————————– ———————-
DB2INST1 DB2INST2