-구성환경-
Centos7
mariadb
1.db에 계정 추가하기
- db에 접속하여 현재 계정 정보를 확인
- Host,User 컬럼확인시 root 계정이 localhost에서만 허용된것을 확인가능
mysql -uroot -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.4.12-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [mysql]> select * from 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 | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | is_role | default_role | max_statement_time |
+-----------------------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| localhost | root | *0B0A8BA0BB47561FA5C1FE726FFDFFC56F3FF585 | 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 | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *0B0A8BA0BB47561FA5C1FE726FFDFFC56F3FF585 | N | N | | 0.000000 |
| localhost | mysql | invalid | 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 | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | invalid | N | N | | 0.000000 |
| localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | | N | N | | 0.000000 |
| localhost.localdomain | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | | N | N | | 0.000000 |
+-----------------------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
4 rows in set (0.001 sec)
- 'create user 'root'@'%' identified by 'qwe123!@#';' 명령어를 통해 root계정으로 외부에서도 접근 가능하도록 추가
MariaDB [mysql]> create user 'root'@'%' identified by 'qwe123!@#';
Query OK, 0 rows affected (0.001 sec)
- 'flush privileges' 명령어를 통해 추가한값을 적용
#mariadb,mysql 의 경우 계정을 추가하거나 수정 삭제후 적용시키기 위해서는 적용명령어를 입력해주어야함
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.000 sec)-추가된 정보 확인
MariaDB [mysql]> select * from 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 | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | is_role | default_role | max_statement_time |
+-----------------------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| localhost | root | *0B0A8BA0BB47561FA5C1FE726FFDFFC56F3FF585 | 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 | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *0B0A8BA0BB47561FA5C1FE726FFDFFC56F3FF585 | N | N | | 0.000000 |
| localhost | mysql | invalid | 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 | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | invalid | N | N | | 0.000000 |
| localhost | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | | N | N | | 0.000000 |
| localhost.localdomain | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | | N | N | | 0.000000 |
| % | root | *2C8DB48367F230AE366C7117C12F354077916B4A | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *2C8DB48367F230AE366C7117C12F354077916B4A | N | N | | 0.000000 |
+-----------------------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
5 rows in set (0.001 sec)
2. 계정에 권한 설정하기
- 권한 설정하기에 앞서 테스트 계정 생성
MariaDB [mysql]> create user 'test'@'localhost' identified by 'qwe123!@#';
Query OK, 0 rows affected (0.002 sec)- [show grants for test@'localhost'] 명령어를 통해 권한 확인
- 'USAGE' 권한으로 설정되어 있으며 접속만 가능하다는 의미
MariaDB [mysql]> show grants for test@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*2C8DB48367F230AE366C7117C12F354077916B4A' |
+-------------------------------------------------------------------------------------------------------------+-[ grant all privileges on *.* to test@'localhost' with grant option; ] 명령어를 통해 전체 DB에 대하여 모든 권한 허용
MariaDB [mysql]> grant all privileges on *.* to test@'localhost' with grant option;
Query OK, 0 rows affected (0.002 sec)- 'flush privileges' 명령어를 통해 추가한값을 적용
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.000 sec)- 추가된 권한 확인
- 아까와는 다르게 USAGE가 아닌 ALL로 바뀌어 모든 권한이 허용되있는것을 확인가능
MariaDB [mysql]> show grants for test@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*2C8DB48367F230AE366C7117C12F354077916B4A' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
'DB' 카테고리의 다른 글
| [centos/mariadb] scv파일을 이용하여 테이블에 insert하기 (0) | 2020.05.31 |
|---|---|
| [mysql/mariadb] mysql,mariadb 테이블 생성하기 (0) | 2020.05.10 |
| [mariadb] centos에 mariadb 설치하기 (0) | 2020.05.03 |