mysql table 권한 관리

1. user를 등록하는데 모든권한을 N 로 해서 추가합니다.

mysql> select * from user;
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host          | User     | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

      |          |            |             |              |             0 |           0 |               0 |
| localhost     | freekang | password| N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                |          |            |             |              |             0 |           0 |               0 |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

2. 해당 유저의 권한설정이 제대로 되어있는지 확인합니다.

[root@test229 root]# mysql -ufreekang -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.9-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 
mysql> 
mysql> show databases;
Empty set (0.00 sec)

mysql> quit
Bye

3. root로 로그인하여 해당 테이블에 대한 권한을 설정합니다.

[root@test229 root]# mysql -uroot -p    
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.1.9-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| MIDAS    |
| myphp    |
| mysql    |
+----------+
3 rows in set (0.00 sec)

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> insert into tables_priv values('%','mysql','freekang','user','','','Select','Select');

 

--> user table에 대해서 select권한만 주도록 설정했습니다.

Query OK, 1 row affected, 1 warning (0.01 sec)

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

mysql> quit
Bye

4. 해당 유저로 로긴하여 권한부여가 제대로 됐는지 확인합니다.

[root@test229 root]# mysql -ufreekang -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.1.9-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql      |
+----------+

 

-> 2번에서 아무것도 보이지 않던것에 비하여 현재는 mysql DB가 보입니다.

1 row in set (0.00 sec)

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> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| user                |
+-----------------+
1 row in set (0.00 sec)

 

-> mysql의 테이블중 user테이블만 보이고 있습니다.

 

mysql> select * from user;
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host          | User     | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

| localhost     | freekang | *B648CC8E9ED597C6394246A09FCD6C8DCF9A2E34 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                |          |            |             |              |             0 |           0 |               0 |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

-> select query에 대해서 정상적으로 실행되고 있습니다.

 

mysql> INSERT INTO `user` VALUES ('localhost','seokjoo','ajf;dljsdsfl;jowiurel;asjdlfjlas;djfl;adsjf;lasdjfasl;fj

34','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0);
ERROR 1142 (42000): INSERT command denied to user 'freekang'@'localhost' for table 'user'
mysql> delete from user where User="kkang";                                                 
ERROR 1142 (42000): DELETE command denied to user 'freekang'@'localhost' for table 'user'
mysql>

-> 권한을 주지않은 insert와 delete에 대해서는 command denied 라는 말이 나오는걸 보니 적용된거 같네요.^^