Are DBADM privs really needed for db2expln?

[Troels Arvin]

Hello,

In a DB2 LUW v. 8.1.1.96, I have a hard time giving users access to the
db2expln utility.

Let's say that user FOOBAR needs to be able to run the db2expln utility. I
do:
- provide IMPLICIT_SCHEMA, CREATETAB, BINDADD, IMPLICIT_SCHEMA
to the user
- ask the user to run /home/db2inst1/sqllib/misc/EXPLAIN.DDL; this
seems to work well

Now, the user tries
db2expln -d dbname -stmtfile somefile.sql -terminal
and gets:

================================================== ===============
Error during VALUES for package check.

Message Text =
SQL0551N "FOOBAR" does not have the privilege to perform operation
"EXECUTE" on object "NULLID.SQLNAE01". SQLSTATE=42501
sqlcaid = SQLCA
sqlcabc = 136
sqlcode = -551
sqlstate = 42501
sqlerrml = 28
sqlerrmc = "FOOBAR", "EXECUTE", "NULLID.SQLNAE01"
sqlerrp = SQLRAEAC
sqlerrd0 = 0x8012006D = -2146303891
sqlerrd1 = 0x00000000 = 0
sqlerrd2 = 0x00000000 = 0
sqlerrd3 = 0x00000000 = 0
sqlerrd4 = 0x00000000 = 0
sqlerrd5 = 0x00000000 = 0
sqlwarn = ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' '
================================================== ===============

If the user proceeds by working only in the command line processor
(EXPLAIN PLAN FOR ...), things work (no error messages; and stuff ends up
in the EXPLAIN_OPERATOR and EXPLAIN_STREAM tables.

Now, if
- I give the user DBADM privileges
- (s)he runs the "db2expln -help -database dbname" command
- I revoke the DBADM privileges from the user, making sure
that only CREATETAB, BINDADD remain,
then db2expln works fine for the user.

What am I missing? I assume that (temporary) DBADM privileges shouldn't be
needed to run db2expln?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/

[Phil Sherman]

It looks like db2expln is expecting to use the SQLNAE01 package. The
error message indicates that the user doesn't have execute authority for
the package.

Try granting execute authority for the package to a user and see if that
solves the problem. If it does, then you'll need to work out a way for
all of the developers to have execute authority for it.

Phil Sherman
Troels Arvin wrote:

Hello,

In a DB2 LUW v. 8.1.1.96, I have a hard time giving users access to the
db2expln utility.

Let's say that user FOOBAR needs to be able to run the db2expln utility. I
do:
- provide IMPLICIT_SCHEMA, CREATETAB, BINDADD, IMPLICIT_SCHEMA
to the user
- ask the user to run /home/db2inst1/sqllib/misc/EXPLAIN.DDL; this
seems to work well

Now, the user tries
db2expln -d dbname -stmtfile somefile.sql -terminal
and gets:

================================================== ===============
Error during VALUES for package check.

Message Text =
SQL0551N "FOOBAR" does not have the privilege to perform operation
"EXECUTE" on object "NULLID.SQLNAE01". SQLSTATE=42501
sqlcaid = SQLCA
sqlcabc = 136
sqlcode = -551
sqlstate = 42501
sqlerrml = 28
sqlerrmc = "FOOBAR", "EXECUTE", "NULLID.SQLNAE01"
sqlerrp = SQLRAEAC
sqlerrd0 = 0x8012006D = -2146303891
sqlerrd1 = 0x00000000 = 0
sqlerrd2 = 0x00000000 = 0
sqlerrd3 = 0x00000000 = 0
sqlerrd4 = 0x00000000 = 0
sqlerrd5 = 0x00000000 = 0
sqlwarn = ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' '
================================================== ===============

If the user proceeds by working only in the command line processor
(EXPLAIN PLAN FOR ...), things work (no error messages; and stuff ends up
in the EXPLAIN_OPERATOR and EXPLAIN_STREAM tables.

Now, if
- I give the user DBADM privileges
- (s)he runs the "db2expln -help -database dbname" command
- I revoke the DBADM privileges from the user, making sure
that only CREATETAB, BINDADD remain,
then db2expln works fine for the user.

What am I missing? I assume that (temporary) DBADM privileges shouldn't be
needed to run db2expln?