Call: +44 (0)7759 277220 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Can You Say That An Oracle Database is nn% secure?

I often get this type of question from customers and also from people I speak to and even a few times by email. The question is "can you tell us how secure our database is?", is it 10% secure, is it 50% secure, is it 100% secure?. Often these people also want to know how their security of Oracle compares across all of their databases in their estates or how their security compares across their market channel or to competitors.

The latter is harder to answer and perhaps unethical as most people do not want to broadcast how secure their database is anyway. The first part is more interesting. We cannot say a specific database is 18% secure or 19% or 60% as the answer will always be qualified by the database itself, how its managed, how it can be accessed and also lots of other related factors, such as other company security, network security, the applications that access the database and much more.

For example if we have an account ORABLOG and its password is very secure, 30 characters long even; profiles are enabled demanding the password to be changed every thirty days, verify functions enforce the rules of the strong password and much more. If we also employ a number of other tools, perhaps a log on trigger that ensures that ORABLOG can only use used from a particular IP Address where the application is deployed or maybe we also use ip chains and valid node checking and much more. We can say that the account is secure BUT this does not prevent SQL Injection attempts from the application to the database or someone taking over the application server and "pretending" to be ORABLOG (and the application). Is the database secure? - is the issue the application?, is it server security? is it application security? - This is hard to assess in a particular case with particular circumstances let along generally as a principle. Should be detach nn% secure database from the surrounding elements (applications, network, people, admin users...). We can never say for sure a database is 60% secure or 17% secure or whatever because we need context and further details.

BUT, if we have a security standard for our company for Oracle databases then we could state whether a particular Oracle database is nn% secure against that policy. This is then defined and measurable. If there are for instance 30 checks, some high severity and some lower then we can also add more weight to the checks that are higher severity and less weight to the lower ones. This can then scale the percentage. The true measure is the number of checks that are failed against those that are not.

We have added this feature to PFCLScan in the latest version. We had an average score previously but that score was a range from 1 - 4 so much less scientific. That previous score was also embedded into the report tool - i.e. the calculation was written into the tool. In the latest version 1.7 we have added the ability to do the calculation in the report template itself. The old way simply had one variable you placed in your template that gave a score now you do the calculation using PFCLScan report variables. This is much better as you can now change the calculation if you wish by changing the variables used or change the weights applied or whatever you wish.

The score is shown here in a report I have ran after scanning an 11.2.0.4 Oracle Database

PFCLScan Report Showing Percentage Secure


The average score (per check) is 59%. This means that 59% of the checks are secure in this database. If I fix something in this database and run the scan again and generate a new report then that percentage would increase. If I undid some of the security work then the percentage would go down. This percentage is a relative score against a standard -In this case the checks that are ran against the database. You can change this score to match your own security policy easily by calculating it only against checks that match your security standard. You can then re-scan the database regularly and graph the changes easily in score against the standard.

Remember this is a score based on whats checks but that is valuable to allow us to check compliance against each database and compare them and also to allow a single database to be scored over time as security work is done.

What to know more then ask us about PFCLScan.
[No Comments]

PFCLScan - A Security Scanner For Oracle Databases - New Website

Our software product PFCLScan can be used to assess your Oracle databases for security issues that could make your data vulnerable to loss or attack. PFCLScan initially had its own website, PFCLScan.com but since the restyle and redesign of our main company site PeteFinnigan.com we decided to bring all of the existing PFCLScan website materials back into PeteFinnigan.com. This was mainly because we want a consistent website with all our offerings in one place and not spread across multiple websites. We also have PFCLObfuscate our product used to protect the IPR (Intellectual Property) in PL/SQL, which also will be brought to PeteFinnigan.com from its existing own website in the next few days.

There is now a hub on PeteFinnigan.com for PFCLScan which is referenced from every page - simply click on the insect or PFCLScan on every page to take you to the PFCLScan Home. We have brought across the Features page, Resellers Page, Licensing details page and create an articles page. The articles are the blog entries from PFCLScan.com and whilst some are a little out of date they are still useful posts. Also note some of the pictures are of the older styled interface.

Any new blog posts about PFCLScan will be posted on the main PeteFinnigan.com Blog Page. We may also add some new articles to the article section where a post may be a little longer than a normal blog post.

Please have a look and learn some more about PFCLScan. If you would like to see an online demo please contact me

We are also looking for resellers particularly for PFCLScan, our training and consulting. If you feel you can offer PFCLScan to your customers then please contact me to discuss. We offer commission on sales and also renewals. Resellers of PFCLScan also can offer up-sell opportunities to their customers such as helping to rectify insecure databases or to help design and create custom PFCLScan policies or reports for customers to check compliance of their databases against their own standards. We are also always interested in any companies who would like to partner with us to resell and host our training courses; again please speak to me if you feel that this would be useful to your company.
[No Comments]

Validating The Length Of An Oracle Database Hashed password?

Q: Can I validate the length of a password in the Oracle database from the password hash?

I have been asked this question a number of times over the years. Even someone emailed me to confirm the answer to this question this week and even though I emailed back I thought it would be a good subject for a blog post here.

If you specify password rules such as the key space to be used (the characters allowed to be used in the password) or the lifetime of the password in the database or the length or many more rules that you can conjure up then these rules can be applied to every user via profiles in the database so that the passwords can be enforced including the length. Oracle allows you to create as many profiles as you wish but there are only two layers in the hierarchy. There is user profiles and the DEFAULT profile. If a setting is not applied in the user profile then the default setting is used. Before we go further we can see what profiles exist in my 11.2.0.3 database:


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
================================================================================
PFCL_OTHER 5 U 2 1 U 2 60 PFCL_VF_OTHER
MONITORING_PROFILE U D D D D D D D
PFCL_SCHEMA 1 U 1 10 U 0 90 PFCL_VF_STDE
PFCL_ADMIN 3 U 3 .04 U 1 30 PFCL_VF_ADMIN
DEFAULT 10 U U 1 U 7 180 NULL
================================================================================
PROFILE F T S L M G L V

PL/SQL procedure successfully completed.

SQL>



We can also see what profiles are assigned to each user:


SQL> @profiles
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
USER Profile F T S L M G L V
================================================================================
SYSTEM DEFAULT 10 U U 1 U 7 180 NULL
SYS DEFAULT 10 U U 1 U 7 180 NULL
CCKEY DEFAULT 10 U U 1 U 7 180 NULL
PFTEST DEFAULT 10 U U 1 U 7 180 NULL
LOG4 DEFAULT 10 U U 1 U 7 180 NULL
USERGRANT1 DEFAULT 10 U U 1 U 7 180 NULL
USERGRANT3 DEFAULT 10 U U 1 U 7 180 NULL
ATKD DEFAULT 10 U U 1 U 7 180 NULL
ZULIA DEFAULT 10 U U 1 U 7 180 NULL
SHAREDDBA DEFAULT 10 U U 1 U 7 180 NULL
ATKR DEFAULT 10 U U 1 U 7 180 NULL
DBACLIENT1 DEFAULT 10 U U 1 U 7 180 NULL
SEED DEFAULT 10 U U 1 U 7 180 NULL
USER02 DEFAULT 10 U U 1 U 7 180 NULL
USER03 DEFAULT 10 U U 1 U 7 180 NULL
ORABLOGDBA DEFAULT 10 U U 1 U 7 180 NULL
HH DEFAULT 10 U U 1 U 7 180 NULL
CCADMIN DEFAULT 10 U U 1 U 7 180 NULL
BOF_SCHEMA DEFAULT 10 U U 1 U 7 180 NULL
ORABLOG_SCHE DEFAULT 10 U U 1 U 7 180 NULL
DEV01 DEFAULT 10 U U 1 U 7 180 NULL
DEV02 DEFAULT 10 U U 1 U 7 180 NULL
BILL DEFAULT 10 U U 1 U 7 180 NULL
TKT_DEV DEFAULT 10 U U 1 U 7 180 NULL
ERIC DEFAULT 10 U U 1 U 7 180 NULL
VU DEFAULT 10 U U 1 U 7 180 NULL
FEED01 DEFAULT 10 U U 1 U 7 180 NULL
TKT DEFAULT 10 U U 1 U 7 180 NULL
PETE DEFAULT 10 U U 1 U 7 180 NULL
ILO DEFAULT 10 U U 1 U 7 180 NULL
TKT_TEST DEFAULT 10 U U 1 U 7 180 NULL
VA DEFAULT 10 U U 1 U 7 180 NULL
EMIL DEFAULT 10 U U 1 U 7 180 NULL
DBAUSER DEFAULT 10 U U 1 U 7 180 NULL
dev.xx();-- DEFAULT 10 U U 1 U 7 180 NULL
USER01 DEFAULT 10 U U 1 U 7 180 NULL
VB DEFAULT 10 U U 1 U 7 180 NULL
USER07 DEFAULT 10 U U 1 U 7 180 NULL
SFTK DEFAULT 10 U U 1 U 7 180 NULL
CORE DEFAULT 10 U U 1 U 7 180 NULL
USER04 DEFAULT 10 U U 1 U 7 180 NULL
USERGRANT2 DEFAULT 10 U U 1 U 7 180 NULL
BATCH01 DEFAULT 10 U U 1 U 7 180 NULL
JIM DEFAULT 10 U U 1 U 7 180 NULL
SQL92 DEFAULT 10 U U 1 U 7 180 NULL
DBACLIENT2 DEFAULT 10 U U 1 U 7 180 NULL
ATK DEFAULT 10 U U 1 U 7 180 NULL
RISK01 DEFAULT 10 U U 1 U 7 180 NULL
DEV03 DEFAULT 10 U U 1 U 7 180 NULL
ATKA DEFAULT 10 U U 1 U 7 180 NULL
USER05 DEFAULT 10 U U 1 U 7 180 NULL
UU DEFAULT 10 U U 1 U 7 180 NULL
USER06 DEFAULT 10 U U 1 U 7 180 NULL
DEV DEFAULT 10 U U 1 U 7 180 NULL
FRED DEFAULT 10 U U 1 U 7 180 NULL
JSON DEFAULT 10 U U 1 U 7 180 NULL
BACK01 DEFAULT 10 U U 1 U 7 180 NULL
FACADM DEFAULT 10 U U 1 U 7 180 NULL
ORABLOG DEFAULT 10 U U 1 U 7 180 NULL
OUTLN DEFAULT 10 U U 1 U 7 180 NULL
OLAPSYS DEFAULT 10 U U 1 U 7 180 NULL
OWBSYS DEFAULT 10 U U 1 U 7 180 NULL
ORDPLUGINS DEFAULT 10 U U 1 U 7 180 NULL
XDB DEFAULT 10 U U 1 U 7 180 NULL
OWBSYS_AUDIT DEFAULT 10 U U 1 U 7 180 NULL
APPQOSSYS DEFAULT 10 U U 1 U 7 180 NULL
ORDSYS DEFAULT 10 U U 1 U 7 180 NULL
SI_INFORMTN_ DEFAULT 10 U U 1 U 7 180 NULL
CTXSYS DEFAULT 10 U U 1 U 7 180 NULL
ORDDATA DEFAULT 10 U U 1 U 7 180 NULL
WMSYS DEFAULT 10 U U 1 U 7 180 NULL
MDSYS DEFAULT 10 U U 1 U 7 180 NULL
SPATIAL_WFS_ DEFAULT 10 U U 1 U 7 180 NULL
MDDATA DEFAULT 10 U U 1 U 7 180 NULL
ORACLE_OCM DEFAULT 10 U U 1 U 7 180 NULL
SPATIAL_CSW_ DEFAULT 10 U U 1 U 7 180 NULL
XS$NULL DEFAULT 10 U U 1 U 7 180 NULL
================================================================================
USER Profile F T S L M G L V

