Oracle drop table 和 truncate table对grant授权的影响

tyxiayu

[oracle@crl ~]$ rlwrap sqlplus / as sysdba

　　SQL*Plus:>　　Copyright (c) 1982, 2013, Oracle.  All rights reserved.
　　Connected to:

　　Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data Mining and Real Application Testing options
　　SQL> create table z2 as select 1 as a from dual;
　　Table created.
　　SQL> grant select on z2 to dwetl;
　　Grant succeeded.
　　SQL>
　　2、以dwetl账号登陆，对表进行查询权限，查询OK，能访问数据

[oracle@crl ~]$ rlwrap sqlplus 'dwetl/"pwd2017"'@DB1

　　SQL*Plus:>　　Copyright (c) 1982, 2013, Oracle.  All rights reserved.
　　Connected to:

　　Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data Mining and Real Application Testing options
　　SQL> select * from sys.z2;
　　A
　　----------
　　3、再以sys登陆，删除表z2后，再次建立表z2

[oracle@crl ~]$ rlwrap sqlplus / as sysdba

　　SQL*Plus:>　　Copyright (c) 1982, 2013, Oracle.  All rights reserved.
　　Connected to:

　　Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data Mining and Real Application Testing options
　　SQL> drop table sys.z2;
　　Table dropped.
　　SQL> create table z2 as select 1 as a from dual;
　　Table created.
　　SQL>
　　SQL> select * from sys.z2;
　　A
　　4、再以dwetl登陆，看是否可以查询到，答案是查不到，报错表或者视图不存在

[oracle@crl ~]$ rlwrap sqlplus 'dwetl/"pwd2017"'@DB1

　　SQL*Plus:>　　Copyright (c) 1982, 2013,http://027yeshenghuowang.com/  Oracle.  All rights reserved.
　　Connected to:

　　Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data feilcheng88.cn Mining and Real Application Testing options
　　SQL> select * from sys.z2;
　　select * from sys.z2
　　*
　　ERROR at line 1:
　　ORA-00942: table or view does not exist
　　5，再次以sys登陆，赋予表z2给dwetl的权限

[oracle@crl ~]$ rlwrap sqlplus / as sysdba

　　SQL*Plus:>　　Copyright (c) 1982, 2013, Oracle.  All rights reserved.
　　Connected to:

　　Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data Mining and Real Application Testing options
　　SQL> grant select on z2 to dwetl;
　　Grant succeeded.
　　6，再次以dwetl登陆，尝试是否可以查询到sys.z2表，OK，可以查询到数据了

[oracle@crl ~]$ rlwrap sqlplus 'dwetl/"pwd2017"'@DB1

　　SQL*Plus:>　　Copyright (c) 1982, 2013, Oracle.  All rights reserved.
　　Connected to:

　　Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data Mining and Real Application Testing options
　　SQL> select * from sys.z2;
　　A
　　----------
　　7、truncate对权限有无影响？
　　以sys登陆，执行truncate操作表z2www.lafeiyl.cn，然后退出，以dwetl登陆查询表z2，发现可以执行select操作
　　SQL> truncate table z2;
　　Table truncated.
　　SQL> exit
　　ERROR:
　　ORA-04088: error during execution of trigger 'TIMDBA.TRIG_LOGOFF_AUDIT'
　　ORA-01403: no data found
　　ORA-06512: at line 5

　　Disconnected from Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data Mining and Real Application Testing options (with complications)

[oracle@crl ~]$ rlwrap sqlplus 'dwetl/"pwd2017"'@DB1

　　SQL*Plus:>　　Copyright (c http://www.yiqianou.cn/) 1982, 2013, Oracle.  All rights reserved.
　　Connected to:

　　Oracle Database 11g Enterprise Edition>　　With the Partitioning, OLAP, Data Mining and Real Application Testing options
　　SQL> select * from sys.www.yigouyule2.cn/ z2;
　　no rows selected
　　SQL>
　　以上测试实验结论标识：
　　(1)：当表被drop掉后，别的用户对这个表的操作权限失效了，请记得重新grant下权限，不然另外的用户就无法对表进行操作；
　　(2)：当表被truncate之后，别的用户对这张表的操作权限依然有效。
　　顶
　　1