0

I'm trying to create a view under my DBA schema on Oracle XE. I already did that on the productive database and it worked with a user with a lot lesser privileges than the DBA role. But now I always get ORA-01031 errors. Here is my query:

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
    )
;

And this is the output I get:

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.

I even tried it with the SYS account, but with the same result. Am I missing something important?

5
  • Check if user has SELECT privilege on those views: select privilege, owner, table_name, grantor from user_tab_privs.
    – vegatripy
    Commented Aug 12, 2013 at 11:11
  • I can select * from ... on any table with this user. With select * from user_tab_privs I get "No rows selected", but how stupid is it, that I can't create a simple view from one of my own tables and the sys.dba_users table? This never happended before.
    – Chris.V
    Commented Aug 12, 2013 at 11:16
  • Is it possible that usr_t_user_reg is also a view and that it has errors? Commented Aug 12, 2013 at 11:55
  • OK, I'm guessing from the above statement that you're using prefixes "usr_t" to mean table and "usr_v" to mean view. Commented Aug 12, 2013 at 11:56
  • Yeah that's correct usr_t_user_reg is a table and it got no errors and works just fine.
    – Chris.V
    Commented Aug 13, 2013 at 5:57

3 Answers 3

5

create table usr_t_user_reg
(username varchar2(30),
db_instance  varchar2(30),
primary key(username,db_instance)
)
/

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_instance = 'TEST_DB'
    AND username NOT IN
    (
      SELECT username
      FROM usr_t_user_reg
      INTERSECT
      SELECT username
      FROM sys.dba_users
    )
/

Running the script with sqlplus gives the following error message

Table created.

          FROM sys.dba_users
               *
ERROR at line 8:
ORA-01031: insufficient privileges

this is one of the errors where the error description of the Oracle Database Error Messages 11g Release 2does not give a usefule hint

ORA-01031: 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.

Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.

In the Oracle Database SQL Language Reference 11g Release 2 you can find the following prerequisite for creating a view

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

Neither SELECT_CATALOG_ROLE role nor DBA role is sufficient for creating your view.

So issue a

GRANT SELECT ON DBA_USERS TO your_user;

and now your view will work.

Instead of the object privilege SELECT ON DBA_USERS you can grant the system privilege SELECT ANY DICTIONARY. In contrast to SELECT_CATALOG_ROLE this is not a role but a system privilege.

Note that the error message spots to line 8 of the CREATE VIEW statement that is the line that actually contains the object that causes the error.

1
  • I granted the SELECT ANY DICTONARY privilege to my user and it works. :) I thought the whole time, that it would be sufficient, because SELECT ANY DICTONARY is included in the DBA role. It's really kinda confusing with the direct grants and stuff, but now it works. Thanks a lot.
    – Chris.V
    Commented Aug 13, 2013 at 6:26
0

Please check if the user you are using has access to the following tables

  1. sys.dba_users
  2. usr_t_user_reg

If you have access, you should be able to run a query like select * from usr_t_user_reg and select * from sys.dba_users.

If you are unable to run the above queries login as the SYS user and grant SELECT privilege to the user -- GRANT SELECT ON sys.dba_users to <username>;

Once you are able to run the select * queries, retry view creation.

NOTE: I'm assuming from the tag that this is an 11gr2 database.

3
  • I already checked the user I'm using. The SELECT statements are no problem and the user got DBA and SELECT_CATALOG_ROLE which should be sufficient. The view creation query doesn't even work with the SYS user, which is kind of strange.
    – Chris.V
    Commented Aug 12, 2013 at 11:11
  • Does the user have the roles marked as DEFAULT? If not, new sessions of that user won't have them enabled. Just a thought. Commented Aug 12, 2013 at 14:57
  • The roles are marked as DEFAULT, I have checked that.
    – Chris.V
    Commented Aug 13, 2013 at 6:27
-1

ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges"

If you are receiving this error for view there are two things:

  1. missing grant
  2. open the view and check from which table its selecting data as maybe the view is selecting the data from some other table. Grant it to user.
1
  • do you really think your post adds some useful inormation to my accepted answer that was written 5 years ago? your point 2 is completely wrong.
    – miracle173
    Commented Feb 7, 2018 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.