Pete Finnigan's Oracle Security Weblog

I want to briefly discuss how we might detect that an attacker is trying to guess passwords and trying to connect to an Oracle database. One simple way that I have been aware of for many years, decades even is the LCOUNT column on the SYS.USER$ table. In recent years USER$ is harder and harder for a non-SYSDBA to see. In the old days anyone with SELECT ANY TABLE could see this table and column BUT because it exposes password hashes it has been locked down first with O7_dictionary_accessibility parameter that prevented "ANY" privileges from accessing the data dictionary tables. Oracle added SELECT ANY DICTIONARY to allow access to a lot of the SYS tables and SELECT_CATALOG_ROLE for a lot of SYS views but SYS.USER$ was blocked from SELECT ANY DICTIONARY.

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                                            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>

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

I want to wish a very happy New year for 2026 to all of our customers, web site visitors and all of our social media connections and likers and commenters. Thanks for all of your support over the years!!

We are organising a 3 day detailed Oracle security class here in our offices in York, UK. We are running this between Monday 16th February to Wednesday the 18th February 2026. This is a detailed overview taught by Pete Finnigan of how to secure data in an Oracle database. We cover all aspects of why your data can be insecure and what to do to make that data secure. We cover every area of reviewing and securing the data as well as a look at suitable audit trail designs and how to react to a possible incident. This is a one off event and places are limited; more details to register are here - Oracle 3 day security training in York February 2026.

We are also organising a live one day class also in our offices in York, UK on February 23rd 2026. This class is all about secure coding in PL/SQL. There are details on the class and how to register here - One day secure coding in PL/SQL class in York February 2026

It is also coming close to PeteFinnigan.com Limited 23rd anniversary. We were founded on February 12th 2003 with a goal of helping customers secure data in their Oracle databases via our software products that we have developed to help with security audits, securing PL/SQL, reviewing PL/SQL for security issues, implementing audit trails as a toolkit, forensic and live analysis and also a user rights tool (coming in 2026). Plus we also have a cookie analyser to find cookies in websites. Our second stream is training in all details of securing Oracle from audits, hardening and securing, audit trail design, forensics, secure coding and more. Our third stream is to provide consulting in any area related to Oracle security. This can be as fixed term pieces of work or ad-hoc or short term or whatever you need related to Oracle security. We act as the on-call Oracle Security experts for a very small number of companies as well so that we can be the Oracle security experts and part of their teams; we do this for 5 customers normally and we have one space available at the moment.

Finally in this short post some MySQL. I normally focus only on Oracle Security but I do access and use other databases such as MySQL, PostgreSQL, SQL Server and more.

We used a web based product called OS Ticket to manage support and tickets for our software products. This software is based on Linux, MySQL for the database and PHP for the website but the version we use is old now and it is time to move on. We stopped customers directly adding tickets some time ago and we add tickets on what is now a private instance after being made aware via our support email address. We want to move away from this system and move to a new ticket system BUT we don't know exactly what we will use yet.

The first step was to migrate the data we wanted to keep from the existing MySQL database so we can decide what to do next, retire the existing system / application but also keep access to the tickets to allow working on them and to add new ones but to keep the data in a format that will be easy to move to a new system.

The database was easy to understand simply by reviewing the schema via phpMyAdmin. We decided we do not need all of the data stored currently ; basically just the ticket numbers, the title, the created date, status, the original issue and any responses and notes. We also decided to only move OPEN tickets and forget all other ticket status. The tickets are stored in a table called OST_TICKET and the messages in OST_TICKET_MESSAGE, the responses in OSP_TICKET_RESPONSE and the notes in OST_TICKET_NOTE.

The ticket table has one entry per ticket but each of the other three have one or more entries per ticket and the messages and responses in my database for OPEN tickets have at least one message per ticket BUT the notes also are mostly NULL per ticket BUT some that are not null still have more than one note. This may be different in other instances of OS Ticket in that maybe each ticket does not have at least one response for instance.

So we needed a query against the MySQL database that is driven on tickets and for responses, messages and notes return a single row for each join to tickets and for notes where there are no joined rows for most return null - i.e. we needed normal joins for the messages and responses and an outer join for the notes.

Here is the SQL we created to extract all the open tickets from MySQL:

