2023.11.07(화) - Day 7

• 강의 내용
  • Database 5
    • view, index, 권한

이론

뷰는 원래 저장되어 있는 테이블이 아니다.
뷰는 원래의 테이블과 용도가 다르다.
즉, 보기 쉽게 보여주는 역할만 한다는 의미이다.

제약 조건이 많아서 INSERT, DELETE 등의 연산을 수행하지 않는다.
SELECT 용도만을 위한 가상의 테이블이다.

=> 안에 데이터가 없는, 속성만 있는 (틀만 있는) 표이다.

뷰는 원하는 정보만 선택해서 조회한다.

WHERE과 같은 Query문을 사용하면 DB는 모든 테이블의 모든 행을 살펴보는데
예컨대 데이터가 1,000만 건이 있고, 'abc'가 들어간 데이터만 조회하려고 한다면
'abc'가 들어가 있는지 없는지 1,000만 건을 모두 찾아봐야 하는 문제가 생긴다.

하지만 INDEX를 만들어 놓으면 성능이 올라간다.
즉, 빨리 검색할 수 있다.

실습

• 제공받은 employee 테이블을 이용한다.
  
  

• 각 사원의 풀 네임과 부서 이름, 급여를 조회하는 SQL

• employees에서 풀 네임,
  dept_emp에서 사원의 부서 정보,
  가져온 부서 정보에 해당하는 부서 이름을 departments에서,
  급여는 salaries에서 가져온다.

  SELECT employees.emp_no, first_name, last_name, dept_name, salary
  FROM employees
  LEFT OUTER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
  LEFT OUTER JOIN departments ON departments.dept_no = dept_emp.dept_no
  LEFT OUTER JOIN salaries ON employees.emp_no = salaries.emp_no;

  
  

  매번 위와 같은 쿼리를 사용하면 복잡하니까 VIEW로 만들어서 사용하자.

  CREATE VIEW employees_info AS
    SELECT employees.emp_no, first_name, last_name, dept_name, salary
    FROM employees
    LEFT OUTER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
    LEFT OUTER JOIN departments ON departments.dept_no = dept_emp.dept_no
    LEFT OUTER JOIN salaries ON employees.emp_no = salaries.emp_no;

  
  

  생성한 VIEW로 조회해도 같은 결과가 출력된다.
  간단하게 조회할 수 있다.

  SELECT * FROM employees_info;

  
  

• UPDATE

  • employees 테이블의 값을 변경했는데도 VIEW에도 변경 사항 반영됨.

    UPDATE employees SET first_name='abc' WHERE emp_no=10011;
    
    SELECT * FROM employees_info;

    
    

• DB가 VIEW를 조회해도 결국 내부에서 SELECT문을 실행시킨다.
  결국 VIEW를 서능 개선을 위해서 사용하는 건 아니라고 할 수 있다.
  
  

• <VIEW 사용 목적>

  • 단순히 간편하게 사용하려는 목적으로.
  • 보여지면 안되는 중요한 정보가 있을 때 보여주는 정보만 일부 선택해서
    VIEW로 만들고 이를 보여주기 위한 목적으로 사용. (보안 목적)
    
    

• VIEW는 수정이 되지 않는다.
  DROP하고 다시 새로 만들어야 한다.

  
  
  

SQL Injection

백엔드 코드는 백엔드 서버에서 실행된다.
프론트엔드 코드는 client에서 실행된다.

SELECT * FROM salaries WHERE salary > 80000;

CREATE INDEX salariesSalary ON salaries(salary);

SHOW INDEX FROM salaries;

cf.)
RDB(oracle, mysql, mssql...)로 검색?
SQL

NOSQL?
Elastic Search -> 역색

Redis
MongoDB?
검색만을 위해 만들어진 DB

mysql -u root -p

show databases;

use mysql;

show tables;

// cf.)
SELECT * FROM mysql.user\G;
// 한 행이 너무 길어질 때 자동 개해

SELECT user, host FROM mysql.user\G;

현재 MYSQL에 접속할 수 있는 user, host 정보

