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
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?
select * from ...on any table with this user. With
select * from user_tab_privsI 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_userstable? This never happended before.
usr_t_user_regis a table and it got no errors and works just fine.