Oracle on Windows: regain SYSDBA access after losing the sys password
Temporarily disable NTS authentication, rebuild the password file and create Oracle administration accounts on Windows.
Procedure to regain SYSDBA access on an Oracle database installed on Windows when the sys password is lost: temporarily disable Windows native authentication (NTS), rebuild the password file, then create administration accounts.
Principle
- Log on with a Windows account that belongs to the local
ORA_DBAgroup. - Temporarily disable native authentication in
sqlnet.ora. - Connect with
/ as sysdba, rebuild the password file and create the required accounts. - Restore the original
sqlnet.ora.
Preparation
- Find the SID:
echo %ORACLE_SID%, or the registry key underHKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\(a key such asKEY_OraClient18Home1). - Add the current user to the local Windows group
ORA_DBA. - Back up
sqlnet.ora(for examplesqlnet.ora.<date>) in theNETWORK\ADMINfolder of the Oracle Home.
Edit sqlnet.ora
Original file (generated by netca):
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES = (NTS)
Change the value to:
SQLNET.AUTHENTICATION_SERVICES = (NONE)
Open a local SQL*Plus session
set ORACLE_BASE=D:\Oracle\product\11.1.0
set ORACLE_HOME=D:\Oracle\product\11.1.0\db_1
set ORACLE_SID=ORCL
sqlplus "/ as sysdba"
Rebuild the password file
Back up the existing pwd<SID>.ora (copy it to .bak), then regenerate it:
orapwd file=pwd<SID>.ora password=<NEW_SYS_PASSWORD>
Reconnect and check:
sqlplus /nolog
connect sys as sysdba
select * from v$pwfile_users;
Create administration accounts
create user admin_user identified by '<PASSWORD>';
GRANT connect, resource to admin_user;
GRANT CONNECT, RESOURCE, DBA TO admin_user;
grant all privileges to admin_user;
GRANT UNLIMITED TABLESPACE TO admin_user;
GRANT SELECT ANY TABLE to admin_user;
Connection test:
sqlplus "sys/<PASSWORD> as sysdba"
sqlplus "admin_user/<PASSWORD>"
Examples of targeted grants
GRANT SELECT, INSERT, UPDATE, DELETE ON app_schema.customers TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON app_schema.products TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON app_schema.orders TO app_user;
Restore the configuration
Put the original sqlnet.ora back (SQLNET.AUTHENTICATION_SERVICES = (NTS)).
Notes
- This procedure gives full access to the database: use it only to recover lost access, with proper authorisation.
- Do not leave
SQLNET.AUTHENTICATION_SERVICES = (NONE)in place once the work is done. - Reference: How to recover from lost SYS password in Oracle Database.