詳細(xì)說(shuō)明MySQL創(chuàng)建數(shù)據(jù)庫(kù)與創(chuàng)建用戶以及授權(quán)
發(fā)表時(shí)間：2023-09-02 來(lái)源：明輝站整理相關(guān)軟件相關(guān)文章人氣：
[摘要]1.通過(guò)mysql數(shù)據(jù)庫(kù)的user表查看用戶相關(guān)信息mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a...
1.通過(guò)mysql數(shù)據(jù)庫(kù)的user表查看用戶相關(guān)信息
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.創(chuàng)建數(shù)據(jù)庫(kù)
mysql> create database [databasename] default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
3.創(chuàng)建用戶
mysql> create user 'dba'@'%' identified by '*******';
Query OK, 0 rows affected (0.00 sec)
user表中host列的值的意義
% 匹配所有主機(jī)
localhost localhost不會(huì)被解析成IP地址，直接通過(guò)UNIXsocket連接
127.0.0.1 會(huì)通過(guò)TCP/IP協(xié)議連接，并且只能在本機(jī)訪問；
::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1
4.對(duì)dba用戶給予針對(duì)指定數(shù)據(jù)庫(kù)進(jìn)行增刪改查的權(quán)限
mysql> grant select,insert,update,delete,create on [databasename].* to dba;
Query OK, 0 rows affected (0.00 sec)
注意：修改完權(quán)限以后 一定要刷新服務(wù)，或者重啟服務(wù)，刷新服務(wù)用：FLUSH PRIVILEGES
5.可以通過(guò)show grants命令查看權(quán)限，若想要在原來(lái)的基礎(chǔ)上增加權(quán)限則繼續(xù)執(zhí)行g(shù)rant
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.通過(guò)revoke命令可以移除用戶的相關(guān)權(quán)限
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)
以上就是詳解MySQL創(chuàng)建數(shù)據(jù)庫(kù)與創(chuàng)建用戶以及授權(quán)的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！
學(xué)習(xí)教程快速掌握從入門到精通的SQL知識(shí)。
