DBSecWorx: Profiles

Oracle PROFILES Versions All through 21c

Security Advisory

Profiles have been in the Oracle Database for decades and for decades, other than for a very small percentage of Oracle's customers they have been ignored which is a tremendous mistake.

At the bottom of this page are examples of how to make Profiles work for you.

Recommended Security Rules

NEVER

Deploy a database where any user is assigned to Oracle's DEFAULT profile.
Never deploy an Oracle database without modifying the DEFAULT profile so that it is unusable.

WITH GREAT CARE

Design and implement profiles that make sense based on the application and the responsibilities of the users, human or mechanical, that connect to the application.

CAUTIONS

The overwhelming majority of Oracle DBAs know the that PROFILE objects exist but have little more than a clue as to their value in improving security: Be sure that you are not one of them.

How Oracle Works

Purpose Profiles are associated with individual users

Oracle defines two different categories of profile resource.

The first, Kernel Resources, makes it possible to specify resource availability for a group consisting of one or more users.

The second, Password Resources, makes it possible to specify password behavior for a group of one or more users.

Think of Password Resources as securing your database ... Kernel Resources as securing your data.

All Oracle users are assigned a profile. If none is assigned Oracle assigns the DEFAULT profile that it created at the time of database creation.

The first step in working with profiles is understanding their components, what they mean, and their impact on the security of your database. Let' start by viewing the options.

      

      SELECT username, profile, created

      FROM dba_users

      ORDER BY 1;

      

      USERNAME                       PROFILE  CREATED

      ------------------------------ -------- --------------------

      ANONYMOUS                      
      DEFAULT 03-NOV-2020 09:37:59

      APPQOSSYS                      
      DEFAULT 03-NOV-2020 09:35:03

      AUDSYS                         
      DEFAULT 03-NOV-2020 09:24:41

      CTXSYS                         
      DEFAULT 03-NOV-2020 10:08:18

      DBSFWUSER                      
      DEFAULT 03-NOV-2020 09:26:43

      DBSNMP                         
      DEFAULT 03-NOV-2020 09:35:01

      DGPDB_INT                      
      DEFAULT 03-NOV-2020 09:33:16

      DIP                            
      DEFAULT 03-NOV-2020 09:26:25

      DVF                            
      DEFAULT 03-NOV-2020 10:22:12

      DVSYS                          
      DEFAULT 03-NOV-2020 10:22:12

      GGSYS                          
      DEFAULT 03-NOV-2020 09:35:17

      GSMADMIN_INTERNAL              
      DEFAULT 03-NOV-2020 09:26:22

      GSMCATUSER                     
      DEFAULT 03-NOV-2020 09:35:08

      GSMROOTUSER                    
      DEFAULT 03-NOV-2020 09:26:22

      GSMUSER                        
      DEFAULT 03-NOV-2020 09:26:22

      LBACSYS                        
      DEFAULT 03-NOV-2020 10:21:45

      MDDATA                         
      DEFAULT 03-NOV-2020 10:12:05

      MDSYS                          
      DEFAULT 03-NOV-2020 10:12:04

      OJVMSYS                        
      DEFAULT 03-NOV-2020 10:04:36

      OLAPSYS                        
      DEFAULT 03-NOV-2020 10:11:25

      ORACLE_OCM                     
      DEFAULT 03-NOV-2020 09:27:47

      ORDDATA                        
      DEFAULT 03-NOV-2020 10:09:30

      ORDPLUGINS                     
      DEFAULT 03-NOV-2020 10:09:30

      ORDSYS                         
      DEFAULT 03-NOV-2020 10:09:30

      OUTLN                          
      DEFAULT 03-NOV-2020 09:24:43 

      REMOTE_SCHEDULER_AGENT         
      DEFAULT 03-NOV-2020 09:26:42

      SI_INFORMTN_SCHEMA             
      DEFAULT 03-NOV-2020 10:09:30

      SYS                            
      DEFAULT 03-NOV-2020 09:24:40

      SYS$UMF                        
      DEFAULT 03-NOV-2020 09:32:26

      SYSBACKUP                      
      DEFAULT 03-NOV-2020 09:24:41

      SYSDG                          
      DEFAULT 03-NOV-2020 09:24:41

      SYSKM                          
      DEFAULT 03-NOV-2020 09:24:41

      SYSRAC                         
      DEFAULT 03-NOV-2020 09:24:41

      SYSTEM                         
      DEFAULT 03-NOV-2020 09:24:41

      WMSYS                          
      DEFAULT 03-NOV-2020 10:02:01

      XDB                            
      DEFAULT 03-NOV-2020 09:37:59

      XS$NULL                        
      DEFAULT 03-NOV-2020 09:26:40

      

      37 rows selected.

      

      
      Let's create two users ... the first without a PROFILE assignment, the second with an explicit assignment.

      

      SQL> SELECT DISTINCT profile FROM dba_profiles ORDER BY 1;

      

      PROFILE

      ------------------------------

      C##GGADMIN

      DEFAULT

      ORA_STIG_PROFILE

      

      CREATE USER profile_not_specified IDENTIFIED BY pns;

      

      User created.

      

      CREATE USER  profile_specified IDENTIFIED BY psd

      PROFILE ora_stig_profile;

      CREATE USER  profile_specified IDENTIFIED BY psd

      *

      ERROR at line 1:

      ORA-28003: password verification for the specified password failed

      ORA-20000: password length less than 15 bytes

      
      

      Note that  specifying the profile, which includes password verification prevents creation of a user with an inadequate password

      

      CREATE USER  profile_specified IDENTIFIED BY pR0filespe#ified

      PROFILE ora_stig_profile;

      

      User created.

      

      
      The user is created because password now has an upper case letter, a number, and a special character.

      

      Here's what we see in the data dictionary

      

      SQL> SELECT username, profile

        2  FROM dba_users

        3  WHERE username LIKE '%PROF%SPEC%';

      

      USERNAME                     PROFILE

      ---------------------------- ------------------------------

      PROFILE_NOT_SPECIFIED        DEFAULT

      PROFILE_SPECIFIED            ORA_STIG_PROFILE

      

      The user created without specifying a PROFILE was assigned to DEFAULT. And that, by definition is a security risk because the DEFAULT profile, by default, offers essentially zero protection for the data or the database.
      Here is what it specifies.

      

      SQL> SELECT resource_name, resource_type, limit

       
      2  FROM dba_profiles

       
      3  WHERE profile = 'DEFAULT'

       
      4  ORDER BY 2,1;

      

      RESOURCE_NAME             RESOURCE LIMIT

      ------------------------- -------- ----------

      COMPOSITE_LIMIT           KERNEL   UNLIMITED

      CONNECT_TIME              KERNEL   UNLIMITED

      CPU_PER_CALL              KERNEL   UNLIMITED

      CPU_PER_SESSION           KERNEL   UNLIMITED

      IDLE_TIME                 KERNEL   UNLIMITED

      LOGICAL_READS_PER_CALL    KERNEL   UNLIMITED

      LOGICAL_READS_PER_SESSION KERNEL   UNLIMITED

      PRIVATE_SGA               KERNEL   UNLIMITED

      SESSIONS_PER_USER         KERNEL   UNLIMITED

      FAILED_LOGIN_ATTEMPTS     PASSWORD 10

      INACTIVE_ACCOUNT_TIME     PASSWORD UNLIMITED

      PASSWORD_GRACE_TIME       PASSWORD 7

      PASSWORD_LIFE_TIME        PASSWORD 180

      PASSWORD_LOCK_TIME        PASSWORD 1

      PASSWORD_REUSE_MAX        PASSWORD UNLIMITED

      PASSWORD_REUSE_TIME       PASSWORD UNLIMITED

      PASSWORD_ROLLOVER_TIME    PASSWORD 0

      PASSWORD_VERIFY_FUNCTION  PASSWORD NULL

      

      17 rows selected.

      

      
      Just seeing the word "UNLIMITED" should be 
      enough to convince you that this user has been created with far too much 
      permission. But it is worse than that ... the new user:
        Can connect forever
        Can use an unlimited amount of cpu starving other users
        Can be idle forever and not be disconnected
        Can perform a logical read of unlimited size (meaning they can steal 
        all of your data in a single statement)
        Can consume an unrestricted amount of the System Global Area (SGA)
        Can login again and again and create as many simultaneous sessions as they wish, all 
        similarly unrestricted
        Can mess up the password 10 times before the account is locked
        Can use an expired password for 7 days before the account is locked
        Only needs to change their password twice each year and can reuse the same password
        Has no validity checking on their password so a single letter is sufficient
      
      There isn't a single part of this profile that makes even the slightest sense and the user just created has the ability to create a Distributed Denial Of Service (DDOS) attack because that is "the default".

      

      You have a couple of choices if you are part of the 99%. You can do what most DBAs do and ignore the issue or you can fix it as shown below.

