2008-12-09

AUTHID CURRENT_USER range

Last week I learn a new thing about PL/SQL: the range of AUTHID CURRENT_USER is limited! I thought it wasn't :-(!
I had problems with a program with AUTHID CURRENT_USER calling another: I thought that the program it would call would be also with invoker rights but it wasn't!

So I check the documentation and I found this:


How External References Are Resolved in Invoker's Rights Subprograms

If you specify AUTHID CURRENT_USER, the privileges of the current user are checked at run time, and external references are resolved in the schema of the current user. However, this applies only to external references in:

* SELECT, INSERT, UPDATE, and DELETE data manipulation statements
* The LOCK TABLE transaction control statement
* OPEN and OPEN-FOR cursor control statements
* EXECUTE IMMEDIATE and OPEN-FOR-USING dynamic SQL statements
* SQL statements parsed using DBMS_SQL.PARSE()

For all other statements, the privileges of the owner are checked at compile time, and external references are resolved in the schema of the owner.

-- Oracle® Database PL/SQL User's Guide and Reference

So, I change my call to use EXECUTE IMMEDIATE and presto! It worked the way I wanted!
(Maybe I will change the call to DBMS_SQL)

Moral: never think that you know everything!

No comments:

Post a Comment

Os comentários são moderados.
The comments are moderated.