Invoker rights in Oracle

[surabhi2004]

I am using Oracle 8i.
I have a package. I have some functions and procedures in this package. These functions use SELECT statements and return some value. But these procedures use DML statements and insert/update/delete some tables in the database.
I granted execute on this package to browser_rl (This is a role which has select access to tables/views etc).
Since this package has been given browser_rl, anybody who has browser_rl can execute DML procedure.

Package tst_pkg
procedures proc1, proc2, proc3
functions fnc1, fnc2, fnc3.

My requirement is anybody who has browser_rl should be able to access functions fnc1, fnc2, fnc3. But he/she should not have privilege to run procedures proc1, proc2, proc3 which have DML statements and update the database.

I tried using
CREATE OR REPLACE PACKAGE tst_pkg
AUTHID CURRENT_USER
AS
...
END tst_pkg

But this does not seem to work. By default, oracle uses AUTHID DEFINER.

Thanks for your help.

 

[sem]

I suppose this is an issue of wrong design. My suggestion is to split your package and grant only querying part to common role.

Regards, Dima

http://seminihin.narod.ru

 

[surabhi2004]

AUTHID CURRENT_USER in the package specification worked fine. Any user logging in now is able to access the functions. But the schema which owns the packages only is able to run DML procedures as other uses do not have privilege to perform DML statements.