MariaDB 강좌 / 사용자 계정 만들고 필요한 권한만 부여하기

MariaDB 계정은 사용자 이름과 접속 호스트의 조합으로 식별하며, GRANT로 업무에 필요한 범위의 권한을 부여합니다. 관리자 연결과 애플리케이션 연결을 나누고 실제 계정으로 허용 작업·거절 작업을 각각 확인해야 권한 설정을 검증할 수 있습니다.

사용자 이름·접속 출발지·권한 범위 나누기

같은 이름이어도 user@localhost와 user@127.0.0.1은 다른 계정 정의일 수 있습니다. 접속 방식과 이름 해석에 따라 실제 매칭이 달라지므로 CURRENT_USER()로 인증에 사용된 계정을 확인합니다. root를 프로그램의 일반 조회 계정으로 사용하는 구성은 필요한 권한을 제한하기 어렵습니다. 접속한 계정이 의도한 권한 계정인지 확인하는 과정은 로컬·원격 접속과 연결 오류 해결하기, 제한 계정을 사용하는 전체 프로그램 예는 Python에서 MariaDB 연결하고 매개변수 쿼리 사용하기에서 이어집니다.

암호는 예제 문자열을 그대로 사용하지 말고 계정별로 새로 정합니다. 명령줄 옵션에 실제 암호를 직접 붙이면 프로세스 정보·기록에 남을 수 있으므로 --password 프롬프트나 접근 권한을 제한한 설정 파일을 사용합니다.

연습용 상품 테이블과 관리자 연결 준비하기

학습용 MariaDB 서버의 shopcourse에 course_user로 접속합니다. 명령줄 클라이언트와 UTF-8 SQL 파일 실행 방법대로 코드를 UTF-8 파일에 저장하고 SOURCE C:/mariadb-lab/lesson.sql;처럼 실제 파일 경로로 실행할 수 있습니다. SQL 문은 운영체제 터미널이 아닌 MariaDB 연결 안에서 실행합니다.

먼저 SELECT VERSION(), DATABASE();와 SHOW TABLES;로 대상 서버·DB·이름 중복을 확인합니다. 아래 c43_customers, c43_products, c43_orders, c43_items는 이 글의 독립된 연습용 이름입니다. 이미 존재하면 정의와 행을 확인하고, 보관할 자료가 없는 이 글의 실습 테이블임이 확실할 때만 마지막 절의 정리 순서로 지운 뒤 다시 만듭니다. 기존 강좌의 동명 없는 테이블이나 운영 데이터는 삭제하지 않습니다.

USE shopcourse;
CREATE TABLE c43_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c43_order_customer FOREIGN KEY (customer_id)
  REFERENCES c43_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c43_item_order FOREIGN KEY (order_id)
  REFERENCES c43_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c43_item_product FOREIGN KEY (product_id)
  REFERENCES c43_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c43_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c43_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c43_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c43_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

테이블 준비는 shopcourse에 필요한 권한이 있는 학습용 계정으로 할 수 있지만, CREATE USER·GRANT는 해당 권한을 가진 별도 관리자 연결에서 수행합니다. shop_reader43이라는 기존 계정이 있으면 덮어쓰거나 삭제하지 말고 다른 실습용 이름을 정하세요. 이 글은 새 계정을 만든다는 전제입니다.

상품 한 테이블의 SELECT만 부여하기

다음 코드는 관리자 연결에서 실행합니다. IDENTIFIED BY의 예제 암호를 실제로 사용할 새 암호로 바꾼 뒤 실행하고, 출력한 계정 정보에 비밀값이 포함되어 있는지 확인해 공개하지 않습니다.

CREATE USER 'shop_reader43'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON shopcourse.c43_products TO 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';

이 계정은 c43_products 조회만 허용합니다. 고객·주문 테이블 접근과 INSERT·UPDATE·DDL은 부여하지 않았습니다. CREATE USER와 GRANT를 사용한 변경에 관례적으로 FLUSH PRIVILEGES를 붙일 필요는 없습니다. 권한 테이블을 직접 고치는 방식 대신 계정 관리 문법을 사용합니다. CREATE USER와 GRANT 권한 범위를 확인하세요.

새 계정으로 허용·거절 동작을 따로 시험하기

운영체제 터미널에서 아래 명령으로 새 연결을 열고 새 계정의 암호를 프롬프트에 입력합니다. course_user 연결을 재사용한 채 결과를 판단하지 않습니다.

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=shop_reader43 --password --default-character-set=utf8mb4 shopcourse

SELECT USER(),CURRENT_USER();
SELECT product_id,product_name FROM c43_products ORDER BY product_id;

상품 네 개는 조회되어야 합니다. 다음 문장은 새 계정에서 하나씩 실행하면 권한 오류가 예상됩니다. 학습용 객체만 대상으로 하며 기존 운영 테이블에는 시험하지 않습니다.

SELECT * FROM c43_customers;
UPDATE c43_products SET stock=stock WHERE product_id=1;

실패했다고 즉시 ALL PRIVILEGES를 부여하지 말고 실제 요구가 조회인지 수정인지부터 확인합니다. 역할을 사용한 환경에서는 직접 권한뿐 아니라 활성 역할의 영향도 함께 확인해야 합니다.

권한 회수와 계정 수명 관리하기

관리자 연결에서 아래처럼 SELECT를 회수하고 새 계정의 새 연결로 다시 조회해 거절되는지 확인할 수 있습니다. 이미 열린 세션의 권한 반영 시점은 범위에 따라 다를 수 있으므로 회수 검증을 기존 세션 한 번으로 끝내지 않습니다.

REVOKE SELECT ON shopcourse.c43_products FROM 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';

USAGE가 보이는 것은 계정이 존재하지만 그 범위의 실제 작업 권한을 부여하지 않았다는 뜻일 수 있습니다. DROP USER는 계정 삭제이며 해당 계정이 만든 데이터나 모든 정의자 의존 객체를 자동으로 안전하게 정리하는 절차는 아닙니다. 프로그램 연결·뷰·루틴의 정의자와 사용 여부를 확인해야 합니다.

권한을 회수한 뒤에는 새 연결에서 shopcourse를 기본 DB로 선택하는 단계 자체가 거절될 수 있습니다. 이 경우 DB 이름 없이 서버에 접속한 뒤 SELECT * FROM shopcourse.c43_products;를 실행해 테이블 권한 거절을 따로 확인합니다.

이 실습에서 만든 계정과 테이블만 정리하기

shop_reader43이 이 글에서 새로 만든 학습용 계정인 것을 확인한 뒤 관리자 연결에서 삭제합니다. 실제 사용하는 다른 계정은 정리 대상이 아닙니다. 열린 새 계정 연결도 종료하고 테이블은 자식부터 정리합니다.

DROP USER 'shop_reader43'@'127.0.0.1';

반복하기 전 현재 DB와 실제 정의·행을 확인하고 필요한 자료를 백업합니다. 보관할 데이터가 없는 이 글의 전용 실습 객체만 아래 순서로 정리합니다. 외래키가 있으면 자식부터 지웁니다. DROP TABLE은 구조와 데이터를 모두 없애며 일반적인 ROLLBACK으로 복원되지 않으므로 취소 가능한 DML 실습과 분리합니다.

DROP TABLE shopcourse.c43_items;
DROP TABLE shopcourse.c43_orders;
DROP TABLE shopcourse.c43_products;
DROP TABLE shopcourse.c43_customers;