[DDL, DCL] 객체의 구조 설정과 권한

Ⅰ. DDL

DATA DEFINITION LANGUAGE의 약자로 데이터 정의 언어를 뜻한다.

객체를 생성(CREATE), 변경(ALTER), 삭제(DROP)하는 구문

DDL 적용시 ROLLBACK 불가능

 

ⅰ. SQL(ORACLE)의 객체

 

오라클의 객체
테이블(TABLE)	뷰(VIEW)	시퀀스(SEQUENCE)
인덱스(INDEX)	패키지(PACKAGE)	동의어(SYNONYM)
트리거(TRIGGER)	프로시저(PROCEDUER)	함수(FUNCTION)
-	사용자(USER)	-

ⅱ. SQL(ORACLE)에서 자주 쓰이는 데이터타입

데이터타입	설명	제한
CHAR(길이)	고정길이 문자형	-
VARCHAR2(길이)	가변길이 문자형	최대 2000 Byte
LONG	가변길이 문자형	최대 2GB
DATE	날짜형	BC 4712/01/01~AD 4712/01/01
NUMBER	숫자형	-
LOB	가변길이 바이너리 데이터	최대 2GB(이미지, 실행파일 등 저장 가능)
BFILE	대용량 바이너리 데이터	최대 4GB(이미지, 실행파일 등 저장 가능)
ROWID	DB에 저장되지 않는 행을 식별하는 고유값	-

 

1. CREATE

1-1. 생성의 기초

--TABLE 생성
CREATE TABLE TB_NAME(
    COLUMN1 VARCHAR2(20),
    COLUMN2 NUMBER,
    ...
);

--복제 테이블 생성
CREATE TABLE TB_COPY
AS SELECT * FROM STUDENT;
--서브쿼리로 테이블을 복사하는 경우, NOT NULL 제약조건을 제외한 제약조건은 복사되지 않는다.

--VIEW 생성
CREATE VIEW TB_VIEW
AS SELECT * FROM STUDENT;

--USER 생성
CREATE USER USERNAME IDENTIFIED BY USERPWD;

도메인만 따온 빈 테이블 복제

CREATE TABLE EMP_OLD
AS SELECT EMP_ID,EMP_NAME,HIRE_DATE,SALARY
FROM EMPLOYEE
WHERE 1 = 0;

---

1-2. 컬럼에 주석달기

CREATE TABLE TEST(
    TEST_ID VARCHAR2(20)
);

COMMENT ON COLUMN TEST.TEST_ID IS '테스트아이디';

--실행 후 TEST 열 정보 확인--

COLUMN_NAME	DATA_TYPE	NULLABLE	DATA_DEFAULT	COLUMN_ID	COMMENT
TEST_ID	NUMBER	Yes	(null)	1	테스트아이디

---

1-3. 제약조건 (CONSTRAINTS)

제약조건이란? 데이터 무결성을 보장하기 위해 원하는 컬럼에 들어갈 수 있는 값의 범위에 제한을 거는 것

컬럼레벨 방식 : 테이블 생성 시 컬럼명 자료형 뒤에 연결하여 기술한다.

테이블레벨 방식 : 생성할 컬럼을 다 기술하고 괄호를 닫기 전에 제약조건을 기술한다.

 

제약조건명은 제약조건을 기술하기 전에 /* CONSTRAINT 제약조건명 */을 붙여줄 수 있다.
따로 설정하지 않은 경우 SYS_C000000의 형태로 이름이 자동생성된다.

 

NOT NULL : 삽입, 수정 시 NULL 값을 넣을 수 없게 제한한다. 컬럼레벨 방식만 가능.

CREATE TABLE TEST_NOTNULL(
TEST_ID VARCHAR2(10) NOT NULL,
TEST_PWD VARCHAR2(20) NOT NULL
);

---

UNIQUE : 중복값 제외. NULL은 가능

--컬럼레벨 방식
CREATE TABLE TEST_NOTNULL(
TEST_ID VARCHAR2(10) UNIQUE,
TEST_PWD VARCHAR2(20) UNIQUE NOT NULL
);

--테이블레벨 방식
CREATE TABLE TEST_NOTNULL(
TEST_ID VARCHAR2(10),
TEST_PWD VARCHAR2(20) /*CONSTRAINT TT_PWD_UQNN*/UNIQUE NOT NULL,
	/*CONSTRAINT TT_ID_UQ*/ UNIQUE(TEST_ID)
);

---

CHECK : 원하는 값만 넣을 수 있다. NULL 가능

--컬럼레벨 방식
CREATE TABLE TEST_NOTNULL(
	TEST_NAME VARCHAR2(10) NOT NULL,
	GENDER CHAR(1) CHECK(GENDER IN('M','F'))
);

