Who is SYSTEM?

In many companies the DBAs – and not only them – are working with the user “SYSTEM“. Is that wrong? Not necessarily but you might what to check “who did what?” for auditing purposes. And the GDPR is pushing you to thing about if using build in users or security relevant build in objects is helpful.

Which priviliges does SYSTEM has?

If you cannot answer this question – shame on you! You are using an acccount and you do not know what this account is allowed to do? If we have a look with a graphical tool like Toad it looks quite simple: SYSTEM is granted the role DBA and AQ_ADMINISTRATOR_ROLE.

But if you have a closer look you might find that the role DBA has 393 (Oracle 19c) system privileges. Many of them multiple times via sub roles. And I’m wondering if there is any one who can explain their meaning and if they are required.

So please don’t use interal users but create your own ones. And lock the SYSTEM account. There is no reason why you should use it. The Oracle documentation says: “For production systems, Oracle recommends creating individual database administrator accounts and not using the generic SYSTEM account for database administration operations.” (Source: Database 2 Day + Security Guide Version 11.1).

Default Roles DBA, RESOURCE, CONNECT

So lets have a closer look to the build in roles DBA, RESOURCE, and CONNECT. They are relics from an ancient (Oracle 6) time. The Oracle documentations says: “The first three roles in Table 11-1 namely, CONNECT, RESOURCE, and DBA, are provided to maintain compatibility with previous versions of Oracle and may not be created automatically in future versions of Oracle. Oracle recommends that you design your own roles for database security, rather than relying on these roles.” (Source: Oracle Security Guide Version 10.2!).
So please don’t user the build in roles DBA, RESOURCE, CONNECT. Look back to the upgrade from Oracle 11g to 12c. You might remember the issues you had because UNLIMITED TABLESPACE was no longer granted to the role RESOURCE. Similar experiences are possible for using DBA as well.

Default Profile

And now the third chapter of the story. Every database user has a profile. And similar to users and roles Oracle offeres some build in objects like the DEFAULT profile. And likewise users and roles this is a example how to create your own profiles – nothing more. Remember again the changes starting with Oracle 12c. The LIMIT for PROFILE_LIFE_TIME has changed from UNLIMITED to 180 days.
I received several calls from customers and DBAs because the application doesn’t work or users are unable to connect. Asking “when did you upgrade to Oracle 12c” the answer was “approx. 6 month ago”…
So please use your own well defined profiles!

Password Complexity Check

And the last chapter. This was a tough experience for me. For some releases Oracle offers some PL/SQL procedures and functions to verify the password complexity. The functions can be used in the profile limit PASSWORD_VERIFY_FUNCTION.
Please don’t use that for the DEFAULT profile because you might have problems with the installation of database options like spatial. That installation procedure creates a default user with a default passwort – which might not meet your password requirements.

And this is the problem: Oracle offers a script named <ORACLE_HOME>/rdbms/catpvf.sql as a base for a password complexity check. The procedure is well defined and easy to understsand. So there is no reason why you shouldn’t use it …
Example:

CREATE OR REPLACE FUNCTION carajan_pwd_verify
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
   differ integer;
   pw_lower varchar2(256);
   db_name varchar2(40);
   i integer;
   simple_password varchar2(10);
   reverse_user varchar2(32);
BEGIN
   IF NOT ora_complexity_check(
             password, chars => 10, letter => 1, upper => 1, 
             lower => 1, digit => 1) THEN
      RETURN(FALSE);
   END IF;
 ...

This procedure checks if the password has a minimum length of 10 characters with minimum one upper, one lower character, and one digit.
Unfortunately in version 18 Oracle changed the procedure ora_complexity_check, which is the base for the check. The variables upper and lower have been changed to uppercase and lowercase – no idea why.
This little change leads to the fact that the function carajan_pwd_verify becomes “invalid” and the users are no longer able to change their passwords. Thats annoying and ends up in the fact that you always have to check the database version to use the correct variable names.
The better way is to use your own Complexitycheck procedure. You can use the one Oracle provides as a template and make the changes as necessary. That’s helpful because it will probably work even if you migrate to version 21 or higher.

By the way neither did I found a hint in the Oracle documentation that the script is only a template nor any information about the two variables.