Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    30

    Question Unanswered: Granting Execute Authority to SYSPROC.ADMIN_CMD

    Hello All,

    Good day.

    I have just found out that our test and production environments are very different. Some of the procedures developed in test do not function in production.


    Here is the grant authority in test for ADMIN_CMD:

    GRANTOR.................GRANTEE..............GRANT EETYPE.....SCHEMA
    --------------------------------------------------------------------
    SYSIBM...................I2TESTENV............U... ..................SYSPROC
    I2TESTENV...............PUBLIC.................G.. ...................SYSPROC
    --------------------------------------------------------------------

    SPECIFICNAME......ROUTINETYPE......EXECUTEAUTH
    --------------------------------------------------------------------
    ADMIN_CMD.................P.....................G
    ADMIN_CMD.................P.....................Y
    --------------------------------------------------------------------


    This is the grant authority in production for ADMIN_CMD:

    GRANTOR.................GRANTEE..............GRANT EETYPE.....SCHEMA
    -------------------------------------------------------------------
    SYSIBM...................PUBLIC.................U. .................SYSPROC
    -------------------------------------------------------------------


    SPECIFICNAME......ROUTINETYPE......EXECUTEAUTH
    -------------------------------------------------------------------
    ADMIN_CMD.................P.....................G
    -------------------------------------------------------------------

    Our instance user in production is I2PRODENV. All this were queried using
    select * from syscat.routineauth where specificname like '%ADMIN%';


    My problem is how do I grant the Execute Authority to I2PRODENV (or any user under production) the SYSPROC.ADMIN_CMD.

    Thank you in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Either I missed something vital in your question or the answer is really a simple GRANT EXECUTE statement:

    http://publib.boulder.ibm.com/infoce...c/r0007699.htm

    Anyway, the instance owner I2PRODENV has SYSADM authority, which includes DBADM. Therefore, that user implicitly has EXECUTE privileges on the routine already.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Mar 2007
    Posts
    30
    Thanks stolze. Pardon me for not understanding. How was it that you determined I2PRODENV has SYSADM authority?

    I have been toying around with that GRANT EXECUTE but does it follow that once the PUBLIC has EXECUTE privilege all other users will have that too? :-)

    Thanks again.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by drogomoss
    Thanks stolze. Pardon me for not understanding. How was it that you determined I2PRODENV has SYSADM authority?
    You said that yourself: "Our instance user in production is I2PRODENV."

    I have been toying around with that GRANT EXECUTE but does it follow that once the PUBLIC has EXECUTE privilege all other users will have that too? :-)
    If you grant the EXECUTE privilege to PUBLIC, then the answer is yes. But you could also grant that privilege to specific users or groups. So you can apply a much finer control if necessary.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Mar 2007
    Posts
    30
    Thanks Stolze. Now its turning up to be not an authority problem. It still is returning an "Unexpected System Error".

    Thanks for all the help.

  6. #6
    Join Date
    Mar 2007
    Posts
    30
    Hello,

    I still keep getting this error.

    call MDMPROD.PROC_SCP_BKP;
    SQL1042C An unexpected system error occurred. SQLSTATE=58004

    Thanks

  7. #7
    Join Date
    Mar 2007
    Posts
    30
    Regarding the same problem above.

    Executing the ADMIN_CMD in production won't work. We have this one rs6000 partitioned to hold the Database servers(SERVER1) and the Applications server (SERVER2). The database holds the test and production database and the applications server holds all applications...

    Using db2 connect on the command line. I can access the test database in SERVER1 and perform a stored proc using the ADMIN_CMD. But I can't access the prod database in SERVER1. And a stroed proc using ADMIN_CMD produces the: call MDMPROD.PROC_SCP_BKP;
    SQL1042C An unexpected system error occurred. SQLSTATE=58004.

    BUT I can access the prod database using db2 connect from SERVER2.

    Our DBA can't help us says its our problem not his. I was pulled from a different project and dumped into this one.

    Any help would be greatly appreciated.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you get an SQL1042, you may want to have a look at the output from the db2diag command to figure out if something was logged during the operation. That can help you (or your DBA) to diagnose and fix the issue.

    p.s: You should try to get a new DBA because SQLxxxxC are critical errors, which should be investigated. And those are the DBA's problem. If you get stuck, you can call IBM support and open a PMR to get this resolved.
    Last edited by stolze; 05-17-07 at 08:31.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •