ORAPWD Utility

Page 1 of 3
ORAPWD Utility
As Of Oracle9i Connect Internal Is No Longer Supported. By Default, The User SYS Is The Only
User That Has The SYSDBA Privilege. Oracle ORAPWD Utility Assists The DBA With Granting
SYSDBA And SYSOPER Privileges To Other Users.
Creating A Password File Via ORAPWD Enables Remote Users To Connect With Administrative
Privileges Through SQL*Net.
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= password= entries=
where
File Specifies The Name Of The File That Will Hold The Password Information. The
Default Location Will Be Current Directory Through Which This Utility Is Executed,
But Must Be Preferably Set To oracle_home/database Directory In Window
Platforms And oracle_home/dbs In Unix Platforms.
And, Also The Filename Should Be Named As PWD.ORA
The Contents Are Encrypted And Are Unreadable.
Password The Required Password Is The One For The SYS User Of The Database
Entries Specified As i.e. An Integer Value Which Denotes The Number Of
Database Users That Cab Be Granted SYSDBA OR SYSOPER Privilege.
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.
Page 2 of 3
2. Edit The Init.Ora Parameter REMOTE_LOGIN_PASSWORDFILE.
In Addition To Creating The Password File, You Must Also Set The Database Parameter Files
REMOTE_LOGIN_PASSWORDFILE Parameter To Either NONE Or EXCLUSIVE Or
SHARED.
Where
None
(default)
Oracle Ignores Any Password File. Privileged Users Must Be Authenticated By The
Operating System. This Is The Default Value For This Parameter.
Exclusive An EXCLUSIVE Password File Can Be Used With Only One Database. An
EXCLUSIVE File Can Contain The Names Of Users Other Than SYS. Using An
EXCLUSIVE Password File Lets You Grant SYSDBA And SYSOPER System
Privileges To Individual Users And Have Them Connect As Themselves.
Shared A SHARED Password File Can Be Used By Multiple Databases Running On The
Same Server. The Only User Recognized By A SHARED Password File Is SYS. All
Users Needing SYSDBA Or SYSOPER System Privileges Must Connect Using The
Same Name, SYS, And Password. You Cannot Change The Password For SYS If The
Password Fole Is SHARED.
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 which user is registered to password file.
You Can Query The Data Dictionary View Named V$PWFile_Users For Finding Users To Whom
You Have Granted SYSDBA OR SYSOPER Privilege.
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.
SQL> Show User
USER is “SYS”
Page 3 of 3
SQL> Connect Scott/Tiger
Connected.
SQL> Show User
USER is “SCOTT”
Removing The Password File
If You Determine That You No Longer Require A Password File To Authenticate Users, You Can
Delete The Password File And Reset The REMOTE_LOGIN_PASSWORDFILE Init.Ora
Parameter To NONE. After You Remove This File, Only Those Users Who Can Be Authenticated
By The Operating System Can Perform Database Administration Operations.
Do Not Remove Or Modify The Password File If You Have A Database Or Instance Mounted
Using REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE Or SHARED. If You Do, You Will
Be Unable To Reconnect Remotely Using The Password File. Even If You Replace It, You Cannot
Use The New Password File, Because The Timestamps And Checksums Will Be Wrong.
Changing The Password For The SYS User.
Either Of These Commands Will Change The Password For The SYS User:
ORAPWD Force=Y File=Orapwsid Password=New_Password
-- OR --
SQL> Alter User Sys Identified By New_Password
PDF created with pdfFactory trial version www.pdffactory.com