Stinger and GRANTs

rubystep · #1 (**permalink**) 10-19-04, 10:55

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);

n_i · #2 (**permalink**) 10-19-04, 13:59

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';