Home » RDBMS Server » Security » User Getting Locked (Oracle 11g)
User Getting Locked [message #617672] Wed, 02 July 2014 11:28 Go to next message
azeem87
Messages: 100
Registered: September 2005
Location: dallas
Senior Member
Hi,

we have an Read User , that has access on Application tables, used by QA/DEV team.
ALTER PROFILE ADMIN LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

CREATE USER ANGREADUSR
IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE ADMIN
ACCOUNT UNLOCK;
-- 1 Role for ANGREADUSR
GRANT CREATE SESSION TO ANGREADUSR;
ALTER USER  ANGREADUSR DEFAULT ROLE ALL;


for past few days users complaint about account getting locked.
enable audit
SQL> audit create session whenever not successful;

when i run below sql, why am i seeing SYSTEM user here..
select   os_username, username,terminal,
   to_char(timestamp,'MM-DD-YYYY HH24:MI:SS')rom   dba_audit_trail where username='ANGREADUSR'   order by 4 desc

OS_USERNAME	USERNAME	TERMINAL	TO_CHAR(TIMESTAMP,'MM-DD-YYYYHH24:MI:SS')
ABC1234	ANGREADUSR	unknown	07-02-2014 10:19:33
ABC1234	ANGREADUSR	unknown	07-02-2014 10:09:42
ABC1234	ANGREADUSR	unknown	07-02-2014 10:05:25
ABC1234	ANGREADUSR	unknown	07-02-2014 10:05:21
EFGH1234	ANGREADUSR	unknown	07-02-2014 09:42:20
SYSTEM	ANGREADUSR	unknown	07-02-2014 09:04:05
SYSTEM	ANGREADUSR	unknown	07-02-2014 09:03:14
HIJK1234	ANGREADUSR	unknown	07-02-2014 09:01:17
HIJK1234	ANGREADUSR	unknown	07-02-2014 09:00:43
HIJK1234	ANGREADUSR	unknown	07-02-2014 08:59:50
HIJK1234	ANGREADUSR	unknown	07-02-2014 08:59:25
EFGH1234	ANGREADUSR	unknown	07-02-2014 06:28:14
SYSTEM	ANGREADUSR	unknown	07-02-2014 06:17:09
SYSTEM	ANGREADUSR	unknown	07-02-2014 06:04:25
SYSTEM	ANGREADUSR	unknown	07-02-2014 05:49:18
SYSTEM	ANGREADUSR	unknown	07-02-2014 05:34:11
SYSTEM	ANGREADUSR	unknown	07-02-2014 05:19:04
SYSTEM	ANGREADUSR	unknown	07-02-2014 05:03:57
SYSTEM	ANGREADUSR	unknown	07-02-2014 04:48:51
SYSTEM	ANGREADUSR	unknown	07-02-2014 04:33:44
SYSTEM	ANGREADUSR	unknown	07-02-2014 04:18:36
SYSTEM	ANGREADUSR	unknown	07-02-2014 04:03:30
SYSTEM	ANGREADUSR	unknown	07-02-2014 03:48:22
SYSTEM	ANGREADUSR	unknown	07-02-2014 03:33:16
SYSTEM	ANGREADUSR	unknown	07-02-2014 03:18:08
EFGH1234	ANGREADUSR	unknown	07-02-2014 03:04:43
EFGH1234	ANGREADUSR	unknown	07-02-2014 03:03:48
SYSTEM	ANGREADUSR	unknown	07-02-2014 03:03:01
SYSTEM	ANGREADUSR	unknown	07-02-2014 02:47:55
SYSTEM	ANGREADUSR	unknown	07-02-2014 02:32:47
SYSTEM	ANGREADUSR	unknown	07-02-2014 02:17:39
SYSTEM	ANGREADUSR	unknown	07-02-2014 02:02:32
SYSTEM	ANGREADUSR	unknown	07-02-2014 01:47:25
SYSTEM	ANGREADUSR	unknown	07-02-2014 01:32:18
SYSTEM	ANGREADUSR	unknown	07-02-2014 01:17:12
SYSTEM	ANGREADUSR	unknown	07-02-2014 01:02:05
SYSTEM	ANGREADUSR	unknown	07-02-2014 00:46:57
SYSTEM	ANGREADUSR	unknown	07-02-2014 00:31:50
SYSTEM	ANGREADUSR	unknown	07-02-2014 00:16:43
SYSTEM	ANGREADUSR	unknown	07-02-2014 00:01:36
SYSTEM	ANGREADUSR	unknown	07-01-2014 23:46:29



i have unlocked the account multiple times, not sure why i am seeing SYSTEM, any idea.. what else can i do to find the person locking the account.

Thanks
Re: User Getting Locked [message #617674 is a reply to message #617672] Wed, 02 July 2014 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe an OS scheduled job.
Have a look at userhost column.

Re: User Getting Locked [message #617677 is a reply to message #617674] Wed, 02 July 2014 12:01 Go to previous messageGo to next message
azeem87
Messages: 100
Registered: September 2005
Location: dallas
Senior Member
thanks michel for quick response..
i added userhost and got this "TSAC1W444" For all SYSTEM related rows. this os_username ='SYSTEM' is you mean coming from OS,
confusing to me was it's just a READ user and not sure why that box would connect..
so is this something/someone configured for testing i guess then ?

Any how thanks again for your response.
Re: User Getting Locked [message #617679 is a reply to message #617677] Wed, 02 July 2014 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So have a look at all scheduled job on TSAC1W444.
Often scheduled jobs are executed as SYSTEM because this is the default option.

Re: User Getting Locked [message #617699 is a reply to message #617679] Wed, 02 July 2014 14:25 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Is user knows 11g database is case sensitive ?

If case sensitive enabled .. What will happen if user try more than twice with wrong password ? Is profile also enabled ?
Re: User Getting Locked [message #617700 is a reply to message #617699] Wed, 02 July 2014 14:29 Go to previous message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Password is case sensitive by default in 11g. See parameter "sec_case_sensitive_logon".
About profile, join DBA_USERS and DBA_PROFILES to know the limits for your user.

Previous Topic: Secure Data over Database Link
Next Topic: How can I audit ALL activity for a specific Client Id?
Goto Forum:
  


Current Time: Mon Jun 17 14:33:30 CDT 2019