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 라는 말이 나오는걸 보니 적용된거 같네요.^^