Oracle | Profiles

Oracle Profiles Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Profiles are associated with individual users and define two different categories of behaviour. The first is Kernel Resources which allow a definition of resource availability to be defined across a category consisting of one or more user schemas. The second category is Password Resources and allows for a specific password behaviour to be assigned to a group of one or more user schemas.

Dependencies

CDB_PROFILES	PROFILE$	PROFNAME$
DBA_PROFILES

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

System Privileges

ALTER PROFILE

CREATE PROFILE

DROP PROFILE

RESOURCE_LIMIT=TRUE is required for resource limiting portions of the profile. Password limiting functionality is not affected by this parameter. resource_limit = TRUE

conn sys@pdbdev

      

      set linesize 121

      col name format a30

      col value format a30

      

      SELECT name, value

      FROM gv$parameter

      WHERE name = 'resource_limit';

      

      ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH;

      

      SELECT name, value

      FROM gv$parameter

      WHERE name = 'resource_limit';

Kernel Resources

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 minutesconnect_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 sessionlogical_reads_per_session <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT logical_reads_per_session UNLIMITED;

PRIVATE_SGA Maximum integer bytes of private space in the SGA (useful for systems using multi-threaded server MTS)

private_sga <value | UNLIMITED | DEFAULT> Only valid with TP-monitor

ALTER PROFILE developer LIMIT private_sga 15K;

SESSIONS_PER_USER Number of concurrent multiple sessions allowed per user

sessions_per_user <value | UNLIMITED | DEFAULT>

ALTER PROFILE developer LIMIT sessions_per_user 1;

Password Resources

FAILED_LOGIN_ATTEMPTS The number of failed attempts to log in to the user account before the account is lockedfailed_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 Automatically lock a database user account that has not logged in to the database in a specified number of days inactive_account_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 Number of 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
(new 21c) Days during which both old and new passwords are both valid. This works with passwords only: not certificates. password_rollover_time <value | DEFAULT>

ALTER PROFILE default LIMIT password_rollover_time 
      0;

Password Verification

Sample script for creating a password verify function {ORACLE_HOME}/rdbms/admin/utlpwdmg.sql
it is important to 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.

Create Profiles

View existing profile

The values highlighted in yellow have been reduced from Oracle 19c corresponding to a slight improvement in default security (based on OCI cloud with version 20.3).

In 19c FAILED_LOGIN_ATTEMPTS was 3, PASSWORD_LIFE_TIME was 180, PASSWORD_RESUSE_MAX and PASSWORD_REUSE_TIME were unlimited.

PASSWORD_ROLLOVER_TIME is new in 21c. col profile format a20 col limit format a20 SELECT profile, resource_name, limit FROM dba_profiles 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 3 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 60 DEFAULT PASSWORD_LOCK_TIME 1 DEFAULT PASSWORD_REUSE_MAX 5 DEFAULT PASSWORD_REUSE_TIME 365 DEFAULT PASSWORD_ROLLOVER_TIME 0 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

      conn / as sysdba

      

      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;

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

Password Verify Functions

Note As of version 12.2 comes with 3 different password verify functions implemented in $ORACLE_HOME/rdbms/admin/catpvf.sql.
While two other Password Verision functions exist in 12c+ the only function worth using is the name named ora12c_stig_verify_function because the STIG function is no more onerous than what a bank or credit company would expect for an online account.

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 like the minimum length of the password, password not same as the username, etc. The user may enhance this function according to the need.