Create one or more new profiles, one for Oracle system users, one for the members of the DBA team, and one or more additional profiles for everyone else.

If the database has both human user and mechanical users, for example connections from IOT devices do not share the same profile with both robots and humans. The first thing you must do to create usable, more secure, profiles is to classify the users into logical groups. For this demo we will create 2 groups. "DBAs" and "Everybody Else". To keep this simple the demo will focus on a single resource: Our business rule will state that "A DBA can have up to 3 simultaneous sessions but a Report Writer is limited to only 1. When you do this in your environment, unlike this demo, you should to specify every resource you don't want to have use a default, insecure, value. SQL> CREATE PROFILE dba_profile LIMIT 2 sessions_per_user 3; Profile created. SQL> CREATE PROFILE everybody_else_profile LIMIT 2 sessions_per_user 1; Profile created. SQL> SELECT profile, resource_name, limit 2 FROM dba_profiles 3 WHERE profile IN ('DBA_PROFILE', 'EVERYBODY_ELSE_PROFILE') 4* AND limit <>'DEFAULT'; PROFILE RESOURCE_NAME LIMIT ----------------------- ------------------ ----- DBA_PROFILE SESSIONS_PER_USER 3 EVERYBODY_ELSE_PROFILE SESSIONS_PER_USER 1 Now we have a customized profile for members of the DBA team, one for "Everyone Else", and we recommend creating a profile for SYS, SYSTEM, and other Oracle default accounts that is identical to what Oracle provides but naming it something harmless like 'READONLY' so it doesn't attract attention. All you have to do to get everyone off the DEFAULT profile now is move them. SQL> SELECT profile FROM dba_users WHERE username = 'SCOTT'; PROFILE ----------------------- DEFAULT SQL> alter user scott PROFILE EVERYBODY_ELSE_PROFILE; User altered. SQL> SELECT profile FROM dba_users WHERE username = 'SCOTT'; PROFILE ------------------------------ EVERYBODY_ELSE_PROFILE This step is a success when no users is currently assigned to the DEFAULT profile. SQL> SELECT UNIQUE username 2 FROM dba_users 3 WHERE profile = 'DEFAULT'; no rows selected

