本文将分享Oracle回收dba权限导致无表空间权限解决记录的详细内容，并且还将对oracle回收用户dba权限进行详尽解释，此外，我们还将为大家带来关于1、oracle11g中为system账户赋予
本文将分享Oracle回收dba权限导致无表空间权限解决记录的详细内容，并且还将对oracle回收用户dba权限进行详尽解释，此外，我们还将为大家带来关于1、oracle11g中为system账户赋予sysdba权限？、grid与oracle用户下oracle程序权限不一致导致无法连接ASM问题、JDBC连接oracle的sysdba权限的用户、OB Oracle 系统视图权限导致的故障一例的相关知识，希望对你有所帮助。
本文目录一览：- Oracle回收dba权限导致无表空间权限解决记录（oracle回收用户dba权限）
- 1、oracle11g中为system账户赋予sysdba权限？
- grid与oracle用户下oracle程序权限不一致导致无法连接ASM问题
- JDBC连接oracle的sysdba权限的用户
- OB Oracle 系统视图权限导致的故障一例
Oracle回收dba权限导致无表空间权限解决记录（oracle回收用户dba权限）
一、原因：oracle在回收某用户DBA角色时，会同时收回该用户的UNLIMITED TABLESPACE权限。
二、结果：导致用户无UNLIMITED TABLESPACE权限造成最终造成业务中断
三、解决：回收DBA角色时，需要重新将必要的权限授权给对应用户
实际操作如下：
1、查看当前系统 ORACLE_SID
# su - oracle
$ cat /etc/oratab
erp:/oracle/app/oracle/product/11.2.0/dbhome_1:N
orcl:/oracle/app/oracle/product/11.2.0/dbhome_1:N2、查看默认的 ORACLE_SID
$ echo $ORACLE_SID
erp
$ sqlplus / as sysdba3、切换 ORACLE_SID
$ export ORACLE_SID=orcl
$ echo $ORACLE_SID
orcl
$ sqlplus / as sysdba4、查看当前实例下开放使用的用户
SQL> select username from dba_users where account_status=''OPEN'';
USERNAME
------------------------------
SYS
SYSTEM
BACKUP
CRM
4 rows selected.5、查询开放的用户具有哪些 dba_role 权限（注意CRM要大写）
SQL> select * from dba_role_privs where GRANTEE=''CRM'' ;
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
CRM RESOURCE NO YES
CRM CONNECT NO YES
CRM DBA NO YES6、查询开放的用户具有哪些 dba_sys 权限
SQL> select * from dba_sys_privs where GRANTEE=''CRM'';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CRM CREATE PUBLIC SYNONYM NO
CRM CREATE VIEW NO
CRM DROP PUBLIC SYNONYM NO
CRM UNLIMITED TABLESPACE NO7、回收DBA权限，再次查看CRM具有哪些 dba_role 权限：
SQL> revoke dba from CRM;
Revoke succeeded.
SQL> select * from dba_role_privs where GRANTEE=''CRM'';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
CRM RESOURCE NO YES
CRM CONNECT NO YES再次查看CRM具有哪些 dba_sys 权限（对比发现unlimited tablespace权限也被回收）
SQL> select * from dba_sys_privs where GRANTEE=''CRM'';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CRM CREATE PUBLIC SYNONYM NO
CRM CREATE VIEW NO
CRM DROP PUBLIC SYNONYM NO8、重新授权unlimited tablespace权限，再次查询相关权限
SQL> grant unlimited tablespace to CRM;
Grant succeeded.
SQL> select * from dba_role_privs where GRANTEE=''CRM'';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
CRM RESOURCE NO YES
CRM CONNECT NO YES
SQL> select * from dba_sys_privs where GRANTEE=''CRM'';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CRM CREATE PUBLIC SYNONYM NO
CRM CREATE VIEW NO
CRM DROP PUBLIC SYNONYM NO
CRM UNLIMITED TABLESPACE NO9、最后通过sqlplus验证连接正常；查看业务日志正常；
注：在不影响业务使用的情况下，尽最大可能回收DBA相关的权限，尤其是生产环境，切记权限不可混乱使用，以免造成数据丢失，无法挽回。
connect权限简介：
connect 权限：分配给普通用户；
该权限具有：
alter session —— 修改会话；
create cluster —— 创建聚簇；
create database link —— 创建数据库连接；
create sequence —— 创建序列；
create session —— 创建会话；
create synonym —— 创建同义词；
create view —— 创建视图；resource 权限简介：
resource 权限：分配给设计人员；
该权限具有：
create cluster —— 创建聚簇；
create procedure —— 创建过程；
create sequence —— 创建序列；
create table —— 创建表；
create trigger —— 创建触发器；
create type —— 建类型；符哪些权限为DBA权限：
select * from dba_sys_privs where grantee = ''DBA'' order by privilege;
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA ADMINISTER ANY SQL TUNING SET YES
DBA ADMINISTER DATABASE TRIGGER YES
DBA ADMINISTER RESOURCE MANAGER YES
DBA ADMINISTER SQL MANAGEMENT OBJECT YES
DBA ADMINISTER SQL TUNING SET YES
DBA ADVISOR YES
DBA ALTER ANY ASSEMBLY YES
DBA ALTER ANY CLUSTER YES
DBA ALTER ANY CUBE YES
DBA ALTER ANY CUBE DIMENSION YES
DBA ALTER ANY DIMENSION YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA ALTER ANY EDITION YES
DBA ALTER ANY EVALUATION CONTEXT YES
DBA ALTER ANY INDEX YES
DBA ALTER ANY INDEXTYPE YES
DBA ALTER ANY LIBRARY YES
DBA ALTER ANY MATERIALIZED VIEW YES
DBA ALTER ANY MINING MODEL YES
DBA ALTER ANY OPERATOR YES
DBA ALTER ANY OUTLINE YES
DBA ALTER ANY PROCEDURE YES
DBA ALTER ANY ROLE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA ALTER ANY RULE YES
DBA ALTER ANY RULE SET YES
DBA ALTER ANY SEQUENCE YES
DBA ALTER ANY SQL PROFILE YES
DBA ALTER ANY TABLE YES
DBA ALTER ANY TRIGGER YES
DBA ALTER ANY TYPE YES
DBA ALTER DATABASE YES
DBA ALTER PROFILE YES
DBA ALTER RESOURCE COST YES
DBA ALTER ROLLBACK SEGMENT YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA ALTER SESSION YES
DBA ALTER SYSTEM YES
DBA ALTER TABLESPACE YES
DBA ALTER USER YES
DBA ANALYZE ANY YES
DBA ANALYZE ANY DICTIONARY YES
DBA AUDIT ANY YES
DBA AUDIT SYSTEM YES
DBA BACKUP ANY TABLE YES
DBA BECOME USER YES
DBA CHANGE NOTIFICATION YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA COMMENT ANY MINING MODEL YES
DBA COMMENT ANY TABLE YES
DBA CREATE ANY ASSEMBLY YES
DBA CREATE ANY CLUSTER YES
DBA CREATE ANY CONTEXT YES
DBA CREATE ANY CUBE YES
DBA CREATE ANY CUBE BUILD PROCESS YES
DBA CREATE ANY CUBE DIMENSION YES
DBA CREATE ANY DIMENSION YES
DBA CREATE ANY DIRECTORY YES
DBA CREATE ANY EDITION YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE ANY EVALUATION CONTEXT YES
DBA CREATE ANY INDEX YES
DBA CREATE ANY INDEXTYPE YES
DBA CREATE ANY JOB YES
DBA CREATE ANY LIBRARY YES
DBA CREATE ANY MATERIALIZED VIEW YES
DBA CREATE ANY MEASURE FOLDER YES
DBA CREATE ANY MINING MODEL YES
DBA CREATE ANY OPERATOR YES
DBA CREATE ANY OUTLINE YES
DBA CREATE ANY PROCEDURE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE ANY RULE YES
DBA CREATE ANY RULE SET YES
DBA CREATE ANY SEQUENCE YES
DBA CREATE ANY SQL PROFILE YES
DBA CREATE ANY SYNONYM YES
DBA CREATE ANY TABLE YES
DBA CREATE ANY TRIGGER YES
DBA CREATE ANY TYPE YES
DBA CREATE ANY VIEW YES
DBA CREATE ASSEMBLY YES
DBA CREATE CLUSTER YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE CUBE YES
DBA CREATE CUBE BUILD PROCESS YES
DBA CREATE CUBE DIMENSION YES
DBA CREATE DATABASE LINK YES
DBA CREATE DIMENSION YES
DBA CREATE EVALUATION CONTEXT YES
DBA CREATE EXTERNAL JOB YES
DBA CREATE INDEXTYPE YES
DBA CREATE JOB YES
DBA CREATE LIBRARY YES
DBA CREATE MATERIALIZED VIEW YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE MEASURE FOLDER YES
DBA CREATE MINING MODEL YES
DBA CREATE OPERATOR YES
DBA CREATE PROCEDURE YES
DBA CREATE PROFILE YES
DBA CREATE PUBLIC DATABASE LINK YES
DBA CREATE PUBLIC SYNONYM YES
DBA CREATE ROLE YES
DBA CREATE ROLLBACK SEGMENT YES
DBA CREATE RULE YES
DBA CREATE RULE SET YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE SEQUENCE YES
DBA CREATE SESSION YES
DBA CREATE SYNONYM YES
DBA CREATE TABLE YES
DBA CREATE TABLESPACE YES
DBA CREATE TRIGGER YES
DBA CREATE TYPE YES
DBA CREATE USER YES
DBA CREATE VIEW YES
DBA DEBUG ANY PROCEDURE YES
DBA DEBUG CONNECT SESSION YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA DELETE ANY CUBE DIMENSION YES
DBA DELETE ANY MEASURE FOLDER YES
DBA DELETE ANY TABLE YES
DBA DEQUEUE ANY QUEUE YES
DBA DROP ANY ASSEMBLY YES
DBA DROP ANY CLUSTER YES
DBA DROP ANY CONTEXT YES
DBA DROP ANY CUBE YES
DBA DROP ANY CUBE BUILD PROCESS YES
DBA DROP ANY CUBE DIMENSION YES
DBA DROP ANY DIMENSION YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA DROP ANY DIRECTORY YES
DBA DROP ANY EDITION YES
DBA DROP ANY EVALUATION CONTEXT YES
DBA DROP ANY INDEX YES
DBA DROP ANY INDEXTYPE YES
DBA DROP ANY LIBRARY YES
DBA DROP ANY MATERIALIZED VIEW YES
DBA DROP ANY MEASURE FOLDER YES
DBA DROP ANY MINING MODEL YES
DBA DROP ANY OPERATOR YES
DBA DROP ANY OUTLINE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA DROP ANY PROCEDURE YES
DBA DROP ANY ROLE YES
DBA DROP ANY RULE YES
DBA DROP ANY RULE SET YES
DBA DROP ANY SEQUENCE YES
DBA DROP ANY SQL PROFILE YES
DBA DROP ANY SYNONYM YES
DBA DROP ANY TABLE YES
DBA DROP ANY TRIGGER YES
DBA DROP ANY TYPE YES
DBA DROP ANY VIEW YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA DROP PROFILE YES
DBA DROP PUBLIC DATABASE LINK YES
DBA DROP PUBLIC SYNONYM YES
DBA DROP ROLLBACK SEGMENT YES
DBA DROP TABLESPACE YES
DBA DROP USER YES
DBA ENQUEUE ANY QUEUE YES
DBA EXECUTE ANY ASSEMBLY YES
DBA EXECUTE ANY CLASS YES
DBA EXECUTE ANY EVALUATION CONTEXT YES
DBA EXECUTE ANY INDEXTYPE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA EXECUTE ANY LIBRARY YES
DBA EXECUTE ANY OPERATOR YES
DBA EXECUTE ANY PROCEDURE YES
DBA EXECUTE ANY PROGRAM YES
DBA EXECUTE ANY RULE YES
DBA EXECUTE ANY RULE SET YES
DBA EXECUTE ANY TYPE YES
DBA EXECUTE ASSEMBLY YES
DBA EXPORT FULL DATABASE YES
DBA FLASHBACK ANY TABLE YES
DBA FLASHBACK ARCHIVE ADMINISTER YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA FORCE ANY TRANSACTION YES
DBA FORCE TRANSACTION YES
DBA GLOBAL QUERY REWRITE YES
DBA GRANT ANY OBJECT PRIVILEGE YES
DBA GRANT ANY PRIVILEGE YES
DBA GRANT ANY ROLE YES
DBA IMPORT FULL DATABASE YES
DBA INSERT ANY CUBE DIMENSION YES
DBA INSERT ANY MEASURE FOLDER YES
DBA INSERT ANY TABLE YES
DBA LOCK ANY TABLE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA MANAGE ANY FILE GROUP YES
DBA MANAGE ANY QUEUE YES
DBA MANAGE FILE GROUP YES
DBA MANAGE SCHEDULER YES
DBA MANAGE TABLESPACE YES
DBA MERGE ANY VIEW YES
DBA ON COMMIT REFRESH YES
DBA QUERY REWRITE YES
DBA READ ANY FILE GROUP YES
DBA RESTRICTED SESSION YES
DBA RESUMABLE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA SELECT ANY CUBE YES
DBA SELECT ANY CUBE DIMENSION YES
DBA SELECT ANY DICTIONARY YES
DBA SELECT ANY MINING MODEL YES
DBA SELECT ANY SEQUENCE YES
DBA SELECT ANY TABLE YES
DBA SELECT ANY TRANSACTION YES
DBA UNDER ANY TABLE YES
DBA UNDER ANY TYPE YES
DBA UNDER ANY VIEW YES
DBA UPDATE ANY CUBE YES
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA UPDATE ANY CUBE BUILD PROCESS YES
DBA UPDATE ANY CUBE DIMENSION YES
DBA UPDATE ANY TABLE 
1、oracle11g中为system账户赋予sysdba权限？
问题：之前用Database Configuration Assistant创建数据库时没有注意权限，导致在删除数据库时报ora-01031,提示用sysdba权限删除（system账户不行）。
原因：oracle登录时默认操作系统账户，本机电脑是Administrator账户，发现账户并没有在ora-dba域中
解决办法：
1、我的电脑右键-->管理-->本地用户和组-->组
2、在组中找到ora_dba-->右键添加到组-->添加-->高级-->立即查找-->将微软账户或Administrator双击-->确定
此时，system账户已经有dba的权限了。（注意：我的系统是win10专业版，和你的步骤稍微不同）
验证：
1、打开SQL Plus
2、输入用户名system 口令
3、输入conn /as sysdba ,提示已连接。
grid与oracle用户下oracle程序权限不一致导致无法连接ASM问题
在RAC中，启动数据库时遇到如下报错：
ORACLE instance started.
Total System Global Area 807682048 bytes
Fixed Size 1347964 bytes
Variable Size 549457540 bytes
Database Buffers 251658240 bytes
Redo Buffers 5218304 bytes
ORA-00205: error in identifying control file, check alert log for more info
查看日志，错误如下：
Fatal NI connect error 12547, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/11.2.0/grid/bin/oracle)(ARGV0=oracle+ASM2_asmb_gzyt2)(ENVS=''ORACLE_HOME=/u01/app/11.2.0/grid,ORACLE_SID=+ASM2'')(ARGS=''(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))''))(enable=setuser)(CONNECT_DATA=(CID=(PROGRAM=oracle@node2)(HOST=node2)(USER=oracle))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 23-JAN-2018 22:11:58
Tracing not turned on.
Tns error struct:
ns main err code: 12547
TNS-12547: TNS:lost contact
ns secondary err code: 12560
nt main err code: 517
TNS-00517: Lost contact
nt secondary err code: 32
nt OS err code: 0
ERROR: Failed to connect with connect string: (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/11.2.0/grid/bin/oracle)(ARGV0=oracle+ASM2_asmb_gzyt2)(ENVS=''ORACLE_HOME=/u01/app/11.2.0/grid,ORACLE_SID=+ASM2'')(ARGS=''(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))''))(enable=setuser))
排查：
1.ASM磁盘组已经正常挂载：
SQL> select name,state from v$asm_diskgroup;
NAME STATE
-------------------------------------------------- -----------
DATA MOUNTED
FRA MOUNTED
OCRVOTE MOUNTED2.数据库alert日志：
ORA-00210: cannot open the specified control file
ORA-00202: control file: ''+FRA/gzyt/controlfile/current.256.966128177''
ORA-17503: ksfdopn:2 Failed to open file +FRA/gzyt/controlfile/current.256.966128177
ORA-15001: diskgroup "FRA" does not exist or is not mounted
ORA-15055: unable to connect to ASM instance
ORA-12547: TNS:lost contact问题解决：
1.查看ORACLE程序的权限：
[oracle@node1 ~]$ ls -l /u01/app/oracle/product/11.2.0/db_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 239626665 Jan 6 10:59 oracle
[grid@node1 ~]$ ls -l /u01/app/11.2.0/grid/bin/oracle
-rwxr-x--x 1 grid oinstall 209914471 Jan 6 10:33 oracle 2.修改权限为6751后，恢复正常：
[oracle@node1 ~]$ ls -l /u01/app/oracle/product/11.2.0/db_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 239626665 Jan 6 10:59 oracle
[grid@node1 ~]$ ls -l /u01/app/11.2.0/grid/bin/oracle
-rwsr-s--x 1 grid oinstall 209914471 Jan 6 10:33 oracle3.在安装仅oracle software之后，$ORACLE_HOME/bin/oracle文件属性权限为751(-rwxr-x--x)
在用安装ASM建库(DBCA)时，此文件属性会自动被修改为6751(-rwsr-s--x)
--此权限问题也有可能导致ORA-12537: TNS:connection closed
--此权限问题也有可能导致使用DBCA建库时无法找到ASM磁盘
4.关于6751权限的说明：
6751分别指定了ugoa的权限：
第一位6代表u（所有者）有读、写权限，没有执行权限
第二位7代表g（组）有读、写、执行权限
第三位5代表o（其它用户）有读、执行权限
第四位1代表a（所有者、组、其它用户）有执行权限
四位6751如果用三位表示就是675，第四位继承umask的值
Linux 权限模型有两个专门的位，叫做“suid”和“sgid”。当设置了一个可执行程序
的“suid”这一位时，在用户执行该程序时,用户的权限是该程序文件属主的权限。例如程序文件的属主是root，那么执行该程序的用户就将暂时获得root账户的权限。sgid与suid类似，只是执行程序时获得的是文件属组的权限。
JDBC连接oracle的sysdba权限的用户
JAVA版本：1.8
JDBC连接oracle的jar包：odbc5.jar
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();开一个实例
需要用到OracleDriver的四个properties：
user：用户 String
password：密码 String
defaultRowPrefetch：从服务器预取的默认行数(默认值为“10”) String (containing integer value)
internal_logon：允许您作为sys登录的权限，如sysdba或sysoper
遇到的问题：
由于在本地测试，所以在命令窗口连接oracle的sysdba时，无论密码是什么都能连上，如果连接不上，老是报用户名/密码的exception,所以重新修改sys的密码后，顺利登录
alter user sys idenfitied by oracle;
代码：
package com.jdbc.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
public class TestDBA {
public static void main(String[] args){
connDBA();
}
//测试用dba权限连接oracle
public static void connDBA() {
Connection conn = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
Properties conProps = new Properties();
conProps.put("user","sys");
conProps.put("password", "oracle");
conProps.put("defaultRowPrefetch", "15");
conProps.put("internal_logon", "sysdba");
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.111:1521:orcl",conProps);
System.out.println(conn);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
if(conn!=null) conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
OB Oracle 系统视图权限导致的故障一例
在 Oracle 和 OB Oracle 租户下调用存储过程时，两者表现并不一致，导致获取到的 SQL 文本拼接不完整，影响到了业务侧的功能测试。本文将针对这个问题进行相关的测试和验证。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文约 3100 字，预计阅读需要 10 分钟。
背景
最近在客户这边遇到一个故障，在 Oracle 和 OB Oracle 租户下调用存储过程时，两者表现并不一致，导致获取到的 SQL 文本拼接不完整，影响到了业务侧的功能测试。
客户的存储过程逻辑并不复杂，就是通过查询系统视图 user_tab_columns 来获取用户的表名，然后再进行 SQL 拼接，完成后续的业务逻辑。
本文将针对这个问题进行相关的测试和验证。
问题复现
Oracle 环境中验证
-- 创建测试用户并赋权
[root@localhost ~]# sqlplus / as sysdba
SQL> create user u1 identified by u1;
User created.
SQL> create user u2 identified by u2;
User created.
SQL> grant connect,resource to u1;
Grant succeeded.
SQL> grant create procedure to u1;
Grant succeeded.
SQL> grant connect,resource to u2;
Grant succeeded.
SQL> grant create synonym to u2;
Grant succeeded.
SQL> grant select any table to u2;
Grant succeeded.
-- 创建测试表并赋权
SQL> conn u1/u1
Connected.
SQL> create table t1(id int);
Table created.
SQL> insert into t1(id) values(1);
1 row created.
-- 创建表的同义词
SQL> conn u2/u2
Connected.
SQL> create synonym t1 for u1.t1;
Synonym created.
SQL> set lin 200
SQL> col owner for a5
SQL> col table_owner for a5
SQL> col db_link for a10
SQL> select * from all_synonyms where owner=''U2'';
OWNER SYNONYM_NAME TABLE TABLE_NAME DB_LINK
----- ------------------------------ ----- ------------------------------ ----------
U2 T1 U1 T1
-- 创建存储过程并赋权
SQL> conn u1/u1
Connected.
SQL> create or replace procedure proc_case1 as
v_str varchar2(10);
begin
select table_name into v_str from user_tab_columns where table_name=''T1'';
dbms_output.put_line(v_str);
end;
/ 2 3 4 5 6 7
Procedure created.
SQL> grant execute on proc_case1 to u2;
Grant succeeded.
-- 创建存储过程同义词
SQL> conn u2/u2
Connected.
SQL> create synonym proc_case1 for u1.proc_case1;
Synonym created.
SQL> select * from all_synonyms where owner=''U2'';
OWNER SYNONYM_NAME TABLE TABLE_NAME DB_LINK
----- ------------------------------ ----- ------------------------------ ----------
U2 PROC_CASE1 U1 PROC_CASE1
U2 T1 U1 T1
-- 验证
SQL> conn u1/u1
Connected.
SQL> select * from t1;
ID
----------
1
SQL> set serveroutput on;
SQL> call proc_case1();
T1
Call completed.
SQL> conn u2/u2
Connected.
SQL> select * from t1;
ID
----------
1
SQL> set serveroutput on;
SQL> call proc_case1();
T1
Call completed.
SQL>由此可见，在 Oracle 中，无论是 u1 还是 u2 用户，调用存储过程时都能正确返回表名，说明两者查询 user_tab_columns 视图的返回结果是一致的，这也是符合预期的。
OB Oracle 环境中验证
-- 创建测试用户并赋权
SYS[SYS]> create user u1 identified by u1;
Query OK, 0 rows affected (0.04 sec)
SYS[SYS]> create user u2 identified by u2;
Query OK, 0 rows affected (0.04 sec)
SYS[SYS]> grant connect,resource to u1;
Query OK, 0 rows affected (0.04 sec)
SYS[SYS]> grant create procedure to u1;
Query OK, 0 rows affected (0.03 sec)
SYS[SYS]> grant connect,resource to u2;
Query OK, 0 rows affected (0.05 sec)
SYS[SYS]> grant create synonym to u2;
Query OK, 0 rows affected (0.03 sec)
SYS[SYS]> grant select any table to u2;
Query OK, 0 rows affected (0.03 sec)
-- 创建测试表并赋权
SYS[SYS]> conn u1
Connection id: 269006
Current database: U1
SYS[U1]> create table t1(id int);
Query OK, 0 rows affected (0.21 sec)
SYS[U1]> insert into t1(id) values(1);
Query OK, 1 row affected (0.03 sec)
SYS[U1]> commit;
Query OK, 0 rows affected (0.01 sec)
-- 创建表的同义词
SYS[U1]> conn u2
Connection id: 50837
Current database: U2
SYS[U2]> create synonym t1 for u1.t1;
Query OK, 0 rows affected (0.05 sec)
SYS[U2]> select * from all_synonyms where owner=''U2'';
+-------+--------------+-------------+------------+---------+
| OWNER | SYNONYM_NAME | TABLE_OWNER | TABLE_NAME | DB_LINK |
+-------+--------------+-------------+------------+---------+
| U2 | T1 | U1 | T1 | NULL |
+-------+--------------+-------------+------------+---------+
2 rows in set (0.01 sec)
-- 创建存储过程并赋权
SYS[U2]> conn u1
Connection id: 269078
Current database: U1
SYS[U1]> create or replace procedure proc_case1 as
-> v_str varchar2(10);
-> begin
-> select table_name into v_str from user_tab_columns where table_name=''T1'';
-> dbms_output.put_line(v_str);
-> end;
-> /
Query OK, 0 rows affected (0.17 sec)
SYS[U1]> grant execute on proc_case1 to u2;
Query OK, 0 rows affected (0.06 sec)
-- 创建存储过程同义词
SYS[U1]> conn u2
Connection id: 50896
Current database: U2
SYS[U2]> create synonym proc_case1 for u1.proc_case1;
Query OK, 0 rows affected (0.05 sec)
SYS[U2]> select * from all_synonyms where owner=''U2'';
+-------+--------------+-------------+------------+---------+
| OWNER | SYNONYM_NAME | TABLE_OWNER | TABLE_NAME | DB_LINK |
+-------+--------------+-------------+------------+---------+
| U2 | PROC_CASE1 | U1 | PROC_CASE1 | NULL |
| U2 | T1 | U1 | T1 | NULL |
+-------+--------------+-------------+------------+---------+
2 rows in set (0.01 sec)
-- 验证
SYS[U2]> conn u1
Connection id: 269134
Current database: U1
SYS[U1]> select * from t1;
+------+
| ID |
+------+
| 1 |
+------+
1 row in set (0.01sec)
SYS[U1]> set serveroutput on;
Query OK, 0 rows affected (0.41 sec)
SYS[U1]> call proc_case1();
Query OK, 0 rows affected (0.21 sec)
SYS[U1]> select table_name,column_name from user_tab_columns;
+------------+-------------+
| TABLE_NAME | COLUMN_NAME |
+------------+-------------+
| C | NAME |
| C | ADDRESS |
+------------+-------------+
2 rows in set (0.08 sec)
此处其实已经可以发现一些端倪，在 OB 中虽然可以通过 conn 进行用户切换，切换后的用户也能访问自己的对象，但是在访问 USER_ 等视图时，返回结果与 Oracle 不同。
用户 u1 查询 user_tab_columns 表时，只能看到 SYS 用户下的表（ C 表是由 SYS 用户创建的），所以存储过程无法返回 T1 表的表名，其查询结果为空。
-- 直连 u1 用户验证
U1[U1]> select * from t1;
+------+
| ID |
+------+
| 1 |
+------+
1 row in set (0.01sec)
U1[U1]> set serveroutput on;
Query OK, 0 rows affected (0.02sec)
U1[U1]> call proc_case1();
Query OK, 0 rows affected (0.08sec)
T1
U1[U1]>
-- 直连 u2 用户进行验证
U2[U2]> select * from t1;
+------+
| ID |
+------+
| 1 |
+------+
1 row in set (0.03sec)
U2[U2]> set serveroutput on;
Query OK, 0 rows affected (0.44 sec)
U2[U2]> call proc_case1();
Query OK, 0 rows affected (0.43 sec)
U2[U2]> select * from user_tab_columns;
Empty set (0.08 sec)
# 同样地，u2 也无法从 user_tab_columns 视图中查询到 u1 创建的表，调用存储过程返回结果为空
-- 将 user_tab_columns 替换成 all_tab_columns 视图
U2[U2]> select table_name,column_name from all_tab_columns where owner=''U1'';
+------------+-------------+
| TABLE_NAME | COLUMN_NAME |
+------------+-------------+
| T1 | ID |
+------------+-------------+
1 row in set (0.08 sec)
U2[U2]> create or replace procedure proc_case2 as
-> v_str varchar2(10);
-> begin
-> select table_name into v_str from all_tab_columns where table_name=''T1'' and owner=''U1'';
-> dbms_output.put_line(v_str);
-> end;
-> /
Query OK, 0 rows affected (0.17ec)
U2[U2]> call proc_case2();
Query OK, 0 rows affected (0.16ec)
T1
U2[U2]>
-- 将 SELECT ANY TABLE 权限回收
SYS[SYS]> revoke select any table from u2;
Query OK, 0 rows affected (0.03 sec)
U2[U2]> select table_name,column_name from all_tab_columns where owner=''U1'';
Empty set (0.05 sec)
U2[U2]> set serveroutput on;
Query OK, 0 rows affected (0.01 sec)
U2[U2]> call proc_case2();
Query OK, 0 rows affected (0.05 sec)当用户 u2 没有 SELECT ANY TABLE 系统权限后，即使查询 all_tab_columns 视图，也无法获取其他用户创建表的相关信息。
排查调用系统视图的相关对象
PL 对象
PL 对象，如：函数，存储过程等。
-- dba_source 视图中存放了各种 PL 对象的定义
SQL> select count(*),type from dba_source group by type;
COUNT(*) TYPE
---------- ------------
152202 PROCEDURE
89318 PACKAGE
31504 PACKAGE BODY
1276 TYPE BODY
2210 TRIGGER
3895 FUNCTION
7 JAVA SOURCE
12338 TYPE
8 rows selected.
-- 创建测试存储过程（大小写各1个）
SQL> CREATE OR REPLACE PROCEDURE PROC_1 IS
V_N NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO V_N FROM USER_TAB_COLUMNS;
END;
/ 2 3 4 5 6
Procedure created.
SQL> create or replace procedure proc_2 is
v_n number :=0;
begin
select count(*) into v_n from user_tab_columns;
end;
/ 2 3 4 5 6
Procedure created.
-- 查询常用系统视图名（此处只列举了几个与表相关的视图）
select owner,object_name,object_type from dba_objects where owner=''SYS'' and (object_name like ''USER_PART_%'' or object_name like ''USER_T%'' or object_name like ''ALL_PART_%'' or object_name like ''ALL_T%'' or object_name like ''DBA_PART_%'' or object_name like ''DBA_T%'');
-- 根据上一步获取到的系统视图名，通过模糊搜索，即可捕获到涉及查询这些系统视图的 PL 对象
SQL> set line 200 pages 9999 long 999999
SQL> col owner for a10
SQL> col name for a30
SQL> col text for a80
SQL> select owner,name,type,text from dba_source where owner not in(''SYS'', ''SYSTEM'', ''SYSMAN'', ''OUTLN'', ''DIP'', ''TSMSYS'', ''DBSNMP'',
''ORACLE_OCM'', ''WMSYS'', ''EXFSYS'', ''XDB'', ''ANONYMOUS'', ''ORDSYS'',
''ORDPLUGINS'', ''SI_INFORMTN_SCHEMA'', ''MDSYS'', ''MGMT_VIEW'', ''PERFSTAT'',
''DMSYS'', ''CTXSYS'', ''OLAPSYS'', ''MDDATA'', ''APPQOSSYS'', ''XS$NULL'',
''ORDDATA'', ''SPATIAL_WFS_ADMIN_USR'', ''SPATIAL_CSW_ADMIN_USR'',
''OWBSYS'', ''APEX_PUBLIC_USER'', ''APEX_030200'', ''FLOWS_FILES'', ''SCOTT'',
''OMS'', ''OWBSYS_AUDIT'', ''DSG'', ''DBMGR'', ''PATROL'', ''SPA'', ''GOLDENGATE'',
''DBADM'') and owner not like ''MYNET%'' and (text like ''%USER_TAB_COLUMNS%'' or text like ''%user_tab_columns%'' or text like ''%USER%TABLES%'' or text like ''%user%tables%'' or text like ''%ALL_TAB_COLUMNS%'' or text like ''%ALL_tab_columns%'' or text like ''%ALL%TABLES%'' or text like ''%ALL%tables%''); 2 3 4 5 6 7 8
OWNER NAME TYPE TEXT
---------- ------------------------------ ------------ --------------------------------------------------------------------------------
U1 PROC_CASE1 PROCEDURE select table_name into v_str from user_tab_columns where table_name=''T1'';
ZLM PROC_1 PROCEDURE SELECT COUNT(*) INTO V_N FROM USER_TAB_COLUMNS;
ZLM PROC_2 PROCEDURE select count(*) into v_n from user_tab_columns;视图对象
-- 创建测试视图1
SQL> create view view_1 as select * from user_tables;
View created.
-- 查询 dba_views 获取视图定义
SQL> select owner,view_name,text from dba_views where owner not in(''SYS'', ''SYSTEM'', ''SYSMAN'', ''OUTLN'', ''DIP'', ''TSMSYS'', ''DBSNMP'',
''ORACLE_OCM'', ''WMSYS'', ''EXFSYS'', ''XDB'', ''ANONYMOUS'', ''ORDSYS'',
''ORDPLUGINS'', ''SI_INFORMTN_SCHEMA'', ''MDSYS'', ''MGMT_VIEW'', ''PERFSTAT'',
''DMSYS'', ''CTXSYS'', ''OLAPSYS'', ''MDDATA'', ''APPQOSSYS'', ''XS$NULL'',
''ORDDATA'', ''SPATIAL_WFS_ADMIN_USR'', ''SPATIAL_CSW_ADMIN_USR'',
''OWBSYS'', ''APEX_PUBLIC_USER'', ''APEX_030200'', ''FLOWS_FILES'', ''SCOTT'',
''OMS'', ''OWBSYS_AUDIT'', ''DSG'', ''DBMGR'', ''PATROL'', ''SPA'', ''GOLDENGATE'',
''DBADM'') and (text like ''%USER_TAB_COLUMNS%'' or text like ''%user_tab_columns%'' or text like ''%USER%TABLES%'' or text like ''%user%tables%'' or text like ''%ALL_TAB_COLUMNS%'' or text like ''%ALL_tab_columns%'' or text like ''%ALL%TABLES%'' or text like ''%ALL%tables%'');
2 3 4 5 6 7 8 ''DBADM'') and (text like ''%USER_TAB_COLUMNS%'' or text like ''%user_tab_columns%'' or text like ''%USER%TABLES%'' or text like ''%user%tables%'' or text like ''%ALL_TAB_COLUMNS%'' or text like ''%ALL_tab_columns%'' or text like ''%ALL%TABLES%'' or text like ''%ALL%tables%'')
*
ERROR at line 8:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
dba_source视图中的 text 列是 varchar2 类型的，可以直接使用 like 进行模糊查询。dba_views视图中的 text 列是 long 类型的，无法直接使用 like 进行模糊查询，会报 ORA-00932 的错误。
workaround：先创建一张表，用 to_lob 函数将 text 字段转换为 clob 类型，然后将 dba_views 拷贝到该表中，再通过以上 SQL 进行查询。
-- 创建中间表并将系统视图 dba_views 内容拷贝到该表
SQL> create table my_views as select owner,view_name,to_lob(text) text from dba_views;
Table created.
-- 查询中间表捕获目标视图对象
SQL> select owner,view_name,text from my_views where owner not in(''SYS'', ''SYSTEM'', ''SYSMAN'', ''OUTLN'', ''DIP'', ''TSMSYS'', ''DBSNMP'', ''ORACLE_OCM'', ''WMSYS'', ''EXFSYS'', ''XDB'', ''ANONYMOUS'', ''ORDSYS'', ''ORDPLUGINS'', ''SI_INFORMTN_SCHEMA'', ''MDSYS'', ''MGMT_VIEW'', ''PERFSTAT'', ''DMSYS'', ''CTXSYS'', ''OLAPSYS'', ''MDDATA'', ''APPQOSSYS'', ''XS$NULL'', ''ORDDATA'', ''SPATIAL_WFS_ADMIN_USR'', ''SPATIAL_CSW_ADMIN_USR'', ''OWBSYS'', ''APEX_PUBLIC_USER'', ''APEX_030200'', ''FLOWS_FILES'', ''SCOTT'', ''OMS'', ''OWBSYS_AUDIT'', ''DSG'', ''DBMGR'', ''PATROL'', ''SPA'', ''GOLDENGATE'', ''DBADM'') and (text like ''%USER_TAB_COLUMNS%'' or text like ''%user_tab_columns%'' or text like ''%USER%TABLES%'' or text like ''%user%tables%'' or text like ''%ALL_TAB_COLUMNS%'' or text like ''%ALL_tab_columns%'' or text like ''%ALL%TABLES%'' or text like ''%ALL%tables%''); 2 3 4 5 6 7 8
OWNER VIEW_NAME TEXT---------- ------------------------------ --------------------------------------------------------------------------------
ZLM VIEW_1 select "TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","STATUS","PCT_FR EE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTEN TS","MAX_EXTENTS","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED _UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","A VG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TA BLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","TEMPORARY","SE CONDARY","NESTED","BUFFER_POOL","ROW_MOVEMENT","GLOBAL_STATS","USER_STATS","DURA TION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMPRESSION"," DROPPED" from user_tables
该方法可以满足需求，但每次有新的视图被创建时，需要 drop 并重建表，比较繁琐。
workaround：创建物化视图来代替中间表。
-- 创建物化视图
SQL> create materialized view my_mviews
refresh force
on demand
start with sysdate
next sysdate + 10 /(24*60)
as
select owner,view_name,to_lob(text) text from dba_views; 2 3 4 5 6 7
Materialized view created.
-- 创建测试视图2
SQL> CREATE VIEW VIEW_2 AS SELECT * FROM USER_TABLES;
View created.
-- 查看是否捕获到 view_2 视图
SQL> select owner,view_name,text from my_mviews where owner not in(''SYS'', ''SYSTEM'', ''SYSMAN'', ''OUTLN'', ''DIP'', ''TSMSYS'', ''DBSNMP'',
''ORACLE_OCM'', ''WMSYS'', ''EXFSYS'', ''XDB'', ''ANONYMOUS'', ''ORDSYS'',
''ORDPLUGINS'', ''SI_INFORMTN_SCHEMA'', ''MDSYS'', ''MGMT_VIEW'', ''PERFSTAT'',
''DMSYS'', ''CTXSYS'', ''OLAPSYS'', ''MDDATA'', ''APPQOSSYS'', ''XS$NULL'',
''ORDDATA'', ''SPATIAL_WFS_ADMIN_USR'', ''SPATIAL_CSW_ADMIN_USR'',
''OWBSYS'', ''APEX_PUBLIC_USER'', ''APEX_030200'', ''FLOWS_FILES'', ''SCOTT'',
''OMS'', ''OWBSYS_AUDIT'', ''DSG'', ''DBMGR'', ''PATROL'', ''SPA'', ''GOLDENGATE'',
''DBADM'') and (text like ''%USER_TAB_COLUMNS%'' or text like ''%user_tab_columns%'' or text like ''%USER%TABLES%'' or text like ''%user%tables%'' or text like ''%ALL_TAB_COLUMNS%'' or text like ''%ALL_tab_columns%'' or text like ''%ALL%TABLES%'' or text like ''%ALL%tables%''); 2 3 4 5 6 7 8
OWNER VIEW_NAME TEXT
---------- ------------------------------ --------------------------------------------------------------------------------
ZLM VIEW_1 select "TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","STATUS","PCT_FR
EE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTEN
TS","MAX_EXTENTS","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED
_UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","A
VG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TA
BLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","TEMPORARY","SE
CONDARY","NESTED","BUFFER_POOL","ROW_MOVEMENT","GLOBAL_STATS","USER_STATS","DURA
TION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMPRESSION","
DROPPED" from user_tables
-- 查看物化视图刷新时间
SQL> select owner,mview_name,last_refresh_type,last_refresh_date from user_mviews;
OWNER MVIEW_NAME LAST_REF LAST_REFRESH_DATE
---------- ------------------------------ -------- -------------------
ZLM MY_MVIEWS COMPLETE 2023-08-03 16:07:15
-- 手动刷新物化视图
SQL> exec dbms_mview.refresh(''my_mviews'');
PL/SQL procedure successfully completed.
SQL> select owner,mview_name,last_refresh_type,last_refresh_date from user_mviews;
OWNER MVIEW_NAME LAST_REF LAST_REFRESH_DATE
---------- ------------------------------ -------- -------------------
ZLM MY_MVIEWS COMPLETE 2023-08-03 16:21:45
-- 再次查询物化视图，此时 view_2 也能被捕获到了，这样就无需重复建表，当有新视图被创建的时候，只需手动刷新物化视图即可
SQL> select owner,view_name,text from my_mviews where owner not in(''SYS'', ''SYSTEM'', ''SYSMAN'', ''OUTLN'', ''DIP'', ''TSMSYS'', ''DBSNMP'',
''ORACLE_OCM'', ''WMSYS'', ''EXFSYS'', ''XDB'', ''ANONYMOUS'', ''ORDSYS'',
''ORDPLUGINS'', ''SI_INFORMTN_SCHEMA'', ''MDSYS'', ''MGMT_VIEW'', ''PERFSTAT'',
''DMSYS'', ''CTXSYS'', ''OLAPSYS'', ''MDDATA'', ''APPQOSSYS'', ''XS$NULL'',
''ORDDATA'', ''SPATIAL_WFS_ADMIN_USR'', ''SPATIAL_CSW_ADMIN_USR'',
''OWBSYS'', ''APEX_PUBLIC_USER'', ''APEX_030200'', ''FLOWS_FILES'', ''SCOTT'',
''OMS'', ''OWBSYS_AUDIT'', ''DSG'', ''DBMGR'', ''PATROL'', ''SPA'', ''GOLDENGATE'',
''DBADM'') and (text like ''%USER_TAB_COLUMNS%'' or text like ''%user_tab_columns%'' or text like ''%USER%TABLES%'' or text like ''%user%tables%'' or text like ''%ALL_TAB_COLUMNS%'' or text like ''%ALL_tab_columns%'' or text like ''%ALL%TABLES%'' or text like ''%ALL%tables%''); 2 3 4 5 6 7 8
OWNER VIEW_NAME TEXT
---------- ------------------------------ --------------------------------------------------------------------------------
ZLM VIEW_1 select "TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","STATUS","PCT_FR
EE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTEN
TS","MAX_EXTENTS","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED
_UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","A
VG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TA
BLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","TEMPORARY","SE
CONDARY","NESTED","BUFFER_POOL","ROW_MOVEMENT","GLOBAL_STATS","USER_STATS","DURA
TION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMPRESSION","
DROPPED" from user_tables
ZLM VIEW_2 select "TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","STATUS","PCT_FR
EE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTEN
TS","MAX_EXTENTS","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED
_UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","A
VG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TA
BLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","TEMPORARY","SE
CONDARY","NESTED","BUFFER_POOL","ROW_MOVEMENT","GLOBAL_STATS","USER_STATS","DURA
TION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMPRESSION","
DROPPED" from USER_TABLES
解决方案
将存储过程中的 user_tab_columns 视图替换成 all_tab_columns，虽然可作为临时方案，不过存在以下缺点：
- 需要修改业务代码，即替换存储过程中查询相关系统视图的部分。
- 使用同义词来访问对象的用户，需要有 SELECT ANY TABLE 的系统权限，否则即便使用
all_的视图，也查询不到目标对象。 - 赋予执行用户 dba 权限，并修改原有查询 SQL，增加 owner=''XXX'' 的条件（存在安全隐患，不推荐）。
- OB 能提供一个 hotfix patch 来彻底解决该问题。
问题总结
在 OB 中，普通用户查询 USER_TAB_COLUMNS 系统视图权限的逻辑与 Oracle 并不一致，导致查询结果有差异。
除了 USER_TAB_COLUMNS 视图，还有其他以 USER_ 开头的视图，也存在类似的问题，比如：USER_SYNONYMS、USER_TABLES 等。
对于系统中已有的对象，应尽快排查并确认在哪些对象中用到了这些系统视图，在该问题被彻底修复前，建议先对相关代码进行临时修改，使其能继续完成后续的功能验证。
关于Oracle回收dba权限导致无表空间权限解决记录和oracle回收用户dba权限的问题我们已经讲解完毕，感谢您的阅读，如果还想了解更多关于1、oracle11g中为system账户赋予sysdba权限？、grid与oracle用户下oracle程序权限不一致导致无法连接ASM问题、JDBC连接oracle的sysdba权限的用户、OB Oracle 系统视图权限导致的故障一例等相关内容，可以在本站寻找。
本文标签：
![[转帖]Ubuntu 安装 Wine方法（ubuntu如何安装wine）](https://www.gvkun.com/zb_users/cache/thumbs/4c83df0e2303284d68480d1b1378581d-180-120-1.jpg)
