MySQL, MariaDB 계정 생성 및 권한 추가

접속 및 db 선택

ubuntu@first-instance:~$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.

MariaDB [(none)]> 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

사용자 조회

MariaDB [mysql]> select host, user, password from user;
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *4444375CAAA28B177047A45FDEBFB88000306236 |
| 127.0.0.1 | root             | *4444375CAAA28B177047A45FDEBFB88000306236 |
| ::1       | root             | *4444375CAAA28B177047A45FDEBFB88000306236 |
| localhost | debian-sys-maint | *F35A5DFB4B204DE265E97C41073062CF69939F52 |
| %         | MYSQL_ID         | *44445FEB578128C3C063D0CAA478D827ADB6B3B3 |
| %         | root             | *5162375CAAA28B177047A45FDEBFB88000306236 |
+-----------+------------------+-------------------------------------------+
7 rows in set (0.002 sec)

 

사용자 생성 (host가 %은 전체 허용)

MariaDB [mysql]> create user tichi@localhost identified by 'test';
Query OK, 0 rows affected (0.007 sec)

MariaDB [mysql]> select host, user, password from user;
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *4444375CAAA28B177047A45FDEBFB88000306236 |
| 127.0.0.1 | root             | *4444375CAAA28B177047A45FDEBFB88000306236 |
| ::1       | root             | *4444375CAAA28B177047A45FDEBFB88000306236 |
| localhost | debian-sys-maint | *F35A5DFB4B204DE265E97C41073062CF69939F52 |
| %         | MYSQL_ID         | *00F45FEB578128C3C063D0CAA478D827ADB6B3B3 |
| %         | root             | *4444375CAAA28B177047A45FDEBFB88000306236 |
| localhost | test             | *8DE1BCDB7E9D8CBAED1FB1530078FD5E4D2BDCC9 |
+-----------+------------------+-------------------------------------------+
8 rows in set (0.000 sec)

권한 부여 및 저장

MariaDB [mysql]> grant all privileges on [db명].* to [계정]i@'%' identified by '[비밀번호]';
Query OK, 0 rows affected (0.003 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.003 sec)