 |
|
orapwd TipsOracle Database Tips by Donald Burleson |
Using orapwd
The
Oracle
orapwd command line 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 that
are required to use orapwd.
Creating a password file via orapwd
enables
remote users to connect with administrative privileges through
SQL*Net.
Warning:
Using orapwd gives other users the Oracle super user
privileges.
|
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:
- 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 orapwd 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_users is 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.
-
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
- 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.
- 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.
Orapwd with case
sensitive passwords
In the past, many people were not aware of the
simple fact that Oracle passwords were not case-sensitive.
In Oracle 11g, passwords became case-sensitive.
The Oracle 11g password file can store passwords as
case-sensitive or case-insensitive.
The password file creation
utility evaluates the new parameter
ignorecase to allow case-sensitive
passwords or restrict passwords to case insensitivity. In order to
create a password file with orapwd, which allows case-sensitive
passwords, set ignorecase to N.
[oracle@rhas4 ~]$ orapwd help=y
Usage:
orapwd file=<fname> password=<password> entries=<users>
force=<y/n> ignorecase=<y/n> nosysdba=<y/n>
Where:
file - name
of password file (required), password -
password for SYS (optional), entries -
maximum number of distinct DBA (required),
force - whether to overwrite existing file (optional),
ignorecase - passwords are case-insensitive (optional),
nosysdba - whether to shut out the SYSDBA logon (optional Database
Vault only).
There must be no spaces around the equal-to (=)
character.
|
|
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
|