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