Note_Tech

All technological notes.

Project maintained by simonangel-fong Hosted on GitHub Pages — Theme by mattgraham

DBA - User: User Profile

Back

User Profile

user_profile_diagram01

Common Profile vs Local Profile

connect / as sysdba;
create profile c##cprofile_dev
limi ...
CONTAINER=ALL;

CONNECT sys@pdb_name AS SYSDBA;
create profile c##cprofile_dev
limi ...

Assigning Profiles

connect / as sysdba
ALTER USER common_user  PROFILE common_profile CONTAINER ALL;

connect sys@PDB_name AS SYSDBA;
ALTER USER common_user/local_user  PROFILE common_profile/local_profile;

Password Parameters

password_parameter_diagram

Account Loking

Password aging and expiration

Password History

Password complexity verification

Oracle-Supplied Password Verfication Functions

verify_function_11G Function

ora12c_verify_function Function

ora12c_strong_verify_function Function

Resource Parameters

CREATE PROFILE app_user LIMIT
   SESSIONS_PER_USER          UNLIMITED
   CPU_PER_SESSION            UNLIMITED
   CPU_PER_CALL               3000
   CONNECT_TIME               45
   LOGICAL_READS_PER_SESSION  DEFAULT
   LOGICAL_READS_PER_CALL     1000
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000;

Lab: Default Profile in root

show con_name;
--CON_NAME
--------------------------------
--CDB$ROOT

-- Query default profile
select * from dba_PROFILES
where PROFILE='DEFAULT';
--PROFILE,  RESOURCE_NAME,              RESOURCE_TYPE,  LIMIT,      COMMON,     INHERITED,  IMPLICIT
--DEFAULT	COMPOSITE_LIMIT             KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	SESSIONS_PER_USER           KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	CPU_PER_SESSION             KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	CPU_PER_CALL                KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	LOGICAL_READS_PER_SESSION   KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	LOGICAL_READS_PER_CALL      KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	IDLE_TIME                   KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	CONNECT_TIME                KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	PRIVATE_SGA                 KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	FAILED_LOGIN_ATTEMPTS       PASSWORD        10	        NO          NO	        NO
--DEFAULT	PASSWORD_LIFE_TIME          PASSWORD        180	        NO          NO	        NO
--DEFAULT	PASSWORD_REUSE_TIME         PASSWORD        UNLIMITED	NO          NO	        NO
--DEFAULT	PASSWORD_REUSE_MAX          PASSWORD        UNLIMITED	NO          NO	        NO
--DEFAULT	PASSWORD_VERIFY_FUNCTION    PASSWORD        NULL        NO          NO	        NO
--DEFAULT	PASSWORD_LOCK_TIME          PASSWORD        1	        NO          NO	        NO
--DEFAULT	PASSWORD_GRACE_TIME         PASSWORD        7	        NO          NO	        NO
--DEFAULT	INACTIVE_ACCOUNT_TIME       PASSWORD        UNLIMITED	NO          NO	        NO

Lab: Create a Common Profile

show con_name;
--CON_NAME
--------------------------------
--CDB$ROOT

-- create common profile
create profile C##GENERAL limit
    SESSIONS_PER_USER 4
    IDLE_TIME 15
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LIFE_TIME 180
    container=all;

-- confirm the profile
select * from DBA_PROFILES
where PROFILE= upper('C##GENERAL')
order by limit;
--PROFILE,      RESOURCE_NAME,              RESOURCE_TYPE,  LIMIT,  COMMON, INHERITED,  IMPLICIT
--C##GENERAL	IDLE_TIME                   KERNEL          15      YES     NO          NO
--C##GENERAL	PASSWORD_LIFE_TIME          PASSWORD        180     YES     NO          NO
--C##GENERAL	FAILED_LOGIN_ATTEMPTS       PASSWORD        3       YES     NO          NO
--C##GENERAL	SESSIONS_PER_USER           KERNEL          4       YES     NO          NO
--C##GENERAL	LOGICAL_READS_PER_CALL      KERNEL          DEFAULT YES     NO          NO
--C##GENERAL	CONNECT_TIME                KERNEL          DEFAULT YES     NO          NO
--C##GENERAL	INACTIVE_ACCOUNT_TIME       PASSWORD        DEFAULT YES     NO          NO
--C##GENERAL	PASSWORD_REUSE_TIME         PASSWORD        DEFAULT YES     NO          NO
--C##GENERAL	PASSWORD_REUSE_MAX          PASSWORD        DEFAULT YES     NO          NO
--C##GENERAL	PASSWORD_VERIFY_FUNCTION    PASSWORD        DEFAULT YES     NO          NO
--C##GENERAL	PASSWORD_LOCK_TIME          PASSWORD        DEFAULT YES     NO          NO
--C##GENERAL	PASSWORD_GRACE_TIME         PASSWORD        DEFAULT YES     NO          NO
--C##GENERAL	LOGICAL_READS_PER_SESSION   KERNEL          DEFAULT YES     NO          NO
--C##GENERAL	CPU_PER_CALL                KERNEL          DEFAULT YES     NO          NO
--C##GENERAL	CPU_PER_SESSION             KERNEL          DEFAULT YES     NO          NO
--C##GENERAL	COMPOSITE_LIMIT             KERNEL          DEFAULT YES     NO          NO
--C##GENERAL	PRIVATE_SGA                 KERNEL          DEFAULT YES     NO          NO