Password verification related view SQL> desc ku$_pwdvfc_view Name Null? Type ------------- -------- ------------- VERS_MAJOR CHAR(1) VERS_MINOR CHAR(1) OBJ_NUM NUMBER TYPE_NUM NUMBER SCHEMA_OBJ KU$_SCHEMAOBJ_T SOURCE_LINES KU$_SOURCE_LIST_T SQL> SELECT * FROM ku$_pwdvfc_view; V V OBJ_NUM TYPE_NUM - - ---------- ---------- SCHEMA_OBJ(OBJ_NUM, DATAOBJ_NUM, OWNER_NUM, OWNER_NAME, NAME, NAMESPACE, SUBNAME, TYPE_NUM, TYPE_NAME, CTIME, MTIME, STIM ---------------------------------------------------------------------------------------- SOURCE_LINES(OBJ_NUM, LINE, PRE_NAME, POST_NAME_OFF, POST_KEYW, PRE_NAME_LEN, TRAILING_NL, POST_ATNAME_OFF, ATNAME_OFF, N ---------------------------------------------------------------------------------------- 1 1 18375 8 KU$_SCHEMAOBJ_T(18375, NULL, 0, 'SYS', 'ORA12C_STIG_VERIFY_FUNCTION', 1, NULL, 8, 'FUNCTION', '2018-02-07 19:41:06', '201 8-02-07 19:41:06', '2018-02-07 19:41:06', 1, NULL, NULL, 5308416, NULL, 6, 65535, 0, NULL, NULL, NULL, NULL, 'E1B7CA94F3F 114E80A8ADF9476707B50', 0, 0, 0, 'USING_NLS_COMP', NULL, NULL, NULL, NULL, NULL) KU$_SOURCE_LIST_T(KU$_SOURCE_T(18375, 1, 0, 37, 10, 0, NULL, 0, 0, 0, 'function ora12c_stig_verify_function '), KU$_SOURCE_T(18375, 2, 0, 0, 0, 0, NULL, 0, 0, 0, ' ( username varchar2, '), KU$_SOURCE_T(18375, 3, 0, 0, 0, 0, NULL, 0, 0, 0, ' password varchar2, '), KU$_SOURCE_T(18375, 4, 0, 0, 0, 0, NULL, 0, 0, 0, ' old_password varchar2) '), KU$_SOURCE_T(18375, 5, 0, 0, 0, 0, NULL, 0, 0, 0, ' return boolean IS '), KU$_SOURCE_T(18375, 6, 0, 0, 0, 0, NULL, 0, 0, 0, ' differ integer; '), KU$_SOURCE_T(18375, 7, 0, 0, 0, 0, NULL, 0, 0, 0, ' lang varchar2(512); '), KU$_SOURCE_T(18375, 8, 0, 0, 0, 0, NULL, 0, 0, 0, ' message varchar2(512); '), KU$_SOURCE_T(18375, 9, 0, 0, 0, 0, NULL, 0, 0, 0, ' ret number; '), KU$_SOURCE_T(18375, 10, 0, 0, 0, 0, NULL, 0, 0, 0, ' '), KU$_SOURCE_T(18375, 11, 0, 0, 0, 0, NULL, 0, 0, 0, 'begin '), KU$_SOURCE_T(18375, 12, 0, 0, 0, 0, NULL, 0, 0, 0, ' -- Get the cur context lang and use utl_lms for messages- Bug 22730089 '), KU$_SOURCE_T(18375, 13, 0, 0, 0, 0, NULL, 0, 0, 0, ' lang := sys_context(''userenv'',''lang''); '), KU$_SOURCE_T(18375, 14, 0, 0, 0, 0, NULL, 0, 0, 0, ' lang := substr(lang,1,instr(lang,''_'')-1); '), KU$_SOURCE_T(18375, 15, 0, 0, 0, 0, NULL, 0, 0, 0, ' '), KU$_SOURCE_T(18375, 16, 0, 0, 0, 0, NULL, 0, 0, 0, ' if not ora_complexity_check(password, chars => 15, upper => 1, lower => 1, '), KU$_SOURCE_T(18375, 17, 0, 0, 0, 0, NULL, 0, 0, 0, ' digit => 1, special => 1) then '), KU$_SOURCE_T(18375, 18, 0, 0, 0, 0, NULL, 0, 0, 0, ' return(false); '), KU$_SOURCE_T(18375, 19, 0, 0, 0, 0, NULL, 0, 0, 0, ' end if; '), KU$_SOURCE_T(18375, 20, 0, 0, 0, 0, NULL, 0, 0, 0, ' '), KU$_SOURCE_T(18375, 21, 0, 0, 0, 0, NULL, 0, 0, 0, ' -- Check if the password differs from the previous password by at least '), KU$_SOURCE_T(18375, 22, 0, 0, 0, 0, NULL, 0, 0, 0, ' -- 8 characters '), KU$_SOURCE_T(18375, 23, 0, 0, 0, 0, NULL, 0, 0, 0, ' if old_password is not null then '), KU$_SOURCE_T(18375, 24, 0, 0, 0, 0, NULL, 0, 0, 0, ' differ := ora_string_distance(old_password, password); '), KU$_SOURCE_T(18375, 25, 0, 0, 0, 0, NULL, 0, 0, 0, ' if differ < 8 then '), KU$_SOURCE_T(18375, 26, 0, 0, 0, 0, NULL, 0, 0, 0, ' ret := utl_lms.get_message(28211, ''RDBMS'', ''ORA'', la ng, message); '), KU$_SOURCE_T(18375, 27, 0, 0, 0, 0, NULL, 0, 0, 0, ' raise_application_error(-20000, utl_lms.format_message(m essage, ''eight'')); '), KU$_SOURCE_T(18375, 28, 0, 0, 0, 0, NULL, 0, 0, 0, ' end if; '), KU$_SOURCE_T(18375, 29, 0, 0, 0, 0, NULL, 0, 0, 0, ' end if; V V OBJ_NUM TYPE_NUM - - ---------- ---------- SCHEMA_OBJ(OBJ_NUM, DATAOBJ_NUM, OWNER_NUM, OWNER_NAME, NAME, NAMESPACE, SUBNAME, TYPE_NUM, TYPE_NAME, CTIME, MTIME, STIM ---------------------------------------------------------------------------------------- SOURCE_LINES(OBJ_NUM, LINE, PRE_NAME, POST_NAME_OFF, POST_KEYW, PRE_NAME_LEN, TRAILING_NL, POST_ATNAME_OFF, ATNAME_OFF, N ---------------------------------------------------------------------------------------- '), KU$_SOURCE_T(18375, 30, 0, 0, 0, 0, NULL, 0, 0, 0, ' '), KU$_SOURCE_T(18375, 31, 0, 0, 0, 0, NULL, 0, 0, 0, ' return(true); '), KU$_SOURCE_T(18375, 32, 0, 0, 0, 0, 'N', 0, 0, 0, 'end;'))

Password Verify Functions

Note If what you are looking for is "real" security then there are three places where you need to intersect your activities with your profile. The three solutions are listed below.

First: Tighten the password verification function to eliminate common words by integrating your Microsoft Word dictionary as shown here. This sample is based upon the ORA12C_STIG_VERIFY function.

-- Step 1: download words.txt from https://github.com/dwyl/english-words

                 I 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 * 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_morgan_verify_function(username VARCHAR2, password VARCHAR2, old_password 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, 'The new password must significantly differ from the previous password');

          END IF;

        END IF;

        BEGIN

          SELECT COUNT(*)

          INTO differ

          FROM verify_words

          WHERE wordtext = lower(password);

      

          IF differ <> 0 THEN

            RAISE_APPLICATION_ERROR(-20034, 'The new password must be not be based on any google searchable string');

          END IF;

        END;

      

        RETURN TRUE;

      END ora12c_morgan_verify_function;

      /

      

      GRANT EXECUTE ON ora12c_morgan_verify_function TO PUBLIC container=current;

      

      

      DECLARE

       TYPE myarray IS TABLE OF verify_words%ROWTYPE;

       l_data myarray;

      

       CURSOR r IS

       SELECT wordtext

       FROM verify_words;

      

       batchSize CONSTANT POSITIVE := 2500;

      BEGIN

        OPEN r;

        LOOP

          FETCH r BULK COLLECT INTO l_data LIMIT BatchSize;

      

          FOR j IN 1 .. l_data.COUNT LOOP

            IF regexp_count('UWCLASS', l_data(j).wordtext, 1, 'i') <> 0 THEN

              dbms_output.put_line(l_data(j).wordtext);

            END IF;

          END LOOP;

          EXIT WHEN l_data.COUNT < batchSize;

        END LOOP;

        CLOSE r;

      END;

      /

Second: Eliminate use of the DEFAULT profile. Profiles should be custom crafted based upon the work that a user or mechid needs to perform. For the highest security start with the MORGAN profile at right. ALTER PROFILE DEFAULT LIMIT CONNECT_TIME 1 CPU_PER_CALL 1 CPU_PER_SESSION 1 FAILED_LOGIN_ATTEMPTS 1 IDLE_TIME 1 INACTIVE_ACCOUNT_TIME 1 LOGICAL_READS_PER_CALL 1 LOGICAL_READS_PER_SESSION 1 PASSWORD_GRACE_TIME 1 PASSWORD_LIFE_TIME 1 PASSWORD_LOCK_TIME UNLIMITED PASSWORD_REUSE_MAX 1 PASSWORD_REUSE_TIME 9999 PASSWORD_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION PRIVATE_SGA 1 SESSIONS_PER_USER 1 CREATE PROFILE MORGAN LIMIT COMPOSITE_LIMIT UNLIMITED CONNECT_TIME 600 CPU_PER_CALL UNLIMITED CPU_PER_SESSION UNLIMITED FAILED_LOGIN_ATTEMPTS 3 IDLE_TIME 15 INACTIVE_ACCOUNT_TIME 15 LOGICAL_READS_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED PASSWORD_GRACE_TIME 7 PASSWORD_LIFE_TIME 60 PASSWORD_LOCK_TIME UNLIMITED PASSWORD_REUSE_MAX 1 PASSWORD_REUSE_TIME 9999 PASSWORD_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION PRIVATE_SGA UNLIMITED SESSIONS_PER_USER 1

Third: Limit resources so that a query cannot return more data than is legitimately required to perform a specific job. The number of times the job requirement is literally, "must be able to, in a single query, read every row in every table" is vanishingly small. 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 SQL> SELECT COUNT(*) FROM airplanes 2* WHERE rownum < 37750; SELECT COUNT(*) FROM airplanes * ERROR at line 1: ORA-02395: exceeded call limit on IO usage -- 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. 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, DBMS_RLS, and new 12c row limiting -- capabilities and an Experian-type experience can be rendered essentially -- impossible for the vast majority of database logins.