[Db Admin] 18.Managing Profiles

1. Profile
   - resource 와 password 제한을 설정할 수 있다.
   - CREATE USER, ALTER USER로 assign 한다.
   - ENABLE, DISABLE 할 수 있다.
   - DEFAULT profile을 특별히 만들지 않으면 제한 사항이 전혀 없다.
   - session level 과 call level로 제한 할 수 있다.
   
   [ 제한사항 ]
   1) resource limit
      - CPU time
      - I/O operations
      - Idle time
      - Connect time
      - Memory space
      Concurrent sessions
   2) password limit
      - Password aging and expiration
      - Password history
      - Password complexity verification
      - Account locking
   
   [ Profile 만들어서 적용하는 순서 ]
   1. Profile을 만든다.
   2. User에게 profile을 assign 한다.
   3. RESOURCE_LIMIT 을 enable 한다.
   
2. CREATE PROFILE

   CREATE PROFILE profile LIMIT
      [SESSIONS_PER_USER             max_value]
      [CPU_PER_SESSION               max_value]
      [CPU_PER_CALL                  max_value]
      [CONNECT_TIME                  max_value]
      [IDLE_TIME                     max_value]
      [LOGICAL_READS_PER_SESSION     max_value]
      [LOGICAL_READS_PER_CALL        max_value]
      [COMPOSITE_LIMIT               max_value]
      [PRIVATE_SGA                   max_bytes]
      
   max_value :== { integer | UNLIMITED | DEFAULT }
   max_bytes :== { integer[K|M] | UNLIMITED | DEFAULT }
   
   - DEFAULT : DEFAULT profile에 설정된 값을 적용한다.
   - DEFALUT profile을 보고 싶으면...
     SQL> select * from dba_profiles
       2  where profile='DEFAULT'
   
   예) CREATE PROFILE developer_prof LIMIT
       SESSIONS_PER_USER 2
       CPU_PER_SESSION 10000
       IDLE_TIME 60
       CONNECT_TIME 480 ;
       
   * Session Level
     CPU_PER_SESSION : total cpu time (단위: 1/100초)
     SESSIONS_PER_USER : 각 user에게 허용되는 concurent session 수
     CONNECT_TIME : connect time (단위: 분)
     IDLE_TIME : server process가 몇분동안 작업을 안하고 있으면 session을 종료한다. (단위: 분)
     LOGICAL_READS_PER_SESSION : data block 수
     PRIVATE_SGA : SGA에 있는 private 공간.
     
   * Call Level
     CPU_PER_CALL : call 하는데 걸리는 cpu time (단위: 1/100초)
     LOGICAL_READS_PER_CALL : data bolock 수
     
3. User에게 Profile assign 하는 방법

   1) CREATE USER
      SQL> CREATE USER user3 IDENTIFIED BY user3
        2  DEFAULT TABLESPACE data01
        3  TEMPORATY TABLESPACE temp
        4  QUOTA unlimited ON data01
        5  PROFILE developer_prof ;
        
   2) ALTER USER
      SQL> ALTER USER scott
        2  PROFILE developer_prof ;
        
      * 현재 접속한 session에는 적용되지 않는다.
      
4. RESOURCE LIMIT Enable 하는 방법

   1) $ORACLE_HOME/dbs/initSID.ora  initial file에 parameter 수정.
   
      RESOURCE_LIMIT=TRUE
      --> startup 하면 적용된다.
      
   2) ALTER SYSTEM
      SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE ;
      --> shutdown 하면 FALSE가 된다.
      
5. ALTER PROFILE : profile 수정

   [syntax]
   ALTER PROFILE profile LIMIT
      [SESSIONS_PER_USER             max_value]
      [CPU_PER_SESSION               max_value]
      [CPU_PER_CALL                  max_value]
      [CONNECT_TIME                  max_value]
      [IDLE_TIME                     max_value]
      [LOGICAL_READS_PER_SESSION     max_value]
      [LOGICAL_READS_PER_CALL        max_value]
      [COMPOSITE_LIMIT               max_value]
      [PRIVATE_SGA                   max_bytes]

   SQL> ALTER PROFILE default LIMIT
     2  SESSION_PER_USER 5
     3  CPU_PER_CALL 3600
     4  IDLE_TIME 30 ;
     
6. DROP PROFILE

   1) DROP PROFILE developer_prof ;
      --> 현재 누군가 사용하고 있다면 drop 되지 않는다.
      
   2) DROP PROFILE developer_prof CASCADE ;
      --> developer_prof profile을 사용하고 있는 모든 user에게 DEFAULT profile을 넘겨주고 drop 된다.
      
   * DEFAULT profile은 DROP 되지 않는다.
   * session을 종료하기 전까지는 drop이 되어 있지 않는다.
   
7. RESOURCE LIMIT profile 정보를 갖고 있는 data dictionary

   DBA_USERS : profile, username
   DBA_PROFILES : profile, resource_name, resource_type(=KERNEL), limit
   
   * DBA_PROFILES의 resource_type='KERNEL' 조건을 갖아야 한다.
   
   SVRMGR> SELECT p.profile, p.resource_name, p.limit
        2  FROM dba_users u, dba_profiles p
        3  WHERE p.profile=u.profile
        4  AND username='SCOTT'
        5  AND p.resource_type='KERNEL' ;
        
