Password Files   «Prev  Next»

Lesson 8 Grant SYSDBA privileges to a user
Objective Grant SYSDBA privileges to a database administrator.

Grant SYSDBA Privileges to a User

Different DBA Roles

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.
  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.

Different DBA Roles
The following Slide Show explains what each role allows you to do:

  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.)

Oracle DBA Roles

How to grant roles

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: 
SVRMGR> connect internal/herman
Connected.
SVRMGR> grant sysdba to system;
Statement processed.
SVRMGR>

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.

Create and Manage resource plans

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.

Granting Privileges - Exercise

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

Ad Oracle 13c Cloud