If we wanted to see the LCOUNT column we need access granting on SYS.USER$. This is not good for security so is there another option?
Let us look for any tables/views that have a column called LCOUNT:
SQL> col owner for a30
SQL> col table_name for a30
SQL> select owner,table_name from all_tab_columns where column_name='LCOUNT';
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS USER$
SYS CDB_LOCAL_ADMINAUTH$
SYS _BASE_USER
SYS KU$_USER_BASE_VIEW
SYS KU$_ROLE_VIEW
SYS KU$_USER_VIEW
6 rows selected.
SQL>
That is a good start, so check the number of records in SYS.USER$ and compare to DBA_USERS (which does not have LCOUNT) and KU$_USER_BASE_VIEW and KU$_USER_VIEW
SQL> select count(*) from sys.user$ where type#=1;
COUNT(*)
----------
84
SQL> select count(*) from dba_users;
COUNT(*)
----------
84
SQL> select count(*) from ku$_user_view
2 /
COUNT(*)
----------
84
SQL> select count(*) from ku$_user_base_view;
COUNT(*)
----------
84
SQL>
We are running the queries as SYS at the moment so now check the permissions on these two views:
SQL> @get_tab2
get_tab2: Release 1.2.0.0.0 - Production on Thu Jan 15 12:33:58 2026
Copyright (c) 2007, 2017, PeteFinnigan.com Limited. All rights reserved.
OBJECT TO CHECK [XXX_XXXX]: KU$_USER_BASE_VIEW
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SYS
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Testing root object => [SYS.KU$_USER_BASE_VIEW]
GRANTOR GRANTEE R S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - - -
SYS PUBLIC X
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
And for the second view:
SQL> @get_tab2
get_tab2: Release 1.2.0.0.0 - Production on Thu Jan 15 12:35:28 2026
Copyright (c) 2007, 2017, PeteFinnigan.com Limited. All rights reserved.
OBJECT TO CHECK [XXX_XXXX]: KU$_USER_VIEW
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SYS
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Testing root object => [SYS.KU$_USER_VIEW]
GRANTOR GRANTEE R S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - - -
SYS PUBLIC X
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
This is interesting as the grants are to PUBLIC. This begs a different question now as an aside. Can we need the password hashes:
SQL> col name for a30
SQL> col password for a16
SQL> col spare4 for a120
SQL> set lines 220
SQL> select name,password,spare4 from ku$_user_view;
NAME PASSWORD SPARE4
------------------------------ ---------------- ------------------------------------------------------------------------------------------------------------------------
SYSTEM S:
OUTLN S:0000000000000000000000000000000000000000D11A6AC241D4F7CFC3B6
REMOTE_SCHEDULER_AGENT S:00000000000000000000000000000000000000001D09981D4FDD38DEB86B
GSMUSER S:00000000000000000000000000000000000000009F8A4D11D3CCFE7CB5DE
XS$NULL S:000000000000000000000000000000000000000042D39B5FA5D66485880E
DBSFWUSER S:00000000000000000000000000000000000000009A44AAF1137C8D8AD129
CTXSYS S:00000000000000000000000000000000000000008B00F4E166C9EFAE28DC
MDSYS S:0000000000000000000000000000000000000000E7D1325B0FECC8E203CF
PDBADMIN S:A4CCEA8E7EF3589B56B385B535FD96D39CF7A5F6BE352092C1EA62264FDF
PFCL_VP S:73C7D0167DA1E686F2412242A4CE484B47681D816D1A5C2A36AD4077ECBC
XXB S:A89F5F66F8CFCB267A5316CD6C88549B57565873211B39177BCA975E15E0
PETE S:ABA5A667E5C5FFF9C8FE7CCF96868ED830F2F38ED5CABC4E75250F3CD33E
JIM S:E8943EFA450352DAB64B09F2452D5C5AB8DC3A9B4C591FFE68EE90ED3FB0
DBAUSER S:39E9EF7946470B24EA6BBDA47580936910C67048F3E4D507B84249F18773
SYSKM S:00000000000000000000000000000000000000000EA1996D74EC80ACE7F8
ORACLE_OCM S:0000000000000000000000000000000000000000F3C01AEFD271DABF60A0
GSMCATUSER S:0000000000000000000000000000000000000000B649F4FE4E3F812661D9
WMSYS S:0000000000000000000000000000000000000000F84DA26B2E5F8E1D2DF7
ORDDATA S:0000000000000000000000000000000000000000B7C6BD29B53F0FED1ED6
XXA S:F8ADA69B263CB87FEAFA140DADFE851CA6E66A59443D21221BEFFC58ED96
ZULIA S:4928866353FFD62D192CA72C8FF24E66E3FD34D3F9C7780FF0BDF045E929
SCH S:681655B0237C8C9A5FBEADD9D257B8CF1126A10578FA1809D8777A30442B
NAME PASSWORD SPARE4
------------------------------ ---------------- ------------------------------------------------------------------------------------------------------------------------
VB S:CA8737CA146654C8755742B337628B17E2F6532F03401195BF157339B47F
GSMADMIN_INTERNAL S:00000000000000000000000000000000000000007C3D4016DD3435141295
DGPDB_INT S:000000000000000000000000000000000000000018BB18EADE4831E80232
GGSYS S:00000000000000000000000000000000000000005E974FA0B19E359CEA7A
FRED S:962C3E061F477A2DD165FB396ABFB7D0D191CAFF6E3F3CDC68081AF9C37E
FACADM S:CD33F5E6F6A55CF5538712CDEF7A6A2B07F0D9C63BA49B53290A3F4B5F07
USER05 S:AF56B70FC6AF76360C301FF539E5C0BEB35D6C5846BE3DAE8B72746B9719
DEV01 S:05146DD05E20721685208DD3F143BE5D2ECD7601F45A19E28D687732A377
SYS$UMF S:0000000000000000000000000000000000000000EF1858821FEE3F68ED0F
MDDATA S:00000000000000000000000000000000000000005B2E11407844F99B0871
AA S:6188B9E4CC3346FD7887F405A9D50C8D787B25332A2AD5A4C0C8683C5307
BB S:5971488B2CCE4964D5BB26CFD3FE34D478989349974D071FEAD099FC80E2
U1 S:9AF674ABD5C00D3330ABD21296F2779D08E5108400E297042BB419E08283
ERIC S:C78CE4B6472B7848FAAF30741AC67F6812468243644B14FDB2CB1FA747F3
EMIL S:4381A307437DDB1A7A8974701C54F62C916FCCC4FC33F10BBACF8F38C78F
USE S:89367794B3310F81DBB57A2DA00CA5CF9CF1F643439B262654E564AFC174
SESS S:2E063BEB2C615ED5CC0A3185296D15DBCE5A6358EDF7371D871425C41737
VA S:3C3245A3B0FD3F2342164F13FB3592CF3681ED42356C071B996D09CBE0A2
USER04 S:54247FDE180E68279421DB2074289CC281D11AFB3288A429350D4A01F740
USER06 S:4FE76C82838000247CAAADB6D9BEE0D2D4FBC45AC1DC1657ADFBDC3D913C
FEED01 S:097372F2A8ABAF6493CC0F0CC4528816F47331FB746525B89061BC7A9EEE
PWDP S:AD4AFA17183FC19F931333AE0A98C2E79B167AAFA42FC43310ADA2180168
NAME PASSWORD SPARE4
------------------------------ ---------------- ------------------------------------------------------------------------------------------------------------------------
SYS S:
SYSBACKUP S:0000000000000000000000000000000000000000822A6CE4A80F7B0978E1
OLAPSYS S:000000000000000000000000000000000000000089B3C083F870BD76A9E4
PFCL_VD S:92573DA869DC6183B5FD0B2F8D31E99FEE265D7B7D3538AB67283C12106F
USER03 S:1764DDE8CBEC148DBE6447F881631513B464C09EA4BF7B33140ADDFA52EC
ORABLOGDBA S:4B569C18913FE09404F6C0555C4703DB8412AA38324953CA51F7CA54A306
CCKEY S:958B24BD9389D0ED3934C220465EBFE6DC85FE63E08FD2DE29A95315ACC1
DEV S:5A3713BFEC916345DAE63A3F729078BB15F7DC877575F2FC1B43589A2DF1
SYSDG S:0000000000000000000000000000000000000000B5AA13EB6DA27560CE1E
DIP S:0000000000000000000000000000000000000000C0B42B195F69DFD8E515
DBSNMP S:00000000000000000000000000000000000000003379D2BE24A2FF7287EF
ANONYMOUS S:0000000000000000000000000000000000000000B07826CCABAFED19D301
UU S:9EFBE756E674BA970DA2CED0ED38B7C15622DAB3FC28C9544C0F90D14AED
DEV2 S17C092C6CD893FA8F06CBCAC534BEE266EC1BBFC1D27B7774FF6DF5403A
USER02 S:CDF34EDB2BB92872198F405666284809621E3D51B82618DA64E080641D3D
BACK01 S:E6540CC4B9BD419A3189999EE79DAC95D3B86C89C0805BD0454AB8B9C2F9
DEV02 S:AE12AB7EB8BFFF00A3A39DFC93494ED374851D9D032B8E61CF2C4C8A2CA6
RISK01 S:B0248C5D82077E5B1ABC544AF5A87C9481A0A3C593B00A830C3FC255E934
SCOTT S:14A9FCE175D27A2E8FF590D5314942D295AF2E31611FCBE515FC0FA4AD6E
AUDSYS S:00000000000000000000000000000000000000002FE4A5DA0B3AE2221489
SYSRAC S:0000000000000000000000000000000000000000898108D6B00CCB258AC5
APPQOSSYS S:0000000000000000000000000000000000000000346EE8658D859CABA0E1
NAME PASSWORD SPARE4
------------------------------ ---------------- ------------------------------------------------------------------------------------------------------------------------
OJVMSYS S:0000000000000000000000000000000000000000050D794F5D3FCB6668D6
ORDSYS S:0000000000000000000000000000000000000000A0E7FA3D1D46524FB2B2
ORDPLUGINS S:000000000000000000000000000000000000000024A2A0428995834FB40C
SI_INFORMTN_SCHEMA S:0000000000000000000000000000000000000000024B1CB21161D61D60DC
DVF S:000000000000000000000000000000000000000061CEDFB1E28C8DB0B2C2
IMPORTER S:009D62F2770583F0414F4E71637D623D7A17C7A8EF3F94D6380C0F5E0B55
USER01 S:F51AFDC692F8DEFC793D2DF122995F9F925E948FE94C835C2789804A4945
ORABLOG S:27617D33B3B3AF2A97757B63397836D6735ED465BDBDFC1580E14B2663A0
BATCH01 S:A6B8EE14FDE3325B3BD1B06A4551C94A27AA17C5E45E09CCE1F2CEE1405D
DEV03 S:195AAEA4EE69C3A1614805773EB772E739A8A842BCA620C4238CB776B10A
XDB S:00000000000000000000000000000000000000004DA2D184F7A957867AF0
DVSYS S:0000000000000000000000000000000000000000DA00FD1499E4ACA3312E
LBACSYS S:0000000000000000000000000000000000000000DA51238F8B0AFEF9B10F
ORASCAN S:CFF01E4626C03C32B10C6F3F40D6C6591A68A09707DDD3474264E22A44AA
TESTTEST S:540223EA357E210DCC65CB96FA60F07BF811E64E27F6A6A378BFD12902A5
BILL S:A50DA85E1679A732CE6A8867E830E26FC7779405253A541E3D1B61D942EF
VU S:27106BFE81B06076A72EDFE42ED46C916475EB1FAF144FECC79C63D4E590
USER07 S:56B1451EC1F70155BFD8ADEC9345B5EBA49008123F6BB5A0F23A8AC92980
84 rows selected.
SQL>
Yes we can see them and what about the base view?
SQL> col name for a30
SQL> col password for a16
SQL> col spare4 for a120
SQL> set lines 220
SQL> select name,password,spare4 from ku$_user_base_view;
NAME PASSWORD SPARE4
------------------------------ ---------------- ------------------------------------------------------------------------------------------------------------------------
SYSTEM S:
OUTLN S:0000000000000000000000000000000000000000D11A6AC241D4F7CFC3B6
REMOTE_SCHEDULER_AGENT S:00000000000000000000000000000000000000001D09981D4FDD38DEB86B
GSMUSER S:00000000000000000000000000000000000000009F8A4D11D3CCFE7CB5DE
XS$NULL S:000000000000000000000000000000000000000042D39B5FA5D66485880E
DBSFWUSER S:00000000000000000000000000000000000000009A44AAF1137C8D8AD129
CTXSYS S:00000000000000000000000000000000000000008B00F4E166C9EFAE28DC
MDSYS S:0000000000000000000000000000000000000000E7D1325B0FECC8E203CF
PDBADMIN S:A4CCEA8E7EF3589B56B385B535FD96D39CF7A5F6BE352092C1EA62264FDF
PFCL_VP S:73C7D0167DA1E686F2412242A4CE484B47681D816D1A5C2A36AD4077ECBC
XXB S:A89F5F66F8CFCB267A5316CD6C88549B57565873211B39177BCA975E15E0
PETE S:ABA5A667E5C5FFF9C8FE7CCF96868ED830F2F38ED5CABC4E75250F3CD33E
JIM S:E8943EFA450352DAB64B09F2452D5C5AB8DC3A9B4C591FFE68EE90ED3FB0
DBAUSER S:39E9EF7946470B24EA6BBDA47580936910C67048F3E4D507B84249F18773
SYSKM S:00000000000000000000000000000000000000000EA1996D74EC80ACE7F8
ORACLE_OCM S:0000000000000000000000000000000000000000F3C01AEFD271DABF60A0
GSMCATUSER S:0000000000000000000000000000000000000000B649F4FE4E3F812661D9
WMSYS S:0000000000000000000000000000000000000000F84DA26B2E5F8E1D2DF7
ORDDATA S:0000000000000000000000000000000000000000B7C6BD29B53F0FED1ED6
XXA S:F8ADA69B263CB87FEAFA140DADFE851CA6E66A59443D21221BEFFC58ED96
ZULIA S:4928866353FFD62D192CA72C8FF24E66E3FD34D3F9C7780FF0BDF045E929
SCH S:681655B0237C8C9A5FBEADD9D257B8CF1126A10578FA1809D8777A30442B
NAME PASSWORD SPARE4
------------------------------ ---------------- ------------------------------------------------------------------------------------------------------------------------
VB S:CA8737CA146654C8755742B337628B17E2F6532F03401195BF157339B47F
GSMADMIN_INTERNAL S:00000000000000000000000000000000000000007C3D4016DD3435141295
DGPDB_INT S:000000000000000000000000000000000000000018BB18EADE4831E80232
GGSYS S:00000000000000000000000000000000000000005E974FA0B19E359CEA7A
FRED S:962C3E061F477A2DD165FB396ABFB7D0D191CAFF6E3F3CDC68081AF9C37E
FACADM S:CD33F5E6F6A55CF5538712CDEF7A6A2B07F0D9C63BA49B53290A3F4B5F07
USER05 S:AF56B70FC6AF76360C301FF539E5C0BEB35D6C5846BE3DAE8B72746B9719
DEV01 S:05146DD05E20721685208DD3F143BE5D2ECD7601F45A19E28D687732A377
SYS$UMF S:0000000000000000000000000000000000000000EF1858821FEE3F68ED0F
MDDATA S:00000000000000000000000000000000000000005B2E11407844F99B0871
AA S:6188B9E4CC3346FD7887F405A9D50C8D787B25332A2AD5A4C0C8683C5307
BB S:5971488B2CCE4964D5BB26CFD3FE34D478989349974D071FEAD099FC80E2
U1 S:9AF674ABD5C00D3330ABD21296F2779D08E5108400E297042BB419E08283
ERIC S:C78CE4B6472B7848FAAF30741AC67F6812468243644B14FDB2CB1FA747F3
EMIL S:4381A307437DDB1A7A8974701C54F62C916FCCC4FC33F10BBACF8F38C78F
USE S:89367794B3310F81DBB57A2DA00CA5CF9CF1F643439B262654E564AFC174
SESS S:2E063BEB2C615ED5CC0A3185296D15DBCE5A6358EDF7371D871425C41737
VA S:3C3245A3B0FD3F2342164F13FB3592CF3681ED42356C071B996D09CBE0A2
USER04 S:54247FDE180E68279421DB2074289CC281D11AFB3288A429350D4A01F740
USER06 S:4FE76C82838000247CAAADB6D9BEE0D2D4FBC45AC1DC1657ADFBDC3D913C
FEED01 S:097372F2A8ABAF6493CC0F0CC4528816F47331FB746525B89061BC7A9EEE
PWDP S:AD4AFA17183FC19F931333AE0A98C2E79B167AAFA42FC43310ADA2180168
NAME PASSWORD SPARE4
------------------------------ ---------------- ------------------------------------------------------------------------------------------------------------------------
SYS S:
SYSBACKUP S:0000000000000000000000000000000000000000822A6CE4A80F7B0978E1
OLAPSYS S:000000000000000000000000000000000000000089B3C083F870BD76A9E4
PFCL_VD S:92573DA869DC6183B5FD0B2F8D31E99FEE265D7B7D3538AB67283C12106F
USER03 S:1764DDE8CBEC148DBE6447F881631513B464C09EA4BF7B33140ADDFA52EC
ORABLOGDBA S:4B569C18913FE09404F6C0555C4703DB8412AA38324953CA51F7CA54A306
CCKEY S:958B24BD9389D0ED3934C220465EBFE6DC85FE63E08FD2DE29A95315ACC1
DEV S:5A3713BFEC916345DAE63A3F729078BB15F7DC877575F2FC1B43589A2DF1
SYSDG S:0000000000000000000000000000000000000000B5AA13EB6DA27560CE1E
DIP S:0000000000000000000000000000000000000000C0B42B195F69DFD8E515
DBSNMP S:00000000000000000000000000000000000000003379D2BE24A2FF7287EF
ANONYMOUS S:0000000000000000000000000000000000000000B07826CCABAFED19D301
UU S:9EFBE756E674BA970DA2CED0ED38B7C15622DAB3FC28C9544C0F90D14AED
DEV2 S
USER02 S:CDF34EDB2BB92872198F405666284809621E3D51B82618DA64E080641D3D
BACK01 S:E6540CC4B9BD419A3189999EE79DAC95D3B86C89C0805BD0454AB8B9C2F9
DEV02 S:AE12AB7EB8BFFF00A3A39DFC93494ED374851D9D032B8E61CF2C4C8A2CA6
RISK01 S:B0248C5D82077E5B1ABC544AF5A87C9481A0A3C593B00A830C3FC255E934
SCOTT S:14A9FCE175D27A2E8FF590D5314942D295AF2E31611FCBE515FC0FA4AD6E
AUDSYS S:00000000000000000000000000000000000000002FE4A5DA0B3AE2221489
SYSRAC S:0000000000000000000000000000000000000000898108D6B00CCB258AC5
APPQOSSYS S:0000000000000000000000000000000000000000346EE8658D859CABA0E1
NAME PASSWORD SPARE4
------------------------------ ---------------- ------------------------------------------------------------------------------------------------------------------------
OJVMSYS S:0000000000000000000000000000000000000000050D794F5D3FCB6668D6
ORDSYS S:0000000000000000000000000000000000000000A0E7FA3D1D46524FB2B2
ORDPLUGINS S:000000000000000000000000000000000000000024A2A0428995834FB40C
SI_INFORMTN_SCHEMA S:0000000000000000000000000000000000000000024B1CB21161D61D60DC
DVF S:000000000000000000000000000000000000000061CEDFB1E28C8DB0B2C2
IMPORTER S:009D62F2770583F0414F4E71637D623D7A17C7A8EF3F94D6380C0F5E0B55
USER01 S:F51AFDC692F8DEFC793D2DF122995F9F925E948FE94C835C2789804A4945
ORABLOG S:27617D33B3B3AF2A97757B63397836D6735ED465BDBDFC1580E14B2663A0
BATCH01 S:A6B8EE14FDE3325B3BD1B06A4551C94A27AA17C5E45E09CCE1F2CEE1405D
DEV03 S:195AAEA4EE69C3A1614805773EB772E739A8A842BCA620C4238CB776B10A
XDB S:00000000000000000000000000000000000000004DA2D184F7A957867AF0
DVSYS S:0000000000000000000000000000000000000000DA00FD1499E4ACA3312E
LBACSYS S:0000000000000000000000000000000000000000DA51238F8B0AFEF9B10F
ORASCAN S:CFF01E4626C03C32B10C6F3F40D6C6591A68A09707DDD3474264E22A44AA
TESTTEST S:540223EA357E210DCC65CB96FA60F07BF811E64E27F6A6A378BFD12902A5
BILL S:A50DA85E1679A732CE6A8867E830E26FC7779405253A541E3D1B61D942EF
VU S:27106BFE81B06076A72EDFE42ED46C916475EB1FAF144FECC79C63D4E590
USER07 S:56B1451EC1F70155BFD8ADEC9345B5EBA49008123F6BB5A0F23A8AC92980
84 rows selected.
SQL>
Same. Hmmm BUT we are connected as SYS so let us dig deeper
First create a sample user that can potentially access these two PUBLIC views:
SQL> create user lcount identified by lcount;
User created.
SQL> grant create session to lcount;
Grant succeeded.
SQL>
Connect as the new sample user and test:
SQL> connect lcount/lcount@//192.168.56.33:1539/xepdb1
Connected.
SQL> col name for a30
SQL> col password for a30
SQL> col lcount for 9999
SQL> col spare4 for a120
SQL> set lines 220
SQL> select name,password,lcount,spare4 from sys.ku$_user_view;
no rows selected
SQL>
No rows returned, what about the base view:
SQL> col name for a30
SQL> col password for a30
SQL> col lcount for 9999
SQL> col spare4 for a120
SQL> set lines 220
SQL> select name,password,lcount,spare4 from sys.ku$_user_base_view;
no rows selected
SQL>
Hmmm, why not? let us have a look at the view text:
SQL> select text from dba_views where view_name='KU$_USER_VIEW';
TEXT
--------------------------------------------------------------------------------
select ubv.*,
cast(multiset(select * from ku$_user_editioning_view uev
where uev.user_id = ubv.user_id)
as ku$_user_editioning_list_t)
from sys.ku$_user_base_view ubv
SQL>
The view is defined in the root container so we need to look there for the full text
SQL> select text from dba_views where view_name='KU$_USER_BASE_VIEW';
TEXT
--------------------------------------------------------------------------------
select '2','0',
u.user#,
u.name,
u.type#,
case
when dbms_metadata.get_version <'12.02.00.00.00' and
bitand(u.spare1, 65536) = 65536 then
'S:000000000000000000000000000000000000000000000000000000000000'
else
u.password
end,
TEXT
--------------------------------------------------------------------------------
ts1.name,
ts2.name,
/* Bug 28555193: Upgrade from 12.1 to 19.1 makes the value of spare9
to either zero or NULL which is incorrect. With value zero, local
temp tablespace of user is fetched as SYSTEM and causes error
ora-12911 during import. This new condition restricts fetching
SYSTEM tablespace as local temp tablespace in upgraded DB */
(select ts3.name from ts$ ts3 where u.spare9 = ts3.ts# and
u.spare9 !=0 and
u.spare9 is not null),
TEXT
--------------------------------------------------------------------------------
to_char(u.ctime,'YYYY/MM/DD HH24:MI:SS'),
to_char(u.ptime,'YYYY/MM/DD HH24:MI:SS'),
to_char(u.exptime,'YYYY/MM/DD HH24:MI:SS'),
to_char(u.ltime,'YYYY/MM/DD HH24:MI:SS'),
u.resource$,
p.name,
replace(u.audit$,chr(0),'-'),
u.defrole,
u.defgrp#,
u.defgrp_seq#,
DECODE(NVL(instr(u.spare4, ';H:'),0), 0, u.astatus,
TEXT
--------------------------------------------------------------------------------
u.astatus - BITAND(u.astatus, 9) + 9),
u.astatus,
u.lcount,
NVL((select cgm.consumer_group
from sys.resource_group_mapping$ cgm
where cgm.attribute = 'ORACLE_USER'
and cgm.status = 'ACTIVE'
and cgm.value = u.name), u.defschclass),
u.ext_username,
u.spare1,
u.spare2,
TEXT
--------------------------------------------------------------------------------
nls_collation_name(nvl(u.spare3, 16382)),
NVL(NVL(SUBSTR(u.spare4, 1, instr(u.spare4, ';H:') - 1),
SUBSTR(u.spare4, 1, instr(u.spare4, ';T:') - 1)),
u.spare4),
u.spare4,
u.spare5,
to_char(u.spare6,'YYYY/MM/DD HH24:MI:SS')
from sys.user$ u,
sys.ts$ ts1, sys.ts$ ts2, sys.profname$ p
where u.datats# = ts1.ts# AND
u.tempts# = ts2.ts# AND
TEXT
--------------------------------------------------------------------------------
u.type# = 1 AND
u.resource$ = p.profile#
AND (SYS_CONTEXT('USERENV','CURRENT_USERID') = 0
OR EXISTS ( SELECT * FROM sys.session_roles
WHERE role='EXP_FULL_DATABASE' OR
role='DATAPUMP_CLOUD_EXP' ))
UNION
select '2','0',
u.user#,
u.name,
u.type#,
TEXT
--------------------------------------------------------------------------------
NULL,
ts1.name,
ts2.name,
/* Bug 28555193: Upgrade from 12.1 to 19.1 makes the value of spare9
to either zero or NULL which is incorrect. With value zero, local
temp tablespace of user is fetched as SYSTEM and causes error
ora-12911 during import. This new condition restricts fetching
SYSTEM tablespace as local temp tablespace in upgraded DB*/
(select ts3.name from ts$ ts3 where u.spare9 = ts3.ts# and
u.spare9 !=0 and
TEXT
--------------------------------------------------------------------------------
u.spare9 is not null),
to_char(u.ctime,'YYYY/MM/DD HH24:MI:SS'),
to_char(u.ptime,'YYYY/MM/DD HH24:MI:SS'),
to_char(u.exptime,'YYYY/MM/DD HH24:MI:SS'),
to_char(u.ltime,'YYYY/MM/DD HH24:MI:SS'),
u.resource$,
p.name,
replace(u.audit$,chr(0),'-'),
u.defrole,
u.defgrp#,
u.defgrp_seq#,
TEXT
--------------------------------------------------------------------------------
u.astatus,
u.astatus,
u.lcount,
NVL((select cgm.consumer_group
from sys.resource_group_mapping$ cgm
where cgm.attribute = 'ORACLE_USER'
and cgm.status = 'ACTIVE'
and cgm.value = u.name), u.defschclass),
u.ext_username,
u.spare1,
u.spare2,
TEXT
--------------------------------------------------------------------------------
nls_collation_name(nvl(u.spare3, 16382)),
NULL, NULL,
u.spare5,
to_char(u.spare6,'YYYY/MM/DD HH24:MI:SS')
from sys.user$ u,
sys.ts$ ts1, sys.ts$ ts2, sys.profname$ p
where u.datats# = ts1.ts# AND
u.tempts# = ts2.ts# AND
u.type# = 1 AND
u.resource$ = p.profile#
AND (SYS_CONTEXT('USERENV','CURRENT_USERID') != 0 )
TEXT
--------------------------------------------------------------------------------
AND NOT (EXISTS ( SELECT * FROM sys.session_roles
WHERE role='EXP_FULL_DATABASE' OR
role='DATAPUMP_CLOUD_EXP' ))
AND (EXISTS ( SELECT * FROM sys.session_roles
WHERE role = 'SELECT_CATALOG_ROLE'))
SQL>
So we can see from the where clause of the last part of the union that if we have the SELECT_CATALOG_ROLE role then we should be able to see the records in the views including the LCOUNT column. Grant SELECT_CATALOG_ROLE to our user LCOUNT and lets see:
SQL> grant select_catalog_role to lcount;
Grant succeeded.
SQL>
Now try again:
SQL> connect lcount/lcount@//192.168.56.33:1539/xepdb1
Connected.
SQL> col name for a30
SQL> col password for a30
SQL> col lcount for 9999
SQL> col spare4 for a120
SQL> set lines 220
SQL> select name,password,lcount,spare4 from sys.ku$_user_view;
NAME PASSWORD LCOUNT SPARE4
------------------------------ ------------------------------ ------ ------------------------------------------------------------------------------------------------------------------------
DGPDB_INT 0
OLAPSYS 0
XXB 0
U1 0
ORABLOGDBA 0
ERIC 0
BILL 0
USER02 0
VA 0
USER05 0
SYSKM 0
NAME PASSWORD LCOUNT SPARE4
------------------------------ ------------------------------ ------ ------------------------------------------------------------------------------------------------------------------------
OUTLN 0
DBSFWUSER 0
APPQOSSYS 0
WMSYS 0
SI_INFORMTN_SCHEMA 0
LBACSYS 0
BB 0
...
Now we can see the lcount column BUT not the passwords. BUT it means that we can read LCOUNT without direct access to SYS.USER$ by granting SELECT_CATALOG_ROLE
The other view:
SQL> select name,password,lcount,spare4 from sys.ku$_user_base_view;
NAME PASSWORD LCOUNT SPARE4
------------------------------ ------------------------------ ------ ------------------------------------------------------------------------------------------------------------------------
DGPDB_INT 0
OLAPSYS 0
XXB 0
U1 0
ORABLOGDBA 0
ERIC 0
...
The spare4 column which should hold the password hashes is NULL but if we have granted the EXP_FULL_DATABASE role then we should go through the first part of the union in the view and actually see the hashes. Let us test that
SQL> grant exp_full_database to lcount;
Grant succeeded.
SQL>
Connect as LCOUNT and check again:
SQL> connect lcount/lcount@//192.168.56.33:1539/xepdb1
Connected.
SQL> col name for a30
SQL> col password for a30
SQL> col lcount for 9999
SQL> col spare4 for a120
SQL> set lines 220
SQL> select name,password,lcount,spare4 from sys.ku$_user_view;
NAME PASSWORD LCOUNT SPARE4
------------------------------ ------------------------------ ------ ------------------------------------------------------------------------------------------------------------------------
SYSTEM 0 S:
OUTLN 0 S:0000000000000000000000000000000000000000D11A6AC241D4F7CFC3B6
REMOTE_SCHEDULER_AGENT 0 S:00000000000000000000000000000000000000001D09981D4FDD38DEB86B
GSMUSER 0 S:00000000000000000000000000000000000000009F8A4D11D3CCFE7CB5DE
XS$NULL 0 S:000000000000000000000000000000000000000042D39B5FA5D66485880E
DBSFWUSER 0 S:00000000000000000000000000000000000000009A44AAF1137C8D8AD129
CTXSYS 0 S:00000000000000000000000000000000000000008B00F4E166C9EFAE28DC
MDSYS 0 S:0000000000000000000000000000000000000000E7D1325B0FECC8E203CF
PDBADMIN 0 S:A4CCEA8E7EF3589B56B385B535FD96D39CF7A5F6BE352092C1EA62264FDF
PFCL_VP 0 S:73C7D0167DA1E686F2412242A4CE484B47681D816D1A5C2A36AD4077ECBC
XXB 0 S:A89F5F66F8CFCB267A5316CD6C88549B57565873211B39177BCA975E15E0
...
And the other view:
SQL> select name,password,lcount,spare4 from sys.ku$_user_base_view;
NAME PASSWORD LCOUNT SPARE4
------------------------------ ------------------------------ ------ ------------------------------------------------------------------------------------------------------------------------
SYSTEM 0 S:
OUTLN 0 S:0000000000000000000000000000000000000000D11A6AC241D4F7CFC3B6
REMOTE_SCHEDULER_AGENT 0 S:00000000000000000000000000000000000000001D09981D4FDD38DEB86B
GSMUSER 0 S:00000000000000000000000000000000000000009F8A4D11D3CCFE7CB5DE
XS$NULL 0 S:000000000000000000000000000000000000000042D39B5FA5D66485880E
DBSFWUSER 0 S:00000000000000000000000000000000000000009A44AAF1137C8D8AD129
CTXSYS 0 S:00000000000000000000000000000000000000008B00F4E166C9EFAE28DC
MDSYS 0 S:0000000000000000000000000000000000000000E7D1325B0FECC8E203CF
PDBADMIN 0 S:A4CCEA8E7EF3589B56B385B535FD96D39CF7A5F6BE352092C1EA62264FDF
PFCL_VP 0 S:73C7D0167DA1E686F2412242A4CE484B47681D816D1A5C2A36AD4077ECBC
XXB 0 S:A89F5F66F8CFCB267A5316CD6C88549B57565873211B39177BCA975E15E0
...
So in summary we can access LCOUNT without direct access to USER$ if we have SELECT_CATALOG_ROLE and we can access password hashes if we have the EXP_FULL_DATABASE role.
We wanted to see the LCOUNT column without access to SYS.USER$ to check for strange connection behaviour.
Now lets hit failed login count for the user LCOUNT. First check the current profile values
SQL> @prof
F = Failed Login Attempts
T = Password reuse time
S = Sessions per user
L = Password Lock Time
M = Pasword Reuse Max
G = Password Grace Time
L = Password Life Time
V = Password verify function name
PROFILE F T S L M G L V
================================================================================
ORA_CIS_PROFILE 5 365 10 1 20 5 90 ORA12C_VERIFY_FUNCTION
ORA_STIG_PROFILE 3 175 D U 5 0 35 ORA12C_STIG_VERIFY_FUNCTION
DEFAULT 10 U U 1 U 7 180 NULL
================================================================================
PROFILE F T S L M G L V
PL/SQL procedure successfully completed.
SQL>
Failed logins is set to 10 for our user as it has the DEFAULT profile
Try 11 failed connections
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-28000: The account is locked.
SQL>
Note that the first 10 attempts gave a 1017 error and the first error gave slightly different text because it was logged in already before that. Connect as SYS and check lcount in the KU$_USER_VIEW view
C:\d>sqlplus sys/oracle1@//192.168.56.33:1539/xepdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 15 13:15:24 2026
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> select name,lcount from ku$_user_view where name='LCOUNT';
NAME
--------------------------------------------------------------------------------
LCOUNT
----------
LCOUNT
10
SQL>
There were 11 failed attempts but the lcount value is 10. Try one more, so the count of failed logons is 12 and check the lcount value again:
SQL> connect lcount/wrong@//192.168.56.33:1539/xepdb1
ERROR:
ORA-28000: The account is locked.
Warning: You are no longer connected to ORACLE.
SQL>
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> select name,lcount from ku$_user_view where name='LCOUNT';
NAME
--------------------------------------------------------------------------------
LCOUNT
----------
LCOUNT
10
SQL>
The lcount does not increase so any more attempts will not be seen after the account is locked.
We can detect that an account hit the max failed logins because the account will be locked and the lcount will equal the failed logon attempts for the profile for the user. We want to next check if anything looks odd.
In a company with say 50 accounts in the database you might expect at any time that one or two people type the wrong password so the lcount for those accounts will rise but as soon as they remember their password and log in correctly then the lcount resets to zero.
So; if we see a lot of accounts with an lcount greater than zero it is unlikely that half the staff suddenly forget their passwords and then did not remember and log in. It is much more likely that someone is guessing passwords across the database either manually or within a script.
A hacker can have patience and if they know that the failed logins is 10 they could try one or two guesses per day maybe at home time so that most people will reset the failed logon guesses (lcount) in the morning when they come to work. The hacker can then try again each day but the number of accounts with a non-zero LCOUNT will increase as not everyone will log on each day
You can use this simple SQL to check for a percentage of failed logins; the higher the percentage the more suspicious you should be!
SQL> edit
Wrote file afiedt.buf
1 select l.lcount,t.tot,l.lcount/t.tot
2 from (select count(*) lcount
3 from sys.ku$_user_view
4 where lcount>1) l,
5 (select count(*) tot
6* from sys.ku$_user_view) t
SQL> /
LCOUNT TOT L.LCOUNT/T.TOT
---------- ---------- --------------
1 85 .011764706
SQL>
#oracleace #sym_42 #oracle #password #cracking #hacking #users #connection #failed #logins
