The Oracle orapwd utility assists the DBA with granting
SYSDBA and SYSOPER
privileges to other users. By
default, the user SYS is the only user that has these privileges.
Creating a password file via orapwd enables remote users to
connect with administrative privileges through SQL*Net.
The SYSOPER privilege allows instance startup, shutdown, mount, and
dismount. It allows the DBA to perform general database maintenance
without viewing user data. The SYSDBA privilege is the same as
connect internal was in prior versions. It provides
the ability to do everything, unrestricted.
If orapwd has not yet been executed, attempting
to grant SYSDBA or SYSOPER privileges will result in the following
error:
SQL> grant sysdba to scott;
ORA-01994: GRANT failed: cannot add users to public password file
The following steps can be performed to grant other users these
privileges:
1.
Create the password file. This is done by executing the
following command:
$ orapwd file=filename password=password entries=max_users
The filename is the name of the file that will hold the password
information. The file location will default to the current directory
unless the full path is specified. The contents are encrypted and are
unreadable. The password required is the one for the SYS user of the
database.
The max_usersis the number of database users that can be granted SYSDBA
or SYSOPER. This parameter should be set to a higher value than the
number of anticipated users to prevent having to delete and recreate the
password file.
2.
Edit the init.ora parameter remote_login_passwordfile.
This parameter must be set to either SHARED or EXCLUSIVE.When set
to SHARED, the password file can be used by multiple databases, yet only
the SYS user is recognized. When set to EXCLUSIVE, the file can be used
by only one database, yet multiple users can exist in the file. The
parameter setting can be confirmed by:
SQL> show parameter password
NAME TYPE VALUE
----------------------------- ----------- ----------remote_login_passwordfile string
EXCLUSIVE
3.
Grant SYSDBA or SYSOPER to users. When SYSDBA or SYSOPER privileges
are granted to a user, that user's name and privilege information are
added to the password file.
SQL> grant sysdba to scott;
Grant succeeded.
4.
Confirm that the user is listed in the password file.
SQL> select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER
------------------------------ ------ -------
SYS TRUE TRUE
SCOTT TRUE FALSE
Now the user SCOTT can connect as SYSDBA. Administrative users
can be connected and authenticated to a local or remote database by
using the SQL*Plus connect command. They must connect using
their username and password, and with the AS SYSDBA or AS SYSOPER
clause:
SQL> connect scott/tiger as sysdba;
Connected.
The DBA utilizes the orapwd utility to grant SYSDBA and SYSOPER
privileges to other database users. The SYS password should never be
shared and should be highly classified.
|
For more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo,
Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly
from
Rampant TechPress.
|
Regards,
Don Burleson
www.dba-oracle.com
www.remote-dba.net
|