DB Security: PL/SQL and Roles

All-

I am looking for a work around. Oracle support tells me that PL/SQL cannot recognize roles. We want to use roles to simplify our DB security which has become more complex as we add more users to the system. In any event, Oracle support told us that the only way to enforce security and still be able to use PL/SQL stored procedures is to grant on the individual objects. That's crazy. Even though we could write scripts to dynamically build the grant statements, we are looking at better than 9000 grant statements. We are hoping there is an easier way (granting to public is not an option nor is staying within our own schema). If you have any savvy work-arounds that you implemented at your site, please let me know. Whatever assistance you could provide would be greatly appreciated.

As an aside, I know we are not the first organization to run into this. Is anyone else appalled that two of Oracle's major selling points -- PL/SQL and security implementation through Roles -- are not compatible with each other? Oracle has no plans to fix this. Maybe we should launch a letter writing campaign. Your thoughts please. Received on Wed Jul 02 1997 - 00:00:00 CDT