[MariaDB] 사용자관리, VIEW, 저장 프로시저, DB Dump

사용자관리

• 신규 사용자 생성

  CREATE USER 'testuser'@'localhost' IDENTIFIED BY ‘testpw’;

  • 어떤 곳에서도 원격접속을 가능하도록 하려면 localhost대신 % 기호 사용

• 사용자 목록 조회

  SELECT User, Host FROM mysql.user;

  • 특정DB의 특정테이블에 특정권한 부여

    • GRANT SELECT ON board.author TO ‘testuser'@'localhost';

  • 권한 변경사항 적용

    • FLUSH PRIVILEGES;

  • 특정 사용자 권한 조회

    • SHOW GRANTS FOR 'testuser'@'localhost';

---

VIEW

• 뷰(View)는 데이터베이스의 테이블과 유사한 구조를 가지지만, 가상의 테이블로서 실제 데이터를 저장하지 않는 데이터베이스. 실제 데이터베이스를 참조만
• 기본문법

  • 뷰 생성
    CREATE VIEW author_for_view AS
    SELECT 컬럼1, 컬럼2...
    FROM 테이블명;

  • 뷰를 통한 테이블 조회
    SELECT * FROM 뷰테이블명;

• 특징
  • 복잡한 쿼리 결과를 뷰로 생성해두면, 이후에는 뷰를 간단한 쿼리로 호출
  • 뷰를 사용하여 특정 사용자에게 테이블의 일부 데이터만을 보여주는 것이 가능
    • grant를 통해 뷰에 대한 권한만을 부여

      • GRANT SELECT ON [데이터베이스 이름].[뷰 이름] TO 'testuser'@'localhost';
      • FLUSH PRIVILEGES;

    • 뷰를 사용하면 필요한 컬럼만 선택하여 보여줄 수 있음

---

저장 프로시저(stored procedure)

• Stored Procedure는 데이터베이스에 저장되어 실행될 수 있는 하나 이상의 SQL 문의 집합

• 특성
  • SQL 문을 미리 컴파일하여 저장함으로써 데이터베이스 서버의 부하를 줄이고 성능을 향상
  • 한 번 작성된 저장 프로시저는 여러번 재사용 가능

• 복잡한 조건문 반복문 등을 프로시저에서는 프로시저 문법에 맞게 사용할 수 있음
  • sql은 본질적으로 선언문으로서 제어문에 대한 유연성 낮음(반복문불가)
  • 프로시저는 절차적 언어로서 제어문 사용 가능

• 데이터베이스 수준에서 직접 접근 권한을 제어할 수 있으므로 보안을 강화
• 특정사용자에게 프로시저 실행권한 부여

  GRANT EXECUTE ON PROCEDURE 데이터베이스명.프로시저명 TO 'testuser'@'localhost

• 기본 문법

  프로시저 생성
  DELIMITER //
  CREATE PROCEDURE procedure_name (parameters)
  BEGIN
  -- SQL 문법
  END //
  DELIMITER ;

  • 파라미터의 기본 형식은 (IN 변수명 변수타입)
  • 생성문의 파라미터는 생략가능하고, 함수와 같이 parameter를 전달하여 실행하는 것도 가능

• 프로시저 호출

  CALL 프로시저명();

• 생성한 프로시저 생성문 내용 조회

  SHOW CREATE PROCEDURE 프로시저명;

• 변수 선언

  DECLARE 변수명 변수타입 [DEFAULT default_value];

  • 반드시 프로시저나 함수의 본문 시작 부분, 즉 BEGIN 바로 밑에 위치
  • 조회 쿼리를 통한 "select 컬럼명 into 변수" 문과 함께 많이 사용

• 변수 수정

  SET 변수명 = 수정할값;

제어문

• if문

  IF 조건식 THEN
  -- 조건이 참일 때 실행할 명령
  ELSE
  -- 조건이 거짓일 때 실행할 명령
  END IF;

• while문

  WHILE 조건식 DO
  -- 조건이 참일 동안 반복 실행할 명령
  END WHILE;

---

DB Dump

• 데이터베이스의 구조와 데이터를 SQL 형식으로 추출하는 데 사용되는 방식을 Dump라 함. 주로 특정 DB에서 다른 DB로 데이터와 DB구조를 이전, 복사, 백업 할때 사용

• 데이터베이스 dump

  mysqldump -u [username] -p [password][database_name] > dumpfile.sql

  • mariaDB에서도 mysql명령어를 사용함에 유의

• dump파일 복원

  mysql -u [new_username] -p[new_password][new_database_name] < dumpfile.sql

• mariaDB가 mysql의 fork였던 관계로, mysql관련 명령어 사용됨에 유의