Working with DB2 Database Grants

Being a database administrator and developer, one often needs to restore databases from one database server to another. In DB2, when a database is created, by default, the DB2 instance owner becomes the owner of the database. So when you restore a database backup across different database servers where both, the source and the target database servers have the same instance owner names, it works fine. But when the source and target database servers have different instance owners, the database gets restored successfully, but there can be privilege/authorization issues when you try to access the database objects. The most common being, SQL0551N and SQL1092N.

This problem can be solved by creating the source database server’s instance owner on target database server, connecting to the database through it and then granting the DBADM privileges to the new instance owner. But there is a more elegant and simpler way to do this using the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES DB2 registry variable.

If the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES  registry variable is set to ON when you restore a database, the current instance owner will be automatically granted the SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities on the restored database. So with this, once the database is restored, the instance owner automatically becomes the database owner, and hence no further efforts are required to provide appropriate grants to the new instance owner.

Below are the steps to turn ON this registry variable.

db2stop
db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
db2set -all | grep -i db2_restore_grant
[i] DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
db2start

Sometimes, you may also restore the databases across SIT/UAT and QA database servers. In this case, the application usernames across these different environments may not be same. So after restoring database from the source to target database server, you can use the below commands to generate a script and replicate the grants on database objects from source to target database.

For tables and views (you may consider changing the schemaname and username commented below) use this query:

SELECT ‘GRANT ‘
|| Substr(T.authstring, 1, Length(T.authstring) – 1)
|| T.tabname as string
FROM (SELECT CASE insertauth
WHEN ‘Y’ THEN ‘INSERT,’
WHEN ‘N’ THEN ”
else ”
END
|| CASE alterauth
WHEN ‘Y’ THEN ‘ALTER,’
WHEN ‘N’ THEN ”
else ”
END
|| CASE deleteauth
WHEN ‘Y’ THEN ‘DELETE,’
WHEN ‘N’ THEN ”
else ”
END
|| CASE selectauth
WHEN ‘Y’ THEN ‘SELECT,’
WHEN ‘N’ THEN ”
else ”
END
|| CASE updateauth
WHEN ‘Y’ THEN ‘UPDATE,’
WHEN ‘N’ THEN ”
else ”
END AS AUTHSTRING,
‘ ON ‘
|| TRIM(tabschema)
|| ‘.’
|| TRIM(tabname)
|| ‘ TO ‘
|| CASE granteetype
WHEN ‘U’ THEN ‘USER ‘
WHEN ‘G’ THEN ‘ ‘
else ”
END
||
TRIM(grantee)
— ‘SAURABH’
|| ‘ ;’ AS TABNAME
FROM syscat.tabauth
WHERE (insertauth = ‘Y’
OR alterauth = ‘Y’
OR deleteauth = ‘Y’
OR selectauth = ‘Y’
OR updateauth = ‘Y’)
— AND tabschema=’SAURABHSKA’
ORDER BY tabname
) AS T;

For functions, methods and procedures (you may consider changing the schemaname and username commented below) use this query:

SELECT ‘GRANT EXECUTE ON ‘
|| CASE routinetype
WHEN ‘F’ THEN ‘FUNCTION ‘
WHEN ‘M’ THEN ‘METHOD ‘
WHEN ‘P’ THEN ‘PROCEDURE ‘
ELSE ”
END
|| TRIM(SCHEMA)
|| ‘.’
|| TRIM(specificname)
|| ‘ TO ‘
|| CASE granteetype
WHEN ‘U’ THEN ‘USER ‘
ELSE ”
END
||
TRIM(grantee)
— ‘SAURABH’
|| ‘;’
FROM syscat.routineauth
WHERE executeauth <> ‘N’
— AND SCHEMA=’SAURABHSKA’;

So generate a script by using these two queries on the source database, and execute it on the target database to replicate privileges on the database objects.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s