[MYSQL] 계정 및 권한 관리

계정 및 권한 관리

기본적으로 설치 되는 데이터 베이스인 mysql 데이터베이스에 유저 및 권한 정보가 저장되며, mysql 데이터베이스 user라는 테이블에 사용자 정보가 저장되며, db라는 테이블에 권한 정보가 저장된다. 

 

최초 설치 시에는 비밀번호가 설정되지 않는다. 관리자는 비밀번호를 재설정 해야한다.

설치 시, mysql_install_db 스크립트에는 권한 테이블을 설정 하는 것이다.

 

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host , user , plugin , password_last_changed , password_expired ,  account_locked from user ;
+-----------+------------------+-----------------------+-----------------------+------------------+----------------+
| host      | user             | plugin                | password_last_changed | password_expired | account_locked |
+-----------+------------------+-----------------------+-----------------------+------------------+----------------+
| localhost | mysql.infoschema | caching_sha2_password | 2022-09-26 17:22:30   | N                | Y              |
| localhost | mysql.session    | caching_sha2_password | 2022-09-26 17:22:30   | N                | Y              |
| localhost | mysql.sys        | caching_sha2_password | 2022-09-26 17:22:30   | N                | Y              |
| localhost | root             | caching_sha2_password | 2022-09-26 17:46:15   | N                | N              |
+-----------+------------------+-----------------------+-----------------------+------------------+----------------+
4 rows in set (0.00 sec)

1. Password 변경 방법

alter user 'root'@'localhost' identified by 'mysql';

mysql> flush privileges; -- 변경내용을 메모리상에 반영해 주기 위함

mysql> exit

-- 버전마다 다름
mysql.user 테이블에 password 칼럼이 있는 경우
   
  mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD ('root1122') ;
  mysql> flush privileges ;
  또는
  mysql> update mysql.user set password=password('root1122') where user = 'root' ;
  mysql> flush privileges ;
   
  mysql.user 테이블에 password 칼럼이 없는 경우
   
  mysql> update mysql.user set authentication_string=password('root1122') where user ='root' ;
  mysql> flush privileges ;

 

2. 유저 추가,삭제  (권한추가)

#생성
mysql > create user test_user1;
mysql > alter user 'test_user1'@'%' identified by 'mysql';
or
mysql > create user 'test_user1'@'%' identified by 'mysql';
-- '%' 의 의미는 외부에서의 접근을 허용
-- 특정 IP 대역에서만 접속하게 설정하려면 'IP.%'로 주면 된다.
-- ex) '172.1.%'

#삭제
mysql > drop user test_user1;

#권한 부여
grant all privileges on *.* to '아이디'@'localhost'; 
-- privileges on . 은 모든 DB에 권한을 주는 것

grant all privileges on DB명.* to '아이디'@'localhost';
-- 특정 DB권한 주기

grant select, insert, update on DB명.* to '아이디'@'%';
-- CRUD 권한 주기


#test유저로 접속하여 사용자에게 허가된 권한 확인
mysql> show grants
    -> ;
+-------------------------------------------------------+
| Grants for test_user1@%                               |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user1`@`%`                |
| GRANT ALL PRIVILEGES ON `test1`.* TO `test_user1`@`%` |
+-------------------------------------------------------+

-- root계정에서도 확인이 가능하다.
mysql> show grants for test_user1;
+-------------------------------------------------------+
| Grants for test_user1@%                               |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user1`@`%`                |
| GRANT ALL PRIVILEGES ON `test1`.* TO `test_user1`@`%` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

#권한 삭제
revoke all on DB명.테이블명 from 사용자ID;     // 모든 권한을 삭제

※ MySQL 5.7 까지는 GRANT 명령으로 권한의 부여와 동시에 계정 생성이 가능하였습니다.
하지만 MySQL 8.0 버전 부터는 유저 생성시에는 CREATE USER 명령으로,  권한 부여는 GRANT 명령으로 구분하여 실행 하도록 변경 되었습니다

 

 

2.1 외부에서 접속 테스트

CREATE USER 'root'@'%' identified by 'mysql';
GRANT ALL PRIVILEGES ON *.* to 'root'@'%';

mysql> select host , user , plugin , password_last_changed , password_expired ,  account_locked from user ;
+-----------+------------------+-----------------------+-----------------------+------------------+----------------+
| host      | user             | plugin                | password_last_changed | password_expired | account_locked |
+-----------+------------------+-----------------------+-----------------------+------------------+----------------+
| %         | test_user1       | caching_sha2_password | 2022-10-02 17:17:19   | N                | N              |
| localhost | mysql.infoschema | caching_sha2_password | 2022-09-26 17:22:30   | N                | Y              |
| localhost | mysql.session    | caching_sha2_password | 2022-09-26 17:22:30   | N                | Y              |
| localhost | mysql.sys        | caching_sha2_password | 2022-10-02 16:58:48   | N                | Y              |
| localhost | root             | caching_sha2_password | 2022-09-26 17:46:15   | N                | N              |
+-----------+------------------+-----------------------+-----------------------+------------------+----------------+
5 rows in set (0.00 sec)