PL/SQL procedure successfully completed.

SQL>



So even though I have designed profiles in my database and even thought about the fact that the DEFAULT profile should take on the strongest settings of all other profiles as you can see no user has got a new profile in my database. This is something I see regularly, unfortunately.

So if we have specified good profiles and we have written good solid verify or complexity functions and added them to our profiles then we could be sure that end users have set strong and in this example passwords of the correct length - OR CAN WE?

Well no, as you can see above even though I have got profiles, they are not used. Also I see regularly two rather silly scenarios. The first is that profiles include a life time BUT no complexity function specifying a suitable password length. So whilst the password is forced to be changed on a time based basis there are no rules to make the password strong or long. The other scenario I see is a profile assigned to users with a complexity function specifying a suitable password length BUT there is no lifetime set so that complexity function never executes enforcing a suitable length of password. We end up with users with profiles but weak passwords still.

By the way, we can also have passwords of a suitable length - maybe 10 characters BUT they are still weak, i.e an easily guessed dictionary word. Password security is complex to manage and enforce.

So, back to the question. Can we test the length of a hashed password in the Oracle database - i.e. we do not know the clear text password. In other words could we be sure in a database that all passwords were at least 8 or 10 or 15 characters long? - The answer is no, or maybe in some cases BUT we can never prove this for all users unless we were lucky or we had NSA like hardware (i don't know what hardware they have BUT I guess that they can crack some passwords very fast or in real time).

We need to use a password cracker. The problem is that the longer the password and the bigger the keyspace used then it takes longer to crack passwords. If a password only uses characters A-Z then there are 26 characters then that would take less time to try all possible passwords for an 8 character password than for a password that includes A-Za-z0-9 - i.e. 62 characters. There are simply more options to try. If we try the complete keyspace, A-Za-z0-9 and all special characters then maybe we can verify that all passwords are at least 2 or 3 characters long with a normal PC and simple software written in C. BUT we cannot go much further and check for instance passwords of 8 characters and the complete key space on a normal PC; it simply takes too long and cannot be done.

What if we employed hardware? Dennis Yurichev created an FPGA cracker some years ago and exposed it to the public via his website - I wrote a blog post about it in 2009 but this tested ASCII passwords and it could crack 8 character passwords in 16.5 hours; this was fast at 65-85 Million hashes a second but its still no where near fast enough. A hardware cracker using an array of 25 GPU graphics cards uses HashCat and was shown on the arstechnica website in 2012. This is more impressive and can crack Windows NTLM passwords at 350 Billion hashes per second. This was 5 years ago so its likely that this speed has improved somewhat even for private teams but those three letter guys in the states with enormous budgets are likely to have a similar set up that can crack in real time.

So the problem gets bigger the more characters you need to try and also the bigger the length of password so for us its impossible to verify a password is 8 or 10 or 15 characters long from the hashed password. I did say that there is a "maybe" - if someone chose a weak password - a dictionary word or simple pattern then maybe we can crack it with a normal cracker using dictionaries or permutes SO we can show for those cracked passwords that they are maybe too short (actually they maybe longer than needed BUT still weak) and weak BUT we cannot verify length.

The only way we can verify an Oracle password is the correct length or more is to enforce with profiles and complexity functions and to enforce that all passwords are changed so that the verify function tests the length. Its the best we can do!!

Good Luck!!
[No Comments]

Default Password Hashes for 11g Oracle Database

I often get Oracle Security related questions from people randomly sent to my inbox or occasionally on Social media and less on on this sites forum. I get questions on average probably 4 times per week in these ways. I try and answer some of them or point the poster in the right direction and at least respond. It may take some time though as I am a very busy person. I also think that some questions are generally interesting and worthy of a blog post.

So, this week Priya K R emailed me and said that they looked at my Oracle Default Password List Page BUT where were the 11g default password hashes? Could I point to a list of 11g default password hashes.

There is not a list as the password algorithms have now changed a few times in the versions of Oracle released. In the versions from Oracle 6 until 10g (and continuing onto 12c but in parallel with others) was the DES based password hash algorithm. This is a hash (one way function) but using the DES encryption algorithm. In 11g R1 there was a new SHA1 algorithm added; this also required a SALT to be used to make each password hash different. So is a user used the password MIKE in one database the hash would be different to a password of MIKE in a different database. There is also the difference that in the DES algorithm the username is effectively the SALT as the username and password are concatenated; in 11gR1 the hash is only of the password. There is a third new algorithm in 12.1.0.2 based on SHA2 and other looping concepts to make the algorithm slow to reject password cracking. Finally since 12.1.0.1 there is also a HTTP Digest which by default is MD5.

So, there is no list of password hashes for 11g because the hashes should be different BUT if a database starts from a SEED then in this case where the passwords are the same in the SEED then the 11g hashes can be the same in every database built from the SEED until its changed. the SALT matters. There is a view in the database under the SYS schema called DBA_USERS_WITH_DEFPWD:


SQL> desc dba_users_with_defpwd
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)


