lock sys

In the old days, locking sys had not much effect.

SQL> alter user sys identified by *** account lock;
User altered.
SQL> select account_status
from dba_users
where username='SYS';
ACCOUNT_STATUS
--------------------------------
LOCKED
SQL> conn / as sysdba
Connected.
SQL> conn sys/** as sysdba
Connected.
SQL> conn sys/***@db01 as sysdba
Connected.

Well, in the very-old days, Oracle7, or with the in 12cR2-deprecated parameter O7_DICTIONARY_ACCESSIBILITY, SYS could be locked. But this is out of the scope of this post.

In 12cR2, it is now possible to lock SYS.

SQL> alter user sys
identified by ***
account lock;
User altered.
SQL> select account_status
from dba_users
where username='SYS';
ACCOUNT_STATUS
--------------------------------
LOCKED
SQL> conn / as sysdba
Connected.
SQL> conn sys/** as sysdba
Connected.
SQL> conn sys/***@db01 as sysdba
ERROR:
ORA-28000: the account is locked

I like it 🙂 Oracle recommends you create other users to perform DBA tasks.

SQL> grant dba, sysdba
to user0001
identified by ***;
Grant succeeded.

Still, probably intentionally left so or simply forgotten, Oracle recommends to lock all Oracle supplied accounts except for SYS and SYSTEM (ref: Changing Passwords for Oracle Supplied Accounts)

Also note, you’ll get an ORA-40365 if you use an old-style password file


SQL> alter user sys identified by *** account lock;
alter user sys identified by *** account lock
*
ERROR at line 1:
ORA-40365: The SYS user cannot be locked
while the password file is in its current format.

5 thoughts on “lock sys”

  1. Geert De Paep

    So if you lock all accounts in the database (incl. SYS) and then log out, can you still get in then?…

  3. Oliver Welsch

    Using the role ‘sysdba’ is not really the same like ‘sys’.
    One thing it’s not possible to grant rights to the dictionary:

    newsysadm@db01> grant select on sys.dba_users to new_user;
    ORA-1031 insufficient privileges

    That’s to keep in mind! 🙂

    Nevertheless a good article!!! :):):)

  4. Laurent Schneider Post author

    SYSDBA is connecting as SYS

    SQL> grant sysdba to newsysadm;
    Grant succeeded.
    SQL> conn newsysadm/***@DB01 as sysdba
    Connected.
    SQL> sho user
    USER is "SYS"
    SQL> grant select on sys.dba_users to new_user;
    Grant succeeded.
    SQL>

    Don’t confuse role DBA and administrative privilege SYSDBA.
    Maybe of interest : sysdba

  5. ALI

    Hi , can we lock the sys and system accounts in Oracle 11g. and how i can unlock them if i don’t have other administrator accounts

Comments are closed.