Maximum Security Default Profile

Now that legitimate users connecting to the database no longer require the DEFAULT profile the only way anyone should be assigned it is a DBA not following security procedures or someone trying to hack the system.

By limiting everything the default profile authorizes DBA errors will be quickly found and hackers will have to work far harder to create a gain access. The following list isn't intended to be complete but rather to illustrate the high approach. SQL> ALTER PROFILE DEFAULT LIMIT 2 CONNECT_TIME 1 3 CPU_PER_CALL 1 4 CPU_PER_SESSION 1 5 FAILED_LOGIN_ATTEMPTS 1 6 IDLE_TIME 1 7 INACTIVE_ACCOUNT_TIME 15 8 LOGICAL_READS_PER_CALL 1 9 LOGICAL_READS_PER_SESSION 1 10 PASSWORD_GRACE_TIME 0 11 PASSWORD_LIFE_TIME 0.00001 12 PASSWORD_LOCK_TIME UNLIMITED 13 PASSWORD_REUSE_MAX 1 14 PASSWORD_REUSE_TIME 9999 15 PASSWORD_ROLLOVER_TIME 0 16 PASSWORD_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION 17 PRIVATE_SGA 1 18* SESSIONS_PER_USER 1; Profile created. I don't think anyone is doing too much damage with this profile that we will assign to the user "test". SQL> conn test/"testTES#T!2test"; ERROR: ORA-02394: exceeded session limit on IO usage, you are being logged off And, as you can see, an I/O limit set to 1 forces a failure. No one, not assigned to a valid profile has much chance of hacking this database. What was done in this demo is within the existing license agreement of every Oracle customer and would require no more than an hour's effort. So what are you waiting for? Get approval, define the logical user groups, write the script, run first in Dev, then Test, and get final approval before migrating to production.

