#Step 22 — 사용자와 보안

CREATE USER 'app_ro'@'%'     IDENTIFIED WITH caching_sha2_password BY 'Ro#Pass123';
CREATE USER 's22_native'@'%' IDENTIFIED WITH mysql_native_password  BY 'Nat#Pass123';

SELECT user, host, plugin FROM mysql.user
WHERE user IN ('app_ro','app_rw','s22_native') ORDER BY user;

CREATE ROLE s22_reader, s22_writer, s22_analyst;

GRANT SELECT                         ON *.*            TO ...;  -- ① 글로벌 (모든 DB) — 거의 쓰지 마라
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.*         TO s22_writer;  -- ② DB 레벨
GRANT SELECT                         ON shop.orders    TO s22_analyst; -- ③ 테이블 레벨
GRANT SELECT (customer_id, grade, city, points)
                                     ON shop.customers TO s22_analyst; -- ④ 컬럼 레벨

GRANT s22_reader             TO 'app_ro'@'%';
GRANT s22_reader, s22_writer TO 'app_rw'@'%';

-- ★ 롤은 "부여"만으로 켜지지 않는다. 로그인 시 자동 활성화되도록 기본 롤 지정
SET DEFAULT ROLE ALL TO 'app_ro'@'%', 'app_rw'@'%';

SHOW GRANTS FOR 'app_ro'@'%';

SHOW GRANTS FOR 'app_rw'@'%' USING s22_writer;

