Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    55

    Unanswered: control privilege for DB2 function

    Any body know how can I control the execute privilege on a SQL DB2 function. ( Unix / Linux or Windows)

    eg. revoke / grant execute on function from/to user.

    Thanks,

    Guyang

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Guyang,

    You can only grant/revoke privaleges on packages. The trick is to figure out what the package name for a function is. Try SQL similar to this to figure it out:

    select pd.pkgschema,pd.pkgname,p.valid from syscat.packagedep as pd,syscat.packages as p, syscat.functions as f where (pd.bname = f.specificname) and (f.funcschema = 'MYSCHEMA') and (funcname = 'MYFUNC') and (pd.pkgschema = p.pkgschema) and (pd.pkgname = p.pkgname) and (pd.bschema = f.funcschema)


    Note: you may get back more than one entry back (I do not know why) so be prepared.

    HTH

    Andy

  3. #3
    Join Date
    May 2002
    Posts
    55
    Andy,

    Thanks for you reply.
    Based on your SQL, I provide my function name and schema and get the package name related to that function back.

    With this package name, I query the syscat.packageauth and then revoke the " execute and bind " privilege from public. I hope to use this way to control the execute on my function.

    But when I do the test, It seems all normal (public) users still can use the function retrieve data.

    Any idea or suggestion.

    Thanks,

    Guyang

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Guyang,

    I am not really a security guru on functions. You can try the SET PATH statement and leave out the Schema that the UDF resides in.
    This will disallow then to use any function in that schema though.

    Andy

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Andy ... I assume you mean SET FUNCTION PATH ...

    Cheers
    Sathyaram

    Originally posted by ARWinner
    Guyang,

    I am not really a security guru on functions. You can try the SET PATH statement and leave out the Schema that the UDF resides in.
    This will disallow then to use any function in that schema though.

    Andy
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Sathyaram,

    Yes, according to my SQL manual the words CURRENT and FUNCTION are optional.

    Andy

Posting Permissions

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