5.5 The SYSDBA and SYSOPER Roles In Chapter 8, we'll examine the use of operating system privileges for database access. One of the privileges used for authentication is the OSDBA operating system privilege. Another operating system privilege is OSOPER. Working hand in hand with these privileges is a mechanism known as the SYSDBA and SYSOPER roles, created in version 7.1.6 of the Oracle RDBMS. 5.5.1 Remote Database Administration If a database account has been granted the SYSDBA or SYSOPER role, that account gains the ability to connect in any of the following ways: - CONNECT INTERNAL AS SYSDBA
- CONNECT / AS SYSDBA
- CONNECT INTERNAL AS SYSOPER
- CONNECT / AS SYSOPER
The user can then perform privileged actions like starting up and shutting down the database. An account connected as SYSDBA can perform any function a user who has been granted the DBA role can perform. The SYSDBA privilege should be granted as cautiously as the normal DBA role. The SYSDBA role enables you to perform the following actions (as well as execute any of the privileges shown in Table 5.4): 5.5.2 System Privileges for the SYSDBA and SYSOPER Roles SYSDBA role system privileges are shown in Table 5.4. There are 77 of these privileges in Oracle7, and 89 in Oracle8. The 12 new privileges in Oracle8 encompass actions with directory, library, and type. From a privilege perspective, the SYSDBA privileges are identical to the DBA privileges.  | A user who has been granted the SYSDBA role or SYSOPER role has ADMIN OPTION and can pass any of the associated privileges on to other users with or without ADMIN OPTION if he chooses. | | Table 5.4. SYSDBA Role System Privileges | Privilege | | ALTER ANY CLUSTER | | ALTER ANY INDEX | | ALTER ANY LIBRARY (new in Oracle8) | | ALTER ANY PROCEDURE | | ALTER ANY ROLE | | ALTER ANY SEQUENCE | | ALTER ANY SNAPSHOT | | ALTER ANY TABLE | | ALTER ANY TRIGGER | | ALTER ANY TYPE (new in Oracle8) | | ALTER DATABASE | | ALTER PROFILE | | ALTER RESOURCE COST | | ALTER ROLLBACK SEGMENT | | ALTER SESSION | | ALTER SYSTEM | | ALTER TABLESPACE | | ALTER USER | | ANALYZE ANY | | AUDIT ANY | | AUDIT SYSTEM | | BACKUP ANY TABLE | | BECOME USER | | COMMENT ANY TABLE | | CREATE ANY CLUSTER | | CREATE ANY DIRECTORY (new in Oracle8) | | CREATE ANY INDEX | | CREATE ANY LIBRARY (new in Oracle8) | | CREATE ANY PROCEDURE | | CREATE ANY SEQUENCE | | CREATE ANY SNAPSHOT | | CREATE ANY SYNONYM | | CREATE ANY TABLE | | CREATE ANY TRIGGER | | CREATE ANY TYPE (new in Oracle8) | | CREATE ANY VIEW | | CREATE CLUSTER | | CREATE DATABASE LINK | | CREATE LIBRARY (new in Oracle8) | | CREATE PROCEDURE | | CREATE PROFILE | | CREATE PUBLIC DATABASE LINK | | CREATE PUBLIC SYNONYM | | CREATE ROLE | | CREATE ROLLBACK SEGMENT | | CREATE SEQUENCE | | CREATE SESSION | | CREATE SNAPSHOT | | CREATE SYNONYM | | CREATE TABLE | | CREATE TABLESPACE | | CREATE TRIGGER | | CREATE TYPE (new in Oracle8) | | CREATE USER | | CREATE VIEW | | DELETE ANY TABLE | | DROP ANY CLUSTER | | DROP ANY DIRECTORY (new in Oracle8) | | DROP ANY INDEX | | DROP ANY LIBRARY (new in Oracle8) | | DROP ANY PROCEDURE | | DROP ANY ROLE | | DROP ANY SEQUENCE | | DROP ANY SNAPSHOT | | DROP ANY SYNONYM | | DROP ANY TABLE | | DROP ANY TRIGGER | | DROP ANY TYPE (new in Oracle8) | | DROP ANY VIEW | | DROP PROFILE | | DROP PUBLIC DATABASE LINK | | DROP PUBLIC SYNONYM | | DROP ROLLBACK SEGMENT | | DROP TABLESPACE | | DROP USER | | EXECUTE ANY LIBRARY (new in Oracle8) | | EXECUTE ANY PROCEDURE | | EXECUTE ANY TYPE (new in Oracle8) | | FORCE ANY TRANSACTION | | FORCE TRANSACTION | | GRANT ANY PRIVILEGE | | GRANT ANY ROLE | | INSERT ANY TABLE | | LOCK ANY TABLE | | MANAGE TABLESPACE | | RESTRICTED SESSION | | SELECT ANY SEQUENCE | | SELECT ANY TABLE | | UPDATE ANY TABLE | The major difference between the SYSDBA and SYSOPER accounts is that the SYSOPER account cannot create or perform recovery of a database. As you will see in Chapter 8, when an account is granted SYSDBA or SYSOPER, an entry is placed in an operating system password file. An account which has been granted SYSDBA or SYSOPER can access the database from a remote node and perform administrator duties . The connection convention of CONNECT / AS SYSDBA or CONNECT / AS SYSOPER has been created to replace the CONNECT INTERNAL convention, which has been around for several versions of the RDBMS. Currently, CONNECT INTERNAL has been retained for backward compatibility. | | If your account has been granted the operating system privileges OSDBA or OSOPER, you do not need to be explicitly granted the SYSDBA or SYSOPER privilege to connect to the database and perform privileged tasks . | | 5.5.2.1 About OSOPER and SYSOPER Generally, the OSOPER role has the ability to issue the following commands: - STARTUP
- SHUTDOWN
- ALTER DATABASE OPEN/MOUNT
- ALTER DATABASE BACKUP
- ARCHIVE LOG
- RECOVER
This role also allows the user to enable RESTRICTED SESSION. The SYSOPER role enables the same privileges as its operating system counterpart , OSOPER, but allows the account granted these privileges to perform the actions remotely, as well as directly from the local operating system account. 5.5.2.2 About OSDBA and SYSDBA The OSDBA role encompasses all of the system privileges with ADMIN OPTION, the OSOPER role values, and the ability for the user to CREATE DATABASE and perform time-based recovery. Likewise, the SYSDBA role enables the same privileges as its operating system counterpart, OSDBA, but allows the account granted these privileges to perform the actions remotely, as well as directly from the local operating system account. 5.5.3 About CONNECT INTERNAL In the previous sections, we briefly mentioned a new convention for connecting to the database through the Server Manager or SQLDBA. The reference was to CONNECT INTERNAL AS or CONNECT / AS a privilege set. Let's look at what happens when you perform a CONNECT / or CONNECT INTERNAL. The only way a database can be created, started, or shut down is through a connection to the heart of the database. Some forms of recovery can only be performed from this connection. The connection is accomplished in one of two ways: either directly through the SQLDBA utility or Server Manager utility ( svrmgrl or svrmgrm ) or through a remote connection using the Oracle Enterprise Manager and an intelligent agent connection. Either way, when you CONNECT INTERNAL, you are connecting to the database as sys with much higher privilege levels. There are subtle differences between logging on to SQL*Plus as the user sys and connecting through the utilities as CONNECT INTERNAL. The only accounts that have the ability to connect to the internal sys account are those which are either in the DBA operating system group or have been granted the SYSOPER or SYSDBA privilege. Care and thought must be given to allowing anyone but a trained DBA the ability to access the database using the CONNECT INTERNAL mechanism. Great harm can be inadvertently done to a database if a person who is not a skilled DBA attempts to perform work connected in this man ner. New Default Roles in Oracle8 As we mentioned, three new roles have been added in Oracle8. Why are these roles needed? Normally, users who have been granted explicit privileges on objects and users who have been granted the SYSDBA or DBA role can access the objects in the data dictionary. However, there may be times when you need to interact with objects to which you have not been granted access. In Oracle8, you can be granted one of the following roles to enable you to access these objects: - DELETE_CATALOG_ROLE
-
Grants DELETE on the SYS.AUD$ table. - EXECUTE_CATALOG_ROLE
-
Grants EXECUTE on all dictionary packages. - SELECT_CATALOG_ROLE
-
Grants SELECT on all dictionary packages. |  | 
