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.
Best Answer
In theory you should login as the user with as few privileges as possible that can still accomplish the task. In practice you can maintain a better password, save time, and have more useful auditing/logging information if you login as yourself. Here is the guideline I follow:
Only login using an account other than your own when…