[MYSQL] 스키마구조

MYSQL 에서 스키마와 데이터베이스

MYSQL의 경우 사람에따라 스키마를 데이터베이스라고 부르는 지만 같은 의미다.

CREATE SCHEMA test DEFAULT CHARACTER SET utf8;
CREATE DATABASE test DEFAULT CHARACTER SET utf8;

두 명령어가 동일한 결과를 냅니다.

EX> mysql schema(database) , user 관계

1. root와 test_user1계정이 존재

mysql> select host , user , plugin , password_last_changed , password_expired ,  account_locked from user ;
+-----------+------------------+-----------------------+-----------------------+------------------+----------------+
| host      | user             | plugin                | password_last_changed | password_expired | account_locked |
+-----------+------------------+-----------------------+-----------------------+------------------+----------------+
| %         | root             | caching_sha2_password | 2022-10-07 13:54:28   | N                | N              |
| %         | test_user1       | caching_sha2_password | 2022-10-02 17:17:19   | N                | N              |
| localhost | mysql.infoschema | caching_sha2_password | 2022-09-26 17:22:30   | N                | Y              |
| localhost | mysql.session    | caching_sha2_password | 2022-09-26 17:22:30   | N                | Y              |
| localhost | mysql.sys        | caching_sha2_password | 2022-10-02 16:58:48   | N                | Y              |
| localhost | root             | caching_sha2_password | 2022-09-26 17:46:15   | N                | N              |
+-----------+------------------+-----------------------+-----------------------+------------------+----------------+

2. Database=Schema 목록
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

3. test_user1으로 접속
> mysql -u test_user1 -p
 부여된 권한 확인
 mysql> show grants;
+-------------------------------------------------------+
| Grants for test_user1@%                               |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user1`@`%`                |
| GRANT ALL PRIVILEGES ON `test1`.* TO `test_user1`@`%` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

4. test1 database로 접근 확인
Database changed
mysql> use mysql; -- mysql 스키마로는 권한이 없어서 접근이 안된다.
ERROR 1044 (42000): Access denied for user 'test_user1'@'%' to database 'mysql'

mysql> use test1;
mysql> select * from test_tab1;
+------+-------+
| c1   | c2    |
+------+-------+
|    1 | test1 |
+------+-------+