[MySQL] User Account CRUD

MySql을 공부하기 마음을 먹고 맥북에 설치를 했으나

버전도 모르고 계정 정보도 생각나지 않아 현재 정보를 조회해보기로 했다.

$ brew services list
$ brew search mysql
$ brew services start mysql
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.31 Homebrew

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> status
--------------
mysql  Ver 8.0.31 for macos12.6 on x86_64 (Homebrew)

--------(이하 생략)----------

어렵게 root 계정의 패스워드를 찾아내 로그인에 성공하니 서비스 계정을 확인해 보고 싶었다.

Local mysql에 설정된 계정 현황

그럼 AllyMarket Service 개발을 위해 신규로 계정을 만들어 보자. 

mysql> CREATE USER allyuser@localhost IDENTIFIED BY '*************';
Query OK, 0 rows affected (0.02 sec)
mysql> FLUSH PRIVILEGES;
mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| smileuser        | %         |
| allyuser         | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| smileuser        | localhost |
+------------------+-----------+
7 rows in set (0.00 sec)

mysql> CREATE USER 'allyuser'@'%' IDENTIFIED BY '***************';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'allyuser'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

 

기존 계정 날리기 
> DROP USER 'account'@'localhost';

호스트가 localhost로 되어있는 계정은 외부 접속이 안되니 새롭게 계정을 생성한다.
> CREATE USER 'allyuser'@'%' IDENTIFIED BY '***************';

'allyuser'@'%' 계정에게 편의상 모든 데이터베이스, 모든 테이블에 권한을 부여해 보자.
> GRANT ALL PRIVILEGES ON *.* TO 'allyuser'@'%' WITH GRANT OPTION;

특정 Database에만 권한 부여

> GRANT ALL PRIVILEGES ON DATABASE_NAME.* TO USER_ID@'localhost' WITH GRANT OPTION;

변경된 내용을 메모리에 반영
> FLUSH PRIVILEGES;

workbench 설치

$ brew install mysqlworkbench --cask
--------(생략)---------
==> Downloading https://cdn.mysql.com/Downloads/MySQLGUITools/mysql-workbench-community-8.0.31-maco
######################################################################## 100.0%
==> Installing Cask mysqlworkbench
==> Moving App 'MySQLWorkbench.app' to '/Applications/MySQLWorkbench.app'
🍺  mysqlworkbench was successfully installed!