mysql 8.0 부터는 아래와 같은 드라이버 설정이 필요하다.

외부 IP로 접속 가능 확인

3. 관리자, 객체 권한 목록

-. 관리자 권한

create user	create user, drop user, rename user, revoke all privileges 사용 가능
file	서버 호스트에 파일을 읽고, 쓰는 권한
grant option	다른 사용자에게 grant option권한을 포함해 자신이 가진 권한을 부여 할 수 있다.
process	show processlist, mysqladmin processlist 문을 사용할 수 있다
proxy	다른 사용자의 권한을 대행 할 수 있다. 다른 사용자의 대리인 역할을 수행하며, 해당 사용자가 수행 할 수 있는 모든 일을 할 수 있다.
reload	flush 와 reset 수행 가능 reload, refresh, flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads 등의 mysqladmin 명령어 수행
replication client	show master status, show slave status 수행 가능
replication slave	마스터 서버에 연결과, 서버의 업데이트 요청을 가능하게 한다. show slave hosts, show binlog events 수행. 마스터 서버에 자주 접근하는 슬레이브 서버 계정에는 반드시 부여 해야 한다.
show databases	show database 명령문 수행 시 전체 데이터 베이스를 보이게 할지 여부 결정 이 권한이 없는 상태에서 show databases명령을 수행하면 자기에게 부여된 데이터 베이스만 보임.
shutdown	mysqladmin shutdown 명령과 같은 서버 중단 권한
super	kill , mysqladmin kill 명령을 이용하여 서버 프로세스를 종료 시킬 수 있다. set, change master, purge binary logs, show master status, show slave status, start slave, stop slave 가능

 

-. 객체 권한

create	데이터 베이스와 테이블을 생성하는 권한
alter	테이블에 따라서 추가 권한이 필요할 수도 있지만, 기본적으로 alter table 사용권한
drop	데이터 베이스와 테이블을 삭제 할 수 있는 권한. 인덱스는 삭제 할 수 없다.
create routine	프로시저나 펑션을 생성할 수 있는 권한
alter routine	프로시저나 펑션을 변경하거나 제거 할 수 있는 권한
execute	프로시저나 펑션 수행 권한
create tablespace	테이블 스페이스를 생성, 변경, 삭제 할 수 있는 권한
create temporary tables	create temporary table 구문을 이용하여 임시 테이블을 생성 할 수 있는 권한
create view	뷰를 생성 할 수 있는 권한
show view	뷰 정의 내용을 볼 수 있는 show create view 명령 수행 가능
index	테이블의 인덱스를 생성, 삭제 및 key 캐시에 인덱스 할당 및 프리로드 권한
lock table	명시적으로 lock table 구문을 이용해서 테이블 락을 걸수 있는 권한. 기본으로 SELECT 권한이 있는 테이블에 대하여 lock table문을 사용할 수 있다.
trigger	트리거 추가, 제거 권한
event	이벤트 스케줄러의 이벤트 조정 권한
insert	테이블에 행 추가 권한
update	테이블의 행 변경 권한
delete	테이블의 행 삭제 권한
select	테이블의 데이터를 조회할 권한

 

-. 권한 레벨 식별자

ON *.*	모든 데이터 베이스와 데이터 베이스 안의 모든 객체
ON *	기본 데이터 베이스에 대한 데이터 베이스 레벨의 권한
ON dbname.*	dbname 데이터베이스 내의 모든 객체
ON dbname.tb_name	dbname 데이터베이스의 tb_name 테이블의 모든 칼럼
ON tb_name	기본 데이터 베이스내의 지정된 테이블의 모든 칼럼
ON dbname.routine_name	dbname 데이터베이스내의 지정된 루틴의 권한
ON account	프록시 권한: 계정 프록시 사용자

 

 

# 권한 부여 예)    GRANT   ALL    ON      *.*         TO    'user1'@'localhost' ;    'user1'@'localhost' 유저에게 모든 데이터베이스에 대한 권한을 부여 GRANT  reload  ON      *.*        TO     'flush'@'localhost' ;      'flush'@'localhost' 유저에게 flush 가능한 reload 권한을 부여 GRANT   ALL     ON  sampdb.*   TO    'bill'@'mamba.example.com' ;   'bill'@'mamba.example.com' 유저에게 sampledb의 모든 객체 권한 부여 GRANT  select   ON  sampdb.*   TO    'reader'@'%' ;           reader유저로 접속하는 세션에게 sampdb의 모든 객체에 대하여 SELECT 권한 부여 GRANT  select,insert,update,delete ON sampdb.*  TO 'jenie'@'%' ;  GRANT select, update(experation,street,city,state,zip)  ON sampdb.member  TO  'assis'@'%' ;           assis유저에게  sampdb 데이터베이스의 member 테이블에 대하여 select 및  (experation,street,city,state,zip) 칼럼에 대한 업데이트만 허용 REVOKE   ALL   ON  *.*  FROM  'user1'@'localhost' ; REVOKE  insert, delete  ON  sampdb.*  FROM  'jenie'@'%' ; REVOKE  grant option  ON  sampdb.*  FROM  'bill'@'localhost' ;