73 - [Oracle 19C] User profile

 

 

• User Profile

• a user profile is a named set of resource limits and password parameters that restrict
database usage and database instance resources for a user.
• If you assign a profile to a user, then that user can not exceed those limits.
• Every user , including the administrators is assigned to only one profile.
• By default when you crate a user, it will be assigned to default profile, unless you specified
another profile

 

 

 

• Example - Create Profile

SQL> CONNECT / AS SYSDBA 
SQL> CREATE PROFILE c##cprofile dev limit .... CONTAINER=ALL;


SQL> CONNECT SYS@PDB1 AS SYSDBA 
SQL> CREATE PROFILE profile PDB1 limit .... ;

 

 

 

• Example - Assigning Profile

There are two ways to assign a profile:

# Commonly: The profile assignment is replicated in all current and future containers.

SQL> CONNECT I AS SYSDBA
SQL> ALTER USER <common user> PROFILE <common profile> CONTAINER=ALL;


# Locally: The profile assignment occurs in one PDB (stand-alone or application container) only.

SQL> CONNECT SYS@PDB1 AS SYSDBA
SQL> ALTER USER <common or local user> PROFILE <common or local profile>;

 

 

 

Creating common profile - example

show con_name
/*
CON_NAME 
------------------------------
CDB$ROOT
*/



select * from dba_PROFILES where PROFILE='DEFAULT';
/*
Resource_Type is Kernel >> these parameters related to the resources
# 자주 설정하는 파라미터들
SESSIONS_PER_USER   >> 동일한 계정으로 생성 가능한 세션 제한 인자
IDLE_TIME           >> 입력이 일정시간 없으면 자동으로 세션을 종료
CONNECT_TIME        >> 계정에서 하루 동안 디비 서버에 접속 가능한 시간


PROFILE    RESOURCE_NAME                    RESOURCE LIMIT           COM INH IMP
---------- -------------------------------- -------- --------------- --- --- ---
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 >> 오류 10번까지 가능.
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 

17 rows selected. 
*/

 

 

 

• Create profile

create profile C##GENERAL
limit
SESSIONS_PER_USER 4
IDLE_TIME 15--세션종료, 15분동안 아무런 통신이 없다면.
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 180
container=all
/*
Profile C##GENERAL created.
*/


select * from DBA_PROFILES
where PROFILE= upper('C##GENERAL');
/*

PROFILE    RESOURCE_NAME                    RESOURCE LIMIT           COM INH IMP
---------- -------------------------------- -------- --------------- --- --- ---
C##GENERAL COMPOSITE_LIMIT                  KERNEL   DEFAULT         YES NO  NO 
C##GENERAL SESSIONS_PER_USER                KERNEL   4               YES NO  NO 
C##GENERAL CPU_PER_SESSION                  KERNEL   DEFAULT         YES NO  NO 
C##GENERAL CPU_PER_CALL                     KERNEL   DEFAULT         YES NO  NO 
C##GENERAL LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT         YES NO  NO 
C##GENERAL LOGICAL_READS_PER_CALL           KERNEL   DEFAULT         YES NO  NO 
C##GENERAL IDLE_TIME                        KERNEL   15              YES NO  NO 
C##GENERAL CONNECT_TIME                     KERNEL   DEFAULT         YES NO  NO 
C##GENERAL PRIVATE_SGA                      KERNEL   DEFAULT         YES NO  NO 
C##GENERAL FAILED_LOGIN_ATTEMPTS            PASSWORD 3               YES NO  NO 
C##GENERAL PASSWORD_LIFE_TIME               PASSWORD 180             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 INACTIVE_ACCOUNT_TIME            PASSWORD DEFAULT         YES NO  NO 

17 rows selected. 

*/

 

 

 

• Create a user and grant the profile to  a user

create user c##lord identified by lord container=all
/*
User C##LORD created.
*/

grant create session, create table, unlimited tablespace to c##lord container=all;
/*
Grant succeeded.
*/

select * from DBA_users
where USERNAME= upper('c##lord');
/*

*/

alter user c##lord profile C##GENERAL container=all;
/*
User C##LORD altered.
*/


select * from DBA_users
where USERNAME= upper('c##lord');
/*

*/

 

 

 

• Testing the profile

--go to vm and test SESSIONS_PER_USER ,FAILED_LOGIN_ATTEMPTS


/*
[oracle@test ~]$ sqlplus c##lord/lord

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 7 16:03:52 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


------------------------------------


Enter user-name: c##lord
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@test ~]$
*/


# sysdba 로 로그인하여 unlock 하는 방법
alter user c##lord account unlock;
/*
User C##LORD altered.


SQL> conn c##lord/lord
Connected.
SQL>
*/

 

 

Profiles cannot impose resource limitations on users unless the RESOURCE_LIMIT initialization parameter is set to TRUE
  ° With RESOURCE_LIMIT at its default value of FALSE, profile resource limitations are ignored.

SQL> show parameter resource

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
resource_limit			     boolean	 TRUE
resource_manage_goldengate	     boolean	 FALSE
resource_manager_cpu_allocation      integer	 4
resource_manager_plan		     string
SQL>