Profile Information

Dependencies

CDB_PROFILES	KU$_USER_BASE_VIEW	PROFNAME$
DBA_PROFILES	PROFILE$	RESOURCE_MAP
KU$_PWDVFC_VIEW

Documented Yes

Exceptions

Error Code	Reason
ORA-02017	Integer Value Required
ORA-02377	Invalid Profile Limit

Security Model Owned by SYS

Source The default profile is created by {$ORACLE_HOME}/rdbms/admin/denv.bsq

Modifications that improve governance, compliance, and security can be found, commented out, in {$ORACLE_HOME}/rdbms/admin/utlpwdmg.sql

Password verify function DDL is in $ORACLE_HOME/rdbms/admin/catpvf.sql.

System Privileges

ALTER PROFILE

CREATE PROFILE

DROP PROFILE

Profile Discovery and Resources

Profile Discovery The first step in working with Profiles is to determine the names of profiles that exist in the database. col profile_name format a20 col flag format a30 SELECT profile#, name AS PROFILE_NAME, DECODE(flags, 1, 'Common (CDB or App)', 2, 'App Common', 4, 'Implicit App', 8, 'Oracle Supplied', 16, 'Mandatory', flags) AS FLAG FROM profname$ ORDER BY name; PROFILE# PROFILE_NAME FLAGS ---------- -------------------- ---------------- 0 DEFAULT Oracle Built-in 1 ORA_CIS_PROFILE Oracle Built-in 2 ORA_STIG_PROFILE Oracle Built-inIn a container database you can create regular profiles in a PDB but you must create a COMMON PROFILE in CDB$ROOT. The following might be a reasonable starting point for a profile for a GoldenGate admin schema. conn / as sysdba show con_name CON_NAME ------------------------------ CDB$ROOT CREATE PROFILE ggadmin LIMIT PASSWORD_GRACE_TIME 10 PASSWORD_REUSE_TIME 9999 PASSWORD_REUSE_MAX 1 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;CREATE PROFILE ggadmin LIMIT * ERROR at line 1: ORA-65140: invalid common profile name If we move the session from CDB$ROOT to a PDB the profile is created with the legacy name. ALTER SESSION SET container=PDBDEV; Session altered. CON_NAME ------------------------------ PDBDEV CREATE PROFILE ggadmin LIMIT PASSWORD_GRACE_TIME 10 PASSWORD_REUSE_TIME 9999 PASSWORD_REUSE_MAX 1 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION ora12c_verify_function; Profile created. In the root container you must use the container prefix which defaults to C## at installation. ALTER SESSION SET container=CDB$ROOT; Session altered. CREATE PROFILE c##ggadmin LIMIT PASSWORD_GRACE_TIME 10 PASSWORD_REUSE_TIME 9999 PASSWORD_REUSE_MAX 1 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION ora12_stig_verify_function; Profile created. SELECT profile#, name AS PROFILE_NAME, DECODE(flags, 0, 'Oracle Built-in', 1, 'Common', 2, 'App Common', 4, 'Implicit', flags) AS FLAG FROM profname$ ORDER BY name; PROFILE# PROFILE_NAME FLAG ---------- -------------------- -------------------- 3 C##GGADMIN Common 0 DEFAULT 8 1 ORA_CIS_PROFILE 8 2 ORA_STIG_PROFILE 8 Looking at the above listing you may be wondering what happened to the GGADMIN profile created just a few minutes earlier in PDBDEV. Check the PROFNAME$ table in that PDB. Data Dictionary tables do not have a con_id column identifying the container as do the CDB_ view. SELECT DISTINCT con_id, profile FROM cdb_profiles ORDER BY 1,2; CON_ID PROFILE ------- ----------------- 1 C##GGADMIN 1 DEFAULT 1 ORA_STIG_PROFILE 3 C##GGADMIN 3 DEFAULT 3 GGADMIN 3 ORABASE_DBA 3 ORA_STIG_PROFILE

