By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

Are DBADM privs really needed for db2expln?

P: n/a
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/
Feb 12 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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?
Feb 12 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.