Oracle drop table 和 truncate table對grant授權的影響

標籤：wrap   his   view   audit   each   acl   ext   影響   nec   

1、以sys登陸，建表賦予許可權，準備測試表z2

[[email protected] ~]$ rlwrap sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 14:59:27 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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，能訪問資料

[[email protected] ~]$ rlwrap sqlplus ‘dwetl/"pwd2017"‘@DB1SQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 14:59:01 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from sys.z2;     A----------     1SQL> 

3、再以sys登陸，刪除表z2後，再次建立表z2

[[email protected] ~]$ rlwrap sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 14:59:27 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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----------     1SQL> 

4、再以dwetl登陸，看是否可以查詢到，答案是查不到，報錯表或者視圖不存在

[[email protected] ~]$ rlwrap sqlplus ‘dwetl/"pwd2017"‘@DB1SQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 15:03:10 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from sys.z2;select * from sys.z2                  *ERROR at line 1:ORA-00942: table or view does not existSQL> 

5，再次以sys登陸，賦予表z2給dwetl的許可權

[[email protected] ~]$ rlwrap sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 15:14:16 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> grant select on z2 to dwetl;Grant succeeded.SQL> 

6，再次以dwetl登陸，嘗試是否可以查詢到sys.z2表，OK，可以查詢到資料了

[[email protected] ~]$ rlwrap sqlplus ‘dwetl/"pwd2017"‘@DB1SQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 15:14:57 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from sys.z2;     A----------     1SQL> 

7、truncate對許可權有無影響？

以sys登陸，執行truncate動作表z2，然後退出，以dwetl登陸查詢表z2，發現可以執行select操作

SQL> truncate table z2;Table truncated.SQL> exitERROR:ORA-04088: error during execution of trigger ‘TIMDBA.TRIG_LOGOFF_AUDIT‘ORA-01403: no data foundORA-06512: at line 5Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options (with complications)[[email protected] ~]$ rlwrap sqlplus ‘dwetl/"pwd2017"‘@DB1SQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 15:18:08 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from sys.z2;no rows selectedSQL> 

以上測試實驗結論標識：

(1)：當表被drop掉後，別的使用者對這個表的操作許可權失效了，請記得重新grant下許可權，不然另外的使用者就無法對錶進行操作；

(2)：當表被truncate之後，別的使用者對這張表的操作許可權依然有效。

Oracle drop table 和 truncate table對grant授權的影響