以前在给一个用户赋于权限的时候,总会赋于create table这个权限,认为create table 系统权限包含了DML操作,今天做了一个测试,和想
象的不一样
创建一个用户并赋于相应权限
SQL> create user myuser1 identified by myuser1
2 default tablespace tools
3 quota unlimited on tools;
User created.
SQL> grant create session,create table to myuser1;
Grant succeeded.
创建表T并且做各种操作,包括创建删除索引,创建primary key,创建删除表,DML操作
SQL> drop table t;
Table dropped.
SQL> create table t(a number,b number);
Table created.
SQL> alter table t add primary key(a);
Table altered.
SQL> create index t_i1 on t(b);
Index created.
SQL> insert into t values(1,1);
1 row created.
SQL> update t set b = 2;
1 row updated.
SQL> delete from t;
1 row deleted.
SQL> drop index t_i1;
Index dropped.
SQL> drop table t;
Table dropped.
那这些权限是通过create table权限得到的吗?下面我们做个实验,先创建T表,再删除create table权限
SQL> create table t(a number,b number);
Table created.
SQL> conn /as sysdba
Connected.
SQL> revoke create table from myuser1;
Revoke succeeded.
SQL> conn myuser1/myuser1
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
MYUSER1 CREATE SESSION NO
SQL> select * from user_tab_privs;
no rows selected
SQL> select * from user_role_privs;
no rows selected
SQL> select * from user_col_privs;
no rows selected
可以看出目前除了CREATE SESSION权限外没有任何其他权限,下面我们做一下上面的操作
SQL> alter table t add primary key(a);
Table altered.
SQL> create index t_i1 on t(b);
Index created.
SQL> insert into t values(1,1);
1 row created.
SQL> update t set b = 2;
1 row updated.
SQL> delete from t;
1 row deleted.
SQL> drop index t_i1;
Index dropped.
SQL> drop table t;
Table dropped.
从上面的实验可以看出,删除索引,创建primary key,创建删除表,DML这些操作和create table权限是无关的
所以,凡事都不能靠想象,要做实验来确认
那自己的表和索引是不是天生就有权限操作呢?那怎样限制用户删除表和索引呢?请高手们解答一下
象的不一样
创建一个用户并赋于相应权限
SQL> create user myuser1 identified by myuser1
2 default tablespace tools
3 quota unlimited on tools;
User created.
SQL> grant create session,create table to myuser1;
Grant succeeded.
创建表T并且做各种操作,包括创建删除索引,创建primary key,创建删除表,DML操作
SQL> drop table t;
Table dropped.
SQL> create table t(a number,b number);
Table created.
SQL> alter table t add primary key(a);
Table altered.
SQL> create index t_i1 on t(b);
Index created.
SQL> insert into t values(1,1);
1 row created.
SQL> update t set b = 2;
1 row updated.
SQL> delete from t;
1 row deleted.
SQL> drop index t_i1;
Index dropped.
SQL> drop table t;
Table dropped.
那这些权限是通过create table权限得到的吗?下面我们做个实验,先创建T表,再删除create table权限
SQL> create table t(a number,b number);
Table created.
SQL> conn /as sysdba
Connected.
SQL> revoke create table from myuser1;
Revoke succeeded.
SQL> conn myuser1/myuser1
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
MYUSER1 CREATE SESSION NO
SQL> select * from user_tab_privs;
no rows selected
SQL> select * from user_role_privs;
no rows selected
SQL> select * from user_col_privs;
no rows selected
可以看出目前除了CREATE SESSION权限外没有任何其他权限,下面我们做一下上面的操作
SQL> alter table t add primary key(a);
Table altered.
SQL> create index t_i1 on t(b);
Index created.
SQL> insert into t values(1,1);
1 row created.
SQL> update t set b = 2;
1 row updated.
SQL> delete from t;
1 row deleted.
SQL> drop index t_i1;
Index dropped.
SQL> drop table t;
Table dropped.
从上面的实验可以看出,删除索引,创建primary key,创建删除表,DML这些操作和create table权限是无关的
所以,凡事都不能靠想象,要做实验来确认
那自己的表和索引是不是天生就有权限操作呢?那怎样限制用户删除表和索引呢?请高手们解答一下
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8745319/viewspace-567454/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8745319/viewspace-567454/