Type 0, Kernel Resources col name format a30 SELECT name, resource# FROM resource_map WHERE type# = 0 ORDER BY 1; NAME RESOURCE# --------------------------- --------- COMPOSITE_LIMIT 0 CONNECT_TIME 7 CPU_PER_CALL 3 CPU_PER_SESSION 2 IDLE_TIME 6 LOGICAL_READS_PER_CALL 5 LOGICAL_READS_PER_SESSION 4 PRIVATE_SGA 8 SESSIONS_PER_USER 1

COMPOSITE_LIMIT Maximum weighted sum of: CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. If this limit is exceeded, Oracle aborts the session and returns an error.

composite_limit <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT composite_limit 5000000;

CONNECT_TIME Allowable connect time per session in minutes

connect_time <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT connect_time 600;

CPU_PER_CALL Maximum CPU time per call (100ths of a second)

cpu_per_call <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT cpu_per_call 3000;

CPU_PER_SESSION Maximum CPU time per session (100ths of a second)

cpu_per_session <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT cpu_per_session UNLIMITED;

IDLE_TIME Allowed idle time before user is disconnected (minutes)

idle_time <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT idle_time 20;

LOGICAL_READS_PER_CALL Maximum number of database blocks read per call

logical_reads_per_call <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT logical_reads_per_call 1000;

LOGICAL_READS_PER_SESSION Maximum number of database blocks read per session

logical_reads_per_session <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT logical_reads_per_session UNLIMITED;

Type 1 Password Resources SQL> col name format a30 SQL> SELECT name, resource# 2 FROM resource_map 3 WHERE type# = 0 4* ORDER BY 1; NAME RESOURCE# --------------------------- --------- FAILED_LOGIN_ATTEMPTS 0 INACTIVE_ACCOUNT_TIME 7 PASSWORD_GRACE_TIME 6 PASSWORD_LIFE_TIME 1 PASSWORD_LOCK_TIME 5 PASSWORD_REUSE_MAX 3 PASSWORD_REUSE_TIME 2 PASSWORD_VERIFY_FUNCTION 4

FAILED_LOGIN_ATTEMPTS
The number of failed attempts to log in to the user account before the account is locked (new 12.2) failed_login_attempts <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT failed_login_attempts 3;

      

      -- to count failed log in attempts:

      SELECT name, lcount

      FROM user$

      WHERE lcount <> 0;

INACTIVE_ACCOUNT_TIME

The number of days of non-use before an account is automatically locked Starting with this release, you can use the INACTIVE_ACCOUNT_TIME parameter to automatically lock the account of a database user who has not logged in to the database instance in a specified number of days

idle_time <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT inactive_account_time 35;

PASSWORD_GRACE_TIME
The number of days during which a login is allowed but a warning is issued password_gracetime <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT password_grace_time 10;

PASSWORD_LIFE_TIME
The number of days the same password can be used for authentication password_life_time <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT password_life_time 60;

PASSWORD_LOCK_TIME
The number of days an account will be locked after the specified number of consecutive failed login attempts defined password_lock_time <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT password_lock_time 30;

PASSWORD_REUSE_MAX
Times a password can be reused password_reuse_max <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT password_reuse_max 99;

PASSWORD_REUSE_TIME
Days between password reuses password_reuse_time <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT password_reuse_time 9999;

