MySQL - 사용자, 그룹, 데이터베이스, 테이블 관리

출처 : http://www.gpgstudy.com/gpgiki/MySQL%EC%97%90%EC%84%9C%20%EC%82%AC%EC%9A%A9%EC%9E%90%EC%99%80%20%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4%20%EB%A7%8C%EB%93%A4%EA%B8%B0

0. 커맨드 모드 접근

C:\mysql-5.1.37-win32\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.37-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

1. 데이터베이스 관리

1-1. 데이터베이스 생성

mysql> create database db_study;
Query OK, 1 row affected (0.00 sec)

1-2. 데이터베이스 목록 조회

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_study           |
| mysql              |
| test               |
+--------------------+

1-3. 데이터베이스 삭제

mysql> drop database db_study;
Query OK, 0 rows affected (0.09 sec)

2. 사용자 관리

2-1. 사용자 추가

mysql> INSERT INTO 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,Create_view_priv
    -> ,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv,
    -> Event_priv,Trigger_priv)
    -> values ('localhost', 'scott', PASSWORD('tiger'),
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

mysql> INSERT INTO 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,Create_view_priv
    -> ,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv,
    -> Event_priv,Trigger_priv)
    -> values ('%', 'scott', PASSWORD('tiger'),
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
Query OK, 1 row affected, 3 warnings (0.00 sec)

2-2. 사용자 조회

mysql> select host, user, password from user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  |                                           |
| 127.0.0.1 | root  |                                           |
| localhost |       |                                           |
| %         | scott | *F2F68D0BB27A773C1D944270E5FAFED515A3FA40 |
| localhost | scott | *F2F68D0BB27A773C1D944270E5FAFED515A3FA40 |
+-----------+-------+-------------------------------------------+
4 rows in set (0.00 sec)

3. 권한부여

3-1. 해당 DB의 사용자에 대한 권한 부여

mysql> INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
    -> VALUES('%', 'db_study', 'scott', 'Y', 'Y', 'Y', 'Y','Y', 'Y');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
    -> VALUES('localhost', 'db_study', 'scott', 'Y', 'Y', 'Y', 'Y','Y', 'Y');
Query OK, 1 row affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

3-2. 조회

mysql> select host, db, user from db;
+-----------+----------+-------+
| host      | db       | user  |
+-----------+----------+-------+
| %         | db_study | scott |
| %         | test     |       |
| %         | test\_%  |       |
| localhost | db_study | scott |
+-----------+----------+-------+
3 rows in set (0.00 sec)

4. 테이블 관리

4-1. 테이블 생성



-end