This view can test default passwords that exit in a database. If a user has a default password then in MOST cases bit 16 is set to 16 in the account status; there are some cases where this flag may not have been set yet. Running a query shows some users with a default password in my 11.2.0.4 database:


SQL> select * from dba_users_with_defpwd;

USERNAME
------------------------------
MDSYS
PETE
SPATIAL_WFS_ADMIN_USR
CTXSYS
OLAPSYS
OUTLN
SPATIAL_CSW_ADMIN_USR
ORACLE_OCM
MDDATA
ORDPLUGINS
ORDSYS
APPQOSSYS
ORDDATA
XDB
SI_INFORMTN_SCHEMA
WMSYS


This also has issues. As you can see the user PETE supposedly has a default password. The user PETE may be a default in some application supported by Oracle. The view DBA_USERS_WITH_DEFPWD uses a base table DEFAULT_PWD$ that holds details of default passwords:


SQL> desc default_pwd$
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_NAME VARCHAR2(128)
PWD_VERIFIER VARCHAR2(512)
PV_TYPE NUMBER


And there are 843 of them in my 11.2.0.4 database:


SQL> select count(*) from default_pwd$;

COUNT(*)
----------
843
SQL>


Some applications may have default accounts that have the same names as users that are not defaults. In my case my name is PETE and I created the account PETE with a password of PETE so it shows up BUT it is for sure not a default user in my database. The code of the view also reveals some details:


SQL> select text from dba_views where view_name='DBA_USERS_WITH_DEFPWD'
SQL> /

TEXT
--------------------------------------------------------------------------------
SELECT DISTINCT u.name
FROM SYS.user$ u, SYS.default_pwd$ dp
WHERE
(u.type# = 1
AND bitand(u.astatus, 16) = 16
AND dp.pv_type >= 0
) OR
(u.type# = 1
AND u.password = dp.pwd_verifier
AND u.name = dp.user_name
AND dp.pv_type = 0)


This view text also reveals that DES password hashes are checked if the default user has not had the flag set when the default password was changed to a default - the SYS.USER$.PASSWORD column. Again not a perfect solution if the DES password has been removed by forcing a later protocol to be used.

We could also test for default passwords using a password cracker. In this case we do not consider the flag to be set on account_status, if it was set when the default password was changed to a default and we can also consider 11g passwords that are defaults. To do this we need a list of default passwords. The list mentioned at the start of this post was created many years ago and was the biggest list at the time. Since that time I have done a lot of searching for Oracle default passwords and I have installed all versions (or got hold of existing versions) of Oracle from 6 through to 12c and search sample seed databases, non-seed databases, software installed on the server, documents and files. I have found thousands of username/password pairs - over 8000 in fact and from these I have extracted a distinct list of known default passwords; some 1450 or so passwords. I have not used the pairs or considered hashes. These passwords have been added to PFCLScan our commercial database security scanner and also to a free PL/SQL based password cracker that we use in our training classes on how to perform a security audit of an Oracle database. A sample execution is shown here:


SQL> @cracker-v2.8.sql
PL/SQL cracker: Release 2.8.0.0.0 - Production on Tue Mar 14 18:12:00 2017
Copyright (c) 2008 - 2015 PeteFinnigan.com Limited. All rights reserved.

T [Username ] [Password (10g) ] [Password (11g) ] FL ST
================================================================================================================

