You Asked
Hi Tom,
We are using 2 schemas, user1, user2.
User1 has user1_table, and created role role1. The following was issued:
grant select, insert, update, delete on user1_table to role1;
grant role1 to user2
User2 created private synonym user1_table for user1.user1_table
and tried to create :
create or replace procedure aa as
aa user1_table.column%TYPE;
begin
null;
end;
/
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE AA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/4 PLS-00201: identifier 'USER1.USER1_TABLE' must be declared
2/4 PL/SQL: Item ignored
Why user2 cannot access user1_table in his procedure, if he has rights on the this table
via role1?
We do not want to grant user1_table to user2 directly.
Thanks,
Dusan
and we said...
See
http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html
You have no choice but to grant select on user1_table to user2 directly. It is the only
way to make it work.
Why is it that roles are not used during the compilation of a procedure? It has to do
with the dependency model. Oracle is not storing exactly WHY you are allowed to access T
¡V only that you are (directly able to -- not via a role). Any change to your privileges
that might cause access to T to go away will cause the procedure to become invalid and
necessitate its recompilation. Without roles ¡V that means only ¡§REVOKE SELECT ANY
TABLE¡¨ or ¡§REVOKE SELECT ON T¡¨ from the definers account or from PUBLIC. With roles
¡V it greatly expands the number of times we would invalidate this procedure. If some
role that was granted to some role that was granted to this user was modified, this
procedure might go invalid, even if we did not rely on that privilege from that role.
ROLES are designed to be very fluid when compared to GRANTS given to users as far as
privilege sets go. For a minute, let¡¦s say that roles did give us privileges in stored
objects. Now, most any time anything was revoked from ANY ROLE we had, or any role any
role we have has (and so on -- roles can and are granted to roles) -- many of our objects
would become invalid. Think about that -- REVOKE some privilege from a ROLE and suddenly
your entire database must be recompiled!
Consider the impact of revoking some system privilege from a ROLE, it would be like doing
that to PUBLIC now -- don't do it, just think about it (if you do revoke some powerful
system privilege from PUBLIC, do it on a test database). Revoking SELECT ANY TABLE from
PUBLIC for example would cause virtually every procedure in the database to go invalid.
If procedures relied on roles ¡V virtually every procedure in the database would
constantly become invalid due to small changes in permissions. Since one of the major
benefits of procedures is the ¡§compile once, run many¡¨ model ¡V this would be
disastrous for performance.
Also consider that roles may be
ć Non-default: If I have a non-default role and I enable it and I compile a procedure
that relies on those privileges, when I log out I no longer have that role -- should my
procedure become invalid -- why? Why not? I could easily argue both sides.
ć Password Protected: if someone changes the password on a ROLE, should everything that
might need that role be recompiled? I might be granted that role but not knowing the new
password ¡V I can no longer enable it. Should the privileges still be available? Why or
Why not? Again, arguing either side of this is easy. There are cases for and against
each.
The bottom line with respect to roles in procedures with definers rights are:
ć You have thousands or tens of thousands of end users. They don't create stored objects
(they should not). We need roles to manage these people. Roles are designed for these
people (end users).
ć You have far fewer application schema's (things that hold stored objects). For these
we want to be explicit as to exactly what privileges we need and why. In security terms
this is called the concept of 'least privileges' -- you want to specifically say what
privilege you need and why you need it. If you inherit lots of privileges from roles you
cannot do that effectively. We can manage to be explicit since the number of development
schemas is SMALL (but the number of end users is large)...
ć Having the direct relationship between the definer and the procedure makes for a much
more efficient database. We recompile objects only when we need to, not when we might
need to. It is a large efficiency enhancement.
Комментариев нет:
Отправить комментарий