CREATE VIEW shop.s22_customers_masked AS
SELECT
  customer_id, name,
  CONCAT(LEFT(email, 2), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email_masked,
  CONCAT(LEFT(phone, 3), '-****-', RIGHT(phone, 4))               AS phone_masked,
  grade, city
FROM shop.customers;

GRANT SELECT ON shop.s22_customers_masked TO s22_analyst;

CREATE USER 's22_lock'@'%' IDENTIFIED BY 'Lock#Pass123'
  FAILED_LOGIN_ATTEMPTS 3       -- 3회 연속 실패하면
  PASSWORD_LOCK_TIME 1;         -- 1일간 잠금

ALTER USER 's22_lock'@'%' ACCOUNT UNLOCK;

ALTER USER 's22_lock'@'%' PASSWORD EXPIRE;                         -- 다음 로그인 때 강제 변경
ALTER USER 's22_lock'@'%' PASSWORD HISTORY 3                       -- 최근 3개
                          PASSWORD REUSE INTERVAL 365 DAY;         -- 또는 1년 내 재사용 금지

ALTER USER 's22_lock'@'%' IDENTIFIED BY 'NewPass#111';   -- 첫 변경: OK
ALTER USER 's22_lock'@'%' IDENTIFIED BY 'NewPass#111';   -- 재사용 시도
-- ERROR 3638 (HY000): Cannot use these credentials for 's22_lock@%'
--                     because they contradict the password history policy

SELECT user, host,
       JSON_EXTRACT(User_attributes, '$.Password_locking') AS lock_policy,
       password_expired
FROM mysql.user WHERE user = 's22_lock';

REVOKE INSERT, UPDATE, DELETE ON shop.* FROM s22_writer;
SHOW GRANTS FOR s22_writer;

-- 접속 시도/쿼리를 남기는 방법 (개념)
--  * 엔터프라이즈: MySQL Enterprise Audit (audit_log 컴포넌트)
--  * 커뮤니티     : general_log(성능 영향 큼, 임시 진단용) 또는
--                   Percona/MariaDB audit plugin, 또는 프록시(ProxySQL) 레벨 로깅
SELECT user, host, plugin, password_last_changed, password_expired, account_locked
FROM mysql.user WHERE user NOT LIKE 'mysql.%';

-- =====================================================================
-- Step 22 — 사용자와 보안 : practice.sql
-- 실행: mysql -h127.0.0.1 -P3307 -uroot -proot1234 shop -t --force < practice.sql
--   ※ 반드시 root 로 실행합니다 (사용자/롤 생성은 관리 권한 필요).
--   ※ 이 스크립트는 서버에 계정을 "만들고, 검증하고, 마지막에 전부 지웁니다".
--     공용 스키마에는 s22_ 접두사 뷰 하나만 잠깐 만들었다가 지웁니다.
--   ※ 계정 검증(로그인 성공/거부)은 SQL 만으로는 보여줄 수 없어
--     README 에 셸 명령과 실제 출력을 함께 실었습니다.
-- =====================================================================

-- ---------------------------------------------------------------------
-- 0. 깨끗한 시작 (이전 실행 잔재 제거)
-- ---------------------------------------------------------------------
DROP VIEW IF EXISTS shop.s22_customers_masked;
DROP ROLE IF EXISTS s22_reader, s22_writer, s22_analyst;
DROP USER IF EXISTS 'app_ro'@'%', 'app_rw'@'%', 's22_alice'@'%',
                    's22_native'@'%', 's22_lock'@'%';

-- ---------------------------------------------------------------------
-- 22-1. 사용자 생성 + 인증 플러그인
--   caching_sha2_password : MySQL 8.0 기본. SHA-256 + 캐시. 안전.
--   mysql_native_password  : 예전 방식. 구형 드라이버 호환용. 8.0 에서 비권장.
--                           (8.4 에서 기본 비활성, 9.x 에서 제거 예정)
-- ---------------------------------------------------------------------
CREATE USER 'app_ro'@'%'     IDENTIFIED WITH caching_sha2_password BY 'Ro#Pass123';
CREATE USER 'app_rw'@'%'     IDENTIFIED WITH caching_sha2_password BY 'Rw#Pass123';
CREATE USER 's22_native'@'%' IDENTIFIED WITH mysql_native_password  BY 'Nat#Pass123';

SELECT user, host, plugin FROM mysql.user
WHERE user IN ('app_ro','app_rw','s22_native') ORDER BY user;

-- ---------------------------------------------------------------------
-- 22-2. 롤(ROLE) — MySQL 8.0 신기능
--   권한을 "역할"에 모아두고, 역할을 사람에게 부여한다.
--   담당자가 바뀌어도 롤만 갈아끼우면 된다. (권한을 사람마다 일일이 안 준다)
-- ---------------------------------------------------------------------
CREATE ROLE s22_reader, s22_writer, s22_analyst;

-- 22-3. 권한 레벨 : 글로벌 / DB / 테이블 / 컬럼
--   최소권한 원칙: 필요한 것만, 필요한 범위만.
GRANT SELECT                         ON shop.*          TO s22_reader;   -- DB 레벨
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.*          TO s22_writer;   -- DB 레벨
GRANT SELECT                         ON shop.orders     TO s22_analyst;  -- 테이블 레벨
GRANT SELECT (customer_id, grade, city, points)
                                     ON shop.customers  TO s22_analyst;  -- 컬럼 레벨!

-- 22-4. 롤을 유저에게 부여하고 기본 롤 활성화
GRANT s22_reader            TO 'app_ro'@'%';
GRANT s22_reader, s22_writer TO 'app_rw'@'%';
-- ★ 중요: 롤은 부여만 해서는 "활성화"되지 않는다. 로그인 시 켜지도록 기본 롤을 지정.
SET DEFAULT ROLE ALL TO 'app_ro'@'%', 'app_rw'@'%';

-- 22-5. 권한 확인
SHOW GRANTS FOR 'app_ro'@'%';
SHOW GRANTS FOR 'app_rw'@'%' USING s22_writer;   -- 특정 롤을 켠 상태의 유효 권한

-- ---------------------------------------------------------------------
-- 22-6. 뷰를 통한 컬럼 마스킹
--   민감정보(email/phone)를 원본 테이블 대신 "가려진 뷰"로만 노출한다.
--   analyst 에게는 customers 원본 테이블 권한을 주지 않고, 뷰만 준다.
-- ---------------------------------------------------------------------
CREATE VIEW shop.s22_customers_masked AS
SELECT
  customer_id,
  name,
  CONCAT(LEFT(email, 2), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email_masked,
  CONCAT(LEFT(phone, 3), '-****-', RIGHT(phone, 4))               AS phone_masked,
  grade, city
FROM shop.customers;

GRANT SELECT ON shop.s22_customers_masked TO s22_analyst;
SELECT * FROM shop.s22_customers_masked LIMIT 3;

-- ---------------------------------------------------------------------
-- 22-7. 비밀번호 정책 (계정 단위)
--   서버 전역 정책(validate_password 컴포넌트)은 공용 서버라 건드리지 않는다.
--   대신 "계정 단위" 정책은 자유롭게 걸 수 있다.
-- ---------------------------------------------------------------------

-- 계정 잠금 : 연속 실패 N회 시 잠금 (FAILED_LOGIN_ATTEMPTS, 8.0.19+)
CREATE USER 's22_lock'@'%' IDENTIFIED BY 'Lock#Pass123'
  FAILED_LOGIN_ATTEMPTS 3       -- 3회 연속 틀리면
  PASSWORD_LOCK_TIME 1;         -- 1일간 잠금 (N=UNBOUNDED 면 무기한)

-- 비밀번호 만료 : 다음 로그인 때 강제 변경
ALTER USER 's22_lock'@'%' PASSWORD EXPIRE;

-- 비밀번호 재사용 제한 : 최근 3개 / 365일 이내 재사용 금지
ALTER USER 's22_lock'@'%' PASSWORD HISTORY 3 PASSWORD REUSE INTERVAL 365 DAY;

-- 계정에 걸린 정책 확인 (mysql.user.User_attributes 는 JSON)
SELECT user, host,
       JSON_EXTRACT(User_attributes, '$.Password_locking') AS lock_policy,
       password_expired
FROM mysql.user WHERE user = 's22_lock';

-- 잠긴 계정 해제
ALTER USER 's22_lock'@'%' ACCOUNT UNLOCK;

-- ---------------------------------------------------------------------
-- 22-8. REVOKE — 권한 회수
-- ---------------------------------------------------------------------
REVOKE INSERT, UPDATE, DELETE ON shop.* FROM s22_writer;
SHOW GRANTS FOR s22_writer;    -- 이제 SELECT 만 남는다

-- ---------------------------------------------------------------------
-- 22-9. 뒷정리 — 만든 계정/롤/뷰를 전부 제거
--   (공용 서버이므로 실습 흔적을 반드시 지운다)
-- ---------------------------------------------------------------------
DROP VIEW IF EXISTS shop.s22_customers_masked;
DROP ROLE IF EXISTS s22_reader, s22_writer, s22_analyst;
DROP USER IF EXISTS 'app_ro'@'%', 'app_rw'@'%', 's22_alice'@'%',
                    's22_native'@'%', 's22_lock'@'%';

SELECT 'cleanup done' AS status,
       (SELECT COUNT(*) FROM mysql.user
        WHERE user LIKE 's22%' OR user IN ('app_ro','app_rw')) AS leftover_users;

-- =====================================================================
-- Step 22 — 사용자와 보안 : exercise.sql (연습문제)
-- 실행: mysql -h127.0.0.1 -P3307 -uroot -proot1234 shop -t --force < exercise.sql
--   ※ root 로 실행. 마지막 [문제 6] 에서 반드시 정리하세요.
-- =====================================================================

-- 깨끗한 시작
DROP ROLE IF EXISTS s22_batch_role, s22_prod_role;
DROP USER IF EXISTS 's22_batch'@'%', 's22_temp'@'%';

-- ---------------------------------------------------------------------
-- [문제 1] 배치 계정 s22_batch@'%' 를 만들고,
--          롤 s22_batch_role 을 통해 orders/order_items 에만
--          SELECT/INSERT 권한을 부여하라. 기본 롤도 활성화하라.
-- ---------------------------------------------------------------------
-- TODO
-- CREATE USER 's22_batch'@'%' IDENTIFIED WITH caching_sha2_password BY 'Batch#123';
-- CREATE ROLE s22_batch_role;
-- GRANT ... ;
-- GRANT s22_batch_role TO 's22_batch'@'%';
-- SET DEFAULT ROLE ALL TO 's22_batch'@'%';

-- 확인
-- SHOW GRANTS FOR 's22_batch'@'%' USING s22_batch_role;


-- ---------------------------------------------------------------------
-- [문제 2] s22_batch 가 customers 를 SELECT 하면 어떤 에러가 나는가?
--          (셸에서 s22_batch 로 로그인해 확인. 에러 번호를 주석에 적어라)
-- 힌트: mysql -h127.0.0.1 -P3307 -us22_batch -pBatch#123 shop -e "SELECT * FROM customers LIMIT 1;"
-- ---------------------------------------------------------------------
-- 답: ERROR ____ (____) : ____


-- ---------------------------------------------------------------------
-- [문제 3] 롤 s22_prod_role 을 만들고, products 에서 cost(원가)를 제외한
--          모든 컬럼만 읽는 컬럼 레벨 SELECT 권한을 부여하라.
-- 힌트: 컬럼: product_id, category_id, name, price, cost, stock, status, attrs, created_at
--       cost 만 빼고 GRANT SELECT (...) ON shop.products TO ...
-- ---------------------------------------------------------------------
-- TODO
-- CREATE ROLE s22_prod_role;
-- GRANT SELECT (...) ON shop.products TO s22_prod_role;
-- SHOW GRANTS FOR s22_prod_role;


-- ---------------------------------------------------------------------
-- [문제 4] s22_temp@'%' 를 만들되:
--          - 연속 실패 5회 시 2일 잠금
--          - 생성 즉시 비밀번호 만료(다음 로그인 때 변경 강제)
-- ---------------------------------------------------------------------
-- TODO
-- CREATE USER 's22_temp'@'%' IDENTIFIED BY 'Temp#123' ... ;
-- ALTER USER ... PASSWORD EXPIRE;
-- SELECT user, JSON_EXTRACT(User_attributes,'$.Password_locking') AS p, password_expired
--   FROM mysql.user WHERE user='s22_temp';


-- ---------------------------------------------------------------------
-- [문제 5] s22_batch_role 에서 INSERT 권한만 회수하고 남은 권한을 확인하라.
-- ---------------------------------------------------------------------
-- TODO
-- REVOKE INSERT ON ... FROM s22_batch_role;
-- SHOW GRANTS FOR s22_batch_role;


-- ---------------------------------------------------------------------
-- [문제 6] 실습에서 만든 계정/롤을 전부 삭제하라. (반드시 실행!)
-- ---------------------------------------------------------------------
-- TODO
-- DROP USER IF EXISTS ... ;
-- DROP ROLE IF EXISTS ... ;

-- =====================================================================
-- Step 22 — 사용자와 보안 : solution.sql (정답)
-- 실행: mysql -h127.0.0.1 -P3307 -uroot -proot1234 shop -t --force < solution.sql
-- =====================================================================

-- 깨끗한 시작
DROP ROLE IF EXISTS s22_batch_role, s22_prod_role;
DROP USER IF EXISTS 's22_batch'@'%', 's22_temp'@'%';

-- ---------------------------------------------------------------------
-- [정답 1] 배치 계정 + 롤
-- ---------------------------------------------------------------------
CREATE USER 's22_batch'@'%' IDENTIFIED WITH caching_sha2_password BY 'Batch#123';
CREATE ROLE s22_batch_role;
GRANT SELECT, INSERT ON shop.orders      TO s22_batch_role;
GRANT SELECT, INSERT ON shop.order_items TO s22_batch_role;
GRANT s22_batch_role TO 's22_batch'@'%';
SET DEFAULT ROLE ALL TO 's22_batch'@'%';

SHOW GRANTS FOR 's22_batch'@'%' USING s22_batch_role;
-- GRANT USAGE ON *.* ...
-- GRANT SELECT, INSERT ON `shop`.`orders` ...
-- GRANT SELECT, INSERT ON `shop`.`order_items` ...
-- GRANT `s22_batch_role`@`%` ...

-- ---------------------------------------------------------------------
-- [정답 2] s22_batch 가 customers 를 읽으면:
--   ERROR 1142 (42000): SELECT command denied to user 's22_batch'@'...'
--                       for table 'customers'
--   (customers 에는 아무 권한도 주지 않았으므로 테이블 레벨 거부)
-- ---------------------------------------------------------------------

-- ---------------------------------------------------------------------
-- [정답 3] cost 를 제외한 컬럼 레벨 SELECT
-- ---------------------------------------------------------------------
CREATE ROLE s22_prod_role;
GRANT SELECT (product_id, category_id, name, price, stock, status, attrs, created_at)
  ON shop.products TO s22_prod_role;   -- cost 만 빠졌다
SHOW GRANTS FOR s22_prod_role;

-- ---------------------------------------------------------------------
-- [정답 4] 실패 5회 → 2일 잠금 + 즉시 만료
-- ---------------------------------------------------------------------
CREATE USER 's22_temp'@'%' IDENTIFIED BY 'Temp#123'
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 2;
ALTER USER 's22_temp'@'%' PASSWORD EXPIRE;

SELECT user,
       JSON_EXTRACT(User_attributes, '$.Password_locking') AS lock_policy,
       password_expired
FROM mysql.user WHERE user = 's22_temp';
-- lock_policy: {"failed_login_attempts": 5, "password_lock_time_days": 2}
-- password_expired: Y

-- ---------------------------------------------------------------------
-- [정답 5] 롤에서 INSERT 만 회수
-- ---------------------------------------------------------------------
REVOKE INSERT ON shop.orders      FROM s22_batch_role;
REVOKE INSERT ON shop.order_items FROM s22_batch_role;
SHOW GRANTS FOR s22_batch_role;
-- 이제 orders/order_items 에 SELECT 만 남는다.
-- ★ 롤에서 회수하면 s22_batch 유저에게도 즉시 반영된다(사람마다 REVOKE 불필요).

-- ---------------------------------------------------------------------
-- [정답 6] 정리
-- ---------------------------------------------------------------------
DROP USER IF EXISTS 's22_batch'@'%', 's22_temp'@'%';
DROP ROLE IF EXISTS s22_batch_role, s22_prod_role;

SELECT 'cleanup done' AS status,
       (SELECT COUNT(*) FROM mysql.user WHERE user LIKE 's22%') AS leftover;