Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    85

    Unanswered: Stinger and GRANTs

    1. If one issues GRANT EXECUTE on procedure schema.* (or even the exact procedure name), should one need to issue
    GRANT EXECUTE on schema.package (package associated with the exact procedure name)?
    2. In the past, at a client, they've done both steps. The way the associated package name was retrieved was taking the 1st 8 characters
    of the IMPLEMENTATION column from syscat.procedures and placing that value in the GRANT statement for the package.
    However, I just noticed that with V8.2 (we're at Stinger signature 8.1.1.72) there's a db2pvm!... value in the IMPLEMENTATION
    column and not the usual Pnnnnnnn beginning. I even tried in the stored procedure specifying NO EXTERNAL ACTION (which isn't
    the default) but the same db2pvm! showed up in syscat.procedures.implementation column.
    Bottom line: Assuming that one needs to correlate a procedure with its package, how can one accomplish this?
    Ruby

    P.S. Here's an UGLY possible solution:
    select char(procname,30) procname, char(pkgname,30) pkgname, create_time from syscat.procedures a, syscat.packages b where procschema='schemaname' and procname='procname' and a.procschema=b.pkgschema and timestampdiff(4,char(timestamp(b.pkg_create_time) - timestamp(a.create_time))) < 1 and pkg_create_time in (select max(pkg_create_time) from syscat.packages);
    Last edited by rubystep; 10-19-04 at 12:21. Reason: typo

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think that would be the right way to figure out which package a procedure belongs to:
    Code:
    select bschema as pkgschema, bname as pkgname 
    from syscat.routines r, sysibm.sysdependencies d
    where r.routinename=? and r.routineschema=?
    and r.specificname=d.dname and r.routineschema=d.dschema
    and d.btype='K' and d.dtype='F';

  3. #3
    Join Date
    Sep 2003
    Posts
    85
    Thanks much. Worked like a champ.
    What about the 1st question about (not) requiring GRANT EXECUTE on package ...?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, since you can have a procedure without a corresponding package (and vice versa) I guess the grants should be given separately.

Posting Permissions

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