Oracle Profiles
Version 18.1.0.0

General Information
Library Note Morgan's Library Page Header
"We don't live in a dictatorship or a monarchy. I swore an oath in the military and in the Senate to preserve, protect and defend the Constitution of the United States, not to mindlessly cater to the whims of Cadet Bone Spurs and clap when he demands I clap,"
~ Sen. Tammy Duckworth
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 Resrouces 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 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;
INACTIVE_ACCOUNT_TIME 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;
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;
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 locked
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
inactive_account_time <value | UNLIMITED | DEFAULT>
ALTER PROFILE developer LIMIT inactive_account_time 15;
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
FAILED_LOGIN_ATTEMPTS

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 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 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;
 
Password Verify Functions
Note Version 12.2 comes with 3 different password verify functions implemented 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 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.

Related Topics
Built-in Packages
Built-in Functions
Consumer Groups
Fine Grained Access Control
Product User Profiles
Roles
Security
Users
What's New In 12cR2
What's New In 18cR1

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved