自己的表和索引的DML操作权限

以前在给一个用户赋于权限的时候，总会赋于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权限是无关的
所以，凡事都不能靠想象，要做实验来确认

那自己的表和索引是不是天生就有权限操作呢？那怎样限制用户删除表和索引呢？请高手们解答一下

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8745319/viewspace-567454/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8745319/viewspace-567454/