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?
Best Answer
Running the script with sqlplus gives the following error message
this is one of the errors where the error description of the Oracle Database Error Messages 11g Release 2does not give a usefule hint
In the Oracle Database SQL Language Reference 11g Release 2 you can find the following prerequisite for creating a view
Neither SELECT_CATALOG_ROLE role nor DBA role is sufficient for creating your view.
So issue a
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.