select 	t.ticketID, 
		t.ticket_id, 
		t.subject, 
		case when mess.message='Ticket created by staff' then '' else mess.message end as message,
		resp.response,
		note.title,
		note.note,
		t.status, 
		t.created
from 	ost_ticket t
		inner join 
		(
			select 	m.ticket_id, 
					group_concat(m.message,' ') message
			from 	ost_ticket_message m, 
					ost_ticket t 
			where 	t.ticket_id=m.ticket_id 
			and 	t.status='open' 
			group by m.ticket_id
		) mess on t.ticket_id=mess.ticket_id
		inner join 
		(
			select 	r.ticket_id r_id, 
					group_concat(r.response,' ') response
			from 	ost_ticket_response r, 
					ost_ticket t 
			where 	t.ticket_id=r.ticket_id 
			and 	t.status='open' 
			group by r.ticket_id
		) resp on t.ticket_id=resp.r_id
		left outer join
		(
			select 	n.ticket_id not_id, 
					group_concat(n.title,' ') title,
					group_concat(n.note,' ') note
			from 	ost_ticket_note n, 
					ost_ticket t 
			where 	t.ticket_id=n.ticket_id 
			and 	t.status='open'
			and		n.note not like 'Ticket assigned'
			group by n.ticket_id	
		) note on t.ticket_id=note.not_id
where 	t.status='open' 

I decided to post it here in case its useful to anyone else. Review it before use as it may not be 100% correct. We tested various tickets in the web interface against what it returns and its good enough for our next step - i.e. the data looks fine and the right amount of records exist. It can be extended to add more fields and to include more status records but for us this is fine.

I ran this in phpMyAdmin and then extracted to csv for Excel. So we can still view tickets or add tickets in Excel before we decide what to do next. Excel is good as an intermediate step as SQL scripts can easily be created from Excel to allow the data to be added to a new database or similarly, CSV or XML.

What will we use next? not sure yet, we are still evaluating. We may use another free system we can host or a commercial system or ideally create our own. PFCLScan is very flexible and open so we can easily host our tickets as an app in this product. For instance we currently use PFCLScan to manage licenses and build PFCLScan. This is done via plugins

Let me know your thoughts on ticket systems, commercial, free, home grown via our social media posts

#oracleace #sym42 #oracle #security #training #mysql

PL/SQL packages can have a single BEGIN/EXCEPTION/END block as part of the package such as:

create or replace package body block 
as
	procedure test
	is
	begin
		dbms_output.put_line('Hello from test()');
	end;
begin
	dbms_output.put_line('Hello from begin; end');
exception
	when others then
		dbms_output.put_line('BEGIN:['||sqlerrm||']');
end;
/

This begin block is executed when the package is instantiated in a session:

C:\ >sqlplus orablog/orablog@//192.168.56.33:1539/xepdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 20 16:14:03 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Tue Nov 18 2025 12:45:55 +00:00

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
Set serveroutput on and compile the code:
SQL> set serveroutput on
SQL> @pack

Package created.


Package body created.

SQL>

Now we can execute the code:

SQL> exec block.test;
Hello from begin; end
Hello from test()

PL/SQL procedure successfully completed.

SQL>

As we can see the output from the begin/end block is printed first then the print line from the procedure test().

If we execute again, what happens:

SQL> exec block.test;
Hello from test()

PL/SQL procedure successfully completed.

SQL>

The begin/end block is not executed again. This is a feature of PL/SQL packages and the begin/end block at the package level. The begin/end block will execute again if the package became invalid and was re-compiled dynamically before its run or if we log into a new session then its run again:

SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> exec block.test;
Hello from begin; end
Hello from test()

PL/SQL procedure successfully completed.

SQL>

Now; imagine that this package contains some sensitive procedures and functions and we want to implement a check/test to ensure that the user is allowed to execute the procedure(s) before he/she is allowed to do so.

Maybe we want to check the IP address or time of the day or date or role enabled or?? We can write a procedure that is executed that tests the security fields we choose and if we pass the test allow the execution to continue.

If we add this test/check to the begin/end block what happens. We will simulate this as a simple raise command in the begin/end block to simulate that the security check failed. We change the code as follows:

create or replace package body block 
as
	security_fail exception;
	pragma exception_init(security_fail,-20001);
	procedure test
	is
	begin
		dbms_output.put_line('Hello from test()');
	end;
begin
	dbms_output.put_line('Hello from begin; end');
	raise security_fail;
exception
	when others then
		dbms_output.put_line('BEGIN:['||sqlerrm||']');
end;
/

Now reinstall this:

SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> @pack.sql

Package created.


Package body created.

SQL>

Now we can execute it and simulate that the security check failed and therefore we are stopped from running the test() procedure or indeed any other procedure:

SQL> exec block.test;
Hello from begin; end
BEGIN:[ORA-20001: ]
Hello from test()

PL/SQL procedure successfully completed.

SQL>

Hmmm, we caught the exception that the user failed the simulated security check and this sent us to the main package exception block and printed its message BUT we also then ran the test() procedure. So that did not work.

What if we do a raise_application_error() in the exception block. Lets change the code to do that:

create or replace package body block 
as
	security_fail exception;
	pragma exception_init(security_fail,-20001);
	procedure test
	is
	begin
		dbms_output.put_line('Hello from test()');
	end;
begin
	dbms_output.put_line('Hello from begin; end');
	raise security_fail;
exception
	when others then
		dbms_output.put_line('BEGIN:['||sqlerrm||']');
		raise_application_error(-20001,'Error in Security check');
end;
/

And install it:

SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> @pack

Package created.


Package body created.

SQL>

So now we can run the test() procedure:

SQL> exec block.test;
Hello from begin; end
BEGIN:[ORA-20001: ]
BEGIN block.test; END;

*
ERROR at line 1:
ORA-20001: Error in Security check
ORA-06512: at "ORABLOG.BLOCK", line 16
ORA-06512: at line 1


SQL>

That is better as the print message from the package begin/end block was printed and then the raise security_error took us to the package exception block which in turn printed out the sqlerrm error message before raise_application_error() took us out of the PL/SQL code before the procedure test() would have been executed.

So, we failed the security check and the code did not run. If run it again we get:

SQL> exec block.test;
Hello from begin; end
BEGIN:[ORA-20001: ]
BEGIN block.test; END;

*
ERROR at line 1:
ORA-20001: Error in Security check
ORA-06512: at "ORABLOG.BLOCK", line 16
ORA-06512: at line 1


SQL>

So, we can add a security check in the package begin/end block BUT we need to ensure that raise_application_error() throws us out of the package begin/end block back to the caller.

If we did not use raise_application_error() then we would have raised an error but initialised the package correctly and the next time we ran it the begin/end block would not be executed and the code would run BUT with this method even though we would catch the exception the package procedure still executes.
In summary if you wish to check security at the package level rather than in a single (or multiple) procedures then the package initialise MUST FAIL so that next time its executed then the security is guaranteed to run and fail again.

BUT, BUT, we missed a step; This is because the example is too simple. Yes, the raise_application_error() will kill the package initialise and even if its run again the begin/end block will execute again and fail again â€" IF the simulated security check failed. This at first glance seems to work as a solution BUT if the security check in the begin/end block succeeded first time then the raise_application_error() is not fired so the initialisation does not fail and therefore this design does not work as next time no matter if the security check would pass or fail the begin/end block is not processed.

So, if on first execution the security check passed then on a second execution it would potentially fail then the security check DOES NOT WORK.
Let us create a simple example by modifying my code. I will add an integer at the start as a counter and set to zero. Then the test function will increment the integer which will happen after the begin/end block as the begin/end block is called. I will also change the simulated security check to test the integer. If the integer is greater than zero the security check fails and then raise_application_error() is called.

In summary on first execution the security check passes but subsequent checks would fail.

Here is the modified code:

create or replace package body block 
as
	--
	i number:=0;
	security_fail exception;
	pragma exception_init(security_fail,-20001);
	--
	procedure test
	is
	begin
		dbms_output.put_line('Hello from test() - value is ['||i||']');
		i:=i+1;
	end;
	--
begin
	dbms_output.put_line('Hello from begin; end, the check value is=['||i||']');
	if(i>0) then
		raise security_fail;
	end if;
