程序,任务与承认
2024-07-21 02:34:14
供稿：网友
存储过程中需要显示的授予用于用户对象权限，而不是通过角色。全文为tom kyte的回答。 jeffli大虾http://jeffli73.china-pub.com/ 的理解： 刚工作时，编写Oracle的存储过程，就知道即使一个用户被授予了DBA，但其模式下的存储过程在引用其它用户模式下的数据对象（如表）时，仍需要显式授权。当时只是记住了这个结论，也没细想为什么，最近几年已基本不编程了，所以就更没进一步关心这个问题。最近又编了一个存储过程，碰到类似的情况，爱好使然就多查了一些资料，知道了这是与角色相关的问题。 在使用定义者权限的情况下，Oracle在编译存储过程时并不检查定义者拥有的角色，只是检查其被显式授予的权限，而DBA也是一种角色，所以即使是DBA，也需要显式授权。 Oracle之所以这样处理，主要是因为角色可能被授予多个用户，假如存储过程考虑角色权限，在角色权限变动时可能会引发大量存储过程失效或大量的检查工作，而角色又具有非默认性与密码保护，假如某用户在启用某非默认角色时编译了一个存储过程，那么当他注销该角色时该存储过程是否应该继续有效或被执行？假如另外一个用户更改了某角色的密码，那么其它依靠此角色的存储过程是否需要重新编译、是否还能执行？这些都是不好明确回答的问题。 另外，Oracle认为角色的设计目的是治理众多的最终用户，这些用户一般不应该创建数据对象。而拥有数据对象的用户应该只有少数几个，从安全性的角度，也应该明确、显式地说明其应有的权限，而应用角色将降低这种明确性，所以多做一些显式授权的工作是值得的。 See http://osi.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.tom每次都可以从设计的高度看问题。tune desing，not tune application
