데이터베이스 권한 관리와 데이터 조작

DCL: Data Control Language (데이터 제어 언어)

데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어이다.
주로 DBA(Database Administrator) 가 데이터 관리를 목적으로 사용한다.

 

DCL의 종류

• COMMIT

  • 명령에 의해 수행된 결과를 실제 물리적 디스크에 저장하고,
    DB 조작 작업이 정상적으로 완료되었음을 관리자에게 알려줌.

• ROLLBACK

  • DB 조작 작업이 비정상적으로 종료되었을 때,
    원래 상태로 복구하는 명령어.

• GRANT

  • DB 사용자에게 특정 권한을 부여하는 명령어.

• REVOKE

  • DB 사용자에게 부여한 권한을 취소하는 명령어.

 

테이블 및 속성에 대한 권한 부여 및 취소

사용 예시

-- 사용자에게 권한 부여
GRANT SELECT, INSERT ON 고객 TO user1;

-- 사용자 권한 취소
REVOKE SELECT, INSERT ON 고객 FROM user1;

CASCADE 옵션 사용 시, 해당 사용자가 다른 사용자에게 부여한 권한까지 모두 취소됨.

REVOKE SELECT ON 고객 FROM user1 CASCADE;

권한 종류

권한 종류에는 다음이 있음

• ALL: 모든 권한 부여

• SELECT: 테이블 조회 권한

• INSERT: 데이터 삽입 권한

• DELETE: 데이터 삭제 권한

• UPDATE: 데이터 수정 권한

추가적인 권한 옵션

• WITH GRANT OPTION

  • 부여받은 권한을 다른 사용자에게도 다시 부여할 수 있는 권한

GRANT SELECT ON 고객 TO user1 WITH GRANT OPTION;

GRANT OPTION FOR

• 다른 사용자에게 권한을 부여할 수 있는 권한을 취소

REVOKE GRANT OPTION FOR SELECT ON 고객 FROM user1;

ROLLBACK (롤백)

COMMIT 되지 않은 모든 변경 내용을 취소하고, DB를 이전 상태로 되돌리는 명령어.

트랜잭션이 성공적으로 끝나지 못하면, 일부 변경된 내용만 DB에 반영되어 비일관성 상태(Inconsistent State) 가 될 수 있음.
따라서 일부분만 완료된 트랜잭션은 ROLLBACK되어야 함.

사용 예시

BEGIN TRANSACTION;
UPDATE 고객 SET 이름 = 'KJ' WHERE 고객ID = 1;
ROLLBACK;

변경 내용이 취소되고, 고객 테이블은 원래 상태로 돌아감.

DML: Data Manipulation Language (데이터 조작어)

DB 사용자가 저장된 데이터를 실질적으로 관리하는 데 사용되는 언어.

• SELECT: 테이블에서 데이터를 조회

• INSERT: 새로운 데이터를 삽입

• DELETE: 데이터를 삭제

• UPDATE: 데이터를 수정

INSERT 문 예시

INSERT INTO 고객 (이름, 나이, 주소)
VALUES ('KJ', 34, '분당');

모든 속성을 사용할 경우 속성명 생략 가능

INSERT INTO 고객
VALUES (2, 'SJ', 31, '성남');

SELECT 문을 사용하여 다른 테이블의 데이터를 삽입할 수도 있음

INSERT INTO VIP고객 (이름, 나이, 주소)
SELECT 이름, 나이, 주소 FROM 고객 WHERE 나이 > 40;

• 고객 테이블에서 나이가 40 이상인 고객만 VIP고객 테이블에 삽입됨.

 

DELETE 문 예시

DELETE FROM 고객 WHERE 나이 < 18;

모든 데이터 삭제 (WHERE 생략 시)

DELETE FROM 고객;

(⚠️ 단, 테이블 자체는 삭제되지 않음!)

테이블 자체를 삭제하려면 DROP TABLE (DDL) 사용

 

UPDATE 문 예시

UPDATE 고객
SET 주소 = '대구'
WHERE 이름 = 'SH';

• SH 고객의 주소를 '대구'로 변경.

모든 행의 데이터를 변경하려면 WHERE 절을 생략

UPDATE 고객 SET 나이 = 나이 + 1;

모든 고객의 나이를 1살씩 증가.

 

SELECT 문 예시

SELECT 절

• PREDICATE: 검색할 튜플 수를 제한하는 명령어

• DISTINCT: 중복을 제거하고 한 번만 표시

• AS: 속성명을 다른 이름으로 표시

• FROM: 데이터를 검색할 테이블 지정

• WHERE: 검색 조건 지정

SELECT DISTINCT 이름, 주소 FROM 고객 WHERE 나이 >= 30;

설명:

• 중복을 제거(DISTINCT)하여 나이가 30 이상인 고객의 이름과 주소를 조회.

 

GROUP BY 예시

각 도시별 고객 수를 조회하는 예시

SELECT 주소, COUNT(*) AS 고객수
FROM 고객
GROUP BY 주소;

설명:

• GROUP BY 주소 → 같은 주소(도시)별로 그룹화

• COUNT(*) → 각 그룹에 포함된 고객 수 계산

 

HAVING 예시

고객 수가 2명 이상인 도시만 조회

SELECT 주소, COUNT(*) AS 고객수
FROM 고객
GROUP BY 주소
HAVING COUNT(*) >= 2;

설명:

• HAVING을 사용하여 그룹화된 결과에 조건 적용

• 고객 수(COUNT(*))가 2명 이상인 도시만 출력

 

ORDER BY 예시 (정렬)

SELECT 이름, 나이
FROM 고객
ORDER BY 나이 DESC;

설명:

• ORDER BY 나이 DESC → 나이를 기준으로 내림차순 정렬.

오름차순 정렬 (기본값)

ORDER BY 나이;