exception
	when others then
		dbms_output.put_line('BEGIN:['||sqlerrm||']');
		raise_application_error(-20001,'Error in Security check');
end;
/

Now install in the database:

SQL> connect orablog/orablog@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> @pack

Package created.


Package body created.

SQL>

And run the first time where the security check should succeed:

SQL> exec block.test;
Hello from begin; end, the check value is=[0]
Hello from test() - value is [0]

PL/SQL procedure successfully completed.

SQL>

This worked correctly. Now run a second time where the security check should fail:

SQL> exec block.test;
Hello from test() - value is [1]

PL/SQL procedure successfully completed.

SQL>

As we can see the integer is now 1 and the execution of the protected code succeeded even though it should have failed the security check because the package BEGIN/END block did not fire again.

In summary do not put security checks in the begin/end block of a package. It may work once or twice if the begin/end block is not completed and the package not initialised but will fail to check security if the package is initialised. This would give false security

#oracleace #sym_42 #oracle #database #security #checks #plsql #package

Are your organisation’s Oracle databases fully secured or are you leaving the door ajar to unwanted access, data leaks or worse? If you’re responsible for managing, designing or auditing Oracle databases, then the upcoming three-day course by Pete Finnigan is one you won’t want to miss.

What’s on offer

A three-day intensive training event, running from Wednesday 3rd to Friday 5th December 2025, in the historic city of York (Tower Court, Oakdale Rd, YO30 4XL).

Live demo coverage of both theory and practice: you’ll start by exploring how an Oracle database can be attacked from end-user, developer and DBA vantage points and then you’ll dig into how to defend, design and audit your Oracle database environment fully.

Topics include authentication, authorisation, roles & profiles, the underlying OS and network layers, Oracle specific security options (Database Vault, VPD, TSDP, Encryption), audit trails and forensic response processes.

Suitable whether your databases are on-premises or in the cloud this training covers the architecture, threats and mitigation strategies relevant to both.

Why this course stands out

Pete Finnigan is a recognised expert an Oracle ACE Pro, member of the OakTable and SYM42 groups, with decades of experience in auditing and securing Oracle environments.

The training provides over 650 pages of slides and notes, 150+ free tools and scripts, and a 30-day engagement licence for PFCLScan and PFCLForensics (tools created by Pete’s team).

With a small-group format, you’ll benefit from direct interaction, real-life anecdotes and hands-on learning rather than generic lectures.

Who should attend

Whether you’re a DBA, system architect, developer (especially PL/SQL/Apex), security auditor, or a manager overseeing Oracle teams if you engage with Oracle databases in any way and care about protecting data, this class is highly relevant.

Investment & registration

The fee is £1,095 GBP + VAT for the full three days.

Tea/coffee and lunch are included each day.

Places are limited to keep the class intimate so if you’re serious, book early.

Final word

In a world where data breaches and insider threats loom large, this is your chance to sharpen your Oracle-security toolkit, learn from one of the field’s best, and return to your organisation with actionable insights and tools you can deploy immediately. Plus you’ll be based in beautiful York, a city steeped in Roman and Viking history, with the chance to enjoy the surroundings while you learn.

Ready to take your Oracle security game to the next level? Let me know by emailingpete@petefinnigan.com, and I can send you the registration and key details!

#OracleSecurity #DatabaseSecurity #OracleDBA #CyberSecurityTraining #OracleTraining #YorkEvents #InfoSec #DataProtection #DBASkills #OracleExpert #PeteFinnigan #CyberAwareness #ITTraining #SecureOracle #York2025 #oracleace #sym_42

I spoke about AI and Oracle security back in February in this blog - AI and Oracle Security. I asked the question can we use AI (LLM / Generative AI) in Oracle Security and if so how?

The two most obvious things would be to analyse audit data to find things we do not spot naturally and to use AI to check configurations. I also talked about how the rise of AI might have come to be in the last few years - hardware is available now to build large neural nets (LLMs) in memory and process with matrix calculations in video cards and also the large availability of training data through search websites indexes, books and many other sources happened around the same time.

My view also is that these potential learning data sources are being corrupted by the web based get rich crowd who use AI to generate huge amounts of content for websites and social media. We can all spot fake posts but wrong or inaccurate facts in posts are harder to spot. If the AI model learning data gets corrupted and the AI models learn from it and people who do not know better then use that AI data to generate more bad data where are we left in the end?

