정보처리산업기사 - SQL 활용 - DCL

※ 공부 내용의 복습 개념으로 정리된 글입니다. - 출처 시나공 

 

DCL(Data Control Language, 데이터 제어어)의 개념

DCL(데이터 제어어)는 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어입니다.

• DCL은 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용합니다.
• DCL에는 GRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINT 등이 있습니다.

 

CRANT / REVOKE

데이터베이스 관리자가 데이터베이스 사용자에게 권한을 부여하거나 취소하기 위한 명령어입니다.

• GRANT : 권한 부여를 위한 명령어
• REVOKE : 권한 취소를 위한 명령어

사용자등급 지정 및 해제

- GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
- REVOKE 사용자등급 FROM 사용자_ID_리스트;

 

예제1

사용자 ID가 "NABI"인 사람에게 데이터베이스 및 테이블을 생성할 수 있는 권한을 부여하는 SQL문을 작성하시오.

GRANT RESOURCE TO NABI;

 

예제2

사용자 ID가 "STAR"인 사람에게 단순히 데이터베이스에 있는 정보를 검색할 수 있는 권한을 부여하는 SQL문을 작성하시오.

GRANT CONNECT TO STAR;

 

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

- GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
- REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];

• 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등
• WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여함.
• GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소합니다.
• CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소합니다.

예제3

사용자 ID가 "NABI"인 사람에게 <고객> 테이블에 대한 모든 권한과 다른 사람에게 권한을 부여할 수 있는 권한까지 부여하는 SQL문을 작성하시오.

CRANT ALL ON 고객 TO NABI WITH GRANT OPTION;

 

예제4

사용자 ID가 "STAR"인 사람에게 부여한 <고객> 테이블에 대한 권한 중 UPDATE 권한을 다른 사람에게 부여할 수 있는 권한만 취소하는 SQL문을 작성하시오.

REVOKE GRANT OPTION FOR UPDATE ON 고객 FORM STAR;

 

 

※ 사용자등급

• DBA : 데이터베이스 관리자
• RESOURCE : 데이터베이스 및 테이블 생성 가능자
• CONNECT : 단순 사용자

 

※ 사용자 종류

• 사용자 ID : 단순 사용자
• ROLE : 사용자에게 부여할 수 있는 권한들을 하나로 묶은 그룹
• RUBLIC : 모든 사용자

 

 

COMMIT

트랜잭션이 성공적으로 끝나면 데이터베이스가 새로운 일관성(Consistency) 상태를 가지기 위해 변경된 모든 내용을 데이터베이스에 반영하여야 하는데, 이때 사용하는 명령어 COMMIT입니다.

• COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT되고, DML이 실패하면 자동으로 ROLLBACK이 되도록 Auto Commit 기능을 설정할 수 있습니다.

 

※ 트랜잭션(Transaction)

• 트랜잭션은 데이터베이스에서 하나의 논리적 기능을 수행하기 위한 일련의 연산 집합으로서 작업의 단위가 됩니다.
• 트랜잭션은 데이터베이스 관리 시스템에서 회복 및 병행 제어시에 처리되는 작업의 논리적 단위입니다.
• 하나의 트랜잭션은 COMMIT 되거나 ROLLBACK 되어야 합니다.

※ COMMIT 명령 사용 여부

트랜잭션이 시작되면 데이터베이스의 데이터를 주기억장치에 올려 처리하다가 COMMIT 명령이 내려지면 그때서야 처리된 내용을 보조기억장치에 저장합니다. 그러니까 COMMIT 명령을 사용하지 않고 DBMS를 종료하면 그때까지 작업 했던 모든 내용이 보조기억장치의 데이터베이스에 하나도 반영되지 않고 종료되는 것이지요. 이처럼 실수로 COMMIT 명령 없이 DBMS를 종료하는 것에 대비하여 대부분의 DBMS들은 Auto Commit 기능을 제공하고 있습니다.

 

※ Auto Commit 설정 명령

• Oracle
  • 설정 : set autocommit on;
  • 해제 : set autocommit off;
  • 확인 : show autocommit;
• MySQL
  • 설정 : set autocommit = true;
  • 해제 : set autocommit = false;
  • 확인 : select @@autocommit;

 

ROLLBACK

ROLLBACK은 아직 COMMIT되지 않은 변경된 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어입니다.

• 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 데이터베이스에 반영되는 비일관성(Inconsistency)인 상태를 가질 수 있기 때문에 일부분만 완료된 트랜잭션은 롤백(Rollback)되어야 합니다.

 

728x90

 

SAVEPOINT

SAVEPOINT는 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어입니다.

• 저장점을 지정할 때는 이름을 부여하며, ROLLBACK 시 지정된 저장점까지의 트랜잭션 처리 내용이 취소됩니다.

 

예제1

<사원> 테이블에서 '사원번호'가 40인 사원의 정보를 삭제한 후 COMMIT을 수행하시오.

DELETE FROM 사원 WHERE 사원번호 = 40;
COMMIT;

 

해설

 

예제2

'사원번호'가 30인 사원의 정보를 삭제하시오.

DELETE FROM 사원 WHERE 사원번호 = 30;

 

해설

 

예제3

SAVEPOINT 'S1'을 설정하고 '사원번호'가 20인 사원의 정보를 삭제하시오.

SAVEPOINT S1;
DELETE FROM 사원 WHERE 사원번호 = 20;

 

해설

 

예제4

SVAEPOINT 'S2'를 설정하고 '사원번호'가 10인 사원의 정보를 삭제하시오.

SVAEPOINT S2;
DELETE FROM 사원 WHERE 사원번호 = 10;

 

해설

 

예제5

SAVEPOINT 'S2'까지 ROLLBACK을 수행하시오.

ROLLBACK TO S2;

 

해설

 

예제6

SAVEPOINT 'S1'까지 ROLLBACK을 수행하시오.

ROLLBACK TO S1;

 

해설

 

예제7

SVAEPOINT 없이 ROLLBACK을 수행하시오.

ROLLBACK;

 

해설

 

 

※ DELETE문

DELETE문은 레코드를 삭제할 때 사용하는 명령어입니다.