I am currently busy trying to lock down (secure wise) APEX, which is, to say the least, very hard… Anyway one of my tricks from the old days (the days you couldn’t lock an account / database schema), was to lock an user account via the following alternative use of the ALTER USER statement
ALTER USER {username} IDENTIFIED BY VALUES ' {String} ' |
of course nowadays you can use the more complete syntax
ALTER USER {username} IDENTIFIED BY VALUES ' {String} ' ACCOUNT LOCK |
Most of the time this method is used to reset the password to its original value.
I noticed in my (secured) Oracle 11g EE database version that passwords are not shown anymore via DBA_USERS
SQL> SHOW parameter sec_ sec_case_sensitive_logon BOOLEAN TRUE sec_max_failed_login_attempts INTEGER 10 sec_protocol_error_further_action string CONTINUE sec_protocol_error_trace_action string TRACE sec_return_server_release_banner BOOLEAN FALSE sql92_security BOOLEAN FALSE SQL> SHOW USER USER IS "SYSTEM" SQL> SELECT * FROM v$version; BANNER -------------------------------------------------------------------------------- Oracle DATABASE 11g Enterprise Edition Release 11.1.0.7.0 - Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS FOR Linux: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production SQL> ALTER USER sys IDENTIFIED BY locked account UNLOCK; USER altered. SQL> SELECT username, account_status, password FROM dba_users 2 WHERE username IN ('SYS','SYSTEM'); USERNAME ACCOUNT_STATUS PASSWORD ---------- --------------- ------------------------------ SYSTEM OPEN SYS OPEN |
Thats not an big issue, because passwords are stored in the base table SYS.USER$. So you can query that one as well… If the TYPE# has the value 1 then these are users, if TYPE# has the value 0, then it is a ROLE or “PUBLIC”. An other old trick (but now idea if it still works) was to update TYPE#=1 where NAME=’PUBLIC’. Via EXP you could now export all “PUBLIC” objects…
Anyway…
SQL> DESC SYS.USER$ Name NULL? TYPE ----------------------------------------- -------- ---------------------------- USER# NOT NULL NUMBER NAME NOT NULL VARCHAR2(30) TYPE# NOT NULL NUMBER PASSWORD VARCHAR2(30) DATATS# NOT NULL NUMBER TEMPTS# NOT NULL NUMBER CTIME NOT NULL DATE PTIME DATE EXPTIME DATE LTIME DATE RESOURCE$ NOT NULL NUMBER AUDIT$ VARCHAR2(38) DEFROLE NOT NULL NUMBER DEFGRP# NUMBER DEFGRP_SEQ# NUMBER ASTATUS NOT NULL NUMBER LCOUNT NOT NULL NUMBER DEFSCHCLASS VARCHAR2(30) EXT_USERNAME VARCHAR2(4000) SPARE1 NUMBER SPARE2 NUMBER SPARE3 NUMBER SPARE4 VARCHAR2(1000) SPARE5 VARCHAR2(1000) SPARE6 DATE SQL> SELECT name, password, astatus 2 FROM sys.USER$ 3 WHERE name IN ('SYS','SYSTEM') 4 AND TYPE#=1; NAME PASSWORD ASTATUS ---------- ------------------------------ ---------- SYS 477C2D3AE566D7F1 0 SYSTEM C8E32F716C57E38A 0 |
The ACCOUNT_STATUS from dba_users can be found via the SYS.USER_ASTATUS_MAP base table.
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_USERS" ("USERNAME", "USER_ID", "PASSWORD", "ACCOUNT_STATUS", "LOCK_DATE", "EXPIRY_DATE", "DEFAULT_TABLESPACE", "TEMPORARY_TABLESPACE", "CREATED", "PROFILE", "INITIAL_RSRC_CONSUMER_GROUP", "EXTERNAL_NAME", "PASSWORD_VERSIONS", "EDITIONS_ENABLED" ) AS SELECT u.name, u.USER#, decode(u.password, 'GLOBAL', u.password, 'EXTERNAL', u.password, NULL ), m.STATUS, decode(u.astatus, 4, u.ltime, 5, u.ltime, 6, u.ltime, 8, u.ltime, 9, u.ltime, 10, u.ltime, to_date(NULL) ), decode(u.astatus, 1, u.exptime, 2, u.exptime, 5, u.exptime, 6, u.exptime, 9, u.exptime, 10, u.exptime, decode(u.ptime, '', to_date(NULL), decode(pr.LIMIT#, 2147483647, to_date(NULL), decode(pr.LIMIT#, 0,decode(dp.LIMIT#, 2147483647, to_date(NULL), u.ptime +dp.LIMIT#/86400 ), u.ptime + pr.LIMIT#/86400 ) ) ) ), dts.name, tts.name, u.ctime, p.name, nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'), u.ext_username, decode(LENGTH(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL), decode(bitand(u.spare1, 16), 16, 'Y','N') FROM sys.USER$ u LEFT OUTER JOIN sys.resource_group_mapping$ cgm ON (cgm.attribute = 'ORACLE_USER' AND cgm.STATUS = 'ACTIVE' AND cgm.VALUE = u.name ), sys.ts$ dts, sys.ts$ tts, sys.profname$ p, sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp WHERE u.datats# = dts.ts# AND u.resource$ = p.profile# AND u.tempts# = tts.ts# AND u.astatus = m.STATUS# AND u.TYPE# = 1 AND u.resource$ = pr.profile# AND dp.profile# = 0 AND dp.TYPE#=1 AND dp.resource#=1 AND pr.TYPE# = 1 AND pr.resource# = 1 1 ROW selected. SQL> DESC SYS.USER_ASTATUS_MAP Name NULL? TYPE ----------------------------------------- -------- ---------------------------- STATUS# NOT NULL NUMBER STATUS NOT NULL VARCHAR2(32) SQL> SELECT * FROM SYS.USER_ASTATUS_MAP; STATUS# STATUS ---------- -------------------------------- 0 OPEN 1 EXPIRED 2 EXPIRED(GRACE) 4 LOCKED(TIMED) 8 LOCKED 5 EXPIRED & LOCKED(TIMED) 6 EXPIRED(GRACE) & LOCKED(TIMED) 9 EXPIRED & LOCKED 10 EXPIRED(GRACE) & LOCKED 9 ROWS selected. |
Pete Finnigan (who else) has a good post and the principles behind it about USER_ASTATUS_MAP called: SYS.USER_ASTATUS_MAP missing values solved
As Pete is also discussing in his post “is it possible to lock out SYS using FAILED_LOGIN_ATTEMPTS in a profile“, it IS possible to lock the SYS account, but it hasn’t a big effect if you have an password file.
By the way I do not have an sqlnet.ora file with for example values for the parameter SQLNET.AUTHENTICATION_SERVICES set.
As Pete has demonstrated in his post the following is still possible if the SYS account is locked:
SQL> SHOW parameter pass NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE SQL> ALTER USER SYS IDENTIFIED BY locked account LOCK; USER altered. SQL> -- I have an password file... SQL> -- From the LOCAL machine (Linux as the Oracle software owner - ORACLE_SID has been set) SQL> conn sys/locked AS sysdba Connected. SQL> SELECT username, account_status 2 FROM dba_users 3 WHERE username IN ('SYS','SYSTEM') 4 ; USERNAME ACCOUNT_STATUS ---------- -------------------------------- SYSTEM OPEN SYS LOCKED 2 ROWS selected. SQL> -- From a REMOTE machine via a SQL*Plus client SQL> conn sys/locked@stress11g AS sysdba Connected. SQL> SELECT username, account_status 2 FROM dba_users 3 WHERE username IN ('SYS','SYSTEM') 4 ; USERNAME ACCOUNT_STATUS ---------- -------------------------------- SYSTEM OPEN SYS LOCKED 2 ROWS selected. |
The only way to block this “remote” behavior is to set an “impossible password”.
SQL> SELECT name, astatus, password 2 FROM sys.USER$ 3 WHERE name IN ('SYS','SYSTEM') 4 AND TYPE#=1 5 ; NAME ASTATUS PASSWORD ---------- ---------- ------------------------------ SYS 8 477C2D3AE566D7F1 SYSTEM 0 C8E32F716C57E38A 2 ROWS selected. SQL> ALTER USER sys IDENTIFIED BY VALUES '-- LOCKED --' account LOCK; USER altered. SQL> SELECT name, astatus, password 2 FROM sys.USER$ 3 WHERE name IN ('SYS','SYSTEM') 4 AND TYPE#=1 5 ; NAME ASTATUS PASSWORD ---------- ---------- ------------------------------ SYS 8 -- LOCKED -- SYSTEM 0 C8E32F716C57E38A 2 ROWS selected. SQL> -- My REMOTE client SQL*Plus 10.2.0.1.0 version on Windows now gives... SQL> conn sys/locked@stress11g AS sysdba ERROR: ORA-00600: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s] Warning: You are no longer connected TO ORACLE. |
The database on the server didn’t crash. But this is a good example that you should be careful with unsupported statements. To be honest this is also the first time I see an ORA-00600 error while applying this “method”. I will have to check if this is also happening on “unsecured” older versions like Oracle 10gRx.
For normal users the following happens
SQL> CREATE USER LOCKED IDENTIFIED BY LOCKED account UNLOCK; USER created. SQL> GRANT dba TO LOCKED; GRANT succeeded. SQL> conn LOCKED/LOCKED Connected. SQL> ALTER USER LOCKED IDENTIFIED BY VALUES 'LOCKED' account UNLOCK; USER altered. SQL> SELECT name, astatus, password 2 FROM sys.USER$ 3 WHERE name IN ('SYS','SYSTEM','LOCKED') 4 AND TYPE#=1; NAME ASTATUS PASSWORD ---------- ---------- ------------------------------ SYS 8 -- LOCKED -- SYSTEM 0 C8E32F716C57E38A LOCKED 0 LOCKED 3 ROWS selected. SQL> conn LOCKED/LOCKED ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected TO ORACLE. SQL> conn LOCKED/"LOCKED" ERROR: ORA-01017: invalid username/password; logon denied SQL> |
So there is no way in anymore. Locked or not…
Luckily no ORA-00600 error here.
Is this information useful? Not really, anymore at least… In the old days (Oracle 7) this was the only way to lock an account. Nowadays there are a lot off methods you can apply (LOCK, EXPIRE etc). Removing the password file so remote login isn’t allowed anymore is one off those methods.
For me this was a nice exercise on a TEST environment (a virtual machine I always can reset to its snapshot) to actually see what the consequences are / could be too lock the SYS account and what still works and doesn’t work in such an Oracle database environment…
HTH
PS
I noticed that when the database parameter remote_login_passwordfile is set and has the value EXCLUSIVE, that you are still allowed to login REMOTELY with “the old password” (the one used before setting the “impossible” one). To avoid this behavior you can set the database parameter remote_login_passwordfile to NONE, but you could ask yourself if setting an “impossible” password still makes sense.
7 comments
1 ping
Skip to comment form ↓
Connor
13 November, 2008 at 14:23 (UTC 1) Link to this comment
I actually prefer setting the password to impossible values and *not* locking the account.
If you lock an account, then the error “Account is locked” reveals information to a hacker (ie, that the account exists). If the account is open with an impossible password, then that information is not revealed.
Cheers
Connor
Marco Gralike
13 November, 2008 at 14:29 (UTC 1) Link to this comment
Thanks Connor, that’s a good suggestion.
The less info revealed, the better. Totally agree.
chen ruiqing
26 November, 2008 at 5:19 (UTC 1) Link to this comment
there is an built-in user call “ANONYMOUS” is in the way.
Marco Gralike
26 November, 2008 at 14:36 (UTC 1) Link to this comment
Chen can you elaborate, I am not certain what you are trying to say with this remark about the anonymous account.
chen ruiqing
27 November, 2008 at 1:31 (UTC 1) Link to this comment
just notice that oracle treat user “ANONYMOUS” an impossible password, the same idea as yours.
it is great your idea.
chen ruiqing
27 November, 2008 at 8:09 (UTC 1) Link to this comment
Great minds think alike!
coskan
7 March, 2009 at 2:38 (UTC 1) Link to this comment
this is very coo. thanks for sharing with us. it was a nice reading after 5 pints on my way back home on a typical london night:)
Log Buffer #124: a Carnival of the Vanities for DBAs
21 November, 2008 at 18:31 (UTC 1) Link to this comment
[...] Gralike discusses the value of setting an “impossible” password for SYS — even when you can use [...]