MYSQL 계정 생성, 권한부여, 권한 삭제

MYSQL  계정 생성, 권한부여, 권한 삭제 

 

1.사용자계정 생성 (mysql create user)

    mysql> create user 'userid'@'%'  IDENTIFIED BY 'userpasswd';

  

mysql> CREATE USER 'userid'@'192.168.%' IDENTIFIED BY 'user_password';
Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)


mysql> ALTER USER 'userid'@'localhost' IDENTIFIED BY 'user_password';

-- 변경된 패스워드를 시스템 환경에 적용 하려면 아래 명령이 필요합니다. 
mysql> flush privileges;

* 특정 사용자가 접속하는 IP를 '192.168.%' 으로 제한하는 경우 이며, Flush privileges 명령으로 변경된 패스워드를 시스템에 적용합니다. 

 

 

2.사용자에게 모든 것을 할 수 있는 권한 주기

    mysql> grant all privileges on *.* to 'userid'@'%';

mysql> CREATE USER 'userid'@'192.168.%' IDENTIFIED BY 'user_password';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'userid'@'192.168.%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.05 sec)

 

 

3.사용자에게 특정DB 접근 권한 부여

mysql> grant all privileges on dbname.* to 'userid'@'%';

mysql> grant all privileges on mydb.* to 'userid'@'192.168.%';
Query OK, 0 rows affected (0.03 sec)

-- [주의] grant를 할때 생성할때 사용했던 접속 권한을 사용해야함.
-- root 계정을 생성할때  'userid'@'%' 로 생성하지 않고 'userid'@'192.168.%' 올 생성하였으므로
-- 관련 권한이 없어서 안된다는 것이다. 
mysql> grant all privileges on mydb.* to 'userid'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

 

 

 

4.호스트명(localhost) client 접속 사용자 관리

mysql> create user 'userId'@'localhost' identified by 'userpassword';

mysql> grant all privileges on *.* to 'userid'@'localhost';
mysql> grant all privileges on *.* to 'userid'@'192.168.73.%';
mysql> grant all privileges on *.* to 'userid'@'192.168.73.11';

 

 

 

5. DB 접근 사용자 권한 부여 (Alter privileges)

    * 일반 사용자 권한 부여
       SELECT, INSERT, UPDATE, DELETE, INDEX,ALTER,CREATE, DROP, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, CREATE VIEW, SHOW VIEW 

 mysql> grant SELECT, INSERT, UPDATE, DELETE, INDEX,ALTER,CREATE, DROP, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, CREATE VIEW, SHOW VIEW  privileges on *.* to 'userid'@'192.168.%';

 

   * DBA 관리자 권한 

    DBA의 경우    PROCESS, RELOAD, SHOW DATABASES, SHUTDOWN, SUPER 등을 추가 하면 됩니다.

mysql> grant SELECT, INSERT, UPDATE, DELETE, INDEX,ALTER,CREATE, DROP, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, CREATE VIEW, SHOW VIEW, \
        PROCESS, RELOAD, SHOW DATABASES, SHUTDOWN, SUPER \
        privileges on *.* to 'userid'@'192.168.%';

   * Replication 권한 

     REPL_SLAVE, REPL_CLIENT, [RELOAD]

mysql> grant SELECT, INSERT, UPDATE, DELETE, INDEX,ALTER,CREATE, DROP, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, CREATE VIEW, SHOW VIEW, \
        REPL_SLAVE, REPL_CLIENT, RELOAD \
        privileges on *.* to 'userid'@'192.168.%';

 

 

6. DB 접근 권한 부여와 회수 방법 

 

  * DB를 설치하고 각 사용 계정에 적함한 계정을 적용하기 위해서 기존의 기능 권한을 제거 (REVOKE)후에 필요한 기능 권한을 부여 하는 것이 필요합니다. 아래와 같이 전체 기능 권한을 회수하거나 일부 권한을 추가 부여하고 수정하는 방법을 참고 바랍니다.

 

(1)  모든 권한 부여 와 모든 권한 삭제

 mysql> grant all on * to 'userid' with grant option;
 mysql> revoke  all privileges, grant from 'userid' ;

 -- revoker for replication user 
mysql> revoke  SUPER, SELECT, INSERT, UPDATE, DELETE, INDEX,ALTER,CREATE, DROP, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, CREATE VIEW, SHOW VIEW  on *.* from 'repuser'@'192.168.10.%' ; ;

mysql> grant   REPL_SLAVE, REPL_CLIENT, RELOAD  on *.* to 'repuser'@'192.168.10.%' ;

 

 (2) 일부 개별 권한 부여 와 삭제

# 권한 회수
mysql> grant SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, DROP  on  dbname.* to 'userid' with grant option; 

# 슈퍼 권한만 회수
mysql> revoke super on *.* from 'userid';

# 권한 변경 적용
mysql> FLUSH PRIVILEGES;   -- 메모리상에 commit 적용
* 권한 설정은 명령후 flush privileges 명령 없이는 적용이 되지 않는다.

 

 

6. 권한 설정 상태를 보관하는  참조 테이블

  - 각 DBMS는 다양한 시스템의 관리 정보를 VIEW, TABLE, Config File 등을 이용하여 관리하고 있습니다. Mysql은 이러한 정보를 "Information" 이라는 DB에 시스템에서 관리하는 정보를 보관 하고 있습니다.

> select * from mysql.user;       -- 계정별 접근관한 설정
> select * from mysql.db;         -- 개별 DB에 접근 권한 설정
> select * from mysql.host;       -- 개별 Host별 접근 권한 설정
> select * from mysql.table_priv;  -- 각 테이블별 접근 권한 설정
* 권한 설정은 위의 테이블 내용이 바뀐다고 적용 되지 않는다.
  반드시 "FLUSH PRIVILEGES" 명령을 수행해야 적용된다. (flush privileges)

  

7. 사용자의 권한 조회

   mysql> show grants for 'userid';

   mysql> show grants for 'userid'@'%';

 

 

8. mysql 계정 생성 examples

#user 생성 및 grant
mysql> CREATE USER 'friend'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'friend'@'localhost'  -> WITH GRANT OPTION; 
mysql> CREATE USER 'friend'@'%' IDENTIFIED BY 'some_pass'; 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'friend'@'%'  -> WITH GRANT OPTION; 
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost'; 
mysql> CREATE USER 'dummy'@'localhost';

#grant 권한 조회
mysql> SHOW GRANTS FOR 'admin'@'localhost'; 
+-----------------------------------------------------+ 
| Grants for admin@localhost | 
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' | 
+-----------------------------------------------------+
 
#사용자 계정별 grant 권한 부여로 접근 제어
mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'some_pass'; 
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP  -> ON db_name.*  -> TO 'custom'@'localhost'; 

mysql> CREATE USER 'custom'@'host1.example.com' IDENTIFIED BY 'some_pass';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP  -> ON expenses.*  -> TO 'custom'@'host4.example.com'; 

mysql> CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'some_pass'; 
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP  -> ON customer.*  -> TO 'custom'@'%.example.com';