U [SYS ] [ORACLE1 ] [oracle1 ] DI OP
U [SYSTEM ] [ORACLE1 ] [oracle1 ] DI OP
U [OUTLN ] [OUTLN ] [outln ] PU EL
U [ORACLE_OCM ] [OCM_3XP1R3D ] [OCM_3XP1R3D ] DE EL
U [APPQOSSYS ] [APPQOSSYS ] [APPQOSSYS ] PU EL
U [WMSYS ] [WMSYS ] [wmsys ] PU EL
U [XS$NULL ] [ ] [ ] -- EL
U [CTXSYS ] [CHANGE_ON_INSTALL ] [change_on_install ] DE EL
U [XDB ] [CHANGE_ON_INSTALL ] [change_on_install ] DE EL
U [ORDSYS ] [ORDSYS ] [ordsys ] PU EL
U [ORDDATA ] [ORDDATA ] [orddata ] PU EL
U [ORDPLUGINS ] [ORDPLUGINS ] [ordplugins ] PU EL
U [SI_INFORMTN_SCHEMA ] [SI_INFORMTN_SCHEMA ] [si_informtn_schema ] PU EL
U [MDSYS ] [MDSYS ] [mdsys ] PU EL
U [OLAPSYS ] [NO_PASSWORD ] [no_password ] DE EL
U [MDDATA ] [MDDATA ] [MDDATA ] PU EL
U [SPATIAL_WFS_ADMIN_USR ] [SPATIAL_WFS_ADMIN_USR ] [spatial_wfs_admin_usr ] PU EL
U [SPATIAL_CSW_ADMIN_USR ] [SPATIAL_CSW_ADMIN_USR ] [spatial_csw_admin_usr ] PU EL
U [OWBSYS ] [TEMP ] [TEMP ] DE EL
U [OWBSYS_AUDIT ] [TEMP ] [TEMP ] DE EL
R [OWB$CLIENT ] [S ] [s ] DE OP
U [USER05 ] [ ] [ ] -- OP
U [ORABLOG ] [ORABLOG ] [orablog ] PU LO
U [FRED ] [FRED ] [fred ] PU OP
U [BILL ] [BILL ] [bill ] PU OP
U [JIM ] [JIM ] [jim ] PU OP
U [USER01 ] [ ] [ ] -- OP
U [USER02 ] [ ] [ ] -- OP
U [USER03 ] [ ] [ ] -- OP
U [USER04 ] [ ] [ ] -- OP
U [LOG4 ] [LOG4 ] [log4 ] PU OP
U [USER06 ] [ ] [ ] -- OP
U [USER07 ] [ ] [ ] -- OP
U [BACK01 ] [BACK01 ] [back01 ] PU EG
U [BATCH01 ] [BATCH01 ] [batch01 ] PU OP
U [FEED01 ] [FEED01 ] [feed01 ] PU OP
U [DEV01 ] [DEV01 ] [dev01 ] PU OP
U [DEV02 ] [DEV02 ] [dev02 ] PU OP
U [DEV03 ] [DEV03 ] [dev03 ] PU OP
U [RISK01 ] [RISK01 ] [risk01 ] PU OP
U [ILO ] [ILO ] [ilo ] PU OP
U [PETE ] [PETE ] [pete ] PU OP
U [ERIC ] [ERIC ] [eric ] PU OP
U [JSON ] [JSON ] [json ] PU OP
U [PFTEST ] [PFTEST ] [pftest ] PU OP
U [CORE ] [CORE ] [core ] PU OP
U [TKT_DEV ] [TKT_DEV ] [TKT_DEV ] PU OP
U [TKT_TEST ] [TKT_TEST ] [TKT_TEST ] PU OP
U [TKT ] [TKT ] [TKT ] PU OP
U [SEED ] [SEED ] [seed ] PU OP
U [EMIL ] [EMIL ] [emil ] PU OP
U [ZULIA ] [ZULIA ] [zulia ] PU OP
U [ORABLOG_SCHEMA ] [ORABLOG_SCHEMA ] [orablog_schema ] PU OP
U [FACADM ] [FACADM ] [facadm ] PU EG
U [USERGRANT1 ] [USERGRANT1 ] [usergrant1 ] PU OP
U [USERGRANT2 ] [USERGRANT2 ] [usergrant2 ] PU OP
U [USERGRANT3 ] [USERGRANT3 ] [usergrant3 ] PU OP
U [UU ] [UU ] [uu ] PU OP
U [ORABLOGDBA ] [ORABLOGDBA ] [orablogdba ] PU OP
U [dev.xx();-- ] [ ] [ ] -- OP
U [SQL92 ] [SQL92 ] [sql92 ] PU OP
U [HH ] [HH ] [hh ] PU OP
U [SHAREDDBA ] [IMP {--locked--} ] [ ] IM OP
U [DBACLIENT1 ] [DBACLIENT1 ] [dbaclient1 ] PU OP
U [DBACLIENT2 ] [DBACLIENT2 ] [dbaclient2 ] PU OP
U [BOF_SCHEMA ] [BOF_SCHEMA ] [bof_schema ] PU OP
U [VU ] [ ] [ ] -- OP
U [VA ] [ ] [ ] -- OP
U [VB ] [ ] [ ] -- OP
U [CCADMIN ] [CCADMIN ] [ccadmin ] PU OP
U [CCKEY ] [ ] [ ] -- OP
U [SFTK ] [SFTK ] [sftk ] PU OP
U [DEV ] [ ] [ ] -- OP
U [ATK ] [ATK ] [atk ] PU OP
U [ATKD ] [ATKD ] [atkd ] PU OP
R [ATK_ADMIN ] [ATK_ADMIN ] [atk_admin ] PU OP
R [ATK_REPORT ] [ATK_REPORT ] [atk_report ] PU OP
U [ATKA ] [ATKA ] [atka ] PU OP
U [ATKR ] [ATKR ] [atkr ] PU OP
U [DBAUSER ] [ ] [ ] -- OP