--테이블레벨 방식
CREATE TABLE TEST_NOTNULL(
	TEST_NAME VARCHAR2(10) NOT NULL,
	GENDER CHAR(1),
    UNIQUE(TEST_NAME),
	CHECK(GENDER IN('M','F'))
);

---

PRIMARY KEY : 한 테이블 당 한 개의 컬럼만 설정 가능

UNIQUE+NOT NULL

복합키 : 두개 이상의 묶어서 하나의 기본키처럼 이용하는 방법.

--컬럼레벨 방식
CREATE TABLE TEST_PK(
TEST_ID VARCHAR2(10) PRIMARY KEY,
TEST_PWD VARCHAR2(20) NOT NULL
);

--테이블레벨 방식
CREATE TABLE TEST_PK(
TEST_ID VARCHAR2(10),
TEST_PWD VARCHAR2(20) NOT NULL,
PRIMARY KEY(TEST_ID)
);

--복합키 설정
CREATE TABLE TEST_PK(
TEST_NO NUMBER,
TEST_ID VARCHAR2(10),
TEST_PWD VARCHAR2(20) NOT NULL,
PRIMARY KEY(TEST_ID, TEST_NO)
);

---

FOREIGN KEY : 참조하는 자식 테이블(FOREIGN KEY 구문을 사용한 테이블)에 값이 지정될 경우 부모 테이블에 다른 설정을 하지 않으면 해당 컬럼을 삭제할 수 없다. 관계를 형성할 수 있음

--컬럼레벨 방식
CREATE TABLE TEST_FK(
TEST_ID VARCHAR2(10) PRIMARY KEY,
TEST_PWD VARCHAR2(20) NOT NULL
TEST_CLUB VARCHAR2(10) REFERENCES SCHOOL(CLUB)
);

--테이블레벨 방식
CREATE TABLE TEST_FK(
TEST_ID VARCHAR2(10) PRIMARY KEY,
TEST_PWD VARCHAR2(20) NOT NULL
TEST_CLUB VARCHAR2(10),
FOREIGN KEY(CLUB) REFERENCES SCHOOL(CLUB)
);

외래키 제약조건 부여 시, 조건을 설정하지 않으면 ON DELETE RESTRICTED(삭제 제한)으로 기본설정

ON DELETE RESTRICTED : 자식 데이터 때문에 부모 데이터를 삭제할 수 없다고 오류 발생

 

테이블 생성 시 자식테이블에 외래키 제약조건 설정하기

CREATE TABLE TEST(
    TEST_ID VARCHAR2(20) PRIMARY KEY,
    TEST_PWD VARCHAR2(20) NOT NULL,
    CLUB VARCHAR2(10) REFERENCES SCHOOL(CLUB) ON DELETE SET NULL
);--ON DELETE SET NULL : 부모데이터 삭제하면 자식데이터를 NULL로 변경

CREATE TABLE TEST(
    TEST_ID VARCHAR2(20) PRIMARY KEY,
    TEST_PWD VARCHAR2(20) NOT NULL,
    CLUB VARCHAR2(10) REFERENCES SCHOOL(CLUB) ON DELETE CASCADE
);--ON DELETE CASCADE : 부모테이블의 튜플을 삭제하면 
--		        외래키로 사용하는 자식 테이블의 해당 튜플도 같이 삭제

---

※계정 생성

CREATE USER USERNAME 
IDENTIFIED BY PASSWORD--해당 유저의 비밀번호를 설정하는 옵션
DEFAULT TABLESPACE TABLESPACENAME --기본 테이블스페이스 지정
TEMPORARY TABLESPACE TEMP_TABLESPACENAME --임시 테이블스페이스 지정
QUOTA SIZE/UNLIMITED ON --특정 테이블스페이스에 해당 유저 공간 용량을 설정
PROFILE PROFILE/DEFAULT--의 password 나 resource 에 대해 제한
PASSWORD EXPIRE--최초 접속 시 password 재설정
ACCOUNT LOCK/UNLOCK;--계정에 대한 lock 상태

---

2.ALTER

ALTER란? 객체 구조를 변경하는 구문. 

 

ADD : 컬럼을 추가. DEFAULT 값을 정해주지 않으면 NULL로 채워짐.

ALTER TABLE STUDENT ADD ST_LOCATION VARCHAR2(20);
ALTER TABLE STUDENT ADD ST_LOCATION VARCHAR2(20) DEFAULT '한국';

---

MODIFY : 컬럼에 수정할 수 있는 파트는 데이터 타입과 기본값이 있다.

현재 변경하고자 하는 컬럼에 이미 담겨있는 값과 완전히 다른 타입으로 변경은 불가. 크기가 줄어즐 수도 없다.

