Oracle SQL Jumpstart with Examples- P12

Oracle SQL Jumpstart with Examples- P12: Review"As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...
Nội dung trích xuất từ tài liệu:
Oracle SQL Jumpstart with Examples- P12 520 23.2 Privileges PRINCE originally granted the CREATE VIEW privilege to ARIEL. Revoked system privileges do not cause cascading revokes; only object priv- ilege revokes can do that. CREATE VIEW CA_ARTISTS AS SELECT * FROM MUSIC.ARTIST WHERE STATE_PROVINCE=CA; We will now examine some rules about revoking privileges. Using graphic examples, here are some key points to remember about how revok- ing of privileges works. 23.2.2.1 Revoked System Privileges DO NOT Cascade When you revoke a system privilege, the revoke affects only the user you are naming and does not affect any objects or users created. For example, SYS- TEM grants the CREATE USER privilege WITH ADMIN OPTION to ASSISTANT. Then ASSISTANT creates a user named INTERN and grants her the CREATE USER privilege. Now, INTERN creates another user named JOE. Figure 23.10 illustrates these events. Figure 23.10 One New User Is Created by Each of These Users: SYSTEM, ASSISTANT, and INTERN.Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 23.2 Privileges 521 Figure 23.11 ASSISTANT Failed to Create MATTHEW, but INTERN Created BETH. Now, as the DBA, you decide that your assistant does not need to create users at this point, so you revoke the CREATE USER privilege from ASSISTANT. ASSISTANT can no longer create users; however, the users she created still exist. And, INTERN, who received the system privilege CREATE USER from ASSISTANT, retains that privilege. Figure 23.11 illustrates this idea by showing that ASSISTANT cannot create a user, while INTERN can create a user. 23.2.2.2 Revoked Object Privileges DO Cascade Revoking an object privilege does result in a cascading set of revoked privi- leges. For example, imagine that SYSTEM grants SELECT on MUSIC.ARTIST to ASSISTANT using the WITH GRANT OPTION clause. Then ASSISTANT grants the same object privilege to INTERN who in turn grants the privilege (without the WITH GRANT OPTION) to JOE. Figure 23.12 shows the scenario. After careful thought, you decide that your assistant no longer requires the SELECT privilege on the MUSIC.ARTIST table, so you revoke the privilege. The revoke actually cascades and revokes the privilege from INTERN, and then it cascades again and revokes the privilege from JOE. Chapter 23Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 522 23.3 Grouping Privileges Using Roles Figure 23.12 SYSTEM, ASSISTANT, and INTERN Grant Object Privileges. Now, only SYSTEM can successfully query the MUSIC.ARTIST table. Figure 23.13 shows how this works. Remember that revoked system privileges do not cascade and revoked object privileges do cascade. One of the more repetitive DBA tasks is that of granting the proper privileges to new users and maintaining privileges for all existing users. Very often, a group of users has identical privileges. The next section shows you how to take advantage of this with roles. Roles allow groupings of privileges and subsequent granting of privilege groups with a single granting or revoke of a role. 23.3 Grouping Privileges Using Roles A role is a set or grouping of object and/or system privileges that is assigned ...