Wednesday 14 September 2011

AUTHID for viewers

Question:
Can I use AUTHID_CURRENT_USER for a view if I know we can use it for packages and procedures?
Answer:
Sonal, here are some rules to keep in mind regarding AUTHID and views:
• AUTHID CURRENT_USER is only available for use in PL/SQL program units.
• It cannot be used in the definition of a view.
• The name of a table or a view referred to by a PL/SQL subprogram is resolved at compile-time with authid definer and at run-time with authid current_user.
• A view is effectively always definer's rights. The names to which it refers are resolved at view compile time.
• An invoker's-rights PL/SQL subprogram cannot make a particular view see different things according to the current user. (However, it can see a different view according to the current user.) By extension, the owner of the view needs whatever privileges are required to access the referred objects. The privileges of the invoker on the view's referred objects is never significant.
To test this for yourself, download this zip file and run the script authid_with_view_test_setup.sql to set up schemas and objects. Then run the authid_with_view_test.sql script to see what happens with AUTHID and views.

No comments:

Post a Comment