INFO: Number of crack attempts = [45495]
INFO: Elapsed cracking time = [.67 Seconds]
INFO: Total elapsed time = [.67 Seconds]
INFO: Cracks per second = [67900]

PL/SQL procedure successfully completed.

SQL>


The output shows whether the account is a User or a Role and also shows the name, DES password, 11g password and also how it was cracked - DE= DEFAULT, BF=Brute Force, DI=Dictionary Word, PU= Password=Username. Again the problem is that if a non default user has a default password it will show up. We also have a script called use_any-11.sql that extracts permissions at a summary level for all users / Oracle users (defaults) or customer users. A sample is shown here:


SQL> @use_anl-11



use.sql: Release 2.2.0.0.0 - Production on Tue Mar 14 18:15:54 2017
Copyright (c) 2007, 2009, 2016 PeteFinnigan.com Limited. All rights reserved.

OUTPUT FLAG [A|O|C] [A]: O
NUMBER OF USERS [200]:

Typ Rol RSO Sys Ob Tab PL USER
================================================================================
ADM 58 200 10 1018 1483 SYS
ADM 3 5 218 167 7 SYSTEM
DEF 1 3 1 3 1 OUTLN
DEF 0 1 59 0 6 ORACLE_OCM
DEF 0 3 11 4 0 APPQOSSYS
DEF 3 30 14 44 52 WMSYS
DEF 2 9 53 50 141 CTXSYS
DEF 3 10 501 32 79 XDB
DEF 1 1 1046 5 87 ORDSYS
DEF 0 1 0 73 0 ORDDATA
DEF 0 0 5 0 10 ORDPLUGINS
DEF 0 1 0 0 0 SI_INFORMTN_SCHEMA
DEF 2 20 24 129 254 MDSYS
DEF 2 13 44 126 89 OLAPSYS
DEF 2 1 0 0 0 MDDATA
DEF 3 8 268 0 0 SPATIAL_WFS_ADMIN_USR
DEF 3 8 144 0 0 SPATIAL_CSW_ADMIN_USR
DEF 10 22 44 1 0 OWBSYS
DEF 0 2 0 0 0 OWBSYS_AUDIT
DEF 0 0 0 0 0 XS$NULL
================================================================================
Typ Rol RSO Sys Ob Tab PL USER

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/use.sql

SQL>


We can combine the output for both and compare cracked passwords to users that are actually defaults.

Nothing is perfect in checking default passwords but the issue is really not just defaults but also all weak passwords; there should be none. Also default passwords are just the start; its OK to recognise default passwords or weak passwords BUT we also need to design profiles, life times, verify functions, enforce password changes, encrypted connections (ssl, ssh, etc) and much more. A weak password is simple to spot but it is harder to enforce strong passwords.

Good luck.
[No Comments]

12.2 is Available For Download For Linux And Solaris

The Oracle database 12.2 has been available on the cloud for some time but it has not been available for download so that you can install it on your own servers or virtual Machines. You can download from OTN or delivery.oracle.com. I have already downloaded Oracle Database 12.2.0.1.0 for Linux x64 and I hope I will get time to install this weekend. I am already aware of some security changes / updates / deleted parameters etc in 12.2 but I want to have a detailed look at whats changed and I will blog about some of the things here.

Of course also watch out for Tim Hall to create his famous installation guides for this version.

Good luck!!
[No Comments]

Delete from AUD$

We have been working on a new audit trail toolkit / product for some time now which is called PFCLATK - The PFCL means "P"ete"F"innigan."C"om "L"imited so most of our tools products end up with a consistent name starting PFCL. I will talk about the audit trail toolkit and show some demo's over the coming weeks. PFCLATK will (is) a free PL/SQL and SQL toolkit that is used to provide a layer over standard audit. It is currently around 11k lines of PL/SQL and SQL presently and is being implemented by some sites into their Oracle databases with our help. Core audit is supported now and its trivial to also support Unified Audit when there is a demand. The toolkit will be free BUT we plan to create a dashboard / admin interface most likely in our Oracle vulnerability scanner PFCLScan so that a "link" can be created between security auditing of a database and audit trails of a database. The toolkit will be free but probably will not be available as a download - just ask me for it if you would like to implement it. We also give the same toolkit away as a free download for students of my one day class "Designing Practical Audit Trails for Oracle".

The main idea behind PFCLATK is to solve the problem that most sites I visit and also people I talk to do not have comprehensive audit trails for the database engine itself. Some may have very minimal audit for CREATE SESSION or even use audit for resource planning but very few actually use audit trails to understand who may access your Oracle database and what they might be doing. Included in this is the problem that audit in Oracle is actually complex when you get into the details.

What was needed was a simple "Turn it all on" - I mean "ALL" in a sense of all you want not all audit possible. Manage it at a policy level, provide events and alerts to assess if a breach or misuse has occurred and also provide centralised audit trails. All of this by simply running one SQL*Plus script in a database that becomes the central audit trail and then running the same script in each target database. Of course the central database is also audited by the same toolkit. Then running one simple configuration script to "add" each target database to the central database where it is then automatically managed. The central database PUL's the audit from each target and checksums and purges it from the remote source. Reporting can be done on the central database or the targets.

