Question: I want to know what SYSDBA privilege
can do. I know SYSDBA is SUPERSET of SYSOPER privilege, but what is
the SYSDBA privilege?
SYSDBA is like a role in the sense that it is granted, but SYSDBA is
a special built-in privilege to allow the DBA full control over the
- Access to all data dictionary tables (dba views)
- access to all v$ views and x$ structures
- perform startup/shutdown commands
- alter database: mount, open, back up, or change
character set commands
- create/drop database commands
Granting SYSDBA privileges
Oracle creates three roles when you first create your database,
CONNECT, RESOURCE and DBA. When a use is granted SYSDBA, they are
allowed to connect as SYSDBA within SQL*Plus (via the
connect sys/* as sysdba;
myuser identified by mypass;
grant sysdba to myuser;
If you intend to use OS authentication (identified externally or
OPS$), we have the OSDBA role which is created when you install the
The SYSDBA privilege is a special
"global" privilege, grantable only when connected from SYS or
SYSTEM, and you cannot see the roles within SYSDBA by querying the
dba_role_privs and related system views.
role (and the ability to connect via SYSDBA includes all system
privileges (95 separate grants), the capability to grant those
system privileges to others, and UNLIMITED TABLESPACE is granted in
the same way as with the RESOURCE role.
exp_full_database and imp_full_database roles have
been created, the delete, execute and select_catalog_role
roles are granted implicitly with the SYSDBA role (and hence the
connect as SYSDBA command).
How to connect as SYSDBA
In UNIX, you connect as sysdba thusly, being careful not to put
the password on the UNIX prompt (where it can be seen):
root> sqlplus /
SQL> connect myuser as sysdba password
In Windows, you connect from the DOS prompt:
C:\>sqlplus "sys as sysdba"
But it's never a good idea on a Windows server to include
the password for a SYSDBA user outside of Oracle:
C:\>sqlplus "sys/my_password as sysdba"
Prior to the connect xxx as sysdba syntax, Oracle
offered an alternative mechanism called connect internal
via granting a DBA role to give a user the equivalent of a SYSDBA
connection, using an obsolete command executable called svrmgrl.
See these notes if you get an
error trying to connect as SYSDBA.
And see our related notes on
connecting as SYSDBA.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.