The AI could be trained on inaccurate data in the first place and the data used to train AI can be corrupted by spam/inaccurate data generated by AI so devaluing the content generated by prompts. It is like a self-fulfilling prophecy in reverse.

Another aspect is copyright. I see everywhere emails and adverts and articles (again driven often by get rich quick schemes) about give a generative AI an idea and ask it to make an app including hosting, deploying the full stack and payments, generate a web site and more. In this case its less of an issue for me as the code would never become public - maybe, maybe not.

But, if you use code assist in you development environment and then deploy that code as part of a commercial application / Oracle database or ? then what is the source - the real source of that code generated by AI and who owns it. Did the AI learning phases check the license of every code snippet that is in the future used to generate new code? I think this is something managers need to consider before their commercial code base is updated with generative code. Yes, it makes simple things easy and quick and saves research time and document discovery tasks BUT is it legally the companies code as it was not written keyword for keyword by a developer.

If you generate code for something it often does not compile; as time goes on and the code gets better then developers could be replaced by generative AI (let me be very clear, I do not agree with this) BUT the skills needed to work out why something does not compile or why there is a logical bug in compiled code will be lost and suddenly your commercial application and database are developed and supported by AI but the skill base is lost when something critical happens you cannot support it or fix it.

I think that AI that uses specific reliable input data via RAG maybe will be the best to assist finding and searching that data and compiling answers BUT it depends on the quality of the input data. This can be FAQ, manuals, previous tickets and bugs and more. The creation of this data cannot be by Gen AI if it does not exist. For instance imagine we create a database, queries, tables, PL/SQL code and more and build a finance or CRM or ERP on top of Oracle. Gen AI cannot document it and teach itself; it needs input from designers/developers and more. Yes, i know AI could mine/learn from the source, designs etc but its unlikely to test the system and use it and create data that can answer any question. Well maybe we could do exactly that and the code generation if its exclusively from our code (we own the copyright) can solve the previous issue

In terms of using AI to mine data; this is clearer. We can point audit data at AI and ask it general questions or specific ones to find anomalies and edge cases and potential violations in our audit. This can work as a viable assist to security

I watched and read quite a lot on AI recently and there are some interesting discussions going on. For instance Steven Bartlett interviewed Dr. Roman Yampolskiy and he made a number of statements that i did not agree with. He said by 2027/2030/2045 99% of people will be redundant and only 5 jobs will exist because jobs will be replaced by AI LLMs. This does not make sense. Yes, in principal this could be a worst case Armageddon and companies will want to replace people with generative AI but going to 99% of people replaced is not including the practicalities. If everything went to AI where are all the servers/learning/bots and so on hosted, how do all companies transition to this quickly and how does AI availability increase to cope with the demand of 99% of jobs. Also stated was that cars and lorries will be replaced by self driving lorries and cars. Who is going to make all these cars and lorries very quickly and replace people driving them. The scale of manufacturing is immense.

Imagine a world in a few years where millions of lorries are cars are self driving with no one in them. How is that going to work, imagine 99% of people are redundant; they will no longer need shopping delivered from super markets - plenty of time to walk and buy, no more online retailers as they do not have money to buy, no more take away deliveries... would the take aways need to cook burgers automatically and send them down a Shute to a self driving car waiting outside and the poor redundant people have to go outside their homes to collect the burger from the same self driving cars or do these drive up to the house and an accurate AI slingshot send the food to the letter box, a bit like the reverse of the trains in the past picking up mail bags from hangers as they passed without stopping.

Others are talking that the AI bubble might burst like Web 1.0 and the dot com crash; Don't know, even if it did there will still be AI at some level.

What is needed is AI models built / learned for hundreds of dollars on small devices and not massive data center devices for billions or hundreds of billions. Maybe we ought to have specialised AI models and even create AI people; like tens/hundreds/thousands of models all taught slightly differently so they are like people!! We can have an AI Dinesh, AI Gilfoyle, AI Pete doing Oracle Security, AI Oracle Tuning, AI... the first two are from the excellent Silicon Valley comedy