Anyway, I will talk in much more detail about the toolkit over coming weeks and show some demos and setup and show what it does. As part of the development and research into audit trails in Oracle for this tool and also for our one day class we discovered many nuances. I want to talk about purging / deleting SYS.AUD$. Here is a demo; First connect as SYS and create a user ATKP:


C:\____atk>sqlplus sys/oracle1@//192.168.1.39:1521/bofora.localdomain as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 28 10:26:55 2017

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


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL>


Give ATKP the ability to connect and also the ability to delete records from AUD$:


SQL> create user atkp identified by atkp;

User created.

SQL> grant create session to atkp;

Grant succeeded.

SQL> grant delete on sys.aud$ to atkp;

Grant succeeded.

SQL>


Clear down AUD$ as SYSDBA - we are still connected as SYSDBA:


SQL> truncate table sys.aud$;

Table truncated.

SQL> commit;

Commit complete.

SQL>


Set up some formatting and check what audit is enabled:


SQL> col user_name for a20
SQL> col privilege for a30
SQL> set lines 220

SQL> select user_name,privilege,success,failure from dba_priv_audit_opts;

USER_NAME PRIVILEGE SUCCESS FAILURE
-------------------- ------------------------------ ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
ADMINISTER DATABASE TRIGGER BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY TRIGGER BY ACCESS BY ACCESS
ALTER ANY TRIGGER BY ACCESS BY ACCESS

USER_NAME PRIVILEGE SUCCESS FAILURE
-------------------- ------------------------------ ---------- ----------
EXECUTE ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
AUDIT ANY BY ACCESS BY ACCESS
ALTER ANY ROLE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
DROP ANY ROLE BY ACCESS BY ACCESS
CREATE ROLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS

USER_NAME PRIVILEGE SUCCESS FAILURE
-------------------- ------------------------------ ---------- ----------
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
BECOME USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS

32 rows selected.

SQL>
SQL> select user_name,audit_option,success,failure from dba_stmt_audit_opts;

USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ---------------------------------------- ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
ADMINISTER DATABASE TRIGGER BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY TRIGGER BY ACCESS BY ACCESS
ALTER ANY TRIGGER BY ACCESS BY ACCESS

USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ---------------------------------------- ---------- ----------
EXECUTE ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
SYSTEM GRANT BY ACCESS BY ACCESS
AUDIT ANY BY ACCESS BY ACCESS
ALTER ANY ROLE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
DROP ANY ROLE BY ACCESS BY ACCESS
CREATE ROLE BY ACCESS BY ACCESS

USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ---------------------------------------- ---------- ----------
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
PROFILE BY ACCESS BY ACCESS
ROLE BY ACCESS BY ACCESS
DATABASE LINK BY ACCESS BY ACCESS
PUBLIC SYNONYM BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
BECOME USER BY ACCESS BY ACCESS

USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ---------------------------------------- ---------- ----------
CREATE USER BY ACCESS BY ACCESS
USER BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
SYSTEM AUDIT BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS

38 rows selected.

SQL>
SQL> select * from dba_obj_audit_opts;

OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
------------------------------ ------------------------------ ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
SYS AUD$ TABLE A/A A/A A/A A/A A/A A/A A/A A/A A/A A/A A/A -/- -/- -/- -/- -/- A/A
ORABLOG BIN$LO2rapM5UdbgUycBqMBEsQ==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG BIN$LO3duR7cUlHgUycBqMBiIQ==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG BIN$LO3mL8NUUn7gUycBqMBI4g==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG BIN$LOt2+8lUTmrgUycBqMDJEg==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG BIN$LOuOjTzSTqfgUycBqMDBbg==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG BIN$LOunNR3oTuHgUycBqMDPcw==$0 TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG CREDIT_CARD TABLE -/- A/A -/- A/A -/- -/- A/A -/- -/- S/S A/A -/- -/- -/- -/- -/- -/-
ORABLOG WP_USERMETA TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ORABLOG WP_USERS TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
ATKD PFCLATK_ALERTS TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/-

OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
------------------------------ ------------------------------ ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
ATKD PFCLATK_AUDIT TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/-
ATKD PFCLATK_RULES TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/-
ORABLOG RUN_SQL PROCEDURE -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/-
ORABLOG ORABLOG_CRYPTO PROCEDURE -/- A/A -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/-
ORABLOG CUSTA PROCEDURE -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/-

16 rows selected.

SQL>


As you can see there are many audit settings in my database BUT the one i want to focus on is the audit on SYS.AUD$ itself. We are auditing select, insert, update, delete, audit and more. If i connect as SYSTEM and access AUD$ a couple of times I get:


SQL> connect system/oracle1@//192.168.1.39:1521/bofora.localdomain
Connected.
SQL> select * from sys.aud$;
...
SQL> select count(*) from sys.aud$;

COUNT(*)
----------
2
SQL>


If we check what is in AUD$ as SYSTEM we can see:


SQL> col userid for a20
SQL> col obj$name for a10
SQL> col action# for 9999
SQL> select userid, obj$name, action# from sys.aud$;

USERID OBJ$NAME ACTION#
-------------------- ---------- -------
SYSTEM 100
SYSTEM AUD$ 3
SYSTEM AUD$ 3
SYSTEM AUD$ 3

