學習筆記 使用者和許可權

sys與system的區別物件許可權與系統許可權角色許可權的區別與許可權相關的資料字典

[@more@]

一句話知識：
1 sysdba 比 sysoper 多的許可權是建庫和更改字符集

對授權後的處理
grant object privileges 之後使用者的刪除造成的影響：
情景一：
A授權給B with grant option
B授權給C
if drop user B then C lost privilege on A.tab

情景二：
A授權給B和D with grant option
B授權給C
D也授權給C
if drop user B then C lost one of two privileges on A.tab
C can select a.tab through D's grant

grant system and role privilege之後與上面的差異如下：
if Oliver grants DBA to Bill using WITH ADMIN OPTION, then Bill grants DBA
to Bonnie, the database does not record that Bill granted to Bonnie.only
that Bonnie has the role privilege. If Bill is dropped, Bonnie still retains the
system and role privileges that Bill granted to her

一個使用者的許可權被收回後，他賦給別的使用者的許可權也一併被收回
if Joshua has used his grant
option and granted David object privileges, then when Joshua’s privilege is
revoked, David’s privilege is revoked along with Joshua’s..

系統許可權與角色許可權與之不同，資料庫不儲存grantor的資訊，這就與賦權者無關，
A notable difference between object privileges and system or role privileges
is that the grantor of the system or role privilege is not kept. Thus, if
Oliver grants DBA to Bill using WITH ADMIN OPTION, then Bill grants DBA
to Bonnie, the database does not record that Bill granted to Bonnie.only
that Bonnie has the role privilege.

練習：
connect system/manager
grant select any table to jon with admin option;
grant select any table to jason;
connect jon/seekrit
grant select any table to jason;
revoke select any table from jason;

A. Jason can select from any table regardless of any individual table
privileges.
B. Jason can only select from tables that he has been granted SELECT
privileges on or has acquired via a role.
C. Jason can only select from his own tables.
D. Jason continues to enjoy the SELECT ANY TABLE privilege.

正確解答：
B. Oracle does not retain the grantor on system privileges, so if anyone
revokes a system privilege, that privilege is gone, even if the
grantee obtained it from more than one grantor. This behavior is the
same as role privileges, but different from object privileges, such as
SELECT, INSERT, or EXECUTE.