In order to allow a user to connect as a DBA and perform administrative functions, you need to grant the user one of two special roles. These roles are named SYSDBA and SYSOPER. The SYSDBA role is the most powerful because it lets you do anything. The SYSOPER role allows you to grant someone the ability to perform relatively routine tasks. Oracle also provides a DBA role, but DBA is not quite the same as SYSDBA. The SYSDBA and SYSOPER privilege allows access to database instance even when it is not open.
SYSDBA: A special type of privilege that allows the holder to administer an Oracle database. A user logged in as SYSDBA can do anything, including starting, stopping, and recovering the database. It is automatically created and granted the DBA role when an oracle database is installed. The default password for SYS is change_on_install.
The sysdba allows the user to perform following routine function.
- Startup the Database
- Shutdown the Database.
- Open and Close the Database
- Recover the database (not time based)
- Connect when database is in restricted session mode.
- Stop and Start archive logging
- Manually archive a log file.
- View the current archive log status.
To grant someone the SYSOPER or SYSDBA roles, you must be logged on as either SYSDBA or as INTERNAL. It’s not enough just to have the SYSDBA role yourself; you must connect in that role. You will learn how to connect as SYSDBA later in this module. For now, continue to connect as the INTERNAL user.
Here is an example that shows how to grant the SYSDBA role to a SYSTEM user:
SQL> Connect sys/oracle@orcl3;
SQL> Grant sysdba to system;
When you grant the SYSDBA or SYSOPER roles to a user, that user is immediately given an entry in the password file. The entry for that user will remain as long as the user continues to hold either of those roles.
SYSOPER: A special type of privilege that allows the holder to perform several routine administrative tasks against an Oracle database. A user logged in as SYSOPER is limited to starting the database, stopping the database, certain types of recovery, and a few other routine chores.
- The sysdba privilege allows the user do anything, including the following:
- Anything that SYSOPER can do.
- Time based recovery.
- Create a new database.
Note: sysdba also cover all system privileges on the user .If you connect as sysdba you become sys and if you connect as sysoper you become public.
C: connect /nolog
SQL> connect sys/oracle as sysoper;
User is “PUBLIC”
SQL> connect sys/oracle as sysdba;
SQL> show user;
User is “SYS”
SQL> Grant sysdba, sysoper to Shahid;
Now I can connect as sysdba
SQL> connect Shahid/ahmed as sysdba;
SQL> show user;
User is “SYS”
SYS and SYSTEM Users Difference:
The following administrative user accounts are automatically created when you install Oracle Database. They are both created with the password that you supplied upon installation, and they are both automatically granted the DBA role.
This account can perform all administrative functions. All base tables and views for the database data dictionary are stored in the SYS schema. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS schema.
The SYS user is granted the SYSDBA privilege, which enables a user to perform high-level administrative tasks such as backup and recovery.
This account is automatically created and granted DBA role when oracle database is installed. SYSTEM account has default password “MANAGER”. This account can perform all administrative functions except the following:
- Backup and recovery
- Database upgrade
While this account can be used to perform day-to-day administrative tasks, Oracle strongly recommends creating named users account for administering the Oracle database to enable monitoring of database activity.