PASSWORD_ROLLOVER_TIME
Days during which both old and new passwords are valid (new 21c) password_rollover_time <value | DEFAULT>

ALTER PROFILE default LIMIT password_rollover_time 
      0;

PASSWORD_VERIFY_FUNCTION
Enforces password complexity password_reuse_time <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer develop ZZYZXpassword_verify_function 9999;

Password Verification

Sample script for creating a password verify function {ORACLE_HOME}/rdbms/admin/utlpwdmg.sql
Note that the 12c password verify function has a different name and enhanced functionality

PASSWORD_VERIFY_FUNCTION
Verifies a passwords for length, content, and complexity password_verify_function <function_name | NULL | DEFAULT>

ALTER PROFILE developer LIMIT

      password_verify_function uw_pwd_verification;

Changing passwords with a password verify function The function requires the old and new passwords so password changes can not be done with ALTER USER. Password changes should be performed with the SQL*Plus PASSWORD command or through a stored procedure that requires the correct inputs.

Password Verify Functions

Note Oracle 12.2-19 come with 3 different password verify functions with DDL in $ORACLE_HOME/rdbms/admin/catpvf.sql. While other versions exist in 12c the only function worth using is the name named ora12c_stig_verify_function.

ora_complexity_check

If not null, each of the following parameters specifies the minimum number of characters of the corresponding type.

chars - All characters (i.e. string length)
letter - Alphabetic characters A-Z and a-z
upper - Uppercase letters A-Z
lower - Lowercase letters a-z
digit - Numeric characters 0-9
special - All characters not in A-Z, a-z, 0-9 except double quote which is a password delimiter

ora_string_distance Calculates the Levenshtein distance between two strings 's' and 't'.

The Levenshtein distance between two words is the minimum number of single-character edits (insertion, deletion, substitution) required to change one word into the other.

ora12c_stig_verify_function This function is provided to give stronger password complexity function that would take into consideration recommendations from Department of Defense Database Security Technical Implementation Guide (STIG) v1 r2 released on 22-Jan-2016.

ora12c_strong_verify_function Provided from 12c onwards for stringent password check requirements

ora12c_verify_function Makes the minimum complexity checks like the minimum length of the password, password not same as the username, etc. The user may enhance this function according to the need. This function must be created in SYS schema. connect sys/<password> as sysdba before running the script

verify_function Sets the default password resource parameters. This script needs to be run to enable the password features. However the default resource parameters can be changed based on the need. A default password complexity function is also provided. This function makes the minimum complexity checks like the minimum length of the password, password not same as the username, etc. The user may enhance this function according to the need.
This function must be created in SYS schema.
connect sys/<password> as sysdba before running the script

verify_function_11g Makes the minimum complexity checks such as the minimum length of the password, password not same as the username, etc. Oracle says the user may enhance this function according to their need. But it is of minimum value and should be ignored. Customize the strongest, not the weakest, to get maximum value for your effort.

Profile Security Enahncements

If what you are looking for is security significantly better than what Oracle provides then there are two places in the profile you should consider as you limit what users can do. The first is a guidance, not finished code, to improve the Password Verification function by adding a dictionary of real words. The second is based on the fact that end-users that logged in through a website should never be able to grab such a large percentage of available resources that they can either access more data than they could reasonably require or could create a DDOS attack.

Consider the following scenario with respect to using the Profile to limit resources. Assume you have a table with 143,000,000 records related to credit cards. Assume further a member of the public is logging into your database to look at their own information. Do they need to be able to return a result set of 143,000,000 rows? Do they need to return a query result of even 100 rows? Talk to subject matter experts in your organization and find out the maximum amount of data an individual accessing your application could legitimately ask for. Then test what that means in terms of SGA and Logical I/O. Then limit that PROFILE accordingly.

