-- 사용자 삭제
DROP USER user_name ;

-- 사용자와 사용자가 생성한 모든 객체 삭제
DROP USER user_name CASCADE;

SYS 사용자 패스워드 변경 방법은 'sys 유저 패스워드 변경 방법' 페이지를 참고하세요.

REVODE '시스템 권한' FROM 사용자;

-- 수행 예
REVOKE CREATE TABLE FROM USER1;

-- user6 사용자가 employees 테이블의 SELECT, DELETE 권한을 다른 사용자에게 부여할 수 있는 권한을 가지고 있는 경우
REVOKE SELECT, DELETE ON employees FROM user6;
GRANT SELECT, DELETE ON employees TO user6;

$ ls -l $ALTIBASE_HOME/conf/altibase.properties
-rw-r--r-- 1 heejung.lee heejung.lee 27652 2014-09-26 12:52 /data/heejung.lee/63119/conf/altibase.properties

$ find $ALTIBASE_HOME -name altibase.properties -exec chmod 600 {} \;

$ ls -l $ALTIBASE_HOME/conf/altibase.properties
-rw------- 1 heejung.lee heejung.lee 27652 2014-09-26 12:52 /data/heejung.lee/63119/conf/altibase.properties

$ ls -l $ALTIBASE_HOME/logs/loganchor*
-rw-r----- 1 heejung.lee heejung.lee 14156 2014-09-26 12:53 /data/heejung.lee/63119/logs/loganchor0
-rw-r----- 1 heejung.lee heejung.lee 14156 2014-09-26 12:53 /data/heejung.lee/63119/logs/loganchor1
-rw-r----- 1 heejung.lee heejung.lee 14156 2014-09-26 12:53 /data/heejung.lee/63119/logs/loganchor2


$ find $ALTIBASE_HOME --name loganchor* -exec chmod 600 {} \;

$ ls -l $ALTIBASE_HOME/logs/loganchor*
-rw------- 1 heejung.lee heejung.lee 14156 2014-09-26 12:53 /data/heejung.lee/63119/logs/loganchor0
-rw------- 1 heejung.lee heejung.lee 14156 2014-09-26 12:53 /data/heejung.lee/63119/logs/loganchor1
-rw------- 1 heejung.lee heejung.lee 14156 2014-09-26 12:53 /data/heejung.lee/63119/logs/loganchor2

$ chmod 600 ~/.sh_history

SELECT OBJECT_OWNER_NAME, SYNONYM_NAME FROM SYSTEM_.SYS_SYNONYMS_ WHERE OBJECT_OWNER_NAME = 'SYSTEM_';

DROP PUBLIC SYNONYM synonym_name;

ALTER USER USER1 LIMIT (FAILED_LOGIN_ATTEMPTS 3, PASSWORD_LOCK_TIME 3);

CREATE OR REPLACE FUNCTION pwd_verify_function
( username varchar(20),
    password varchar(20))
RETURN varchar(100)
AS
result        varchar(100);
pwdLength     integer;
isDigit       boolean;
isChar        boolean;
isPunction    boolean;
digitArray    varchar(20);
punctionArray varchar(25);
charArray     varchar(52);

BEGIN
    digitArray    := '0123456789';
    charArray     := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    punctionArray :='!"#$%&()``*+,-/:;<=>?_';

    -- Check if the password is same as the username
    IF LOWER(password) = LOWER(username) THEN
        result := 'Password same as or similar to user';
        RETURN result;
    END IF;

    -- Check for the minimum length of the password
    IF LENGTH(password) < 4 THEN
        result := 'Password length less than 4';
        RETURN result;
    END IF;

    -- Check if the password is too simple.
    IF LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'altibase', 'computer', 'abcd') THEN
        result := 'Password too simple';
        RETURN result;
    END IF;

    -- Check if the password contains at least one letter, one digit and one
    -- punctuation mark.
    -- 1. Check for the digit
    isDigit := FALSE;
    pwdLength := length(password);
    FOR i IN 1...10 LOOP
    FOR j IN 1...pwdLength LOOP
    IF substr(password,j,1) = substr(digitArray,i,1) THEN
        isDigit := TRUE;
        GOTO findchar;
    END IF;
    END LOOP;
    END LOOP;
    IF isDigit = FALSE THEN
        result := 'Password should contain at least one digit, one character and one punctuation';
        RETURN result;
    END IF;

    -- 2. Check for the character
    <<findchar>>
    isChar := FALSE;
    FOR i IN 1...length(charArray) LOOP
    FOR j IN 1...pwdLength 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
        result := 'Password should contain at least one digit, one character and one punctuation';
        RETURN result;
    END IF;

    -- 3. Check for the punctuation
    <<findpunct>>
    isPunction := FALSE;
    FOR i IN 1...length(punctionArray) LOOP
    FOR j IN 1...pwdLength LOOP
    IF substr(password,j,1) = substr(punctionArray,i,1) THEN
        isPunction := TRUE;
        GOTO endsearch;
    END IF;
    END LOOP;
    END LOOP;
    IF isPunction = FALSE THEN
        result := 'Password should contain at least one digit, one character and one punctuation';
        RETURN result;
    END IF;

    <<endsearch>>

    result := 'TRUE';
    RETURN result;
END;
/

ALTER USER USER1 LIMIT (PASSWORD_LIFE_TIME 7);