[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
----------
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
4、再以dwetl登陆,看是否可以查询到,答案是查不到,报错表或者视图不存在
[oracle@crl ~]$ rlwrap sqlplus 'dwetl/"pwd2017"'@DB1
SQL*Plus: Release 11.2.0http://www.xuancai88c.cn/ .4.0 Production on Tue May 16 15:03:10 2017
Copyright (c) 1982, 2013,http://027yeshenghuowang.com/ Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
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: 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.
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
----------
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 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 http://www.yiqianou.cn/) 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.www.yigouyule2.cn/ z2;
no rows selected
SQL>
以上测试实验结论标识:
(1):当表被drop掉后,别的用户对这个表的操作权限失效了,请记得重新grant下权限,不然另外的用户就无法对表进行操作;
(2):当表被truncate之后,别的用户对这张表的操作权限依然有效。
顶
1
Oracle drop table 和 truncate table对grant授权的影响
最新推荐文章于 2023-09-12 09:40:49 发布