Results 1 to 6 of 6

Thread: Privilege issue

  1. #1
    Join Date
    Aug 2012
    Posts
    23

    Unanswered: EXECUTE versus REPLACE procdure

    Hi,

    In my environment we are using DB2 v9.7 on RHEL 5.5.
    my question is when the user is compiling procedures it is giving error.
    error is user doesn't have required authorization or privilege to perform REPLACE PROCEDURE operation.
    but he executed the stored procedures before the day.
    eventhough i have given the privilege at my end like
    db2 "grant execute on procedure schemaname.procedurename to user";
    the command executed successfully.
    so,please suggest on this issue with high priority.....

  2. #2
    Join Date
    Aug 2012
    Posts
    23
    Hi,

    In my environment we are using DB2 v9.7 on RHEL 5.5.
    my question is when the user is compiling procedures it is giving error.
    error is user doesn't have required authorization or privilege to perform REPLACE PROCEDURE operation.
    but he executed the stored procedures before the day.
    eventhough i have given the privilege at my end like
    db2 "grant execute on procedure schemaname.procedurename to user";
    the command executed successfully.
    so,please suggest on this issue with high priority.....

  3. #3
    Join Date
    Aug 2012
    Posts
    23

    Privilege issue

    Hi,

    We are using DB2 v9.7 fix pack 0 with ESE on RHEL5.5.
    when the user is compiling stored procedures he is getting the error like user is not able to perform required authorization or privilege on REPLACE PROCEDURES under schemaname.packagename.
    but i have given the privilege to user like
    db2 "grant execute on procedure schemaname.procedurename to user";
    eventhough he is not able to compile it.

    please give me reply asap.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a rough analogy, but maybe it will help illustrate your problem. With stored procedures, EXECUTE is analagous to SELECT on a table while REPLACE is analagous to UPDATE. If you give a login permission to SELECT on a table, would you expect it to automatically get permission to update that table? I certainly hope not!

    Please review Help - IBM to see what permission apply to creating and managing stored procedures so you can determine which permissions you need to grant.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2012
    Posts
    23
    Hi,
    We are using DB2 v9.7 fix pack 0 with ESE on RHEL5.5.
    I am trying to create a simple procedure but the screen is getting hang.

    create or replace PROCEDURE DATA.SP_MUST_INSERT_DATATYPES (
    pIn IN INTEGER
    )
    AS
    i INTEGER;

    BEGIN
    i :=1;

    END DATA.SP_MUST_INSERT_DATATYPES;

    please suggest on the above issue.i need urgent help.and will just want to know this issue is related to Client tool issue or server issue.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    grant execute is after the create to able to run the procedure
    according the doc:
    Authorization
    The privileges held by the authorization ID of the statement must include at least one of the following:
    BINDADD privilege on the database, and one of the following:
    IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the procedure does not exist
    CREATEIN privilege on the schema, if the schema name of the procedure refers to an existing schema SYSADM or DBADM authority
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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