LLI Wiki — My Digital Knowledge Base

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

  1. Log on with a Windows account that belongs to the local ORA_DBA group.
  2. Temporarily disable native authentication in sqlnet.ora.
  3. Connect with / as sysdba, rebuild the password file and create the required accounts.
  4. Restore the original sqlnet.ora.

Preparation

Registry Editor: Oracle keys including ORACLE_SID

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>"

Creating an administration account and granting privileges in SQL*Plus

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

Mastodon LLIMastodon SocialFosstodon