程序,任務與承認
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