Yes, AI can be good, can speed things up and reduce costs but there is a big risk that the use of it is being imagined too far ahead and far too end of days.

For Oracle security; Yes, if we have good data that can be learned from and good clean sources to use in other tasks then AI can work

#oracleace #sym_42 #ukoug #ai #UKOUGDiscover25 #OracleCommunity #JoelKallmanDay #oracle #database #AI

If i want to create layered security in a database around something such as specific data or to protect access to a resource or perhaps control access to a privilege then we can create a security layer using standard features BUT the next thing we should think about if we create a solution is someone (a hacker) then turning off parts of that solution.

Therefore we need to then create a protection layer that protects the security we have just implemented. We can even then create another layer that protects the protection layer.

Finally we must also audit all of the layers to test if anyone is trying to access the thing we are protecting or trying to change the security layer to beat the protection.

So, as part of this goal we created DDL triggers as part of the security protection so I want to see if there is a way to detect if someone disables a DDL trigger.

Let us create a simple DDL trigger that can capture all DDL to see if we can catch the disabling of a trigger. Actually not all DDL is caught by a DDL trigger as we already know that ALTER SYSTEM whilst is described as DDL is not caught.

Lets create a DDL trigger that will debug out details of actions:

SQL> get ddl
  1  create or replace trigger atk_ddl
  2  after ddl on database
  3  declare
  4     lv_sql ora_name_list_t;
  5     lv_stmt varchar2(32767);
  6     lv_n pls_integer:=0;
  7  begin
  8     --
  9     lv_n:=ora_sql_txt(lv_sql);
 10     for i in 1 .. lv_n loop
 11             lv_stmt:=lv_stmt||lv_sql(i);
 12     end loop;
 13     --
 14     dbms_output.put_line('DDL>'||lv_stmt);
 15* end;
SQL> @ddl

Trigger created.

SQL>

Now run a DISABLE command on an other trigger and see if its caught:

SQL> sho user
USER is "SYS"
SQL> set serveroutput on
SQL> alter trigger atk_sec_logon disable;
DDL>alter trigger atk_sec_logon disable

Trigger altered.

SQL> alter trigger atk_sec_logon enable;
DDL>alter trigger atk_sec_logon enable

Trigger altered.

SQL>

Yes, fantastic, we can detect disabling of a system trigger, what about disabling our DML trigger. First find out the owner of our trigger:

SQL> col owner for a30
SQL> col trigger_name for a30
SQL> l
  1* select owner,trigger_name from dba_triggers where trigger_name like 'ATK%'
SQL> /

OWNER                          TRIGGER_NAME
------------------------------ ------------------------------
ATK_SEC                        ATK_DML
SYS                            ATK_DDL
SYS                            ATK_SEC_ALTER
SYS                            ATK_SEC_CREATE
SYS                            ATK_SEC_DROP
SYS                            ATK_SEC_GRANT
SYS                            ATK_SEC_LOGON
SYS                            ATK_SEC_TRUN

8 rows selected.

SQL>

Now we can try and disable and re-enable this trigger:

SQL> alter trigger atk_sec.atk_dml disable;
DDL>alter trigger atk_sec.atk_dml disable

Trigger altered.

SQL> alter trigger atk_sec.atk_dml enable;
DDL>alter trigger atk_sec.atk_dml enable

Trigger altered.

SQL>

Yes, we can detect system triggers and DML triggers being disabled. Now we can write a new DDL trigger that can block disable of a trigger such as the DML trigger used in out AUDSYS.AUD$UNIFIED experiment. Now create a new trigger that can detect the disabling of a trigger and block it. First create a version that will find a trigger and report it. We will extend it soon:

SQL> get disable
  1  create or replace trigger atk_disable
  2  after ddl on database
  3  declare
  4     lv_sql ora_name_list_t;
  5     lv_stmt varchar2(32767);
  6     lv_n pls_integer:=0;
  7     atk_disable exception;
  8     pragma exception_init(atk_disable,-20012);
  9  begin
 10     --
 11     lv_n:=ora_sql_txt(lv_sql);
 12     for i in 1 .. lv_n loop
 13             lv_stmt:=lv_stmt||lv_sql(i);
 14     end loop;
 15     if(ora_dict_obj_type = 'TRIGGER') then
 16             if(ora_dict_obj_name like 'ATK%') then
 17                     dbms_output.put_line('Found '||ora_dict_obj_type||'.'||ora_dict_obj_name);
 18             end if;
 19     end if;
 20     --
 21     dbms_output.put_line('DDL>'||lv_stmt);
 22* end;
