授予选择对象的表后ORA

授予选择对象的表后ORA-00904错误(ORA-00904 error after granting select on a table of object)

Oracle在授予对象表的选择权方面存在问题。

当创建一个表时

create table t (name char, ...)

那么我没有任何问题可以将任何特权授予任何用户。

但是，当表是从一个对象即创建

create or replace type type_client under type_personne ( num int , username varchar(30), balance int, ta table_achat, ref_admin ref type_admin, member function get_prix_achat_total return int ); create table t of type_client

例如，我尝试将选择选项放到user1上，但是当我从user1连接并尝试从此表中选择任何数据时：

select * from system.table_client

我看到这个消息：

ORA-00904：：无效的标识符 00904. 00000 - “％s：无效标识符” *原因： *行动： 线路错误：1列：34

有时我会看到这样的信息：

内部错误：未知或未实现的存取器类型：9

I have a problem in Oracle granting select on an object table.

When a table is created like

create table t (name char, ...)

then I don't have any problem to grant any privileges on it to any user.

But when the table is created from an object i.e.

create or replace type type_client under type_personne ( num int , username varchar(30), balance int, ta table_achat, ref_admin ref type_admin, member function get_prix_achat_total return int ); create table t of type_client

I try to give select on it to a user1 for example, but when I connect from user1 and try to select any data from this table:

select * from system.table_client

I see the message:

ORA-00904: : invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 1 Column: 34

Sometimes I see the message:

Internal Error: Unknown or unimplemented accessor type: 9

最满意答案

看起来像你的Select查询不correct 。你可以这样做，如下所示：

SQL> show user USER is "SCOTT" SQL> CREATE TYPE emp_type AS OBJECT ( 2 eno NUMBER, 3 ename VARCHAR2(36)); 4 / Type created. SQL> CREATE TABLE emp_tp OF emp_type; Table created. SQL> GRANT SELECT on emp_tp TO system ; Grant succeeded. SQL> connect Enter user-name: system Enter password: **** Connected. SQL> show user USER is "SYSTEM" SQL> Select * from scott.emp_tp;--<--Make sure you put schema name before table name no rows selected SQL>

编辑：

正如我在评论中提到的那样，对象定义也具有member函数。 所以如果你Select tb.get_prix_achat_total() from t tb ，它应该返回结果。 当定义中有任何功能时，您必须使用列名代替* 。

Seems like your Select query is not correct.You can do it as below:

SQL> show user USER is "SCOTT" SQL> CREATE TYPE emp_type AS OBJECT ( 2 eno NUMBER, 3 ename VARCHAR2(36)); 4 / Type created. SQL> CREATE TABLE emp_tp OF emp_type; Table created. SQL> GRANT SELECT on emp_tp TO system ; Grant succeeded. SQL> connect Enter user-name: system Enter password: **** Connected. SQL> show user USER is "SYSTEM" SQL> Select * from scott.emp_tp;--<--Make sure you put schema name before table name no rows selected SQL>

Edit:

As mentioned in my comments,the object definition is having a member function as well. So if you do Select tb.get_prix_achat_total() from t tb , it should return result. You must use column name in place of *when there is any fucntion in the definition.