This discussion is archived
9 Replies Latest reply: Nov 29, 2012 12:04 PM by Seyed_G RSS

Occasional lock of SYSTEM user ID

Seyed_G Newbie
Currently Being Moderated
Hi all,
We are running Oracle 10g. We have a problem with the ‘SYSTEM’ user ID getting locked occasionally. Is there a way to see what causes this account to get locked? I found out about this when a stored procedure that uses DBLink to delete data from two different databases failed with “ORA-28000: the account is locked”.

Thank you in advance,

Seyed
  • 1. Re: Occasional lock of SYSTEM user ID
    sb92075 Guru
    Currently Being Moderated
    Seyed_G wrote:
    Hi all,
    We are running Oracle 10g. We have a problem with the ‘SYSTEM’ user ID getting locked occasionally. Is there a way to see what causes this account to get locked? I found out about this when a stored procedure that uses DBLink to delete data from two different databases failed with “ORA-28000: the account is locked”.

    Thank you in advance,

    Seyed
    enable AUDIT CREATE SESSION

    likely batch job is using wrong password.
  • 2. Re: Occasional lock of SYSTEM user ID
    Mark D Powell Guru
    Currently Being Moderated
    Another potential cause of locked Oracle users is network security software that graps userID's off the network then attemps to log in using the ID and known weak passwords. Our customer has such software and while it does not try to log into Oracle as far as I can tell it does attempt to log onto SQL Server and into UNIX. We have traced locked OS accounts to the software but no exemptions are allowed. ^%@#!.

    But cron and/or batched access using a wrong password as Sb suggested is much more commonly the cause of Oracle username account locks.

    HTH -- Mark D Powell --
  • 3. Re: Occasional lock of SYSTEM user ID
    Seyed_G Newbie
    Currently Being Moderated
    Hi SB,
    Thank you for your input. I believe, our organization has opted to leave the AUDIT off. The reasons is that our load jobs are taking too long to run and turning audit on would make matters worse. With regards to wrong password, this stored procedure uses Invoker's rights and it never fails on production but occasionally fails on development. I just checked, our DBA unlocked the 'SYSTEM' user ID and my co-worker re-ran the stored procedure and it ran fine.

    Thanks again,

    Seyed
  • 4. Re: Occasional lock of SYSTEM user ID
    Seyed_G Newbie
    Currently Being Moderated
    Hi Mark,
    Thank you for the information. I will check with the Security unit to see if they run Security software against various user IDs such as Oracle accounts.

    Thanks again,

    Seyed
  • 5. Re: Occasional lock of SYSTEM user ID
    sb92075 Guru
    Currently Being Moderated
    Seyed_G wrote:
    Hi SB,
    Thank you for your input. I believe, our organization has opted to leave the AUDIT off. The reasons is that our load jobs are taking too long to run and turning audit on would make matters worse. With regards to wrong password, this stored procedure uses Invoker's rights and it never fails on production but occasionally fails on development. I just checked, our DBA unlocked the 'SYSTEM' user ID and my co-worker re-ran the stored procedure and it ran fine.
    It is fine until SYSTEM account goes locked again, again.
    You have not corrected the root cause of this failure.

    Enjoy your mystery.
  • 6. Re: Occasional lock of SYSTEM user ID
    Mark D Powell Guru
    Currently Being Moderated
    Turning audit on should have zero impact on your system because you can control what you audit! You could just audit unsuccessful login attempts for user system. All you have to do is check the audit rules set and remove any you do not want.

    Why is user system being used to begin with? As a general rule Oracle provided usernames should only be used by Oracle provided applications/features. Any applications your shop is running should be using application usernames set up for the applications so a DBA task would lbe the suspect in most well configured shops.

    HTH -- Mark D Powell --
  • 7. Re: Occasional lock of SYSTEM user ID
    Seyed_G Newbie
    Currently Being Moderated
    Mark,
    I will see if our DBAs could audit only unsuccessful logging attempts to 'SYSTEM'. With regards to user 'SYSTEM', I haven't been able to find where attempts to logon to 'SYSTEM' are made. Used Toad and searched all packages, stored procedures, functions, etc for any traces of logging on scripts. For the most part, application proxy ids have been used to run jobs. I just don't know where attempts are made to logon to system user ID.

    Thank you for your assistance,

    Seyed
  • 8. Re: Occasional lock of SYSTEM user ID
    sb92075 Guru
    Currently Being Moderated
    Seyed_G wrote:
    I just don't know where attempts are made to logon to system user ID.
    you would know after AUDIT gets enabled; since default behavior is to not record the failed logins.
  • 9. Re: Occasional lock of SYSTEM user ID
    Seyed_G Newbie
    Currently Being Moderated
    Thank you very much, I discussed turning audit on with our DBA. He said he will look into it and get back with me.

    Seyed

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points