SQL> @disable

Trigger created.

SQL>

Now test it to make sure we can find a trigger and also its our trigger:

SQL> set serveroutput on
SQL>  alter trigger atk_sec.atk_dml disable;
Found TRIGGER.ATK_DML
DDL>alter trigger atk_sec.atk_dml disable

Trigger altered.

SQL>

Fantastic, we captured the fact that the DML trigger was being disabled. Now lets change the code in the disable trigger to prevent this for our triggers:

SQL> get disable
  1  create or replace trigger atk_disable
  2  after ddl on database
  3  declare
  4     lv_sql ora_name_list_t;
  5     lv_stmt varchar2(32767):='*';
  6     lv_n pls_integer:=0;
  7     atk_disable exception;
  8     pragma exception_init(atk_disable,-20012);
  9  begin
 10     --
 11     lv_n:=ora_sql_txt(lv_sql);
 12     for i in 1 .. lv_n loop
 13             lv_stmt:=lv_stmt||lv_sql(i);
 14     end loop;
 15     if(ora_dict_obj_type = 'TRIGGER') then
 16             if(ora_dict_obj_name like 'ATK%') then
 17                     dbms_output.put_line('Found '||ora_dict_obj_type||'.'||ora_dict_obj_name);
 18                     if(instr(upper(lv_stmt),'DISABLE')<>0) then
 19                             raise atk_disable;
 20                     end if;
 21             end if;
 22     end if;
 23     --
 24     dbms_output.put_line('DDL>'||lv_stmt);
 25* end;
SQL> @disable

Trigger created.

SQL>

Note that we initialise the SQL string to * to prevent a NULL search. It does not matter if the SQL string now starts with *. Now run a DISABLE on our trigger:

SQL> set serveroutput on
SQL> alter trigger atk_sec.atk_dml disable;
Found TRIGGER.ATK_DML
alter trigger atk_sec.atk_dml disable
              *
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_DISABLE'
ORA-00604: error occurred at recursive SQL level 1
ORA-20012:
ORA-06512: at line 17


SQL>

Fantastic, this works, we can now block the disabling of our DML and system triggers. This is one further layer of protection that we can add to protect our security interface on our table.

#oracleace #sym_42 #oracle #database #security #grants #ddl #trigger #protect #hacking #databreach

I have just been notified that I have had two sessions accepted for the UKOUG conference from 30th November to 2nd December 2025 at the East Side Rooms in Birmingham.

My first session is called Fuzzing PL/SQL and is overviewed here from my submission:

traditionally developers may review PL/SQL code by hand or by using free or commercial tools to parse the code looking for potential exploits.

In this talk we take a different approach. We attack the PL/SQL packages and procedures just as an attacker would.

I will explain what fuzzing is and how it can be used on PL/SQL and also do some live demos to show how errors may be generated and what that means for security of the PL/SQL.

Of course we will take some of the fuzzing to the limit and exploit the code. What does that mean? - i.e. if we exploit the code successfully then maybe an error is not generated.

I will discuss the limits of this type of code testing and what they mean

Fuzzing is an other string to your bow of securing PL/SQL

The second talk is called "A design Pattern to Secure Data in Your Database" and is overviewed here from my submission:

I have been advising and teaching people to use a simple design pattern to secure something critical in the database such as a system privilege like ALTER USER or an external resource such as a file system or to secure your most important data.

We can use standard features of the database, some security, some not strictly security.

We can use a simple design pattern that isolates the thing we want to secure and then build layers of security around that.

Sounds intriguing?

I will walk through a complete example to show how a particular set of data can be secured from tampering and change and allows only the access you wish.

The method can be tweaked and changed to your desires and can be used on any type of database ADB, cloud, premise, EE, SE, etc

Hope to see you all there this year!

#oracleace #sym_42 #UKOUGDiscover25 #UKOUG #conference #community #oracle #database security