Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 XE SYS account, but with the same result. Am I missing something important?

share|improve this question
    
Check if user has SELECT privilege on those views: select privilege, owner, table_name, grantor from user_tab_privs. –  vegatripy Aug 12 '13 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 Aug 12 '13 at 11:16
    
Is it possible that usr_t_user_reg is also a view and that it has errors? –  Colin 't Hart Aug 12 '13 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. –  Colin 't Hart Aug 12 '13 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 Aug 13 '13 at 5:57
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
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 Aug 13 '13 at 6:26
add comment

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.

share|improve this answer
    
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 Aug 12 '13 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. –  Colin 't Hart Aug 12 '13 at 14:57
    
The roles are marked as DEFAULT, I have checked that. –  Chris.V Aug 13 '13 at 6:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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