创建视图为DBA时，ORA-01031

Question

我试图在Oracle XE上在我的DBA模式下创建一个视图。我已经在生产性数据库上这样做了，它使用的用户权限比DBA角色低得多。但是现在我总是收到ORA-01031错误。以下是我的疑问：

CREATE OR REPLACE VIEW usr_v_user_not_reg AS
  SELECT username "User", db_instance "Instance",
    (
      CASE 
        WHEN username IN
        (
          SELECT username
          FROM sys.dba_users
          MINUS
          SELECT username
          FROM usr_t_user_reg
        ) THEN 'not registered'
        WHEN username IN
        (
          SELECT username
          FROM usr_t_user_reg
          MINUS
          SELECT username
          FROM sys.dba_users
        ) THEN 'no longer present'
      END
    ) "Status"
  FROM usr_t_user_reg
  WHERE db_instanz = 'TEST_DB'
    AND username NOT IN
    (
      SELECT username
      FROM usr_t_user_reg
      INTERSECT
      SELECT username
      FROM sys.dba_users
    )
;

这是我得到的输出：

Line: 10 Column:20
SQL-Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.

我甚至用SYS帐户尝试过，但结果是一样的。我错过了什么重要的事情吗？

Answer 1

请检查您所使用的用户是否有权访问下表

1. sys.dba_users
2. usr_t_user_reg

如果您有访问权限，您应该能够运行像select * from usr_t_user_reg和select * from sys.dba_users这样的查询。

如果您无法以SYS用户的身份运行上述查询，并将SELECT特权授予用户-- GRANT SELECT ON sys.dba_users to <username>;

一旦您能够运行select *查询，请重试视图创建。

注意:我从标记中假设这是一个11gr2数据库。

Answer 2

ORA-01031:特权不足01031.00000 -“特权不足”

如果收到此视图错误，有两件事：

1. 遗失补助金
2. 打开视图，检查从哪个表中选择数据，因为视图可能是从其他表中选择数据。授权给用户。