Search for common words embedded in passwords -- Step 1: download words.txt from https://github.com/dwyl/english-words We stored it in a directory pointed to by the database directory object ctemp. CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp'; CREATE TABLE words_xtab ( wordtext VARCHAR2(50)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY ctemp ACCESS PARAMETERS ( RECORDS DELIMITED BY 0x'0a' CHARACTERSET WE8ISO8859P1 FIELDS TERMINATED BY '|' MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS (wordtext)) LOCATION('words.txt')) -- Step 2: Load the external table into an Oracle table using an external table -- and make the internal table case insensitive CREATE TABLE verify_words AS SELECT lower(wordtext) FROM words_xtab; DELETE FROM verify_words WHERE length(wordtext) < 3; DELETE FROM verify_words WHERE INSTR(wordtext, '.', 1, 1) <> 0; COMMIT; -- Step 3: Drop the external table and delete the file DROP TABLE words_xtab; CREATE OR REPLACE FUNCTION ora12c_dbswx_verify_function(username IN VARCHAR2, password IN VARCHAR2, old_password IN VARCHAR2) RETURN BOOLEAN AUTHID DEFINER IS differ INTEGER; BEGIN IF NOT ora_complexity_check(password, chars => 15, upper => 1, lower => 1, digit => 1, special => 1) THEN RETURN(FALSE); END IF; IF old_password IS NOT NULL THEN differ := ora_string_distance(old_password, password); IF differ < 8 THEN RAISE_APPLICATION_ERROR(-20033, 'New password must significantly differ from the previous password'); END IF; END IF; BEGIN -- assign to a variable the new password stripped of numbers and special characters -- convert the "stripped" new password to lower case -- test the "stripped" new password against the words in the verify_words table -- test again using the reverse function against the words in the verify_words table -- assign the new password to a variable after converting numbers to similar letters -- also convert special characters to most likely substitutions, ie "!" to 1 -- also removing any special character at the far right end of the string -- test as above -- return FALSE or RAISE an exception if a match is found END; RETURN TRUE; END ora12c_dbsxw_verify_function; / GRANT EXECUTE ON ora12c_dbsxw_verify_function TO PUBLIC container=current; -- as much as we dislike grants to PUBLIC this is an exception

Limiting resources so that a query cannot return more data than is legitimately required conn sys@pdbdev as sysdba SQL> SELECT profile 2 FROM dba_users 3* WHERE username = 'UWCLASS'; PROFILE -------- DEFAULT col resource_name format a26 col limit format a11 SQL> SELECT resource_name, limit 2 FROM dba_profiles 3 WHERE profile = 'DEFAULT' 4 AND resource_name LIKE 'LOGICAL%' 5 ORDER BY 1; RESOURCE_NAME LIMIT -------------------------- ----------- LOGICAL_READS_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED SQL> ALTER PROFILE DEFAULT LIMIT 2 LOGICAL_READS_PER_CALL 100; Profile altered. conn uwclass/uwclass@pdbdev SQL> SELECT COUNT(*) FROM airplanes; SELECT COUNT(*) FROM airplanes * ERROR at line 1: ORA-02395: exceeded call limit on IO usage SQL> SELECT COUNT(*) FROM airplanes 2* WHERE rownum < 37500; COUNT(*) ---------- 37499 -- Note: An exception is not raised when a smaller number of rows is accessed. -- 100 reads per call limits rows retrieved fewer than 37749 for this statement. -- assuming a hacker was trying to steal 145,000,000 rows to do so with this -- limit would take 3,840+ statements uniquely crafted statements. -- If we reduce logical reads further the number of statements required to steal -- the data increases as does the ability to catch the thief. -- Add to this other profile capabilities, Row Level ecurity, and new 12c row -- limiting capabilities and an Experian-type breach can be rendered impossible -- from the practical standpoint.

Create Profiles

