RelationalDBDesign RelationalDBDesign


Password Files   «Prev 

How to Grant System Privileges

As with object privileges, you use the GRANT statement to confer system privileges on either a user or a role. Unlike object privileges, the optional keywords WITH ADMIN OPTION are required to additionally allow the grantee to confer these privileges on other users and roles. For example, to give the CREATE USER, ALTER USER, and DROP USER privileges to the role APPL_DBA, you execute the following statement:
GRANT create user, alter user, 
drop user TO appl_dba;
  1. System and role privileges require the wording WITH ADMIN OPTION;
  2. object privileges require the wording WITH GRANT OPTION.
Because the function is so similar but the syntax is different, be sure you know when to use ADMIN and when to use GRANT—a question involving this subtle difference may appear on the exam.

AS SYSDBA Clause

This CONNECT command is similar to what you have seen so far, the major addition is the SYSDBA clause at the end.

CONNECT system/manager@coin AS SYSDBA
The username is system
CONNECT system/manager@coin AS SYSDBA
The password is manager

CONNECT system/manager@coin AS SYSDBA
Coin is the name of a Network Service. The @coin syntax is being used to connect to a remote database over a network.

CONNECT system/manager@coin AS SYSDBA
The AS SYSDBA Clause lets the system user connect in its role as a SYSDBA

CONNECT system/manager@coin

The system user could connect like this, but then would not have access to any privileges confered by the SYSDBA role.

CONNECT system/manager@coin AS SYSOPER
To connect using the SYSOPER role, assuming that you have been granted that role, use AS SYSOPER instead of SYSDBA.