SQL>


So we have some records in AUD$ we can connect to ATKP and because ATKP has DELETE rights on SYS.AUD$ we can delete all of the records:


SQL> delete from sys.aud$;

8 rows deleted.

SQL> commit;

Commit complete.

SQL>


Check now as SYSTEM and see what's left:


SQL> select userid, obj$name, action# from sys.aud$;

USERID OBJ$NAME ACTION#
-------------------- ---------- -------
ATKP AUD$ 7

SQL>


This leaves one record in SYS.AUD$ with an action of 7; 7 means DELETE. This means that the DELETE on SYS.AUD$ was itself audited - Good! Interestingly also the select above on SYS.AUD$ by SYSTEM to see that the DELETE audit record also creates another audit records for action 3 - SELECT BUT of course we cannot see that now as its created by the SELECT. Now the issue; If we go back to the session at ATKP and DELETE from SYS.AUD$ again - we have permission to do that:


SQL> sho user
USER is "ATKP"
SQL> delete from sys.aud$;

1 row deleted.

SQL> commit
2 /

Commit complete.

SQL>


Lets now go to SYSTEM and check the contents of SYS.AUD$


SQL> select userid, obj$name, action# from sys.aud$;

USERID OBJ$NAME ACTION#
-------------------- ---------- -------
ATKP AUD$ 7
ATKP AUD$ 7

SQL>


So a user with DELETE rights on SYS.AUD$ can delete any audit records from SYS.AUD$ (including SELECT) BUT it cannot delete an audit record for DELETE on SYS.AUD$ itself. This is good in one sense but sort of not perfect in another. If we create an audit trail solution we want to create an AUDIT MANAGER type user who has the ability to manage the audit rails completely BUT this user has some restrictions (yes there are more restrictions but I will leave them for another post) in that it cannot manage the audit trails completely. In this case SYSDBA must empty AUD$ including the DELETE audit records for AUD$ itself.

I know there are solutions to this and I will also talk about some of these going forward in future blog posts along with some more related issues. Also watch out for the blog post coming soon on PFCLATK audit toolkit.



[No Comments]

Fourteenth Anniversary For PeteFinnigan.com Limited And New Website

Wow, has it really been fourteen years since I started PeteFinnigan.com Limited? - Time has gone so fast and business is getting better and better. We have great customers, great Oracle Security trainings and consulting projects meeting new people and we love passing on knowledge. We also have great customers of our software PFCLScan - our database security scanner for Oracle and also PFCLObfuscate our product to help people protect their PL/SQL.

We have a number of new products in the wings most notably PFCLATK our audit trail toolkit - More on this soon in this blog, I promise!!!. We have some customers implementing PFCLATK already and we would really like to get a couple more customers to implement and test it with us. As I said more details on this great new audit trail toolkit soon but if you are interested to test let me know.

I hope that visitors to our company website PeteFinnigan.com have also noticed that the home page style has somewhat changed. Thanks to Kariba Web Design in Harrogate here in the UK. They have designed a new look for us based on initial designs by me. They provided a HTML5, JavaScript and CSS set of samples styles and we have converted that to fit the main site code structure. The home page and a few other static pages have been live for a few weeks but the blog has only just gone live this week on the new style and design. The blog was a big effort to shoehorn a new modern HTML5 / CSS design into GreyMatter which we have used for many years. Greymatter was one of the first blog software packages from 2001 (written initially by Noah Grey) and then in the early years modded and updated by people like FlippedCracker, Linear, Hazelorb and others. Then around 11 - 12 years ago myself, Coldstone and Carlos Phelps took the reigns and updated and released a number of new versions. The last official version was 1.8.2 in May 2008 BUT... I also created after that (September 2009) a version 1.8.3 that included some new bug fixes and some new features BUT by that time no one was interested in Greymatter blogging anymore and only one or two people tested 1.8.3.

As part of re-learning the templates and some other features to update to our new website design I have also made a couple of small changes to the Greymatter blog code to remove the matrix calendar and include inline links of the current months posts and also to add HTML titles from the blog itself. This will be added to a new Greymatter 1.8.4 version. If anyone is interested in 1.8.4 then please let me know BUT there will not be an upgrade script from 1.8.2 as it is just not worth it as there are so few people using it now. That said I do get people from time to time asking if they can have 1.8.3. Version 1.8.3 has over 60 changes to the code base of 1.8.2 and also some structural changes to the storage.

The main site still has quite a few pages in the old style and indeed I captured a screen shot of the old front page and saved this to the design page for posterity and also you will notice that the new logo is a caricature of me from the old site!! The remaining pages but probably not the forum will be updated to the new style as soon as possible - Maybe the forum if I can find a way to do it without major effort.

There may also be a 1.9 or possibly version 2.0 of Greymatter Blog coming soon at least in this blog. I want to add some features to my blog that do not exist in 1.8.2 or earlier; these changes will likely not be fully integrated in Perl and may be add-ons in php. After all back in 2002-2005 that is how most of the mods for 1.2 were done!!

Welcome to the new look blog and also now watch out for new blog posts on Oracle Security; I have a backlog of things to write about now so a few posts are coming.
[No Comments]