Oracle PROFILES
Versions All

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.

SQL> SQL> SELECT username, profile, created
  2  FROM dba_users
  3  ORDER BY 1;

USERNAME                       PROFILE  CREATED
------------------------------ -------- ------------------- --------------------
ANONYMOUS                      DEFAULT  30-MAR-2018 22:11:37
APPQOSSYS                      DEFAULT  30-MAR-2018 22:07:26
AUDSYS                         DEFAULT  30-MAR-2018 21:38:07
CTXSYS                         DEFAULT  30-MAR-2018 23:21:00
DBSFWUSER                      DEFAULT  30-MAR-2018 21:47:23
DBSNMP                         DEFAULT  30-MAR-2018 22:07:22
DIP                            DEFAULT  30-MAR-2018 21:46:10
DVF                            DEFAULT  31-MAR-2018 00:05:28
DVSYS                          DEFAULT  31-MAR-2018 00:05:28
GGSYS                          DEFAULT  30-MAR-2018 22:08:07
GSMADMIN_INTERNAL              DEFAULT  30-MAR-2018 21:45:54
GSMCATUSER                     DEFAULT  30-MAR-2018 22:08:01
GSMUSER                        DEFAULT  30-MAR-2018 21:45:54
LBACSYS                        DEFAULT  31-MAR-2018 00:03:24
MDDATA                         DEFAULT  30-MAR-2018 23:46:34
MDSYS                          DEFAULT  30-MAR-2018 23:27:47
OJVMSYS                        DEFAULT  30-MAR-2018 23:03:31
OLAPSYS                        DEFAULT  30-MAR-2018 23:44:09
ORACLE_OCM                     DEFAULT  30-MAR-2018 21:49:39
ORDDATA                        DEFAULT  30-MAR-2018 23:27:46
ORDPLUGINS                     DEFAULT  30-MAR-2018 23:27:46
ORDSYSv                        DEFAULT  30-MAR-2018 23:27:46
OUTLN                          DEFAULT  30-MAR-2018 21:38:11
REMOTE_SCHEDULER_AGENT         DEFAULT  30-MAR-2018 21:47:22
SI_INFORMTN_SCHEMA             DEFAULT  30-MAR-2018 23:27:47
SYS                            DEFAULT  30-MAR-2018 21:38:07
SYS$UMF                        DEFAULT  30-MAR-2018 21:59:02
SYSBACKUP                      DEFAULT  30-MAR-2018 21:38:07
SYSDG                          DEFAULT  30-MAR-2018 21:38:07
SYSKM                          DEFAULT  30-MAR-2018 21:38:07
SYSRAC                         DEFAULT  30-MAR-2018 21:38:07
SYSTEM                         DEFAULT  30-MAR-2018 21:38:07
WMSYS                          DEFAULT  30-MAR-2018 22:51:33
XDB                            DEFAULT  30-MAR-2018 22:11:37
XS$NULL                        DEFAULT  30-MAR-2018 21:47:10

42 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_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 ... let's examine what the new user can do. 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
  • Can consume an unrestricted amount of the SGA
  • Can login again and again and create as many simultaneous sessions as they wish, all 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
Now that no users connecting to the database 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 breach.
The following list isn't intended to be complete but will illustrate the 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_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION
 16  PRIVATE_SGA 1
 17* 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 a20

SQL> SELECT profile#, name AS PROFILE_NAME,
     DECODE(flags,  0, 'Oracle Built-in',
                    1, 'Common',
                    2, 'App Common',
                    4, 'Implicit',
                    flags) AS FLAG
  2  FROM profname$
  3  ORDER BY name;

 PROFILE#  PROFILE_NAME          FLAGS
---------- -------------------- ----------------------------------------
         0 DEFAULT              Oracle Built-in
         1 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.

SQL> conn / as sysdba

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

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


If we move the session from CDB$ROOT to a PDB the profile is created with the legacy name.

SQL> ALTER SESSION SET container=PDBDEV;

Session altered.

CON_NAME
------------------------------
PDBDEV

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.

In the root container you must use the container prefix which defaults to C## at installation.

SQL> ALTER SESSION SET container=CDB$ROOT;

Session altered.

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.

SQL> SELECT profile#, name AS PROFILE_NAME,
     DECODE(flags,  0, 'Oracle Built-in',
                    1, 'Common',
                    2, 'App Common',
                    4, 'Implicit',
                    flags) AS FLAG
  2  FROM profname$
  3  ORDER BY name;

 PROFILE# PROFILE_NAME          FLAG
---------- -------------------- --------------------
         3 C##GGADMIN           Common
         0 DEFAULT              Oracle Built-in
         1 ORA_STIG_PROFILE     Oracle Built-in

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.



SQL> SELECT DISTINCT con_id, profile
2 FROM cdb_profiles
3* ORDER BY 1,2
SQL> /

 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 SQL> col name format a30

SQL> SELECT name, resource#
  2  FROM resource_map
  3  WHERE type# = 0
  4* 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
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_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