오라클 유저 (Oracle User) 관련 정리

Oracle 11g 이상부터 User생성 시 주의사항

※ Oracle 11g 부터는 User생성 시 암호 유지간이 기본 180일로 정해져 있다.

   User 생성 후 180일이 지나면 암호가 자동으로 잠겨 로그인이 불가능하게 되어있다.

 1) 시스템 계정 접속

 2) 유저 암호 유지기간 조회
 > SELECT USERNAME, EXPIRY_DATE FROM DBA_USERS WHERE USERNAME = '유저명';

 3) 현재 시스템의 유저 암호 유지기간 조회
 > SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

    PROFILE			RESOURCE_NAME		RESOURCE_TYPE		LIMIT
    ----------------------------------------------------------------------------------
    DEFAULT			PASSWORD_LIFE_TIME	PASSWORD		180
    MONITORING_PROFILE		PASSWORD_LIFE_TIME	PASSWORD		DEFAULT
 
 4) PROFILE이 DEFAULT인 열의 LIMIT항목이 UNLIMITED가 아니면 유지기간이 설정되어있는 것이다.

 5) 암호 유지기간 해제
 > ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

 6) 암호 유지기간을 다시 조회하면 LIMIT 항목이 UNLIMITED로 변경되어있는 것을 확인 할 수 있다.

    PROFILE			RESOURCE_NAME		RESOURCE_TYPE		LIMIT
    ----------------------------------------------------------------------------------
    DEFAULT			PASSWORD_LIFE_TIME	PASSWORD		UNLIMITED
    MONITORING_PROFILE		PASSWORD_LIFE_TIME	PASSWORD		DEFAULT

 7) 암호 유지기간을 다시 설정하려면
 > ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 숫자;

 

User Control

1. USER 생성
 > CREATE USER 유저명
    IDENTIFIED BY 비밀번호
    DEFAULT TABLESPACE 사용할기본테이블스페이스
    TEMPORARY TABLESPACE 임시로사용될테이블스페이스
    QUOTA UNLIMITED ON 할당될테이블스페이스지정

Ex)
 > CREATE USER HJS
    IDENTIFIED BY 1234
    DEFAULT TABLESPACE TS_HJS_DATA
    TEMPORARY TABLESPACE TS_HJS_TEMP
    QUOTA UNLIMITED ON TS_HJS_DATA
  - HJS라는 사용자를 생성하고 
    비밀번호를 1234로 지정
    기본 테이블스페이스를 TS_HJS_DATA로 지정
    임시 테이블스페이스를 TS_HJS_TEMP로 지정
    사용자가 사용할 테이블스페이스 영역을 할당


2. USER 권한
 ※ 권한 부여
 > SYSTEM계정에서 실행한다.
 > GRANT 권한 TO 유저명			- 하나씩 생성
 > GRANT 권한, 권한, 권한 TO 유저명	- 여러개 한번에 생성

 ※ 권한 종류
 1) CONNECT			: 접속권한 - ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE,  
                              CREATE SESSION, CREATE SYNONYM , CREATE TABLE, CREATE VIEW
 2) RESOURCE	    : 모든권한 - CREATE CLUSTER, CREATE PROCEDURE, CREATE TRIGGER, CREATE SEQUENCE, 
                               CREATE TABLE ,INDEXTYPE
				 ※ 각각 하나씩 줄 수도 있다.,
 3) DBA				: ORACLE DBAD의 모든 시스템 권한 - 일반 계정은 되도록이면 주지 말자!
 4) SELECT_CATELOG_ROLE		: 모든 테이블과 뷰에 대한 조회 권한
 5) DELETE_CATELOG_ROLE		: 모든 패키지에 대한 삭제 권한  
 6) EXECURE_CATELOG_ROLE	: 모든 패키지에 대한 실행 권한

 Ex) GRANT CONNECT , RESOURCE, DBA TO HJS;

 7) READ, WRITE ON DIRECTORY 지정된디렉토리명	: 해당 디렉토리의 읽기, 쓰기권한 부여

 Ex) GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO HJS;

 ※ 권한 삭제
 > REVOKE 권한종류 FROM 유저명

 Ex) REVOKE DBA FROM HJS;


3. USER 삭제
 ※ 권한 부여는 SYSTEM계정에서 실행한다.

 > DROP USER 유저명;
  - 해당 계정에 데이터가 없어야 한다.
 > DROP USER 유저명 CASCADE;
  - CASCADE : 해당 계정에 데이터가 있어도 무시하고 삭제한다.
  
 ※ USER 세션이 있을 경우...
 > SELECT USERNAME, SID, SERIAL# FROM V$SESSION;
  - SYSTEM계정에서 모든 세션정보를 출력한다. 
 
 > ALTER SYSTEM KILL SESSION 'SID, SERIAL#';
  - USERNAME으로 삭제할 SID와 SERIAL#의 값을 알아내어 삭제한다.
  - 모든세션을 삭제후에 DROP USER 유저명 CASCADE; 로 사용자를 삭제한다.


4. USER 정보조회
 1) SHOW USER : 현재 계정의 이름을 출력한다.(sqlplus에서만 실행가능)
 
 2) SELECT USER FROM DUAL : 현재 계정의 이름을 출력한다.(모든 툴에서 실행가능)

 3) SELECT * FROM ALL_USERS : 현재 ORACLE서버의 모든 계정을 보여준다.(USERNAME, USER_ID, CREATED)

 4) SELECT * FROM DBA_USERS : 현재 ORACLE서버의 모든 계정을 보여준다.(SYSTEM계정에서만 가능)
   - ACCOUNT_STATUS : 해당계정이 LOCK상태인지 아닌지를 알 수 있다.


5. ALTER USER : 유저정보변경
 > ALTER USER 유저명 변경할정보;

Ex) 
 > ALTER USER IIS DEFAULT ROLE ALL;
 > ALTER USER IIS QUOTA UNLIMITED ON TS_IIS_INDEX;
 > ALTER USER IIS QUOTA UNLIMITED ON TS_IIS_DATA;

 1) 패스워드 변경
 > ALTER USER 유저명 IDENTIFIED BY 변경하려는패스워드;


6. LOCK걸린 USER 정보조회 & UNLOCK
 1) 정보조회
 > SELECT USERNAME,ACCOUNT_STATUS,
    TO_CHAR(LOCK_DATE,'YYYY.MM.DD HH24:MI') LOCK_DATE
    FROM DBA_USERS;
    
 2) UNLOCK
 > ALTER USER 유저명 ACCOUNT UNLOCK;


7. 유저 패스워드 만료시키기
 > ALTER USER 유저명 PASSWORD EXPIRE;