Grant SYSDBA privileges to a database administrator

To grant SYSDBA privileges to a database administrator in Oracle 11g R2, follow these steps:

1. Connect to the Database as a User with SYSDBA Privileges
   • First, connect to the Oracle database as a user who already has SYSDBA privileges (such as the SYS user).
   • Use the following command from the command line or SQL*Plus:

         sqlplus / as sysdba
         

2. Grant SYSDBA Privileges to the User
   • Once connected as SYSDBA, use the GRANT statement to assign SYSDBA privileges to the specific user.
     

     GRANT SYSDBA TO username;
     

   • Replace username with the actual username of the database administrator to whom you want to grant SYSDBA privileges.
3. Verify the Grant
   • You can verify that the user has SYSDBA privileges by querying the V$PWFILE_USERS view.
     

     SELECT * FROM V$PWFILE_USERS WHERE USERNAME = 'username';
     

   • This view will show the privileges assigned to the user, confirming if SYSDBA has been granted.
4. Connect as the User with SYSDBA Privileges
   • Now, the user can connect to the database with SYSDBA privileges by using:
     

     sqlplus username AS SYSDBA
     

   • They will need to provide their password if prompted, assuming the password file is configured correctly.

Important Notes:

• The SYSDBA privilege grants full administrative access to the database, including the ability to start up, shut down, and perform all critical database maintenance tasks. Therefore, only trusted administrators should be granted this privilege.
• Ensure the password file (orapwd) is created and configured if remote login authentication is required. This file allows Oracle to authenticate users with SYSDBA privileges.

Example

sqlplus / as sysdba
GRANT SYSDBA TO admin_user;
SELECT * FROM V$PWFILE_USERS WHERE USERNAME = 'admin_user';

This process will grant the `admin_user` full administrative rights in the Oracle database as a SYSDBA.

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

1. 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.
2. 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 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.
1) SYSOPER Privilege: The SYSOPER privilege allows a user to perform the following, routine functions:

1. Startup the database
2. Shutdown the database
3. Open and close the database
4. Recover the database (not time-based)
5. Connect when the database is in restricted session mode
6. Stop and start archive logging
7. Manually archive a log file
8. View the current archive log status

2) SYSDBA Privilege: The SYSDBA privilege allows a user to do anything, including the following:

1. Anything that SYSOPER can do,
2. time based recovery,
3. create a new database

SYSDBA also covers all system privileges on the user.

Scenario: I have the DBA role and am able to connect as SYSDBA, but I am not able to connect as SYSOPER:

SQL> connect pubs/pubs
 Connected.
SQL> drop user tom; 

User dropped. 

SQL> create user tom identified by tom1234; 
User created. 

SQL> grant dba to tom
SQL> connect tom/tom1234 as sysdba;
 Connected.
 
SQL> connect tom/tom1234 as sysoper;
ERROR:
ORA-01031: insufficient privileges 

Warning: You are no longer connected to ORACLE.
 SQL> 
SQL> connect pubs/pubs;
Connected.
SQL> 
SQL> grant sysoper to tom;
grant sysoper to tom
 *
ERROR at line 1:
ORA-01031: insufficient privileges 

SQL> 
SQL> connect tom/tom1234 as sysoper;
ERROR:
ORA-01031: insufficient privileges

Question: What is the difference between the dba roles

1. sysdba system privilege and
2. sysoper system privilege?

Answer: The DBA role does not include the SYSDBA or SYSOPER system privileges.
The SYSDBA and SYSOPER are special administrative privileges that allow an administrator to perform basic database administration tasks, such as

1. creating the database,
2. instance startup and shutdown,
3. drop a database,
4. open and mount a database, or
5. place database in archivelog mode.

1. The SYSOPER privilege allows a user to perform the following, routine functions: 1) Startup the database, 2) Shutdown the database, 3)Open and close the database, 4) Recover the database (not time-based), 5) Connect when the database is in restricted session mode, 6) Stop and start archive logging, 7) Manually archive a log file, 8) View the current archive log status
2. The SYSDBA privilege allows a user to do anything, including the following: 1. Anything that SYSOPER can do, 2. time based recovery, 3. create a new database (SYSDBA also covers all system privileges on the user.)

To grant someone the SYSOPER or SYSDBA roles, you must be logged on as either SYSDBA or as INTERNAL. It is 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 internal/herman
Connected.
SQL> grant sysdba to system;
Statement processed.
SQL>

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.

In order to create and manage resource plans and resource consumer groups, you must have the ADMINISTER_RESOURCE_MANAGER system privilege enabled for your session. DBAs have this privilege with the WITH ADMIN OPTION. To grant this privilege to non-DBAs, you must execute the GRANT_SYSTEM_PRIVILEGE procedure of the DBMS_RESOURCE_MANAGER_PRIVS package. The following example grants the user GGOULD the ability to manage the Database Resource Manager:

execute DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE -
(grantee_name => 'GGOULD', -
privilege_name => 'ADMINISTER_RESOURCE_MANAGER', -
admin_option => TRUE);

You can revoke GGOULD’s privileges via the REVOKE_SYSTEM_PRIVILEGE procedure of the DBMS_RESOURCE_MANAGER package.

Before moving on to the next lesson, click the Exercise link below to grant the SYSDBA role to the SYSTEM user in your COIN database.
Granting Privileges - Exercise