oracle 谨慎授予用户dba权限

//我们来看下面这个授权的二叉树，实际中肯定是个图(复杂的图): sys / / test001(dba) test002(dba) / / / t1 t2 t3 -- //如上二叉树所示，假设test001和test002都被授予了dba权限， //test001和test002又各自创建了用户t1，t2，t3； //拥有了dba权限的用户，权限是无限大的，就和sys一样的； //下面来看一个实例： Connected as SYS //创建两个用户test001和test002，并授予dba权限: create user test001 identified by test001 default tablespace users; grant resource,connect,dba to test001; create user test002 identified by test002 default tablespace users; grant resource,connect,dba to test002; //在test001用户下面创建普通用户t1，t2，并在test001用户下面建立一张表： Connected as test001 create user t1 identified by t1; grant resource,connect to t1; -- create user t2 identified by t2; grant resource,connect to t2; -- create table t_temp( col_1 number(5), col_2 date default sysdate, col_3 varchar2(30)); -- insert into t_temp select 1001,to_date('2011-01-01','yyyy-mm-dd'),'begin of 2011' from dual union all select 1002,to_date('2011-04-30','yyyy-mm-dd'),'end of april' from dual; //用户t1登陆，并创建一张表: Connected as t1 create table t( id number(2), name varchar2(20), addr varchar2(30)); insert into t select 12,'James','shanghai' from dual union all select 13,'Thomas','changchun' from dual union all select 25,'Smith','beijing' from dual; //因为test002用户具有dba权限，那么他就能够访问任何用户下的对象(table，view，procedure...) //并且可以任意操作其他用户下的对象: Connected as test002 select * from test001.t_temp; COL_1 COL_2 COL_3 ------ ----------- ------------------------------ 1001 2011-01-01 begin of 2011 1002 2011-04-30 end of april -- select * from t1.t; ID NAME ADDR --- -------------------- ------------------------------ 12 James shanghai 13 Thomas changchun 25 Smith beijing -- drop table t1.t; commit; Connected as t1 select * from t1 ORA-00942: table or view does not exist -- Connected as test002 drop table test001.t_temp; -- Connected as test001 select * from t_temp ORA-00942: table or view does not exist //从这里，你可以知道拥有dba权限的用户的权利是多大了吧！ //所以在实际应用中，一定要谨慎的给予用户dba权限. //不具备dba权限的用户，在没有被授权访问的情况下，不能访问其他用户下的对象。 Connected as test002 grant resource,connect to t3; Connected as t3 create table t3_temp( col_1 varchar2(10), col_2 number(5), col_3 varchar2(5)); -- select * from t1.t ORA-00942: table or view does not exist drop table t1.t ORA-00942: table or view does not exist //这里并没有提示ORA-01031:insufficient privileges， //而是告诉当前用户ORA-00942: table or view does not exist， //其实用户t3并不知道用户t1的存在. //但是我们可以通过授权机制，实现不同用户之间的相互访问， //前提是该普通用户具有授权的权限 Connected as t1 grant select on t to t3; Connected as t3 select * from t1.t; ID NAME ADDR --- -------------------- ------------------------------ 12 James shanghai 13 Thomas changchun 25 Smith beijing 

原帖:http://topic.youkuaiyun.com/u/20110430/11/cce8abe1-fb23-4d1f-b257-e05b1db4f53c.html?99867

参考:

[1]oracle 创建用户:http://database.51cto.com/art/200911/160283.htm