데이터베이스 Day5 - 데이터 제어어(DCL)

DCL(Data Control Language)

• DDL / DML / DCL

• 계정 관리

• 데이터의 사용 권한 관리

• 데이터베이스 트랜잭션 명시(COMMIT / REVOKE)

• COMMIT: 작업 완료

• REVOKE

  • 작업 취소
  • 이미 부여된 데이터베이스 객체의 권한 취소

• 트랜잭션 처리 중에 오류 발생: COMMIT 하지 않고 REVOKE 작업

  • 처리하기 이전으로 되돌림

• GRANT: 데이터베이스 객체에 권한 부여

권한

• 특정 유형의 SQL 문을 실행하거나 다른 사용자의 객체를 사용할 수 있는 권리

권한의 종류

• 시스템 권한
• 객체 권한
  • 특정 객체를 조작할 수 있는 권한
  • DML 사용 권한 : SELECT, INSERT, UPDATE, DELETE

예제

• 사용자 계정 조회

use mysql;
SELECT * FROM user;

• 사용자 계정 생성

CREATE USER 계정@호스트 identified by 비밀번호
    호스트
        localhost: 로컬에서 접근 가능
        192.168.172.1: 특정 IP에서 접근 가능
        '%': 어디에서나 접근 가능

CREATE USER newuser1@'%' identified by '1111'

• 비밀번호 변경

SET PASSWORD for '계정명'@호스트 = '새 비밀번호';

SET PASSWORD  for 'newuser1'@'%' = '1234';

• 계정 삭제

DROP USER 계정@호스트;

DROP USER 'newuser1'@'%';

• 권한 조회

SHOW GRANTS FOR 사용자 계정;

SHOW GRANTS FOR dbuser;

• 권한 부여

-- 특정 권한 부여
GRANT 권한 ON 데이터베이스.테이블 TO 계정@호스트;

-- 모든 권한 부여
GRANT ALL PRIVILEGES ON *.* TO 계정@'%';

-- 특정 DB의 모든 테이블에 특정 권한 부여
GRANT select, insert, update, delete ON DB명.* TO 계정@호스트;

• 권한 삭제

-- 특정 DB의 모든 테이블에 대한 권한 삭제
REVOKE ALL PRIVILEGES ON DB명.* FROM 계정@호스트;

-- 특정 DB의 모든 테이블에 대해 특정 권한 삭제
REVOKE select, insert, update, delete ON DB명.* FROM 계정@호스트;

실습

• 계정 생성

CREATE USER newuser1@'%' identified by '1111'

• newuser1의 권한 조회
  • 서버 접속은 가능하지만, 아무런 스키마는 보이지 않는 상태
  • => 스키마 사용 권한 없음

SHOW GRANTS FOR newuser1;

• 모든 권한 부여
  • 다시 접속하면 모든 스키마 / 데이터 접근 가능함을 확인 가능

GRANT ALL PRIVILEGES ON *.* TO newuser1@'%';

• newuser1의 SELECT 권한 삭제
  • 테이블 접근 가능한지 확인: Tables could not fetched
  • 테이블 접근 불가

REVOKE select ON *.* FROM newuser1@'%';

• newuser1 권한 조회
  • select 권한 없음

SHOW GRANTS FOR newuser1;

• sqldb3의 모든 테이블에 select 권한 부여
  • 다른 스키마(데이터베이스) 테이블: Tables could not fetched
  • sqldb3만 테이블 접근 가능

GRANT select ON sqldb3.* TO newuser1@'%';

SHOW GRANTS FOR newuser1;
-- select 권한 추가됨

백업 및 복구(EXPORT / IMPORT)

• 데이터베이스를 주기적으로 백업해두거나 다른 서버로 이관할 때 사용
• 백업: Export
• 복구: Import
• 메뉴 항목 Server
  • Data Export
  • Data Import