mysql 설치 후 사용자 추가, 권한부여(스키마 추가에러-mysql error 1044)

mysql> show databases;

 

** user 확인

mysql> select user, host from mysql.user;

 

+------------------+-----------+

| user             | host      |

+------------------+-----------+

| root             | 127.0.0.1 |

| root             | ::1       |

| debian-sys-maint | localhost |

| root             | localhost |

| root             | ubuntu    |

+------------------+-----------+

5 rows in set (0.00 sec)

 

 

 

** 사용자 계정 추가 및 권한 부여

https://dev.mysql.com/doc/refman/5.5/en/adding-users.html

http://zetawiki.com/wiki/MySQL%EC%97%90_%EC%9B%90%EA%B2%A9_%EC%A0%91%EC%86%8D_%ED%97%88%EC%9A%A9

 

- localhost 에서만 접속가능한 사용자명 tester, 사용자암호 tester1234 만들기.

mysql> create user 'tester'@'localhost' identified by 'tester1234';

Query OK, 0 rows affected (0.00 sec)

 

$ mysql -utester -p

Enter password:

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

+--------------------+

1 row in set (0.00 sec)

 

mysql> create database testerdb;

ERROR 1044 (42000): Access denied for user 'tester'@'localhost' to database 'testerdb'

mysql>

 

 

** 부여된 권한보기

mysql> show grants for tester@localhost;

+----------------------------------------------------------------------------+

| Grants for tester@localhost                                                |

+----------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'tester'@'localhost' IDENTIFIED BY PASSWORD <secret> |

+----------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

 

- localhost 에서 접속하는 tester 사용자에게 모든 권한 부여

mysql> grant all privileges on *.* to 'tester'@'localhost' with grant option;

Query OK, 0 rows affected (0.00 sec)

 

$ mysql -utester -p

Enter password:

 

mysql> create database testerdb;

Query OK, 1 row affected (0.00 sec)

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| myschool           |

| mysql              |

| performance_schema |

| testerdb           |

+--------------------+

5 rows in set (0.00 sec)

 

mysql>

 

 

-- 모든 곳에서 접속가능한 사용자명 tester, 사용자암호 tester1234 만들기.

mysql> create user 'tester'@'%' identified by 'tester1234';

 

- 모든 곳에서 접속하는 tester 사용자에게 모든 권한 부여

mysql> grant all privileges on *.* to 'tester'@'%' with grant option;

 

-- 사용자 계정 삭제

https://dev.mysql.com/doc/refman/5.5/en/removing-users.html

 

mysql> DROP USER 'jeffrey'@'localhost';