Oracle同义词赋权.docx

-1)创建测试用户 user1，user2,user3,并给user2和user3赋创建同义词的权限-dba- Create the user create user user1 identified by user1;- Grant/Revoke role privileges grant connect to user1 with admin option;grant resource to user1 with admin option;- Create the user create user user2 identified by user2;- Grant/Revoke role privileges grant connect to user2 with admin option;grant resource to user2 with admin option;- Grant/Revoke system privileges grant create synonym to USER2 with admin option;-add- Create the user create user user3 identified by user3;- Grant/Revoke role privileges grant connect to user3 with admin option;grant resource to user3 with admin option;- Grant/Revoke system privileges grant create synonym to USER3 with admin option;-add-2）登录user1，创建测试表，将查询权限赋给user2-user1create table syn_test(syn_test char(1);grant select on syn_test to user2;-3）登录user2，创建同义词，测试查询权限-user2- Create the synonym create or replace synonym syn_test for USER1.syn_test;select * from syn_test;-okselect * from user1.syn_test;-ok-4）登录user3，创建同义词，测试查询权限-user3- Create the synonym create or replace synonym syn_test for USER2.syn_test;select * from syn_test;-ORA-00942:表或视图不存在select * from user2.syn_test;-ORA-00942:表或视图不存在-5）登录user2，给user3赋查询同义词权限-user2grant select on syn_test to user3;-ORA-01031:权限不足-dba- Grant/Revoke system privileges grant grant any object privilege to USER2 with admin option;-user2grant select on syn_test to user3;-ok-6）登录user3，测试查询权限，并测试insert和delete权限-user3select * from syn_test;-okselect * from user2.syn_test;-okselect * from user1.syn_test;-okinsert into syn_test values(1);-ORA-01031:权限不足delete from syn_test where syn_test = 1;-ORA-01031:权限不足insert into user2.syn_test values(1);-ORA-01031:权限不足delete from user2.syn_test where syn_test = 1;-ORA-01031:权限不足insert into user1.syn_test values(1);-ORA-01031:权限不足delete from user1.syn_test where syn_test = 1;-ORA-01031:权限不足-7）登录user2，赋insert和delete权限给user3，并测试自己的insert和delete权限-user2grant insert on syn_test to user3;-okgrant delete on syn_test to user3;-okinsert into syn_test values(1);-ORA-01031:权限不足delete from syn_test where syn_test = 1;-ORA-01031:权限不足insert into user2.syn_test values(1);-ORA-01031:权限不足delete from user2.syn_test where syn_test = 1;-ORA-01031:权限不足insert into user1.syn_test values(1);-ORA-01031:权限不足delete from user1.syn_test where syn_test = 1;-ORA-01031:权限不足-8）登录user3，测试insert和delete权限，发现user1表的权限通过user2给放大了-user3insert into syn_test values(1);-ok 权限被 user2 放大了delete from syn_test where syn_test = 1;-ok 权限被 user2 放大了insert into user2.syn_test values(1);-ok 权限被 user2 放大了delete from user2.syn_test where syn_test = 1;-ok 权限被 user2 放大了insert into user1.syn_test values(1);-ok 权限被 user2 放大了delete from user1.syn_test where syn_test = 1;-ok 权限被 user2 放大了-9)登录user2，收回insert和delete权限-user2revoke insert on syn_test from user3;-okrevoke delete on syn_test from user3;-ok-10）登录user3，测试权限回收情况-user3insert into syn_test values(1);-ORA-01031:权限不足delete from syn_test where syn_test = 1;-ORA-01031:权限不足insert into user2.syn_test values(1);-ORA-01031:权限不足delete from user2.syn_test where syn_test = 1;-ORA-01031:权限不足insert into user1.syn_test values(1);-ORA-01031:权限不足delete from user1.syn_test where syn_test = 1;-ORA-01031:权限不足11）登录user1，给user3赋insert和delete权限-user1grant insert on syn_test to user3;-okgrant delete on syn_test to user3;-ok12）登录user3，测试insert和delete权限-user3insert into syn_test values(1);-okdelete from syn_test where syn_test = 1;-okinsert into user2.syn_test values(1);-okdelete from user2.syn_test where syn_test = 1;-okinsert into user1.syn_test values(1);-okdelete from user1.syn_test where syn_test = 1;-ok13）登录user2，测试insert和delete权限-user2insert into syn_test values(1);-ORA-01031:权限不足delete from syn_test where syn_test = 1;-ORA-01031:权限不足insert into user2.syn_test values(1);-ORA-01031:权限不足delete from user2.syn_test where syn_test = 1;-ORA-01031:权限不足inser