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

最新推荐文章于 2023-09-12 09:40:49 发布

原创最新推荐文章于 2023-09-12 09:40:49 发布 · 1.1w 阅读

2 ·

CC 4.0 BY-SA版权

oracle basic 专栏收录该内容

32 篇文章

订阅专栏

本文通过实验演示了在Oracle数据库中，当表被删除(drop)后，其他用户对该表的操作权限将失效，需要重新授予权限；而当表被清空(truncate)后，其他用户的操作权限仍然有效。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、以sys登陆，建表赋予权限，准备测试表z2

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

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 14:59:27 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
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: Release 11.2.0.4.0 Production on Tue May 16 14:59:01 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from sys.z2;

	 A
----------
	 1

SQL>

3、再以sys登陆，删除表z2后，再次建立表z2

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

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 14:59:27 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
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
----------
	 1

SQL>

4、再以dwetl登陆，看是否可以查询到，答案是查不到，报错表或者视图不存在

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

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 15:03:10 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data 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


SQL>

5，再次以sys登陆，赋予表z2给dwetl的权限

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

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 15:14:16 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant select on z2 to dwetl;

Grant succeeded.

SQL>

6，再次以dwetl登陆，尝试是否可以查询到sys.z2表，OK，可以查询到数据了

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

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 15:14:57 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from sys.z2;

	 A
----------
	 1

SQL>

7、truncate对权限有无影响？

以sys登陆，执行truncate操作表z2，然后退出，以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 Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options (with complications)
[oracle@crl ~]$ rlwrap sqlplus 'dwetl/"pwd2017"'@DB1

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 16 15:18:08 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from sys.z2;

no rows selected

SQL>

以上测试实验结论标识：

(1)：当表被drop掉后，别的用户对这个表的操作权限失效了，请记得重新grant下权限，不然另外的用户就无法对表进行操作；

(2)：当表被truncate之后，别的用户对这张表的操作权限依然有效。