트리거란?◆ INSERT, UPDATE, DELETE문이 TABLE에 대해 행해질 때 묵시적으로 수행되는 PROCEDURE 입니다.◆ Trigger는 TABLE과는 별도로 DATABASE에 저장됩니다.◆ Trigger는 VIEW에 대해서가 아니라 TABLE에 관해서만 정의될 수 있습니다.
- BEFORE : INSERT, UPDATE, DELETE문이 실행되기 전에 트리거가 실행됩니다. - AFTER : INSERT, UPDATE, DELETE문이 실행된 후 트리거가 실행됩니다. - trigger_event : INSERT, UPDATE, DELETE 중에서 한 개 이상 올 수 있습니다. - FOR EACH ROW : 이 옵션이 있으면 행 트리거가 됩니다. -- 행 트리거 : 컬럼의 각각의 행의 데이터 행 변화가 생길때마다 실행되며, 그 데이터 행의 실제값을 제어할수 있습니다. -- 문장 트리거 : 트리거 사건에 의해 단 한번 실행되며, 컬럼의 각 데이터 행을 제어할수 없습니다.
간단한 행 트리거 예제SQL>CREATE OR REPLACE TRIGGER triger_testBEFOREUPDATE ON deptFOR EACH ROWBEGINDBMS_OUTPUT.PUT_LINE('변경 전 컬럼 값 : ' :old.dname);DBMS_OUTPUT.PUT_LINE('변경 후 컬럼 값 : ' :new.dname);END;/SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)-- UPDATE문을 실행시키면.. SQL>UPDATE deptSET dname = '총무부'WHERE deptno = 30-- 트리거가 자동 실행되어 결과가 출력됩니당. 변경 전 컬럼 값 : 인사과변경 후 컬럼 값 : 총무부1 행이 갱신되었습니다.
간단한 행 트리거 예제2 (PLSQL BLOCK이 있는 트리거)SQL>CREATE OR REPLACE trigger sum_triggerBEFOREINSERT OR UPDATE ON empFOR EACH ROWDECLARE-- 변수를 선언할 때는 DECLARE문을 사용해야 합니다 avg_sal NUMBER;BEGINSELECT ROUND(AVG(sal),3)INTO avg_salFROM emp;DBMS_OUTPUT.PUT_LINE('급여 평균 : ' avg_sal);END; /트리거가 생성되었습니다.SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)-- INSERT문을 실행합니다..SQL> INSERT INTO EMP(EMPNO, ENAME, JOB, HIREDATE, SAL)VALUES(1000, 'LION', 'SALES', SYSDATE, 5000);-- INSERT문을 실행되기 전까지의 급여 평균이 출력됩니다. 급여 평균 : 2073.2141 개의 행이 만들어졌습니다.
출처 ================================================ * Oracle Community OracleClub.com * http://www.oracleclub.com * http://www.oramaster.net * 운영자 : 김정식 (oramaster _at_ empal.com)================================================
Wednesday, July 29, 2009
[ORACLE]암호관리 정책
No. 11219
(V8.X) 암호관리 정책===================
데이터베이스 보안시스템에서 암호는 항상 기밀로 유지되어야 한다. 데이터베이스 보안에 대한 강력한 제어를 위해 oracle의 암호관리 정책을 DBA에 의해 제어된다.
계정잠금=======특정 사용자가 지정된 횟수 이상 로그인 시도에 실패하면 서버는 자동으로 해당 사용자의 계정을 잠근다. DBA는 CREATE PROFILE명령문을 사용하여 로그인 시도시 허용된 실패횟수를 지정할 수 있다. DBA는 계정이 잠금상태로 남아 있게 되는 시간도 지정할 수 있다.
EX) CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 4 PASWORD_LOCK_TIME 30; ALTER USER scott PROFILE prof;--> 최대 로그인 실패횟수는 4이며, 계정이 잠금상태로 남아있게 되는 기간은 30일이다. 계정은 30일이 지난후에는 자동으로 잠금이 해제된다.EX) CONNECT시에 4번이상 실패한 후 CONNECT를 시도하면 "ORA-28000: the account is locked" 의 ERROR를 받게된다. 이때 해당 계정에 대한 LOCK정보는 DBA_USERS DICTIONARY에서 확인할 수 있다.
상태확인 SQL>SQL> SELECT USERNAME, ACCOUNT_STATUS, TO_CHAR(LOCK_DATE,'YY/MM/DD HH24:MI') LOCK_DATE, PROFILE FROM DBA_USERS;
USERNAME ACCOUNT_STATU LOCK_DATE PROFILE---------- ------------- ---------------- -----------SYS OPEN DEFAULTSYSTEM OPEN DEFAULTSCOTT OPEN DEFAULTKHLEE LOCKED(TIMED) 98/04/29 10:15 LKH
위와 같이 ACCOUNT_STATUS가 LOCK상태로 나타난다.
DBA가 계정잠금해제를 위한 기간을 지정하지 않으면 PASSWORD_LOCK_TIME이 기본값이 된다. DBA가 PASSWORD_LOCK_TIME을 UNLIMITED로 지정하면 시스템보안 담당자가 계정을 명시적으로 해게해야 한다.
EX) 명시적 해제방법 : ALTER USER scott ACCOUNT UNLOCK;
따라서 계정이 잠금상태로 남아있게 되는 기간은 DBA가 사용자에게 할당된 자원 프로파일을 구성하는 방법에 따라 달라진다.
사용자가 계정에 로그인하면 사용자의 실패한 로그인 시도횟수는 0으로 재설정된다.
보안담당자가 명시적으로 사용자의 계정을 잠글 수도 있다. 이경우 계정에 대한 잠금은 자동으로 해제되지 않는다. 보안 담당자가 계정잠금을 해제해야 한다.
EX) 명시적 잠금방법 : ALTER USER scott ACCOUNT LOCK;
암호변경 및 만기==============DBA는 CREATE PROFILE명령문을 사용하여 암호의 최대실행기간을 지정한다. 지정된 기간이 지나면 암호는 만기되고 사용자나 DBA가 암호를 변경해야 한다.
EX)CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 30 PASSWORD_LIFE_TIME 90;
ALTER USER scott PROFILE prof;
--> 만기되지 전에 동일한 암호를 90일 동안 사용할 수 있음을 나타낸다.
암호실행기간과 유예기간 설정(LIFE TIME & GRACE PERIOD)----------------------------------------------------DBA는 CREATE PROFILE명령문을 사용하여 유예기간을 지정할 수도 있다. 사용자는 암호가 만기된 다음 첫번째 DATABASE 로그인 시도부터 유예기간에 들어가게된다. 유예기간 동안에는 계정에 로그인할 때마다 경고메세지가 표시되며 유예기간이 끝날 때까지 계속 표시된다. 사용자는 유예기간안에 암호를 변경해야 한다. 유예기간 동안 암호를 변경하지 않으면 계정이 만기되고 암호가 변경되지 전까지 해당 계정으로 로그인할 수 없게 된다.
EX)CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 30 PASSWORD_LIFE_TIME 60 PASSWORD_GRACE_TIME 3;
ALTER USER scott PROFILE prof;
--> 암호의 실행기간이 60일이고 유에기간은 3일이다.사용자가 60일이 지난 어느날, 70일이나 100일 또는 그 이외의 날, 즉 암호 실행 기간 이후에 첫 번째 로그인을 시도하면 사용자는 암호가 3일 이후 만기된다는 경고 메세지를 받게된다. 사용자가 유예 기간의 첫 번째 날부터 3일 이내에 암호를 변경하지 않으면 사용자 계정은 만기된다. 보안 담당자가 명시적으로 계정을 만기시킬 수도 있다.
EX) 명시적 만기방법 : ALTER USER scott PASSWORD EXPPIRE;
<주의>-------암호관리 기능중 몇가지는 svrmgrl에서 지원되지 않는 부분이 있다.EXPIRE가 가까와지거나 유예기간에 들어간 경우 svrmgrl을 이용하여 로그인하려 할 때 PASSWORD의 만기일자에 대한 메세지가 나타나지 않는다.
같은 경우로, 만약 이미 암호가 이미 만기되었다면, 단순하게 ERROR MESSAGE를 내고 로그인이 거부된다. SQLPLUS를 사용하면 만기기간이 다가온다는 내용과, 새로운 PASSWORD를 받도록 나타난다.
암호 기록========DBA는 CREATE PROFILE 명령문을 사용하여 사용자가 암호를 다시 사용할 수 없는 기간 간격을 지정한다.
EX)CREATE PROFILE prof LIMIT PASSWORD_REUSE_TIME 60 PASSWORD_REUSE_MAX UNLIMITED;
--> 암호를 60일 동안 다시 사용할 수 없음을 나타낸다.
EX)CREATE PROFILE prof LIMIT PASSWORD_REUSE_MAX 3 PASSWORD_REUSE_TIME UNLIMITED;
--> 현재의 암호를 다시 사용하기 전에 암호를 변경해야 하는 횟수가 세번임을 나타낸다.
<주의> PASSWORD_REUSE_TIME과 PASSWORD_REUSE_MAX 중 하나를 지정하여야 한다.둘을 동시에 사용하지 않도록 한다.만약 PASSWORD_REUSE_TIME을 INTEGER값으로 지정하였다면, PASSWORD_REUSER_MAX는 UNLIMITED로 지정하여야 한다.반대의 경우로 PASSWORD_REUSE_MAX를 INTEGER값으로 지정한 경우 PASSWORD_REUSE_TIME는 UNLIMITED로 지정하여야 한다.
암호의 복잡성 검증================암호의 복잡성을 검증하고자 한다면 기본 PROFILE의 매개변수를 설정하는 PL/SQL SCRIPT(utlpwdmg.sql)를 사용하여 지정할 수 있다.
암호의 복잡성 검증루팀은 다음 검사를 수행한다.. 암호의 최소길이는 4인다.. 암호는 사용자ID와 동일하면 안된다.. 암호는 적어도 하나이상의 영문자,숫자,구두점으로 구성되어야 한다.. 암호는 WELCOME, ACCOUNT,DATABASE,USER와 같이 단순한 단어와 일치하면 안된다.. 암호는 이전 암호와 적어도 세자 이상 달라야 한다.
<권장> ALTER USER명령문 보다는 SQL*PLUS아 ENTERPRISE MANAGER와 같은 ORACLE TOOL에서 제공한 도구를 사용하기를 권장한다.
DBA는 PL/SQL이나 타업체의 도구를 사용하여 기존 암호검증루틴을 향상시키거나 자신의 암호검증 루틴을 생성할 수도 있다. 단 다음의 형식을 반드시 지켜야 한다.
routine_name (
userid_parameter IN VARCHAR(30),
password_parameter IN VARCHAR(30),
old_password_parameter IN VARCHAR(30)
)
RETURN BOOLEAN
새로운 루틴이 생성되면 해당루틴은 사용자의 PROFILE이나 시스템 기본 PROFILE을사용하여 암호검증루틴으로 할당하여야 한다. 암호검증 루틴은 반드시 SYS가 소유하여야 한다.
CREATE/ALTER PROFILE profile_name LIMIT
PASSWORD_VERIFY_FUNCTION routine_name
암호검증루틴 예제================+---------------------------------------------------+CREATE OR REPLACE FUNCTION verify_function (username varchar2, password varchar2, old_password varchar2) RETURN boolean IS n boolean; m integer; differ integer; isdigit boolean; ischar boolean; ispunct boolean; digitarray varchar2(20); punctarray varchar2(25); chararray varchar2(52); BEGIN digitarray:= '0123456789'; chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; punctarray:='!"#$%&()``*+,-/:;<=>?_'; --Check if the password is same as the username IF password = username THEN raise_application_error(-20001, 'Password same as user'); END IF; --Check for the minimum length of the password IF length(password) < isdigit =" FALSE"> ischar:=FALSE; FOR i IN 1..length(chararray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(chararray,i,1) THEN ischar:=TRUE; GOTO findpunct; END IF; END LOOP; END LOOP; IF ischar = FALSE THEN raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation'); END IF; --3. Check for the punctuation ispunct:=FALSE; FOR i IN 1..length(punctarray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(punctarray,i,1) THEN ispunct:=TRUE; GOTO endsearch; END IF; END LOOP; END LOOP; IF ispunct = FALSE THEN raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation'); END IF;
--Check if the password differs from the previous password by at least --3 letters IF old_password = '' THEN raise_application_error(-20004, 'Old password is null'); END IF; --Everything is fine; return TRUE ; differ := length(old_password) - length(password); IF abs(differ) < 3 THEN IF length(password) < length(old_password) THEN m := length(password); ELSE m:= length(old_password); END IF; differ := abs(differ); FOR i IN 1..m LOOP IF substr(password,i,1) != substr(old_password,i,1) THEN differ := differ + 1; END IF; END LOOP; IF differ < 3 THEN raise_application_error(-20004, 'Password should differ by at least 3 characters'); END IF; END IF; --Everything is fine; return TRUE ; RETURN(TRUE); END;
+---------------------------------------------------+
[출처] [ORACLE]암호관리 정책 작성자 newag77
(V8.X) 암호관리 정책===================
데이터베이스 보안시스템에서 암호는 항상 기밀로 유지되어야 한다. 데이터베이스 보안에 대한 강력한 제어를 위해 oracle의 암호관리 정책을 DBA에 의해 제어된다.
계정잠금=======특정 사용자가 지정된 횟수 이상 로그인 시도에 실패하면 서버는 자동으로 해당 사용자의 계정을 잠근다. DBA는 CREATE PROFILE명령문을 사용하여 로그인 시도시 허용된 실패횟수를 지정할 수 있다. DBA는 계정이 잠금상태로 남아 있게 되는 시간도 지정할 수 있다.
EX) CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 4 PASWORD_LOCK_TIME 30; ALTER USER scott PROFILE prof;--> 최대 로그인 실패횟수는 4이며, 계정이 잠금상태로 남아있게 되는 기간은 30일이다. 계정은 30일이 지난후에는 자동으로 잠금이 해제된다.EX) CONNECT시에 4번이상 실패한 후 CONNECT를 시도하면 "ORA-28000: the account is locked" 의 ERROR를 받게된다. 이때 해당 계정에 대한 LOCK정보는 DBA_USERS DICTIONARY에서 확인할 수 있다.
상태확인 SQL>SQL> SELECT USERNAME, ACCOUNT_STATUS, TO_CHAR(LOCK_DATE,'YY/MM/DD HH24:MI') LOCK_DATE, PROFILE FROM DBA_USERS;
USERNAME ACCOUNT_STATU LOCK_DATE PROFILE---------- ------------- ---------------- -----------SYS OPEN DEFAULTSYSTEM OPEN DEFAULTSCOTT OPEN DEFAULTKHLEE LOCKED(TIMED) 98/04/29 10:15 LKH
위와 같이 ACCOUNT_STATUS가 LOCK상태로 나타난다.
DBA가 계정잠금해제를 위한 기간을 지정하지 않으면 PASSWORD_LOCK_TIME이 기본값이 된다. DBA가 PASSWORD_LOCK_TIME을 UNLIMITED로 지정하면 시스템보안 담당자가 계정을 명시적으로 해게해야 한다.
EX) 명시적 해제방법 : ALTER USER scott ACCOUNT UNLOCK;
따라서 계정이 잠금상태로 남아있게 되는 기간은 DBA가 사용자에게 할당된 자원 프로파일을 구성하는 방법에 따라 달라진다.
사용자가 계정에 로그인하면 사용자의 실패한 로그인 시도횟수는 0으로 재설정된다.
보안담당자가 명시적으로 사용자의 계정을 잠글 수도 있다. 이경우 계정에 대한 잠금은 자동으로 해제되지 않는다. 보안 담당자가 계정잠금을 해제해야 한다.
EX) 명시적 잠금방법 : ALTER USER scott ACCOUNT LOCK;
암호변경 및 만기==============DBA는 CREATE PROFILE명령문을 사용하여 암호의 최대실행기간을 지정한다. 지정된 기간이 지나면 암호는 만기되고 사용자나 DBA가 암호를 변경해야 한다.
EX)CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 30 PASSWORD_LIFE_TIME 90;
ALTER USER scott PROFILE prof;
--> 만기되지 전에 동일한 암호를 90일 동안 사용할 수 있음을 나타낸다.
암호실행기간과 유예기간 설정(LIFE TIME & GRACE PERIOD)----------------------------------------------------DBA는 CREATE PROFILE명령문을 사용하여 유예기간을 지정할 수도 있다. 사용자는 암호가 만기된 다음 첫번째 DATABASE 로그인 시도부터 유예기간에 들어가게된다. 유예기간 동안에는 계정에 로그인할 때마다 경고메세지가 표시되며 유예기간이 끝날 때까지 계속 표시된다. 사용자는 유예기간안에 암호를 변경해야 한다. 유예기간 동안 암호를 변경하지 않으면 계정이 만기되고 암호가 변경되지 전까지 해당 계정으로 로그인할 수 없게 된다.
EX)CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 30 PASSWORD_LIFE_TIME 60 PASSWORD_GRACE_TIME 3;
ALTER USER scott PROFILE prof;
--> 암호의 실행기간이 60일이고 유에기간은 3일이다.사용자가 60일이 지난 어느날, 70일이나 100일 또는 그 이외의 날, 즉 암호 실행 기간 이후에 첫 번째 로그인을 시도하면 사용자는 암호가 3일 이후 만기된다는 경고 메세지를 받게된다. 사용자가 유예 기간의 첫 번째 날부터 3일 이내에 암호를 변경하지 않으면 사용자 계정은 만기된다. 보안 담당자가 명시적으로 계정을 만기시킬 수도 있다.
EX) 명시적 만기방법 : ALTER USER scott PASSWORD EXPPIRE;
<주의>-------암호관리 기능중 몇가지는 svrmgrl에서 지원되지 않는 부분이 있다.EXPIRE가 가까와지거나 유예기간에 들어간 경우 svrmgrl을 이용하여 로그인하려 할 때 PASSWORD의 만기일자에 대한 메세지가 나타나지 않는다.
같은 경우로, 만약 이미 암호가 이미 만기되었다면, 단순하게 ERROR MESSAGE를 내고 로그인이 거부된다. SQLPLUS를 사용하면 만기기간이 다가온다는 내용과, 새로운 PASSWORD를 받도록 나타난다.
암호 기록========DBA는 CREATE PROFILE 명령문을 사용하여 사용자가 암호를 다시 사용할 수 없는 기간 간격을 지정한다.
EX)CREATE PROFILE prof LIMIT PASSWORD_REUSE_TIME 60 PASSWORD_REUSE_MAX UNLIMITED;
--> 암호를 60일 동안 다시 사용할 수 없음을 나타낸다.
EX)CREATE PROFILE prof LIMIT PASSWORD_REUSE_MAX 3 PASSWORD_REUSE_TIME UNLIMITED;
--> 현재의 암호를 다시 사용하기 전에 암호를 변경해야 하는 횟수가 세번임을 나타낸다.
<주의> PASSWORD_REUSE_TIME과 PASSWORD_REUSE_MAX 중 하나를 지정하여야 한다.둘을 동시에 사용하지 않도록 한다.만약 PASSWORD_REUSE_TIME을 INTEGER값으로 지정하였다면, PASSWORD_REUSER_MAX는 UNLIMITED로 지정하여야 한다.반대의 경우로 PASSWORD_REUSE_MAX를 INTEGER값으로 지정한 경우 PASSWORD_REUSE_TIME는 UNLIMITED로 지정하여야 한다.
암호의 복잡성 검증================암호의 복잡성을 검증하고자 한다면 기본 PROFILE의 매개변수를 설정하는 PL/SQL SCRIPT(utlpwdmg.sql)를 사용하여 지정할 수 있다.
암호의 복잡성 검증루팀은 다음 검사를 수행한다.. 암호의 최소길이는 4인다.. 암호는 사용자ID와 동일하면 안된다.. 암호는 적어도 하나이상의 영문자,숫자,구두점으로 구성되어야 한다.. 암호는 WELCOME, ACCOUNT,DATABASE,USER와 같이 단순한 단어와 일치하면 안된다.. 암호는 이전 암호와 적어도 세자 이상 달라야 한다.
<권장> ALTER USER명령문 보다는 SQL*PLUS아 ENTERPRISE MANAGER와 같은 ORACLE TOOL에서 제공한 도구를 사용하기를 권장한다.
DBA는 PL/SQL이나 타업체의 도구를 사용하여 기존 암호검증루틴을 향상시키거나 자신의 암호검증 루틴을 생성할 수도 있다. 단 다음의 형식을 반드시 지켜야 한다.
routine_name (
userid_parameter IN VARCHAR(30),
password_parameter IN VARCHAR(30),
old_password_parameter IN VARCHAR(30)
)
RETURN BOOLEAN
새로운 루틴이 생성되면 해당루틴은 사용자의 PROFILE이나 시스템 기본 PROFILE을사용하여 암호검증루틴으로 할당하여야 한다. 암호검증 루틴은 반드시 SYS가 소유하여야 한다.
CREATE/ALTER PROFILE profile_name LIMIT
PASSWORD_VERIFY_FUNCTION routine_name
암호검증루틴 예제================+---------------------------------------------------+CREATE OR REPLACE FUNCTION verify_function (username varchar2, password varchar2, old_password varchar2) RETURN boolean IS n boolean; m integer; differ integer; isdigit boolean; ischar boolean; ispunct boolean; digitarray varchar2(20); punctarray varchar2(25); chararray varchar2(52); BEGIN digitarray:= '0123456789'; chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; punctarray:='!"#$%&()``*+,-/:;<=>?_'; --Check if the password is same as the username IF password = username THEN raise_application_error(-20001, 'Password same as user'); END IF; --Check for the minimum length of the password IF length(password) < isdigit =" FALSE"> ischar:=FALSE; FOR i IN 1..length(chararray) LOOP FOR j IN 1..m LOOP IF substr(password,j,1) = substr(chararray,i,1) THEN ischar:=TRUE; GOTO findpunct; END IF; END LOOP; END LOOP; IF ischar = FALSE THEN raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation'); END IF; --3. Check for the punctuation
--Check if the password differs from the previous password by at least --3 letters IF old_password = '' THEN raise_application_error(-20004, 'Old password is null'); END IF; --Everything is fine; return TRUE ; differ := length(old_password) - length(password); IF abs(differ) < 3 THEN IF length(password) < length(old_password) THEN m := length(password); ELSE m:= length(old_password); END IF; differ := abs(differ); FOR i IN 1..m LOOP IF substr(password,i,1) != substr(old_password,i,1) THEN differ := differ + 1; END IF; END LOOP; IF differ < 3 THEN raise_application_error(-20004, 'Password should differ by at least 3 characters'); END IF; END IF; --Everything is fine; return TRUE ; RETURN(TRUE); END;
+---------------------------------------------------+
[출처] [ORACLE]암호관리 정책 작성자 newag77
Subscribe to:
Posts (Atom)