-- Create a common user
create user c##lord identified by lord container=all;
grant create session, create table, unlimited tablespace to c##lord container=all;


-- confirm user has been created
SELECT
    username
    , account_status
    , created
    , profile
    , authentication_type
    , common
    , default_tablespace
    , temporary_tablespace
FROM DBA_users
WHERE USERNAME=upper('c##lord');
--C##LORD	OPEN	12-Mar-2024	DEFAULT	PASSWORD	YES	USERS	TEMP


show parameter RESOURCE_LIMIT;
--NAME           TYPE    VALUE
---------------- ------- -----
--resource_limit boolean TRUE

alter user c##lord profile C##GENERAL container=all;

-- confirm
SELECT
    username
    , account_status
    , created
    , profile
    , authentication_type
    , common
    , default_tablespace
    , temporary_tablespace
FROM DBA_users
WHERE USERNAME=upper('c##lord');
--C##LORD	OPEN	12-Mar-2024	C##GENERAL	PASSWORD	YES	USERS	TEMP

Test resource parameters

lab_common_profile01

Test password parameters

lab_common_profile01

connect sys as sysdba
alter user c##lord account unlock;

connect c##lord/lord

lab_common_profile01

Lab: Default profile in pdb

show con_name;
--CON_NAME
--------------------------------
--CDB$ROOT

alter session set container=orclpdb;

show con_name;
--CON_NAME
--------------------------------
--ORCLPDB

-- Query local default profile
select * from dba_PROFILES
where PROFILE='DEFAULT';
--PROFILE,  RESOURCE_NAME,              RESOURCE_TYPE,  LIMIT,      COMMON,     INHERITED,  IMPLICIT
--DEFAULT	COMPOSITE_LIMIT             KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	SESSIONS_PER_USER           KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	CPU_PER_SESSION             KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	CPU_PER_CALL                KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	LOGICAL_READS_PER_SESSION   KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	LOGICAL_READS_PER_CALL      KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	IDLE_TIME                   KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	CONNECT_TIME                KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	PRIVATE_SGA                 KERNEL          UNLIMITED	NO          NO	        NO
--DEFAULT	FAILED_LOGIN_ATTEMPTS       PASSWORD        10	        NO          NO	        NO
--DEFAULT	PASSWORD_LIFE_TIME          PASSWORD        180	        NO          NO	        NO
--DEFAULT	PASSWORD_REUSE_TIME         PASSWORD        UNLIMITED	NO          NO	        NO
--DEFAULT	PASSWORD_REUSE_MAX          PASSWORD        UNLIMITED	NO          NO	        NO
--DEFAULT	PASSWORD_VERIFY_FUNCTION    PASSWORD        NULL        NO          NO	        NO
--DEFAULT	PASSWORD_LOCK_TIME          PASSWORD        1	        NO          NO	        NO
--DEFAULT	PASSWORD_GRACE_TIME         PASSWORD        7	        NO          NO	        NO
--DEFAULT	INACTIVE_ACCOUNT_TIME       PASSWORD        UNLIMITED	NO          NO	        NO

Lab: Create a local profile

-- create a local profile
create profile orclpdbprofile limit
    SESSIONS_PER_USER UNLIMITED
    IDLE_TIME UNLIMITED
    FAILED_LOGIN_ATTEMPTS 10
    PASSWORD_LIFE_TIME 180
    INACTIVE_ACCOUNT_TIME 10;
--Error report -
--ORA-02377: invalid profile limit INACTIVE_ACCOUNT_TIME
--02377. 00000 -  "invalid profile limit %s"
--*Cause:    A value of 0 or lower was specified for the limit.
--*Action:   Specify a limit greater than 0. For password profile parameters,
--           some additional restrictions apply:
--           * The specified limit of the PASSWORD_ROLLOVER_TIME profile
--           parameter cannot exceed either 60 days, or the current
--           value of the PASSWORD_GRACE_TIME limit of the profile,
--           or the current value of the PASSWORD_LIFE_TIME limit of
--           the profile; whichever value is lowest.
--           * When the PASSWORD_ROLLOVER_TIME profile parameter is
--           non-zero (the password rollover feature is enabled),
--           the specified limit cannot be less than 1 hour, to give
--           the application time to switch over to the new password.
--           * The PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME limits
--           cannot be less than the PASSWORD_ROLLOVER_TIME limit.
--           * For the INACTIVE_ACCOUNT_TIME profile parameter, the specified
--           limit cannot be less than 15 days.
--           * For the PASSWORD_GRACE_TIME profile parameter, 0 is allowed
--           as a permissible value.

create profile orclpdbprofile limit
    SESSIONS_PER_USER UNLIMITED
    IDLE_TIME UNLIMITED
    FAILED_LOGIN_ATTEMPTS 10
    PASSWORD_LIFE_TIME 180
    INACTIVE_ACCOUNT_TIME 15;
--Profile ORCLPDBPROFILE created.

-- confirm
select * from DBA_PROFILES
where PROFILE= upper('orclpdbprofile')
order by limit;
--ORCLPDBPROFILE	FAILED_LOGIN_ATTEMPTS	PASSWORD	10	NO	NO	NO
--ORCLPDBPROFILE	INACTIVE_ACCOUNT_TIME	PASSWORD	15	NO	NO	NO
--ORCLPDBPROFILE	PASSWORD_LIFE_TIME	PASSWORD	180	NO	NO	NO
--ORCLPDBPROFILE	LOGICAL_READS_PER_SESSION	KERNEL	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	LOGICAL_READS_PER_CALL	KERNEL	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	CONNECT_TIME	KERNEL	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	CPU_PER_CALL	KERNEL	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	CPU_PER_SESSION	KERNEL	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	COMPOSITE_LIMIT	KERNEL	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	PRIVATE_SGA	KERNEL	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	PASSWORD_GRACE_TIME	PASSWORD	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	PASSWORD_REUSE_TIME	PASSWORD	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	PASSWORD_REUSE_MAX	PASSWORD	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	PASSWORD_VERIFY_FUNCTION	PASSWORD	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	PASSWORD_LOCK_TIME	PASSWORD	DEFAULT	NO	NO	NO
--ORCLPDBPROFILE	SESSIONS_PER_USER	KERNEL	UNLIMITED	NO	NO	NO
--ORCLPDBPROFILE	IDLE_TIME	KERNEL	UNLIMITED	NO	NO	NO


create user sara identified by sara
profile orclpdbprofile;
grant create session, create table, unlimited tablespace to sara

SELECT
    username
    , account_status
    , created
    , profile
    , authentication_type
    , common
    , default_tablespace
    , temporary_tablespace
FROM DBA_users
WHERE USERNAME=upper('sara');
--SARA	OPEN	12-Mar-2024	ORCLPDBPROFILE	PASSWORD	NO	USERS	TEMP

Lab: Query Password Verification Function

lab_password_verification01

SHOW CON_NAME;
--CON_NAME
--------------------------------
--CDB$ROOT

-- query verification function
select * from dba_objects
where object_name like '%VERIFY%'
AND OBJECT_TYPE='FUNCTION';
--SYS	ORA12C_VERIFY_FUNCTION
--SYS	VERIFY_FUNCTION_11G
--SYS	VERIFY_FUNCTION
--SYS	ORA12C_STRONG_VERIFY_FUNCTION
--SYS	ORA12C_STIG_VERIFY_FUNCTION

ALTER SESSION SET CONTAINER=ORCLPDB;
SHOW CON_NAME;
--CON_NAME
--------------------------------
--ORCLPDB

select * from dba_objects
where object_name like '%VERIFY%'
AND OBJECT_TYPE='FUNCTION';
--SYS	ORA12C_VERIFY_FUNCTION
--SYS	VERIFY_FUNCTION_11G
--SYS	VERIFY_FUNCTION
--SYS	ORA12C_STRONG_VERIFY_FUNCTION
--SYS	ORA12C_STIG_VERIFY_FUNCTION

Lab: Password Verification Function

CREATE PROFILE TEST_PASS LIMIT
    PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION;

SELECT * FROM DBA_PROFILES
WHERE PROFILE='TEST_PASS'
AND resource_name='PASSWORD_VERIFY_FUNCTION';
--TEST_PASS	PASSWORD_VERIFY_FUNCTION	PASSWORD	ORA12C_VERIFY_FUNCTION	NO	NO	NO

create user test100 identified by welcome  profile TEST_PASS;
--ORA-28003: password verification for the specified password failed
--ORA-20000: password length less than 8 bytes
--28003. 00000 -  "password verification for the specified password failed"
--*Cause:    The new password did not meet the necessary complexity
--           specifications and the password_verify_function failed
--*Action:   Enter a different password. Contact the DBA to know the rules for
--           choosing the new password

create user test100 identified by welcome1234 profile TEST_PASS;
--Error report -
--ORA-28003: password verification for the specified password failed
--ORA-20000: password must contain 1 or more special characters
--28003. 00000 -  "password verification for the specified password failed"
--*Cause:    The new password did not meet the necessary complexity
--           specifications and the password_verify_function failed
--*Action:   Enter a different password. Contact the DBA to know the rules for
--           choosing the new password

create user test100 identified by welcome1234#  profile TEST_PASS;
--User TEST100 created.

grant create session to test100;

conn test100/welcome1234#@orclpdb;
show user;
-- USER is "TEST100"

alter user test100 identified by welcome678#;
/* Cause : ORA-28221 error occurs when an user tries to reset his own password
without specifying the REPLACE keyword, provided the user does not have ALTER USER privilege
and the user is having a profile with password verify function. */

alter user test100 identified by welcome678# replace welcome1234#;
-- User altered.

conn test100/welcome678#@orclpdb;

lab_password_verification02

TOP