[MYSQL] 계정 및 역할 쿼리 정리

 

 

## 만약 변수 확인 및 변경시

-- 글로별 변수 확인시
SHOW GLOBAL VARIABLES LIKE 'max_connections%';

-- 세션 변수 확인시
SHOW VARIABLES LIKE 'max_connections%';

-- 변경시
SET GLOBAL max_connections=500;


-- 만약 현재 이용중인 서버의 시스템 변수를 변경하고 my.cnf파일에도 기록하고 싶다면(8.0v부터)
SET PERSISTmax_connections=500;

-- my.cnf파일에만 기록하고 싶다면
SET PERSIST_ONLY max_connections=500;

※ mysqld-auto.cnf파일에는 누가 언제 어떤 시스템 변수를 변경했는지 정보가 기록되어 있음

 

 

 

## 계정생성시

CREATE USER 'user'@'%'
 IDENTIFIED WITH 'mysql_native_password' BY 'PASSWORD'
 REQUIRE NONE
 PASSWORD EXPIRE INTERVAL 30 DAY
 ACCOUNT UNLOCK
 PASSWORD HISTORY DEFAULT
 PASSWORD REUSE INTERVAL DEFAULT
 PASSWORD REQUIRE CURRENT DEFAULT;


--EX)
CREATE USER
  'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password
                                   BY 'new_password1',
  'jeanne'@'localhost' IDENTIFIED WITH caching_sha2_password
                                  BY 'new_password2'
  REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
  PASSWORD HISTORY 5
  ACCOUNT LOCK;
  
  CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

 

 

### PASSWORD EXPIRE

• 계정만료 날짜
• PASSWORD EXPIRE : 계정 생성과 동시에 비밀번호 만료
• PASSWORD EXPIRE NEVER : 비밀번호 만료기간 없음
• PASSWORD EXPIRE DEFAULT : default_password_lifetime 시스템 변수에 지정된 비밀번호 유효기간으로 설정
• PASSWORD EXPIRE INTERVAL n DAY : 유효기간을 오늘부터 N일로 설정

 

 

 

### PASSWORD HISTORY

• 사용했던 비밀번호 이력
• PASSWORD HISTORY EFAULT : password_history 시스템 변수에 지정된 수만큼 비밀번호 이력 저장, 저장된 이력의 비밀번호는 재사용불가
• PASSWORD HISTORY N : 비밀번호 이력을 최근 N개까지 저장

 

 

### PASSWORD REUSE INTERVAL

• 사용했던 비밀번호의 재사용 금지 기간
• PASSWORD REUSE INTERVAL DEFAULT : PASSWORD_REUSE_INTERVAL 시스템 변수에 저장된 기간
• PASSWORD REUSE INTERVAL N DAY : N일자 이후 비밀번호 재사용 가능

 

 

 

### PASSWORD REQUIRE

• 비밀번호를 변경할때 현재 비밀번호를 필요로 할지 말지
• PASSWORD REQUIRE CURRENT : 비밀번호를 변경할때 현재 비밀번호를 넣어줌
• PASSWORD REQUIRE OPTIONAL : 안넣어줘도됌
• PASSWORD REQUIRE DEFAULT : PASSWORD_REQUIRE_CURRENT 시스템 변수의 값

 

 

### ACCOUNT LOCK / UNLOCK

 

 

---

 

## 권한부여

-- 테이블 단위로 권한 부여시
GRANT SELECT, INSERT, UPDATE, DELETE ON DB이름.테이블이름 TO '유저명'@'IP';

--EX)
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'DAMIN'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON employee.professor TO 'DAMIN'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON employee.* TO 'DAMIN'@'%';

 

 

 

## 부여 후 테이블에서 확인

-- 테이블 권한 확인시
SELECT * FROM MYSQL.TABLES_PRIV
WHERE USER=''
AND TABLE_NAME='';


-- DB단위로 부여된 권한 확인시
SELECT * FROM MYSQL.DB;


-- 컬럼단위
SELECT * FROM MYSQL.COLUMNS_PRIV;

-- 프로시저 단위
SELECT * FROM MYSQL.PROCS_PRIV;

-- 계정정보를 알려주고, 계정이나 역할에 부여된 글로벌 권한 확인 가능
SELECT * FROM MYSQL.USER;

 

 

## 8.0부터 ROLE생성 가능

-- ROLE 생성
CREATE ROLE ROLE_EMP_READ;

-- role을 생성하면 MYSQL.USER 테이블에 IS_ROLE 컬럼이 'Y'로 생
SELECT * FROM mysql.user;

-- ROLE에 역할 부여
GRANT SELECT ON EMPLOYEES.* TO ROLE_EMP_READ;

-- user 생성
CREATE USER EMP_READER@'%' IDENTIFIED BY 'TEST123'

-- role 부여
GRANT ROLE_EMP_READ TO EMP_READER@'%'

-- 계정 접속 후 ROLE 활성화
SET ROLE 'ROLE_EMP_READ'

-- 현재 사용중인 ROLE 확인
SELECT CURRENT_ROLE()

※ 만약 role을 자동으로 활성화 하고 싶다면   SET GLOBAL activate_all_roles_on_login=ON;

 

 

 

CREATE ROLE문에서는 따로 호스트를 명시하지 않아도 %로 들어간다.
따라서 아래 두개는 같은 쿼리


mysql> CREATE ROLE ROLE_EMP_READ;

mysql> CREATE ROLE ROLE_EMP_READ@'%';