[Tech] oracle profile

-- sample profile 생성
SQL> create profile sample_prf limit 
  2  failed_login_attempts 3 //로그인시도 3회
  3  password_lock_time 1 //계정을 1일동안 사용못함
  4  password_life_time 100 //계정의 암호는 100일 한번씩 변경
  5  password_reuse_time 200 //동일한 암호는 200일 동안 사용 못하게
  6  ;

Profile created.

 

 

SQL> select * from dba_profiles where profile='SAMPLE_PRF';

PROFILE       RESOURCE_NAME      RESOURCE LIMIT      COM INH IMP ORA MAN
------------- ------------------------------ -------- ---------- --- --- --- --- ---
SAMPLE_PRF    FAILED_LOGIN_ATTEMPTS  PASSWORD 10  YES NO  NO  YES NO //로그인 실패 횟수
SAMPLE_PRF    COMPOSITE_LIMIT         KERNEL   DEFAULT  YES NO  NO  YES NO 
SAMPLE_PRF    SESSIONS_PER_USER       KERNEL   DEFAULT  YES NO  NO  YES NO //세션당 접속할 유저 수
SAMPLE_PRF    CPU_PER_SESSION         KERNEL   DEFAULT  YES NO  NO  YES NO //해당세션에 접속할 CPU시간 (0.01초)
SAMPLE_PRF    CPU_PER_CALL            KERNEL   DEFAULT  YES NO  NO  YES NO //사용 잘 안함 : 구분문석 하는데 걸리는 CPU 시간
SAMPLE_PRF    LOGICAL_READS_PER_SESSION  KERNEL DEFAULT YES NO  NO YES NO //세션이 디스크와 메모리에서 읽을 수 있는 블록 수
SAMPLE_PRF    LOGICAL_READS_PER_CALL KERNEL DEFAULT  YES NO  NO  YES NO //
SAMPLE_PRF    IDLE_TIME               KERNEL   DEFAULT    YES NO  NO  YES NO //아무작업안할때 대기 시간
SAMPLE_PRF    CONNECT_TIME          KERNEL   DEFAULT    YES NO  NO  YES NO //접속했을 때 접속 허용시간
SAMPLE_PRF    PRIVATE_SGA            KERNEL   DEFAULT    YES NO  NO  YES NO //우리는 쓸일이없다
SAMPLE_PRF    PASSWORD_LIFE_TIME   PASSWORD 300     YES NO  NO  YES NO //한 비밀번호를 몇일까지 쓰는지

PROFILE       RESOURCE_NAME       RESOURCE LIMIT      COM INH IMP ORA MAN
------------- ------------------------------ -------- ---------- --- --- --- --- ---
SAMPLE_PRF    PASSWORD_REUSE_TIME  PASSWORD 300   YES NO  NO  YES NO //한번 설정한 비밀번호를 다음에 언제 또 사용하는지
SAMPLE_PRF    PASSWORD_REUSE_MAX  PASSWORD DEFAULT YES NO  NO  YES NO //동일한 비밀번호를 몇번까지 사용하는지
SAMPLE_PRF   PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT YES NO NO YES NO //암호 복잡도
SAMPLE_PRF    PASSWORD_LOCK_TIME   PASSWORD 1        YES NO  NO  YES NO //로그인 실패시 몇일동안 잠기는지 
SAMPLE_PRF    PASSWORD_GRACE_TIME PASSWORD DEFAULT  YES NO  NO  YES NO //life_time 이 만료되어도 이 파라미터에 지정된 값만큼 더 암호를 변경할 기간을 허용하게 됨
SAMPLE_PRF    INACTIVE_ACCOUNT_TIME PASSWORD DEFAULT YES NO  NO  YES NO //
SAMPLE_PRF    PASSWORD_ROLLOVER_TIME PASSWORD DEFAULT YES NO NO YES NO //

18 rows selected.

 

 

SQL> alter profile sample_prf limit 
  2  CPU_PER_SESSION 100 //cpu점유시간 1초
  3  FAILED_LOGIN_ATTEMPTS 5 //5번으로 변경
  4  PASSWORD_REUSE_MAX 1 //동일한 암호 평생 사용할 수 있도록 변경
  5  PASSWORD_GRACE_TIME 300; //암호가 만려되어도 300일 동안 암호변경할 기간을 허용

Profile altered.

 

SQL> select * from dba_profiles
  2  where profile='SAMPLE_PRF';

PROFILE       RESOURCE_NAME                  RESOURCE LIMIT      COM INH IMP
------------- ------------------------------ -------- ---------- --- --- ---
SAMPLE_PRF    COMPOSITE_LIMIT                KERNEL   DEFAULT    YES NO  NO
SAMPLE_PRF    SESSIONS_PER_USER              KERNEL   DEFAULT    YES NO  NO
SAMPLE_PRF    CPU_PER_SESSION                KERNEL   100        YES NO  NO
SAMPLE_PRF    CPU_PER_CALL                   KERNEL   DEFAULT    YES NO  NO
SAMPLE_PRF    LOGICAL_READS_PER_SESSION      KERNEL   DEFAULT    YES NO  NO
SAMPLE_PRF    LOGICAL_READS_PER_CALL         KERNEL   DEFAULT    YES NO  NO
SAMPLE_PRF    IDLE_TIME                      KERNEL   DEFAULT    YES NO  NO
SAMPLE_PRF    CONNECT_TIME                   KERNEL   DEFAULT    YES NO  NO
SAMPLE_PRF    PRIVATE_SGA                    KERNEL   DEFAULT    YES NO  NO
SAMPLE_PRF    FAILED_LOGIN_ATTEMPTS          PASSWORD 5          YES NO  NO
SAMPLE_PRF    PASSWORD_LIFE_TIME             PASSWORD 100        YES NO  NO

PROFILE       RESOURCE_NAME                  RESOURCE LIMIT      COM INH IMP
------------- ------------------------------ -------- ---------- --- --- ---
SAMPLE_PRF    PASSWORD_REUSE_TIME            PASSWORD 200        YES NO  NO
SAMPLE_PRF    PASSWORD_REUSE_MAX             PASSWORD 1          YES NO  NO
SAMPLE_PRF    PASSWORD_VERIFY_FUNCTION       PASSWORD DEFAULT    YES NO  NO
SAMPLE_PRF    PASSWORD_LOCK_TIME             PASSWORD 1          YES NO  NO
SAMPLE_PRF    PASSWORD_GRACE_TIME            PASSWORD 300        YES NO  NO
SAMPLE_PRF    INACTIVE_ACCOUNT_TIME          PASSWORD DEFAULT    YES NO  NO

17 rows selected.