View existing profile col profile format a20 col limit format a20 SQL> SELECT profile, resource_name, limit 2 FROM dba_profiles 3* ORDER BY profile, resource_name; PROFILE RESOURCE_NAME LIMIT -------------------- -------------------------------- -------------------- DEFAULT COMPOSITE_LIMIT UNLIMITED DEFAULT CONNECT_TIME UNLIMITED DEFAULT CPU_PER_CALL UNLIMITED DEFAULT CPU_PER_SESSION UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS 10 DEFAULT IDLE_TIME UNLIMITED DEFAULT INACTIVE_ACCOUNT_TIME UNLIMITED DEFAULT LOGICAL_READS_PER_CALL UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION UNLIMITED DEFAULT PASSWORD_GRACE_TIME 7 DEFAULT PASSWORD_LIFE_TIME 180 DEFAULT PASSWORD_LOCK_TIME 1 DEFAULT PASSWORD_REUSE_MAX UNLIMITED DEFAULT PASSWORD_REUSE_TIME UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION NULL DEFAULT PRIVATE_SGA UNLIMITED DEFAULT SESSIONS_PER_USER UNLIMITED

Create Center for Internet Security (CIS) profile CREATE PROFILE <profile_name> LIMIT <profile_item_name> <value> <profile_item_name> <value> ... [CONTAINER = <CURRENT | ALL>;

-- log into cdb$root

      SQL conn / as sysdba

      

      SQL> CREATE PROFILE ggadmin LIMIT

       
      2  PASSWORD_GRACE_TIME 10

       
      3  PASSWORD_REUSE_TIME 9999

       
      4  PASSWORD_REUSE_MAX 1

       
      5  FAILED_LOGIN_ATTEMPTS 3

       
      6  PASSWORD_LOCK_TIME 1

        7  PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

      CREATE PROFILE ggadmin LIMIT

      *

      ERROR at line 1:

      ORA-65140: invalid common profile name

      

      SQL> ed

      Wrote file afiedt.buf

      

        1  CREATE PROFILE 
      c##ggadmin LIMIT

       
      2  PASSWORD_GRACE_TIME 10

       
      3  PASSWORD_REUSE_TIME 9999

       
      4  PASSWORD_REUSE_MAX 1

       
      5  FAILED_LOGIN_ATTEMPTS 3

       
      6* PASSWORD_LOCK_TIME 1

        7  PASSWORD_VERIFY_FUNCTION ora12c_verify_function

      SQL> /

      

      Profile created.

      

      -- log into a pdb

      SQL> conn sys@pdbdev as sysdba

      Enter password:

      Connected.

      

      SQL> CREATE PROFILE ggadmin LIMIT

        2  PASSWORD_GRACE_TIME 10

        3  PASSWORD_REUSE_TIME 9999

        4  PASSWORD_REUSE_MAX 1

        5  FAILED_LOGIN_ATTEMPTS 3

        6  PASSWORD_LOCK_TIME 1

        7  PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

      

      Profile created.

Alter Profile

Alter profile syntax ALTER PROFILE <profile_name> LIMIT <profile_item_name> <value>;

ALTER PROFILE developer LIMIT FAILED_LOGIN_ATTEMPTS 3;

Modify Oracle 12.2 default profile for Center for Internet Security (CIS) ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 3 PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 20 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

Modify Oracle 12.2 default profile for DOD Security Technical Implementation Guidelines (STIG)

The STIG profile is created as a local object with container = current. Exception is made in PDB code similar to the DEFAULT profile to make sure the STIG profile is created in every container during DB creation time. ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 60 PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 5 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

Assign Profile

Assign During User Creation CREATE USER <user_name> IDENTIFIED BY <password> PROFILE <profile_name>;

CREATE USER uwclass

      IDENTIFIED BY "N0Way!"

      DEFAULT TABLESPACE uwdata

      TEMPORARY TABLESPACE temp

      QUOTA 0 ON SYSTEM

      QUOTA 0 ON SYSAUX

      QUOTA UNLIMITED ON uwdata

      QUOTA 10M ON indx_sml

      PROFILE developer;

Assign Profile After User Creation ALTER USER <user_name> PROFILE <profile_name>;

ALTER USER uwclass PROFILE developer;

Drop Profile

Drop Profile without Users DROP PROFILE <profile_name>;

DROP PROFILE developer;

Drop Profile with associated Users DROP PROFILE <profile_name> CASCADE;

DROP PROFILE developer CASCADE;