oracle 获取 ORA-01031：查询表而不是 ORA-00942 时权限不足：表或视图不存在

Getting ORA-01031: insufficient privileges while querying a table instead of ORA-00942: table or view does not exist

提问by Nitish

When I'm querying a table in schema C from schema A, I'm getting ORA-01031: insufficient privilegesand when I'm querying the same table from schema B, I'm getting ORA-00942: table or view does not exist. On the table neither of the schemas are having any privileges. Why am I getting different error messages in this case?

当我从模式 A 查询模式 C 中的表时，我得到ORA-01031：权限不足，当我从模式 B 查询同一个表时，我得到ORA-00942：表或视图没有存在。在表上，这两个模式都没有任何权限。为什么在这种情况下我会收到不同的错误消息？

回答by Jon Heller

You may get ORA-01031: insufficient privilegesinstead of ORA-00942: table or view does not existwhen you have at least one privilege on the table, but not the necessary privilege.

当您在表上至少拥有一项特权，但没有必要的特权时，您可能会得到ORA-01031: insufficient privileges代替ORA-00942: table or view does not exist。

Create schemas

创建模式

SQL> create user schemaA identified by schemaA;

User created.

SQL> create user schemaB identified by schemaB;

User created.

SQL> create user test_user identified by test_user;

User created.

SQL> grant connect to test_user;

Grant succeeded.

Create objects and privileges

创建对象和权限

It is unusual, but possible, to grant a schema a privilege like DELETE without granting SELECT.

在不授予 SELECT 的情况下授予模式像 DELETE 这样的权限是不寻常的，但也是可能的。

SQL> create table schemaA.table1(a number);

Table created.

SQL> create table schemaB.table2(a number);

Table created.

SQL> grant delete on schemaB.table2 to test_user;

Grant succeeded.

Connect as TEST_USER and try to query the tables

以 TEST_USER 身份连接并尝试查询表

This shows that having someprivilege on the table changes the error message.

这表明对表具有某些特权会更改错误消息。

SQL> select * from schemaA.table1;
select * from schemaA.table1
                      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from schemaB.table2;
select * from schemaB.table2
                      *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

回答by San

ORA-01031: insufficient privilegeshappens when the object exists in the schema but do not have any access to that object.

ORA-01031: insufficient privileges当对象存在于架构中但无权访问该对象时发生。

ORA-00942: table or view does not existhappens when the object does not exist in the current schema. If the object exists in another schema, you need to access it using .. Still you can get insufficient privileges error if the owner has not given access to the calling schema.

ORA-00942: table or view does not exist当对象在当前模式中不存在时发生。如果该对象存在于另一个架构中，您需要使用 ..

回答by Piyush Jain

for ORA-01031: insufficient privileges. Some of the more common causes are:

ORA-01031：权限不足。一些更常见的原因是：

1. You tried to change an Oracle username or password without having the appropriate privileges.
2. You tried to perform an UPDATEto a table, but you only have SELECTaccess to the table.
3. You tried to start up an Oracle database using CONNECT INTERNAL.
4. You tried to install an Oracle database without having the appropriate privileges to the operating-system.

1. 您试图在没有适当权限的情况下更改 Oracle 用户名或密码。
2. 您尝试对UPDATE表执行 an ，但您只能SELECT访问该表。
3. 您尝试使用CONNECT INTERNAL.
4. 您尝试安装 Oracle 数据库而没有相应的操作系统权限。

The option(s) to resolve this Oracle error are:

解决此 Oracle 错误的选项是：

1. You can have the Oracle DBA grant you the appropriate privileges that you are missing.
2. You can have the Oracle DBA execute the operation for you.
3. If you are having trouble starting up Oracle, you may need to add the Oracle user to the dba group.

1. 您可以让 Oracle DBA 授予您您缺少的适当权限。
2. 您可以让 Oracle DBA 为您执行操作。
3. 如果您在启动 Oracle 时遇到问题，您可能需要将 Oracle 用户添加到 dba 组。

For ORA-00942: table or view does not exist.You tried to execute a SQL statement that references a table or view that either does not exist, that you do not have access to, or that belongs to another schema and you didn't reference the table by the schema name.

因为ORA-00942: table or view does not exist.您试图执行引用表或视图的 SQL 语句，该表或视图不存在、您无权访问或属于另一个架构，并且您没有通过架构名称引用该表。

If this error occurred because the table or view does not exist, you will need to create the table or view.

如果由于表或视图不存在而发生此错误，则需要创建表或视图。

You can check to see if the table exists in Oracle by executing the following SQL statement:

您可以通过执行以下 SQL 语句来检查该表是否存在于 Oracle 中：

select *
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = 'OBJECT_NAME';

For example, if you are looking for a suppliers table, you would execute:

例如，如果您正在寻找供应商表，您将执行：

select *
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = 'SUPPLIERS';

OPTION #2

选项#2

If this error occurred because you do not have access to the table or view, you will need to have the owner of the table/view, or a DBA grant you the appropriate privileges to this object.

如果由于您无权访问表或视图而发生此错误，则需要让表/视图的所有者或 DBA 授予您对该对象的适当权限。

OPTION #3

选项 #3

If this error occurred because the table/view belongs to another schema and you didn't reference the table by the schema name, you will need to rewrite your SQL to include the schema name.

如果由于表/视图属于另一个架构而发生此错误，并且您没有通过架构名称引用该表，则需要重写 SQL 以包含架构名称。

For example, you may have executed the following SQL statement:

例如，您可能执行了以下 SQL 语句：

select *
from suppliers;

But the supplierstable is not owned by you, but rather, it is owned by a schema called app, you could fix your SQL as follows:

但是该suppliers表不归您所有，而是归名为 app 的架构所有，您可以按如下方式修复 SQL：

select *
from app.suppliers;

If you do not know what schema the suppliers table/view belongs to, you can execute the following SQL to find out:

如果您不知道供应商表/视图属于什么模式，您可以执行以下 SQL 来查找：

select owner
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = 'SUPPLIERS';

This will return the schema name who owns the suppliers table.

这将返回拥有供应商表的模式名称。

回答by Rajesh Goel

In SQL Developer: Everything was working fine and I had all the permissions to login and there was no password change and I could click the table and see the data tab.

在 SQL Developer 中：一切正常，我拥有所有登录权限并且没有更改密码，我可以单击表格并查看数据选项卡。

But when I run query (simple select statement) it was showing "ORA-01031: insufficient privileges" message.

但是当我运行查询（简单的选择语句）时，它显示“ORA-01031：权限不足”消息。

The solution is simply disconnect the connection and reconnect. Note: only doing Reconnect did not work for me. SQL Developer Disconnect Snapshot

解决方法是简单地断开连接并重新连接。注意：只做重新连接对我不起作用。 SQL Developer 断开连接快照

回答by Subrata Dey

ORA-01031: insufficient privilegesSolution: Go to Your System User. then Write This Code:

ORA-01031：权限不足解决方案：转到您的系统用户。然后编写此代码：

SQL> grant dba to UserName; //Put This username which user show this error message.

SQL> 将 dba 授予用户名；//把这个用户名放在哪个用户显示这个错误信息。

Grant succeeded.

格兰特成功了。