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

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

Related Topics
GLOGIN Exploit
Product User Profiles
Roles
Users