Problem Description
~~~~~~~~~~~~~~~~~~~
A user connected AS SYSOPER can only STARTUP and SHUTDOWN the database, but cannot select any data from the dictionary, nor perform any other operations on the database.
If you grant the appropriate privilege required to the OPERATOR user, it still does not work.
Example:
~~~~~~~~
SVRMGR> CREATE USER operator IDENTIFIED BY operator;
Statement processed.
SVRMGR> GRANT dba TO operator;
Statement processed.
SVRMGR> GRANT sysoper TO operator;
Statement processed.
SVRMGR> CONNECT operator/operator
Connected.
SVRMGR> SELECT username FROM dba_users;
USERNAME
——————————
SYS
SYSTEM
OUTLN
SCOTT
OPERATOR
5 rows selected.
SVRMGR> connect operator/operator as sysoper
Connected.
SVRMGR> select * from dba_users;
select * from dba_users
*
ORA-00942: table or view does not exist
SVRMGR> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup
ORACLE instance started.
Database mounted.
Database opened.
SVRMGR> CONNECT system/manager
Connected.
SVRMGR> GRANT select any table TO operator;
Statement processed.
SVRMGR> CONNECT operator/operator AS SYSOPER
Connected.
SVRMGR> SELECT * FROM DBA_USERS;
SELECT * FROM DBA_USERS
*
ORA-00942: table or view does not exist
Solution Description
~~~~~~~~~~~~~~~~~~~~
Grant the required privilege to the appropriate user.
SVRMGR> SELECT user FROM dual;
USER
——————————
PUBLIC
1 row selected.
SVRMGR> CONNECT system/manager
Connected.
SVRMGR> GRANT select any table TO PUBLIC;
Statement processed.
SVRMGR> CONNECT operator/operator AS SYSOPER
Connected.
SVRMGR> SELECT username FROM dba_users;
USERNAME
——————————
SYS
SYSTEM
OUTLN
SCOTT
OPERATOR
5 rows selected.
Explanation
~~~~~~~~~~~~
A user connected AS SYSOPER does not have the same set of privileges as if logged on as a “normal” user, and is no longer connected under the username he connected with.