Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96

    Unhappy Unanswered: DB2 - Grant Schema

    Hi,
    I have created a user defined function in DB210.5/DB210.1 on Suse Linux by using DB2INST1 schema in sample database.

    CREATE OR REPLACE FUNCTION "DB2INST1"."FN_LEAST"(v1 INT, v2 INT)
    RETURNS INT
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    CONTAINS SQL
    RETURN CASE
    WHEN v1 < v2
    THEN v1
    ELSE v2
    END;

    The udf fn_least will be owned by DB2INST1

    Next I have created a user DB2INST2 and grant the following authorities and privileges.

    GRANT CREATEIN ON SCHEMA DB2INST1 TO USER DB2INST2;
    GRANT DROPIN ON SCHEMA DB2INST1 TO USER DB2INST2;
    GRANT ALTERIN ON SCHEMA DB2INST1 TO USER DB2INST2;

    Then, I login as DB2INST2 and try to replace the function with the same code

    CREATE OR REPLACE FUNCTION "DB2INST1"."FN_LEAST"(v1 INT, v2 INT)
    RETURNS INT
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    CONTAINS SQL
    RETURN CASE
    WHEN v1 < v2
    THEN v1
    ELSE v2
    END;

    A SQL error code show as follow:

    "DB2INST2" does not have the required authorization or privilege to perform operation "REPLACE FUNCTION" on object "DB2INST1.FN_LEAST".. SQLCODE=-551, SQLSTATE=42501, DRIVER=4.16.53

    Script: \.sqlxeditor_project\Script113.sql (SAMPLEB2INST2)

    Next, I granted all authorities to DB2INST2 as follow:
    GRANT CONNECT ON DATABASE TO USER DB2INST2;
    GRANT BINDADD ON DATABASE TO USER DB2INST2;
    GRANT CREATETAB ON DATABASE TO USER DB2INST2;
    GRANT CREATE_NOT_FENCED_ROUTINE ON DATABASE TO USER DB2INST2;
    GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO USER DB2INST2;
    GRANT IMPLICIT_SCHEMA ON DATABASE TO USER DB2INST2;
    GRANT DBADM ON DATABASE TO USER DB2INST2;
    GRANT LOAD ON DATABASE TO USER DB2INST2;
    GRANT QUIESCE_CONNECT ON DATABASE TO USER DB2INST2;
    GRANT SECADM ON DATABASE TO USER DB2INST2;
    GRANT ACCESSCTRL ON DATABASE TO USER DB2INST2;
    GRANT DATAACCESS ON DATABASE TO USER DB2INST2;
    GRANT EXPLAIN ON DATABASE TO USER DB2INST2;
    GRANT SQLADM ON DATABASE TO USER DB2INST2;
    GRANT WLMADM ON DATABASE TO USER DB2INST2;
    GRANT EXECUTE ON FUNCTION DB2INST1.FN_LEAST( INTEGER, INTEGER ) TO USER DB2INST2 WITH GRANT OPTION;

    I try to execute the Create/Replace udf for DB2INST1.FN_LENGTH, it is still the same error code as below.
    "DB2INST2" does not have the required authorization or privilege to perform operation "REPLACE FUNCTION" on object "DB2INST1.FN_LEAST".. SQLCODE=-551, SQLSTATE=42501, DRIVER=4.16.53

    Appreciated if anyone could share some light whether DB2 allow other user than the owner of the schema to replace a udf object. Thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Only the owner of an object can replace that object (which is clearly stated in the manual). Others will need to drop and create the object as separate actions, given sufficient privileges. By the way, schema privileges have nothing to do with it, it's the object privileges that matter.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2012
    Posts
    21
    You need to change the definer of the object. In other words transfer ownership of the object from db2inst1 to db2inst2. Only then will you be able to create/replace w/o dropping and recreating it.

    Hope this helps.

  4. #4
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    Thank you n_i, manawa for the solution. I will proceed with the same.

Posting Permissions

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