【Data Platform】/Oracle
[8i] 프로파일 관리(OLN)
디비랑
2011. 8. 16. 15:44
/********************************************************************************************
-- Title : [8i] 프로파일 관리(OLN)
-- Reference : OLN
-- Key word : profile
********************************************************************************************/
/****************************************************************************************
-- 프로파일 자원 제한 보기
****************************************************************************************/
SQL> SELECT p.profile, u.username, p.resource_name, p.limit
2 FROM dba_users u, dba_profiles p
3 WHERE p.profile=u.profile AND username in ('SCOTT', 'MAPBAK')
4 AND p.resource_type='KERNEL'
5 ORDER BY u.username, p.resource_name;
-- Title : [8i] 프로파일 관리(OLN)
-- Reference : OLN
-- Key word : profile
********************************************************************************************/
/****************************************************************************************
-- 프로파일 자원 제한 보기
****************************************************************************************/
SQL> SELECT p.profile, u.username, p.resource_name, p.limit
2 FROM dba_users u, dba_profiles p
3 WHERE p.profile=u.profile AND username in ('SCOTT', 'MAPBAK')
4 AND p.resource_type='KERNEL'
5 ORDER BY u.username, p.resource_name;
PROFILE USERNAME RESOURCE_NAME LIMIT
--------------- -------- ------------------------------ ----------------
DEVELOPER_PROF MAPBAK COMPOSITE_LIMIT DEFAULT
DEVELOPER_PROF MAPBAK CONNECT_TIME 480
DEVELOPER_PROF MAPBAK CPU_PER_CALL DEFAULT
DEVELOPER_PROF MAPBAK CPU_PER_SESSION 10000
DEVELOPER_PROF MAPBAK IDLE_TIME 60
DEVELOPER_PROF MAPBAK LOGICAL_READS_PER_CALL DEFAULT
DEVELOPER_PROF MAPBAK LOGICAL_READS_PER_SESSION DEFAULT
DEVELOPER_PROF MAPBAK PRIVATE_SGA DEFAULT
DEVELOPER_PROF MAPBAK SESSIONS_PER_USER 2
DEVELOPER_PROF SCOTT COMPOSITE_LIMIT DEFAULT
DEVELOPER_PROF SCOTT CONNECT_TIME 480
DEVELOPER_PROF SCOTT CPU_PER_CALL DEFAULT
DEVELOPER_PROF SCOTT CPU_PER_SESSION 10000
DEVELOPER_PROF SCOTT IDLE_TIME 60
DEVELOPER_PROF SCOTT LOGICAL_READS_PER_CALL DEFAULT
DEVELOPER_PROF SCOTT LOGICAL_READS_PER_SESSION DEFAULT
DEVELOPER_PROF SCOTT PRIVATE_SGA DEFAULT
DEVELOPER_PROF SCOTT SESSIONS_PER_USER 2
--------------- -------- ------------------------------ ----------------
DEVELOPER_PROF MAPBAK COMPOSITE_LIMIT DEFAULT
DEVELOPER_PROF MAPBAK CONNECT_TIME 480
DEVELOPER_PROF MAPBAK CPU_PER_CALL DEFAULT
DEVELOPER_PROF MAPBAK CPU_PER_SESSION 10000
DEVELOPER_PROF MAPBAK IDLE_TIME 60
DEVELOPER_PROF MAPBAK LOGICAL_READS_PER_CALL DEFAULT
DEVELOPER_PROF MAPBAK LOGICAL_READS_PER_SESSION DEFAULT
DEVELOPER_PROF MAPBAK PRIVATE_SGA DEFAULT
DEVELOPER_PROF MAPBAK SESSIONS_PER_USER 2
DEVELOPER_PROF SCOTT COMPOSITE_LIMIT DEFAULT
DEVELOPER_PROF SCOTT CONNECT_TIME 480
DEVELOPER_PROF SCOTT CPU_PER_CALL DEFAULT
DEVELOPER_PROF SCOTT CPU_PER_SESSION 10000
DEVELOPER_PROF SCOTT IDLE_TIME 60
DEVELOPER_PROF SCOTT LOGICAL_READS_PER_CALL DEFAULT
DEVELOPER_PROF SCOTT LOGICAL_READS_PER_SESSION DEFAULT
DEVELOPER_PROF SCOTT PRIVATE_SGA DEFAULT
DEVELOPER_PROF SCOTT SESSIONS_PER_USER 2
18 rows selected.
/****************************************************************************************
-- 프로파일 암호 설정 파라미터
****************************************************************************************/
- FAILED_LOGIN_ATTEMPTS : 계정을 잠그기 전까지 로그인 시도하다 실패한 횟수
- PASSWORD_LOCK_TIME : 암호가 기간 만료되어 계정이 잠겨진 채로 남아 있었던 날 수
- PASSWORD_LIFE_TIME : 날 수로 표시한 암호의 수명으로 이 기간이 지나면 기간 만료 됨
- PASSWORD_GRACE_TIME : 암호가 기간 만료된 후 첫번째 성공적인 로그인부터 암호 변경을
할 때가지의 유예 기간
- PASSWORD_REUSE_TIME : 암호가 재사용될 수 있게 될 때가지의 날 수
- PASSWORD_REUSE_MAX : 암호가 재사용될 수 있는 최대 횟수
- PASSWORD_VERIFY_FUNCTION : 암호를 할당하기 전 복잡성 검사를 수행할 PL/SQL 항수
/****************************************************************************************
-- 프로파일 암호 정보 보기
****************************************************************************************/
SQL> SELECT username, password, account_status, lock_date, expiry_date
2 FROM dba_users;
-- 프로파일 암호 정보 보기
****************************************************************************************/
SQL> SELECT username, password, account_status, lock_date, expiry_date
2 FROM dba_users;
USERNAME PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
--------------- ---------------------- --------------- ---------- ---------
SYS EDB4E0682E9271E8 OPEN 23-AUG-04
SYSTEM D4DF7931AB130E37 OPEN 22-MAY-04
OUTLN 4A3BA55E08595C81 OPEN 22-MAY-04
DBSNMP E066D214D5421CCC OPEN 22-MAY-04
ADAMS 72CDEF4A3483F60D OPEN 07-SEP-04
CLARK 7AAFE7D01511D73F OPEN 07-SEP-04
BLAKE 9435F2E60569158E OPEN 07-SEP-04
KJK 840E4052794EBBAB OPEN 12-AUG-04
SCOTT2 C44C11D4C34DB67D OPEN 19-JUL-04
JONES B9E99443032F059D OPEN 07-SEP-04
TRACESVR F9DA8977092B7B81 OPEN 22-MAY-04
MAPBAK C9C50AA1611E02F2 OPEN 08-SEP-04
SCOTT F894844C34402B67 OPEN 07-SEP-04
--------------- ---------------------- --------------- ---------- ---------
SYS EDB4E0682E9271E8 OPEN 23-AUG-04
SYSTEM D4DF7931AB130E37 OPEN 22-MAY-04
OUTLN 4A3BA55E08595C81 OPEN 22-MAY-04
DBSNMP E066D214D5421CCC OPEN 22-MAY-04
ADAMS 72CDEF4A3483F60D OPEN 07-SEP-04
CLARK 7AAFE7D01511D73F OPEN 07-SEP-04
BLAKE 9435F2E60569158E OPEN 07-SEP-04
KJK 840E4052794EBBAB OPEN 12-AUG-04
SCOTT2 C44C11D4C34DB67D OPEN 19-JUL-04
JONES B9E99443032F059D OPEN 07-SEP-04
TRACESVR F9DA8977092B7B81 OPEN 22-MAY-04
MAPBAK C9C50AA1611E02F2 OPEN 08-SEP-04
SCOTT F894844C34402B67 OPEN 07-SEP-04
13 rows selected.
SQL> SELECT * FROM dba_profiles
2 WHERE resource_type='PASSWORD'
3 AND profile = 'DEFAULT';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
-------- ------------------------- -------------- ----------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
DEFAULT PASSWORD_LIFE_TIME PASSWORD 60
DEFAULT PASSWORD_REUSE_TIME PASSWORD 1800
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION
DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006
DEFAULT PASSWORD_GRACE_TIME PASSWORD 10
2 WHERE resource_type='PASSWORD'
3 AND profile = 'DEFAULT';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
-------- ------------------------- -------------- ----------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
DEFAULT PASSWORD_LIFE_TIME PASSWORD 60
DEFAULT PASSWORD_REUSE_TIME PASSWORD 1800
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION
DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006
DEFAULT PASSWORD_GRACE_TIME PASSWORD 10
/****************************************************************************************
-- 관리용
****************************************************************************************/
-- profile.sql
SELECT p.profile, u.username, p.resource_name, p.limit
FROM dba_users u, dba_profiles p
WHERE p.profile=u.profile
AND u.username not in ('DBSNMP', 'OUTLN', 'PERFSTAT', 'SYS', 'SYSTEM', 'TRACESVR')
AND p.resource_type='KERNEL'
ORDER BY u.username, p.resource_name
/
/****************************************************************************************
-- 프로파일 자원 제한 생성
****************************************************************************************/
SQL> CREATE PROFILE developer_prof LIMIT
2 SESSIONS_PER_USER 2
3 CPU_PER_SESSION 10000
4 IDLE_TIME 60
5 CONNECT_TIME 480;
Profile created.
/****************************************************************************************
-- 사용자에게 프로파일 지정
****************************************************************************************/
SQL> CREATE USER mapbak
2 IDENTIFIED BY passwd
3 DEFAULT TABLESPACE users
4 TEMPORARY TABLESPACE temp
5 QUOTA unlimited ON users
6 PROFILE developer_prof;
User created.
SQL> ALTER USER scott
2 PROFILE developer_prof;
2 PROFILE developer_prof;
User altered.
/****************************************************************************************
-- 자원 제한 Enable
****************************************************************************************/
-- set resource_limit 설정
SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
System altered.
SQL> SHOW PARAMETER resource_limit
NAME TYPE VALUE
--------------- ------- ------
resource_limit boolean TRUE
--------------- ------- ------
resource_limit boolean TRUE
SQL> ALTER SYSTEM SET RESOURCE_LIMIT=FALSE;
System altered.
SQL> SHOW PARAMETER resource_limit
NAME TYPE VALUE
--------------- ------- ------
resource_limit boolean FLASE
--------------- ------- ------
resource_limit boolean FLASE
-- 초기화 파라미터 설정
# more $ORACLE_HOME/dbs/initORCL.ora
...
resource_limit = "true"
...
# more $ORACLE_HOME/dbs/initORCL.ora
...
resource_limit = "true"
...
SQL> SHOW PARAMETER resource_limit
NAME TYPE VALUE
--------------- ------- ------
resource_limit boolean TRUE
--------------- ------- ------
resource_limit boolean TRUE
/****************************************************************************************
-- 프로파일 수정
****************************************************************************************/
SQL> ALTER PROFILE default LIMIT
2 SESSIONS_PER_USER 5
3 CPU_PER_CALL 3600
4 IDLE_TIME 30;
Profile altered.
/****************************************************************************************
-- 프로파일 삭제
****************************************************************************************/
SQL> DROP PROFILE developer_prof
2 CASCADE;
Profile dropped.
/****************************************************************************************
-- 프로파일 암호 설정
****************************************************************************************/
SQL> CREATE PROFILE grace_5 LIMIT
2 FAILED_LOGIN_ATTEMPTS 3
3 PASSWORD_LIFE_TIME 30
4 PASSWORD_VERIFY_FUNCTION default
5 PASSWORD_GRACE_TIME 5;
Profile created.
/****************************************************************************************
-- 사용자 정의 암호 함수
****************************************************************************************/
-- PL/SQL내 사용자 정의 암호 함수 형식
function_name(
userid_parameter IN VARCHAR2(30)
, password_parameter IN VARCHAR2(30)
, old_password_paramter IN VARCHAR2(30)
)
RETURN BOOLEAN
/****************************************************************************************
-- 암호 검증 함수 verify_function
****************************************************************************************/
-- verify_function생성 스크립트 실행
SQL> @ $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.
-- 위의 결과 표시에서 Profile altered.는 다음 명령의 내부 실행 결과이다.
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;
/****************************************************************************************
-- 암호 관리 예
****************************************************************************************/
-- 정확히 이넘이 모 하는 놈이야...??
SQL> @ $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
-- 암호 관리 예
****************************************************************************************/
-- 정확히 이넘이 모 하는 놈이야...??
SQL> @ $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.
-- 프로파일 생성
SQL> create profile user_pro limit
2 sessions_per_user 2
3 idle_time 1
4 cpu_per_call unlimited
5 failed_login_attempts 3
6 password_verify_function verify_function;
SQL> create profile user_pro limit
2 sessions_per_user 2
3 idle_time 1
4 cpu_per_call unlimited
5 failed_login_attempts 3
6 password_verify_function verify_function;
Profile created.
-- 암호 제한 설정
SQL> alter user mapbak profile user_pro password expire;
SQL> alter user mapbak profile user_pro password expire;
User altered.
-- set resource_limit 설정
SQL> alter system set resource_limit= true;
SQL> alter system set resource_limit= true;
System altered.
-- 로긴 3회 에러시 락발생
MAPBAKCOM2:/export/home/oracle8i>sqlplus mapbak/qawe
MAPBAKCOM2:/export/home/oracle8i>sqlplus mapbak/qawe
SQL*Plus: Release 8.1.7.0.0 - Production on Mon May 30 22:46:15 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
ORA-01017: invalid username/password; logon denied
Enter user-name: mapbak
Enter password:
ERROR:
ORA-28000: the account is locked
Enter user-name: mapbak
Enter password:
ERROR:
ORA-28000: the account is locked
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
-- 락된 계정 해제
SQL> alter user mapbak account unlock;
SQL> alter user mapbak account unlock;
User altered.
/****************************************************************************************
-- 시스템 자원 관리 예
****************************************************************************************/
-- 삭제 안됨
SQL> drop profile user_pro cascade;
-- 사용자 kill
SQL> select username, sid, serial# from v$session
2 where username = 'MAY';
SQL> select username, sid, serial# from v$session
2 where username = 'MAY';
USERNAME SID SERIAL#
---------- ----- -------
MAY 12 2929
---------- ----- -------
MAY 12 2929
SQL> alter system kill session '12, 2929';
SQL> drop user may cascade;