--테이터타입 수정 : MODIFY 수정할 컬럼명 바꾸고자하는 데이터타입
ALTER TABLE STUDENT MODIFY CLUB CHAR(10);

--DEFAULT 값 수정 : MODIFY 수정할 컬럼명 DEFAULT 바꾸고자하는 기본값
ALTER TABLE STUDENT MODIFY CLUB DEFAULT '없음';

---

DROP COLUMN

ALTER TABLE SUDENT DROP COLUMN CLUB;
--테이블에 최소 한개의 컬럼은 존재해야한다. 마지막 남은 컬럼은 삭제 불가.

※계정 수정

ALTER USER USERNAME
IDENTIFIED BY PASSWORD--비번 수정
DEFAULT TABLESPACE TABLESPACENAME--테이블 스페이스 수정
TEMPORARY TABLESPACE TEMP_TABLESPACENAME--임시 테이블 스페이스 수정
QUOTA SIZE/UNLIMITED ON TABLESPACE_NAME --용량 수정
ACCOUNT LOCK/UNLOCK;--계정 잠금/해제

---

3. DROP

DROP TABLE STUDENT;
--참조, 제약관계가 있으면 자식 테이블이 존재한다고 표시되며 삭제되지 않음.

DROP TABLE STUDENT CASCADE CONSTRAINTS;
--연결된 제약조건을 삭제하고 테이블을 삭제

---

4. RENAME

테이블, 컬럼, 제약조건의 이름을 바꾸는 구문

--테이블 명 변경 : RENAME 기존테이블명 TO 바꿀테이블명
RENAME TEST TO STUDENT;

--제약조건명 변경 : RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명
ALTER TABLE TABLENAME RENAME CONSTRAINT TEST_TID_PK TO TT_ID_PK;

--컬럼 명 변경 : RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명
ALTER TABLE TABLENAME RENAME COLUMN MATH TO MATH_GRADE;

오라클의 경우, 유저 명을 변경하는 명령이 따로 존재하지 않음

---

5. TRUNCATE

테이블의 구조만 남기고 튜플을 모두 제거한다.

TABLESPACE는 그대로 존재하고, ROLLBACK 불가능. 별도의 조건 제시 불가

TRUNCATE TABLE STUDENT;

---

Ⅱ. DCL

DCL이란?

Data Control Language로 권한을 부여하거나 회수하는 구문.
관리자계정에서 기본적으로 사용할 수 있다. 조건에 따라 다른 사용자도 사용할 수 있는 경우도 있다.

1. GRANT

--시스템권한
GRANT 권한1, 권한2... TO USERNAME;
	CREATE SESSION --계정 접속 권한
	CREATE TABLE --테이블 생성 권한
	CREATE VIEW --뷰 생성 권한
	CREATE SEQUENCE --시퀀스 생성 권한
	CREATE USER --계정 생성 권한
	--ETC
    
--객체 권한
GRANT 권한종류 ON 객체종류 TO USERNAME;

오브젝트 권한

객체권한	객체종류
SELECT	TABLE, VIEW, SEQUENCE
INSERT	TABLE, VIEW
UPDATE	TABLE, VIEW
DELETE	TABLE, VIEW

 

WITH ALL GRANT로 부여받은 권한을 다른 사용자에게도 부여할 수 있는 권한을 준다.

GRANT 권한1, 권한2... ON 개체 TO 사용자 WITH ALL GRANT;

---

2. REVOKE

권한을 회수하는 명령어.

REVOKE 권한1, 권한2 ... FROM USERNAME;

REVOKE 옵션

--다른 사용자에게 부여할 수 있는 권한만 취소
REVOKE GRNAT OPTION FOR UPDATE ON STUDENT FROM USERNAME;

--권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 취소
REVOKE UPDATE ON STUDENT FROM USERNAME;

---

3. ROLE

ROLE이란?

자주 쓰이는 권한들을 하나로 묶어, 쉽게 권한 부여와 회수를 할 수 있는 방법.

 

주로 CONNECT, RESOURCE로 데이터베이스 접속과 객체 생성 및 관리를 할 수 있는 기본적인 권한을 설정할 수 있다.

GRANT CONNECT, RESOURCE TO USERNAME;
REVOKE CONNECT, RESOURCE FROM USERNAME;

데이터 딕셔너리에서 각 롤의 권한을 확인할 수 있다.

SELECT *
FROM ROLE_SYS_PRIVS
WHERE ROLE IN ('CONNECT', 'RESOURCE');

---

확인해볼 내용

DDL>DROP
DROP TABLE STUDENT CASCADE CONSTRAINTS PURGE;-- 영구삭제
SELECT * FROM RECYCLEBIN;--휴지통 확인
PURGE RECYCLEBIN;
FLASHBACK TABLE 테이블명 TO BEFORE DROP;