Re: GRANT/PRIVILEGE PROBLEM: URGENT

Hi Doug (and Julio)

I will describe the following situation, which is how it should work under Oracle 7.
In this particular app no end-user has direct insert, update, delete privileges. Everything is done through packages. The packages are owned by the owner of the tables. Packages run with the privileges of the owner automatically, roles are not being used. In this case no extra privileges need to be given to end-user. That problem should only arise when the package are owned by an user that is not the table owner. The end-users have been granted execute rights on the package through roles. Works without problem. No direct grants on all objects to all users have been made as the packages are owned by the owner of the table. Roles are disregarded because PL/SQL is compiled, and roles potentially change on the fly. This would result in recompilation with every call. Thomas Kyte posted several examples in the last few months. In Oracle 8 this changes in sofar you can run a package with definer rights (the owner of the package) or invokers rights (the user calling the package)

Best regards,

Sybrand Bakker, Oracle DBA

Doug Cowles wrote in message <37408B8B.7AC3120F_at_bigfoot.com>...
>I'd be interested in the script....
>Is this true of the latest and greatest Oracle versions that role
privileges
>are not
>used by a stored procedure or package? Even if they belong to the owner of
>the package or procedure? Not making a judgement, just wondering.
>
>- Dc.
>
>Julio Negueruela wrote:
>
>> The man from the great white north escribió:
>> >
>> > DATABASE: 7.3.4.3 Sparc Solaris 2.5.1
>> >
>> > User TST_ENT wants to let user ADMIN truncate one of its table.
>>
>> You must grant it the drop any table privilege.
>>
>> > PLEASE NOTE: A couple of weeks ago, one of my databases started
>> > showing all kinds of problems related to grants (execute) on
>> > procedures and packages. Even if I granted execute to procedures, I
>> > would get an insufficient privilege or not found message. The solution
>> > was finally to shut down and restart the database. Very strange...
>>
>> You cannot grant execute on procedures, packages or functions to a role
>> (you can, but it won't wotk), you must grant it to every user. I've your
>> interested, I've got a procedure wich grants the execute privilege on a
>> package,... to every user who have a given role.
>>
>> hope this help.
>> --
>> Julio Negueruela
>> DBA Servicio Informático
>> Universidad de La Rioja - Spain
>> Telf: 941-299179 Fax: 941- 299180
>> mailto:julio.negueruela_at_si.unirioja.es
>
>
>
Received on Mon May 17 1999 - 23:17:25 CDT