Re: Stored Procedure Use vs. Role Use

> Evidently who ever executes the stored procedure, has to be granted
> access to the tables directly, not through roles. It's not the owner of
> the stored procedure, but the person executing it.
That's not been my experience. I wrote a package that references dba_* tables. The *only* users with SELECT on them is SYS and SYSTEM. I compiled my package as SYS and granted EXECUTE to PUBLIC. Now everyone can select from these tables but *only* via my package, running my supplied procedures. This is the kind of security you want -- the only access to certain tables is provided strictly through an interface library implemented in PL/SQL stored subprograms. And you can grant EXECUTE to roles; but now you're anticipating having a variety of interface routines for different roles, collected into various packages, perhaps. For example, the insert and select routines for table x go into one package that the SALES role can execute. Meanwhile, the update and delete routines go into another package which the MANAGERS role has execute privilege on (and may also have execute on the package SALES can execute).

• Dan Clamage dclamage_at_idcomm.com