| 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: 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.
- 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 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
- 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
SYSDBA roles, you must be logged on as either
SYSDBA or as
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
Here is an example that shows how to grant the
SYSDBA role to a
SVRMGR> connect internal/herman
SVRMGR> grant sysdba to system;
When you grant the
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
role to the
user in your
Granting Privileges - Exercise