My Oracle Support Banner

A User Connected AS SYSOPER Can Only Perform STARTUP and SHUTDOWN (Doc ID 109891.1)

Last updated on DECEMBER 12, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.1 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
Checked fore relevance on 05-Sep-2010


Symptoms

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.


SQL> CREATE USER <Username> IDENTIFIED BY <Password>;
Statement processed.

SQL> GRANT dba TO <Username>;
Statement processed.

SQL> GRANT sysoper TO <Username>;
Statement processed.

SQL> CONNECT <Username>/<Password> 
Connected.

SQL> SELECT username FROM dba_users;

USERNAME
------------------------------
<List of all DB users>

5 rows selected.

SQL> connect <Username>/<Password> as sysoper
Connected.

SQL> select * from dba_users;
select * from dba_users
*
ORA-00942: table or view does not exist

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Database mounted.
Database opened.

SQL> CONNECT system/<Password>
Connected.

SQL> GRANT select any table TO <Username>;
Statement processed.

SQL> CONNECT <Username>/<Password> AS SYSOPER
Connected.
SQL> SELECT * FROM DBA_USERS;
SELECT * FROM DBA_USERS
*
ORA-00942: table or view does not exist



Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.