ltw(%)는 어떤 IP 주소를 가지던 상관없이 해당 DB 서버에 접속할 수 있다.

CREATE DATABASE smart;

use smart;

CREATE TABLE member (
	idx int auto_increment primary key,
    email VARCHAR(30),
    password VARCHAR(200),
    name VARCHAR(30)
);

CREATE USER 'ltw2'@'110.110.xxx.xxx' IDENTIFIED BY 'xxxx1234';

'ltw' 유저가 DATABASE smart의 member TABLE에 대해 SELECT 권한을 가졌으므로 조회 가능.

권한

• SELECT
• INSERT
• UPDATE
• DELETE
• CREATE
• ALTER
• DROP
• GRANT

• 권한 부여 설정
  • GRANT 권한 ON DB이름.테이블이름 TO 'ltw'@'%' 옵션 ;
    • 옵션 - WITH GRANT OPTION
• 권한 뺏는 설정
  • REVOKE 권한 ON DB이름.테이블이름 TO 'ltw'@'%' 옵션 ;
    • 옵션 - CASCADE,RESTRICT

-- 관리자가 a에게 권한을 부여할 수 있는 권한과 함께 SELECT 권한을 준다.
-- 사용자 a와 b를 어디에서나 접속할 수 있게 추가
-- a가 b에게 select 권한을 준다. 


mysql -u root -p

create user 'test01'@'%' identified by 'xxxx1234'; (a)
create user 'test02'@'%' identified by 'xxxx1234'; (b)

grant select on smart.member to 'test01'@'%' with grant option;

show grants for 'test01'@'%';

exit

mysql -u test01 -p

grant select on smart.member to 'test02'@'%';

// workbench에서 test02 유저로 접속 확인


select * from member; (ok)
drop * from member; (불가)


-- 권한 뺏기

revoke select on smart.member from 'test01'@'%';

-- workbench에서 test
select * from member;
delete * from member;

# 특정 사용자의 권한 확인
SHOW GRANTS FOR 'ltw'@'%';

# 권한 주는 설정
GRANT 권한 ON DB이름.테이블이름 TO 'ltw'@'%' 옵션;

# 권한 뺏는 설정
REVOKE 권한 ON DB이름.테이블이름 FROM 'ltw'@'%' 옵션; # CASCADE, RESTRICT

# 권한 SELECT, INSERT, UPDATE, DELETE, 
# CREATE, ALTER, DROP

# 옵션 WITH GRANT OPTION

# 사용자 A와 B를 어디에서나 접속할 수 있게 사용자 추가
	CREATE USER 'test01'@'%' IDENTIFIED BY 'xxxx1234';
	CREATE USER 'test02'@'%' IDENTIFIED BY 'xxxx1234';
    
# 관리자가 A에게 권한을 부여할 수 있는 권한과 함께 SELECT 권한을 준다. 
	GRANT SELECT ON smart.member TO 'test01'@'%' WITH GRANT OPTION;
    
# A가 B에게 SELECT 권한을 준다.
	exit;
	mysql -u A -p
    GRANT SELECT ON smart.member TO 'test02'@'%';

# 만약에 A의 권한을 뺏으면 B는 어떻게 될까?
	권한을 부여 받은 사람의 권한까지 뺏어가지는 않는다.

숙제

• 회원 테이블
  • idx, email, password, name
• 회원가입 SQL
  • INSERT INTO 회원 (email, password, name)
• 회원가입한 계정 로그인 SQL

  SELECT email, password, name
  FROM 회원
  WHERE email='test01@test.com' AND password='xxxxqwer1;

  SELECT name, password
  FROM 회원
  WHERE email='test01@test.com';

  -> 회원 정보를 가져오고 프로그램 코드로 비밀번호를 확인해야 한다.
• 회원가입한 계정 password 변경 SQL

  UPDATE 회원 
  SET password='1234xxxx' 
  WHERE email='test01@test.com';

• 회원가입한 계정 탈퇴 SQL

  DROP 
  FROM 회원
  WHERE email='test01@test.com' AND password='1234xxxx';