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.
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
Thanks Connor, that’s a good suggestion.
The less info revealed, the better. Totally agree.
there is an built-in user call “ANONYMOUS” is in the way.
Chen can you elaborate, I am not certain what you are trying to say with this remark about the anonymous account.
just notice that oracle treat user “ANONYMOUS” an impossible password, the same idea as yours.
it is great your idea.
Great minds think alike!
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:)