Thanks for the question, Richard.
Asked: March 08, 2002 - 3:19 pm UTC
Last updated: March 08, 2002 - 3:19 pm UTC
Version: 9i
Viewed 1000+ times
You Asked
Hi Tom,
I work on a project where there are several quasi mirrored schemas,
i.e. same table structure different contents.
I would like to implement PL/SQL packages in one schema and
use them for other schemas with:
alter session set current_schema = schema
+
authid current_user in the package definition.
It seems to work so far I could test it.
Q1. Is there a performance penalty when using invoker rights
procedures as compared to definer ones?
Q2. Is there a way to create my packages in a "super"-schema which
is none of the schemas where the package will be utilized
without the need of duplicating the standard schema?
Cheer,
Richard
and Tom said...
First -- if you want the entire story -- I will recommend my book. I go into this in depth (invokers/definers rights)
q1) yes, yes it will. shared sql is definitely impacted. The shared sql cache is somewhat defeated in this environment. The code is also "less stable" due to potential schema difference.
q2) You might want to look into VPD (fine grained access control). See
</code>
http://asktom.oracle.com/~tkyte/article2/index.html <code>
or my book for details. Using this you can use one schema for many people simultaneously -- each thinking they have their OWN schema.