Day_07-1 (DCL 명령어 실습)

6일차 수업내용 간단 요약

• 어제는 클라이언트 서버 <-> 백엔드 서버 <-> DB 서버를 각각 구성 후에 전체적인 동작방식을 확인하였다.
• 클라이언트 서버와 백엔드 서버는 윈도우 환경으로 구성하였고, DB서버는 리눅스
  환경으로 구성하였으며, 구성할때는 VMware 가상머신을 사용하였다.
• 실습결과, 백엔드 서버에서 DB 서버 연결을 해주면, 클라이언트 서버에서 동작하는
  내용이 DB 서버에 반영되는 것을 확인할 수 있었고, 반대로 DB 서버에서 동작한 내용들 역시 클라이언트 서버에서 확인이 가능했다.

• 예를 들어 "Accounts" 라는 회원 테이블이 있고, 속성으로 "email 주소", "name", "password", "active" 가 있을때 사용자의 동작에 따라 작동하는 쿼리문을 예를들어 보면,
  
  
• 사용자 회원가입
  INSERT INTO Accounts(email, password, name) VALUES('test@test.com', 'test123', 'user1');
  
  
• 회원가입한 계정으로 클라이언트 접속
  SELECT password, name FROM Accounts WHERE email = 'test@test.com';
  
  
• 회원가입한 계정의 패스워드 변경
  UPDATE Accounts SET password = 'test456' WHERE email = 'test@test.com';
  
  
• 회원가입한 계정 탈퇴
  DELETE FROM Accounts WHERE email = 'test@test.com'; 또는
  UPDATE Accounts SET active = 'NO' WHERE email = 'test@test.com';
  
  
• 이처럼 클라이언트 서버, DB 서버, 백엔드 서버가 상호간에 유기적으로 동작하는 것을
  알 수 있었던 시간이었다.

7일 차 본문으로 들어가기에 앞서 오늘 배운 SQL 쿼리문 정리 ✍

• 뷰(View) : 하나 이상의 테이블로부터 유도되어 만들어진 가상의 테이블
    　　　　이미 만들어져있는 테이블에서 내가 보여주고 싶은 것만 가져온 테이블
    　　　　즉, 실제로 데이터가 저장되어 있는 것은 아니나, 다른 테이블이나 다른 뷰에      　　　저장되어 있는 데이터를 보여주는 역할
  
  
• 뷰(View)의 장/단점
  • 장점 : 특정 사용자에게 테이블 전체가 아닌 필요한 부분만을 보여주는 것이 가능
       　　복잡한 쿼리를 단순화, 쿼리의 재사용 가능
  • 단점 : 삽입, 삭제, 갱신 작업에 제한 사항이 있고, 자신만의 인덱스를 가질 수 없음
    
    
• 뷰(View) 생성 : CREATE VIEW [뷰이름] AS SELECT 속성1, 속성2, ...
   　　　　　　FROM [테이블명] WHERE 조건;
• 뷰(View) 삭제 : DROP VIEW [뷰이름];

• 인덱스(Index) : 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용
     　　　　　　자주 사용되는 속성으로 만들어진 원본 테이블의 사본과도 같은 것
     　　　　　　DBMS는 DB에서 데이터를 검색할 때 테이블에서 내용을 하나씩
     　　　　　　검색하기 때문에 테이블이 크면 클수록 데이터를 검색하는 시간이
     　　　　　　느려진다. 이때, 인덱스를 사용하면 테이블 전체를 읽지 않고 검색할 수
     　　　　　　있도록 해줘 검색 성능을 높여준다.
  
  
• 인덱스(Index) 생성 : CREATE INDEX [인덱스명] ON [테이블명] (속성1, 속성2, ...);
   　　　　　　조회 : SHOW INDEX FROM [테이블명];
   　　　　　　삭제 : ALTER TABLE [테이블명] DROP INDEX [인덱스명];

• DCL 명령어 ( GRANT 와 REVOKE)
  • GRANT : 권한부여 명령어 / GRANT [권한] ON [DB이름.테이블명] TO [사용자];
  • REVOKE : 권환회수 명령어 / REVOKE [권한] ON [DB이름.테이블명] FROM [사용자];
    
    
• DB 목록 보기 : SHOW databases;
• DB 사용자 정보 보기 : USE mysql; -> SELECT user, host FROM user\G;
  • \G : 한 행이 너무 길어서 글자를 보기 힘들때 사용하면 행별로 보기 좋게 출력됨

DCL 명령어 실습하기

• 관리자(root) 계정으로 로그인하여 smart 라는 이름의 DB를 새로 생성한 뒤
  member 라는 이름의 테이블( 속성 : idx, email, password, name )을 만들어 준다.

  

• 사용자 user1 과 user2 를 어떤 IP에서든지 접속 할 수 있도록 새로 생성해준다.

• user1 사용자에게 SELECT 권한을 부여하고, SELECT 권한을 user2 에게 부여할 수 있도록 설정해준다. ( WITH GRANT OPTION 을 사용 )

  

• user1 으로 접속한 뒤 member 테이블에 대한 SELECT 권한이 부여되었는지 확인하고,
  user2 에게 마찬가지로 SELECT 권한을 부여해준다.
  
  

• user2 로 다시 접속하여 SELECT 권한과 INSERT 권한을 확인한다.

  

• 위에서 보는바와 같이 부여한 SELECT 권한에 대해서는 정상적으로 동작하였고,
  부여하지 않은 INSERT 권한은 거부된 것을 볼 수 있었다.
  (SELECT 했을 때 출력된 결과물은 사전에 미리 입력해 둔 데이터이다.)

• 마지막으로 user1 에게 부여한 SELECT 권한을 회수해보겠다.

  

• user1 으로 접속하여 SELECT 실시 한 결과 권한이 거부됬다는 것을 볼 수 있었지만, user2 에게 부여된 SELECT 권한은 ❗정상적으로 동작❗하는 것을 확인할 수 있었다.

• 예전에는 CASCADE 옵션을 사용하여 user2 의 권한도 같이 회수할 수 있는 기능이 있었으나, 현재 MySQL 8 버전에서는 지원이 되지 않는다고 한다.🤔

다음 글에서 이어서 계속 👉