#8639: db2 LUW -> groups -> extraction incomplete | Aqua Data Studio

Issues
Search Tips

Easily find issues by searching: #<Issue ID>
Example: #1832

Easily find members by searching in: <username>, <first name> and <last name>.

Example: Search smith, will return results smith and adamsmith
You can use:
- Boolean operators AND, OR and NOT (must be in caps, e.g. oracle AND debugger, debugger NOT oracle)
- Single and multiple character wildcard (e.g. de?ug, debug*)
- Grouping (e.g. ((oracle debugger) AND exception))
- Quoted phrase (e.g. "Oracle Debugger")

#loadingpage {display:none}JavaScript and Cookies must be enabled in order for you to use AquaClusters.here

Aqua Data Studio / nhilam

Follow 824

IDE for Relational Databases

Back

Ivan Gron

ivangron reported 2013-04-09T17:19:13Z · last modified 2013-05-01T22:45:05Z

db2 LUW -> groups -> extraction incomplete

Dev	ivangron
QA	tariqrahiman

Priority	Critical
Complexity	Unknown

Component	App - General
Version	12.0

DB2 luw introduced the concept of groups without a create group ddl. Groups are created through grant statements and there existence is stored in permission *auth* tables. Currently extraction and recreation code is only searching through the syscat.dbauth tables. This would only find groups that have permissions at the database level. We need to make sure to search through all *auth* tables in order to extract and recreate groups correctly.

All Comments (3) Change History

Ivan Gron

ivangron 2013-04-09T20:53:55Z · (edited)

Tree node will now work as follows:

db2 luw versions

below 8.0: // this is legacy code and is missing data but 7.x db2 is old and unsupported at this point.

SELECT DISTINCT grantee FROM syscat.dbauth WHERE granteetype = 'G'

8.x:

SELECT DISTINCT grantee FROM syscat.colauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.dbauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.indexauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.packageauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.passthruauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.routineauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.schemaauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.sequenceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tabauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tbspaceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.libraryauth WHERE granteetype = 'G'

9.0 -> below 9.5:

SELECT DISTINCT grantee FROM syscat.colauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.dbauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.indexauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.packageauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.passthruauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.routineauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.schemaauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.sequenceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tabauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tbspaceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.libraryauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.xsrobjectauth WHERE granteetype = 'G'

9.5 -> below 9.7:

SELECT DISTINCT grantee FROM syscat.colauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.dbauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.indexauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.packageauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.passthruauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.roleauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.routineauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.schemaauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.sequenceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tabauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tbspaceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.libraryauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.variableauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.workloadauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.xsrobjectauth WHERE granteetype = 'G'

9.7 and greater:

SELECT DISTINCT grantee FROM syscat.colauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.dbauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.indexauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.packageauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.passthruauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.moduleauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.roleauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.routineauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.schemaauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.sequenceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tabauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tbspaceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.libraryauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.variableauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.workloadauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.xsrobjectauth WHERE granteetype = 'G'

Tree node will now work as follows:

db2 luw versions

below 8.0: // this is legacy code and is missing data but 7.x db2 is old and unsupported at this point.

SELECT DISTINCT grantee FROM syscat.dbauth WHERE granteetype = 'G'

8.x:

SELECT DISTINCT grantee FROM syscat.colauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.dbauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.indexauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.packageauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.passthruauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.routineauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.schemaauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.sequenceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tabauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tbspaceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.libraryauth WHERE granteetype = 'G'

9.0 -> below 9.5:

SELECT DISTINCT grantee FROM syscat.colauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.dbauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.indexauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.packageauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.passthruauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.routineauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.schemaauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.sequenceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tabauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tbspaceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.libraryauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.xsrobjectauth WHERE granteetype = 'G'

9.5 -> below 9.7:

SELECT DISTINCT grantee FROM syscat.colauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.dbauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.indexauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.packageauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.passthruauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.roleauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.routineauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.schemaauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.sequenceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tabauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tbspaceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.libraryauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.variableauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.workloadauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.xsrobjectauth WHERE granteetype = 'G'

9.7 and greater:

SELECT DISTINCT grantee FROM syscat.colauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.dbauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.indexauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.packageauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.passthruauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.moduleauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.roleauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.routineauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.schemaauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.sequenceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tabauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.tbspaceauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.libraryauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.variableauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.workloadauth WHERE granteetype = 'G' UNION

SELECT DISTINCT grantee FROM syscat.xsrobjectauth WHERE granteetype = 'G'

Ivan Gron

ivangron 2013-04-18T16:39:51Z

extraction / object creation / scripting now complete. I am unable to find documentation on how to grant/revoke permissions for library objects. I currently coded in a generic type statement that is commented out as its not supported by the database till we can find docs on this. Groups now come from the union of all the auth tables and those create/drop will need to be tested through use of all grant/revoke statements.

Ivan Gron

ivangron 2013-04-18T17:06:49Z · (edited)

extraction / scripting and tree nodes should work correctly now. Visual GUI does not support all aspects of object authentications yet....

[testers] need to make sure that all the following grant statements issued on a group are extracting and scripting correctly: Version dependent: below version 8.0 code did not change. Above version 8.0 code changes are version dependent and should be tested for versions between 8.0 & 10.1 as some code only runs under specific versions. code falls into 5 blocks [<--8.0],[8.0+--9.0],[9.0+--9.5],[9.5+--9.7],[9.7+-->]

GRANT/REVOKE (database authorities)

topic

GRANT/REVOKE (global variable privileges)

topic

GRANT/REVOKE (index privileges)

topic

GRANT/REVOKE (module privileges)

topic

GRANT/REVOKE (package privileges)

topic

GRANT/REVOKE (role)

topic

GRANT/REVOKE (routine privileges)

topic

GRANT/REVOKE (schema privileges)

topic

GRANT/REVOKE (security label)

topic

GRANT/REVOKE (sequence privileges)

topic

GRANT/REVOKE (server privileges)

topic

GRANT /REVOKE (table space privileges)

topic

GRANT/REVOKE (table, view, or nickname privileges)

topic

GRANT/REVOKE (workload privileges)

topic

GRANT/REVOKE (XSR object privileges)

About AquaClusters Privacy Policy Support Version - 19.0.2-4 AquaFold, Inc Copyright © 2007-2017