1.通過mysql資料庫的user表查看用戶相關信息 2.創建資料庫 3.創建用戶 user表中host列的值的意義 % 匹配所有主機 localhost localhost不會被解析成IP地址，直接通過UNIXsocket連接 127.0.0.1 會通過TCP/IP協議連接，並且只能在本機訪問； ...
1.通過mysql資料庫的user表查看用戶相關信息
mysql> 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 mysql> select host,user,password from user ; +-----------+------+-------------------------------------------+ | host | user | password | +-----------+------+-------------------------------------------+ | localhost | root | *87F2746835A04895BB77E12AA5054A767******* | | qxyw | root | | | 127.0.0.1 | root | | | localhost | | | | qxyw | | | +-----------+------+-------------------------------------------+ 5 rows in set (0.00 sec)
2.創建資料庫
mysql> create database [databasename] default character set utf8 collate utf8_general_ci; Query OK, 1 row affected (0.00 sec)
3.創建用戶
mysql> create user 'dba'@'%' identified by '*******';
Query OK, 0 rows affected (0.00 sec)
user表中host列的值的意義 % 匹配所有主機 localhost localhost不會被解析成IP地址，直接通過UNIXsocket連接 127.0.0.1 會通過TCP/IP協議連接，並且只能在本機訪問； ::1 ::1就是相容支持ipv6的，表示同ipv4的127.0.0.1
4.對dba用戶給予針對指定資料庫進行增刪改查的許可權
mysql> grant select,insert,update,delete,create on [databasename].* to dba; Query OK, 0 rows affected (0.00 sec)
註意：修改完許可權以後 一定要刷新服務，或者重啟服務，刷新服務用：FLUSH PRIVILEGES
5.可以通過show grants命令查看許可權，若想要在原來的基礎上增加許可權則繼續執行grant
mysql> grant drop on [databasename].* to dba; Query OK, 0 rows affected (0.00 sec) mysql> show grants for dba; +----------------------------------------------------------------------------------------------------+ | Grants for dba@% | +----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*****************************************' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `[databasename]`.* TO 'dba'@'%' | +----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
6.通過revoke命令可以移除用戶的相關許可權
mysql> revoke drop on [databasename].* from dba; Query OK, 0 rows affected (0.00 sec) mysql> show grants for dba; +----------------------------------------------------------------------------------------------------+ | Grants for dba@% | +----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dba'@'%' IDENTIFIED BY PASSWORD '*****************************************' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `[databasename]`.* TO 'dba'@'%' | +----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