8. Password limit

   - Account locking : user가 login을 설정한 값만큼 실패하면 계정에 lock을 건다.
   - Password aging and expiry : 설정한 기간이 지나면 password를 바꾸게 만들어 준다.
   - Password history
   - Password complexity verification

   * Password 제한은 RESOURCE_LIMIT와는 무관한다.
   * 즉, password profile을 만들면 바로 적용된다.
   
9. CREATE PROFILE 에서 password limit 설정 방법

   [syntax]
   CREATE PROFILE profile LIMIT
      [FAILED_LOGIN_ATTEMPTS       max_value]
      [PASSWORD_LIFE_TIME          max_value]
      [ {PASSWORD_REUSE_TIME | PASSWORD_REUSE_MAX}  max_value]
      [ACCOUNT_LOCK_TIME           max_value]
      [PASSWORD_GRACE_TIME         max_value]
      [PASSWORD_VERIFY_FUNCTION {function | NULL | DEFAULT} ]
      
   SQL> CREATE PROFILE grace_5 LIMIT
     2    FAILED_LOGIN_ATTEMPTS 3  -- pw 실패를 3번까지만...
     3    PASSWORD_LIFE_TIME 30    -- 30일동안만 pw를 사용하게 한다.
     4    PASSWORD_REUSE_TIME 30   -- 한번 사용한 pw를 다시 사용하려면 30일 후부터 재사용 가능.
     5    PASSWORD_VERIFY_FUNCTION verify_function
     6    PASSWORD_GRACE_TIME 5 ;  -- life time이 끝나고 5일동안 message를 보여준다.
     
10. verify_function
    ; 오라클에 기본적으로 들어 있는 password 제한 profile function
    
    [아래의 내용들이 들어있다.]
    - 4문자 이상이어야 한다.
    - username과 같아선 안된다.
    - 한개이상의 alpha, numeric, special 문자로 구성되어야 한다.
    - 이전 password보다 최소한 3문자 이상 같아선 안된다.
    
    * utlpwdmg.sql에 이 function script가 들어있다.
    
11. Password 정보를 볼수 있는 data dictionary

    DBA_USERS : profile, username, account_status, lock_date, expiry_date
    DBA_PROFILES : profile, resource_name, resource_type(=PASSWORD), limit
    
    SVRMGR> SELECT username, password, account_status, lock_date, expiry_date
         2  FROM dba_users ;
    
    SVRMGR> SELECT * FROM dba_profiles
         2  WHERE resource_type = 'PASSWORD'
         3  AND profile = 'DEFAULT' ;
    --> default profile에 있는 password limit 정보만 보여준다.

---

[ Lab ]

---

1. 현재 system 에 어떠한 profile이 존재하는지 조회하십시오.
$ sqlplus system/manager
SQL> select distinct profile from dba_profiles ;

2. 각 user 에게 어떤 profile 이 assign 되어 있는지 확인하십시오.
SQL> select username, profile from dba_users ;

3. profile 을 이용할 수 있도록 환경을 설정하고, profile을 만들어 보십시오.
initSID.ora file에 RESOURCE_LIMIT=TRUE로 설정하고, DB를 재기동한다.
또는, alter system set resource_limit=true ;
SQL> create profile test_profile limit
  2  sessions_per_user 2
  3  idle_time 1 ;
SQL> alter user bob
  2  profile test_profile ;
  
4. (a) profile 에서 설정한 대로 resource 를 제한하는지 알아보십시오.
   $ sqlplus bob/oracle
   $ sqlplus bob/oracle
   $ sqlplus bob/oracle   ==> error 발생 확인!!!
   
   (b) SQL*Plus 에서 아무런 작업도 하지 않고, 1분이 지난 후에, select 문을 수행하십시오.
   어떤 결과가 나타납니까?
   
5. TEST_PROFILE 프로파일이 할당된 사용자 BOB 에 대해서 다음 사항이 적용되도록 하십시오.
   - 두 번 로그인을 시도한 다음에 계정은 LOCK 되어야 합니다.
   - 패스워드는 30일이 지나면 만료되어야 합니다.
SQL> alter profile test_profile limit
  2  failed_login_attempts 2
  3  password_life_time 30

$ sqlplus bob/ttt     ==> 틀린 password로 login 시도
$ sqlplus bob/ppp     ==> 틀린 password로 login 시도
$ sqlplus bob/oracle  ==> 올바른 password로 login을 시도해도, account 가 LOCK 됨을 확인!!!

6. 문제5 에서 LOCK 된 account를 UNLOCK 하십시오.
$ sqlplus system/manager
SQL> alter user bob
  2  account unlock ;
$ sqlplus bob/oracle  ==> 성공적으로 login

7. test_profile을 삭제하십시오.
$ sqlplus system/manager
SQL> select username, profile from dba_users ;
SQL> drop profile test_profile ;
     ==> 어떤 error가 발생하는가?
SQL> select username, profile from dba_users ;