| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: DB Security: PL/SQL and Roles
the issue is that a developer, the person who writes the stored procedure, needs access to the underlying objects directly (I need to grant select on EMP to BOB before BOB can write a stored procedure that selects from EMP).
You cannot create a stored object (view, procedure, etc) that accesses objects you have access to via a role.
This is typically not an issue though. I create a table. I create a procedure that operates on the table. I grant execute on the procedure to a role. the role is granted to end users (end users typically do not create stored procedures, views and the like). My developers must use explicit grants amonst eachother, my end users all get roles. I have a small number of schema's that own code and tables (typically 10's of schemas). I have large number of schema's that access the procedures/tables (end users, 1,000's of em). They use roles, developers do not.
So, a stored procedure cannot use roles to access objects BUT you use roles to control access to the procedures. the end users all get roles. the developers use explicit grants (and if you do not share direct access to objects across schemas, there are no explicit grants). I manage a database with 700+ users. 10 of them are schemas that 'own' objects. the other 700 are end users. We have 40 roles defined in the database and very few cross schema grants (a couple of grant executes from one development schema to another). To add a user, we create one, grant connect and grant the requisite application roles.
On Wed, 02 Jul 1997 20:33:01 +0200, Marcus Scholten <scholten_at_cww.de> wrote:
>Rodney Meeker wrote:
>>
>> 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.
>
>
>We also use PL/SQL und Roles, but we don't have problems. Eventually
>Oracle support has told you rubbish. Can describe your problems in
>detail?
>Marcus
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |