oracle mysql dba权限_Oracle 谨慎授予用户dba权限

//我们来看下面这个授权的二叉树，实际中肯定是个图(复杂的图): sys / \ test001(dba) test0

//我们来看下面这个授权的二叉树，实际中肯定是个图(复杂的图):

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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！