一. MySQL用戶(hù)管理

【例1.1】使用root用戶(hù)登錄到本地mysql服務(wù)器的test庫(kù)中

mysql -uroot -p -hlocalhost test

【例1.2】使用root用戶(hù)登錄到本地mysql服務(wù)器的test庫(kù)中，執(zhí)行一條查詢(xún)語(yǔ)句

mysql -uroot -p -hlocalhost test -e "DESC person;"

【例1.3】使用CREATE USER創(chuàng)建一個(gè)用戶(hù)，用戶(hù)名是jeffrey，密碼是mypass，主機(jī)名是localhost

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

【例1.4】使用GRANT語(yǔ)句創(chuàng)建一個(gè)新的用戶(hù)testUser，密碼為testpwd。用戶(hù) testUser對(duì)所有的數(shù)據(jù)有查詢(xún)和更新權(quán)限，并授于對(duì)所有數(shù)據(jù)表的SELECT和UPDATE權(quán)限

GRANT SELECT,UPDATE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'testpwd'; /*創(chuàng)建賬戶(hù)并授予權(quán)限*/
SELECT Host,User,Select_priv,Update_priv, FROM mysql.user where user='testUser'; /*查看賬戶(hù)權(quán)限信息*/

【例1.5】使用INSERT創(chuàng)建一個(gè)新賬戶(hù)，其用戶(hù)名稱(chēng)為customer1，主機(jī)名稱(chēng)為localhost，密碼為customer1：

INSERT INTO user (Host,User,Password) VALUES('localhost','customer1',PASSWORD('customer1'));

【例1.6】使用DROP USER刪除用戶(hù)'jeffrey'@‘localhost'

DROP USER 'jeffrey'@'localhost';

【例1.7】使用DELETE刪除用戶(hù)'customer1'@‘localhost'

DELETE FROM mysql.user WHERE host='localhost' and user='customer1';

【例1.8】使用mysqladmin將root用戶(hù)的密碼修改為“rootpwd”

mysqladmin -u root -p password "123456"

【例1.9】使用UPDATE語(yǔ)句將root用戶(hù)的密碼修改為“rootpwd2”：

UPDATE mysql.user set Password=password("rootpwd2")
WHERE User="root" and Host="localhost";

【例1.10】使用SET語(yǔ)句將root用戶(hù)的密碼修改為“rootpwd3”：

SET PASSWORD=password("rootpwd3");

【例1.11】使用SET語(yǔ)句將testUser用戶(hù)的密碼修改為“newpwd”：

SET PASSWORD FOR 'testUser'@'localhost'=password("newpwd");

【例1.12】使用UPDATE語(yǔ)句將testUser用戶(hù)的密碼修改為“newpwd2”：

UPDATE mysql.user set Password=PASSWORD("newpwd2")
WHERE User="testUser" and Host="localhost";

【例1.13】使用GRANT語(yǔ)句將testUser用戶(hù)的密碼修改為“newpwd3”：

sql

【例1.14】testUser用戶(hù)使用SET語(yǔ)句將自身的密碼修改為“newpwd4”：

SET PASSWORD = PASSWORD("newpwd4");

【例1.15】使用GRANT語(yǔ)句創(chuàng)建一個(gè)新的用戶(hù)grantUser，密碼為“grantpwd”。用戶(hù)grantUser對(duì)所有的數(shù)據(jù)有查詢(xún)、插入權(quán)限，并授于GRANT權(quán)限。GRANT語(yǔ)句及其執(zhí)行結(jié)果如下：

MySQL> GRANT SELECT,INSERT ON *.* TO 'grantUser'@'localhost' IDENTIFIED BY 'grantpwd' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)

結(jié)果顯示執(zhí)行成功，使用SELECT語(yǔ)句查詢(xún)用戶(hù)testUser2的權(quán)限：

MySQL> SELECT Host,User,Select_priv,Insert_priv, Grant_priv FROM mysql.user where user='grantUser';
+-----------+------------+-------------+--------------+-------------+
| Host | User | Select_priv | Insert_priv | Grant_priv |
+-----------+------------+-------------+--------------+--------------+
| localhost | testUser2 | Y  | Y  | Y  |
+-----------+------------+-------------+--------------+--------------+
1 row in set (0.00 sec)

【例1.16】使用REVOKE語(yǔ)句取消用戶(hù)testUser的更新權(quán)限。REVOKE語(yǔ)句及其執(zhí)行結(jié)果如下：

MySQL> REVOKE UPDATE ON *.* FROM 'testUser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

執(zhí)行結(jié)果顯示執(zhí)行成功，使用SELECT語(yǔ)句查詢(xún)用戶(hù)test的權(quán)限：

MySQL> SELECT Host,User,Select_priv,Update_priv,Grant_priv FROM MySQL.user where user='testUser';

【例1.17】使用SHOW GRANTS語(yǔ)句查詢(xún)用戶(hù)testUser的權(quán)限信息。SHOW GRANTS語(yǔ)句及其執(zhí)行結(jié)果如下：

MySQL> SHOW GRANTS FOR 'testUser'@'localhost';

案例操作過(guò)程

打開(kāi)MySQL客戶(hù)端工具，輸入登錄命令，登錄MySQL。

C:\&;mysql -u root -p
Enter password: **

輸入正確密碼，按回車(chē)，出現(xiàn)歡迎信息表示登錄成功。

選擇mysql數(shù)據(jù)庫(kù)為當(dāng)前數(shù)據(jù)庫(kù)。

MySQL> use mysql;
Database changed

出現(xiàn)Database changed信息表明切換數(shù)據(jù)庫(kù)成功。

創(chuàng)建新賬戶(hù)，用戶(hù)名稱(chēng)為newAdmin，密碼為pw1，允許其從本地主機(jī)訪問(wèn)MySQL。

使用GRANT語(yǔ)句創(chuàng)建新賬戶(hù)，創(chuàng)建過(guò)程如下：

MySQL> GRANT SELECT, UPDATE(id, name, age)
 -> ON test_db.person_old
 -> TO 'newAdmin'@'localhost' IDENTIFIED BY 'pw1'
 -> WITH MAX_CONNECTIONS_PER_HOUR 30;
SELECT host, user, select_priv, update_priv FROM user WHERE user='newAdmin';
SELECT host, db, user, table_name, table_priv, column_priv 
FROM tables_priv WHERE user='newAdmin';
SELECT host, db, user, table_name, column_name, column_priv 
FROM columns_priv WHERE user='newAdmin';

3條SQL語(yǔ)句的查詢(xún)結(jié)果分別如下：

MySQL> SELECT host, user, select_priv, update_priv FROM user WHERE user='newAdmin';
MySQL> SELECT host, db, user, table_name, table_priv, column_priv
 -> FROM tables_priv WHERE user='newAdmin';
MySQL> SELECT host, db, user, table_name, column_name, column_priv
 -> FROM columns_priv WHERE user='newAdmin';

使用SHOW GRANTS語(yǔ)句查看newAdmin的權(quán)限信息。

查看newAdmin賬戶(hù)的權(quán)限信息，輸入語(yǔ)句如下：

SHOW GRANTS FOR 'newAdmin'@'localhost';

使用newAdmin用戶(hù)登錄MySQL。

退出當(dāng)前登錄，使用EXIT命令，語(yǔ)句如下：

MySQL> exit
Bye

使用newAdmin賬戶(hù)登錄MySQL，語(yǔ)句如下：

C:\&;MySQL -u newAdmin -p
Enter password: ***

輸入密碼正確后，出現(xiàn)“mysql>”提示符，登錄成功。

使用newAdmin用戶(hù)查看test_db數(shù)據(jù)庫(kù)中person_dd表中的數(shù)據(jù)。

newAdmin用戶(hù)被授予test數(shù)據(jù)庫(kù)中person表中3個(gè)字段上的查詢(xún)權(quán)限，因此可以執(zhí)行SELECT語(yǔ)句查看這幾個(gè)字段的值，執(zhí)行過(guò)程如下：

MySQL> SELECT * FROM test_db.person_dd LIMIT 5;

使用newAdmin用戶(hù)向person_dd表中插入一條新記錄，查看語(yǔ)句執(zhí)行結(jié)果。

插入新記錄，輸入語(yǔ)句如下：

INSERT INTO test_db.person_old(name, age,info) VALUES('gaga', 30);

執(zhí)行結(jié)果如下：

ERROR 1142 (42000): INSERT command denied to user 'newAdmin'@'localhost' for table 'person'

可以看到，語(yǔ)句不能執(zhí)行，錯(cuò)誤信息表明newAdmin用戶(hù)不能對(duì)person表進(jìn)行插入操作。因此，用戶(hù)不可以執(zhí)行沒(méi)有被授權(quán)的操作語(yǔ)句。

退出當(dāng)前登錄，使用root用戶(hù)重新登錄，收回newAdmin賬戶(hù)的權(quán)限。

輸入退出命令：exit

重新以root用戶(hù)登錄MySQL，并選擇mysql數(shù)據(jù)庫(kù)為當(dāng)前數(shù)據(jù)庫(kù)。

輸入語(yǔ)句收回newAdmin賬戶(hù)的權(quán)限，執(zhí)行過(guò)程如下：

REVOKE SELECT, UPDATE ON test.person FROM 'newAdmin'@'localhost';

執(zhí)行結(jié)果如下：

MySQL> REVOKE SELECT, UPDATE ON test.person FROM 'newAdmin'@'localhost';
Query OK, 0 rows affected (0.00 sec)

刪除newAdmin的賬戶(hù)信息。

刪除指定賬戶(hù)，可以使用DROP USER語(yǔ)句，輸入如下：

DROP USER 'newAdmin'@'localhost';

二. PostgreSQL用戶(hù)管理

2.1 組角色管理

【例2.1】創(chuàng)建一個(gè)名稱(chēng)為post2的角色，SQL代碼如下：

CREATE ROLE post2;

【例2.2】查看系統(tǒng)中的角色，SQL代碼如下：

SELECT rolname FROM pg_roles;

【例2.3】修改角色post1的名稱(chēng)為post3。SQL語(yǔ)句如下：

ALTER ROLE post1 RENAME TO post3;

【例2.4】刪除角色post3。SQL語(yǔ)句如下：

DROP ROLE post3;

2.2 角色的各種權(quán)限

1、登錄

【例2.5】創(chuàng)建角色post4，此角色具有登錄權(quán)限。SQL語(yǔ)句如下：

CREATE ROLE post4 LOGIN;

2超級(jí)用戶(hù)

【例2.6】創(chuàng)建角色post5，此角色具有超級(jí)用戶(hù)權(quán)限。SQL語(yǔ)句如下：

CREATE ROLE post5 SUPERUSER;

3、創(chuàng)建數(shù)據(jù)庫(kù)

【例2.7】創(chuàng)建角色post6，此角色具有創(chuàng)建數(shù)據(jù)庫(kù)權(quán)限。SQL語(yǔ)句如下：

CREATE ROLE post6 CREATEDB;

4、 創(chuàng)建角色

角色要想創(chuàng)建角色，必須明確給出該權(quán)限（除了超級(jí)用戶(hù)以外）。一旦角色具有CREATEROLE權(quán)限，即可更改和刪除其他角色，還可以給其它角色賦予或者撤銷(xiāo)成員關(guān)系。當(dāng)然，如果想對(duì)超級(jí)用戶(hù)進(jìn)行操作，僅有此權(quán)限還不夠，必須擁有SUPERUSER權(quán)限。

創(chuàng)建具有創(chuàng)建角色權(quán)限的角色，SQL語(yǔ)法如下：

CREATE ROLE name CREATEROLE;

【例2.8】創(chuàng)建角色post7，此角色具有創(chuàng)建數(shù)據(jù)庫(kù)權(quán)限。SQL語(yǔ)句如下：

CREATE ROLE post7 CREATEROLE;

5、口令

在客戶(hù)認(rèn)證方法要求與數(shù)據(jù)庫(kù)建立連接時(shí)，需要口令權(quán)限。常見(jiàn)的認(rèn)證方法包括password、md5和crypt。

創(chuàng)建具有口令權(quán)限的角色，SQL語(yǔ)法如下：

CREATE ROLE name 

口令認(rèn)證方法 具體口令

【例2.9】創(chuàng)建角色post8，此角色具有口令權(quán)限。SQL語(yǔ)句如下：

CREATE ROLE post8 PASSWORD '123456';

2.3 賬戶(hù)管理

1、創(chuàng)建用戶(hù)

【例2.10】創(chuàng)建用戶(hù)名稱(chēng)為postgre02，并具有創(chuàng)建數(shù)據(jù)庫(kù)和創(chuàng)建角色的權(quán)限，同時(shí)登陸密碼為“123456789”。SQL語(yǔ)句如下：

CREATE USER postgre02 PASSWORD '123456789' 
CREATEDB CREATEROLE ;

2、刪除用戶(hù)

【例2.11】使用DROP USER刪除賬戶(hù)“postgre02”，SQL語(yǔ)句如下：

DROP USER postgre02;

3、修改用戶(hù)密碼

【例2.12】將賬戶(hù)“postgre01”的密碼修改為“123123”，SQL語(yǔ)句如下：

ALTER USER postgre01 PASSWORD '123123';

2.4 組角色和用戶(hù)角色管理

1、對(duì)組角色授權(quán)

【例2.13】給“post1”角色添加創(chuàng)建數(shù)據(jù)表和創(chuàng)建角色的權(quán)限，SQL語(yǔ)句如下：

ALTER ROLE post1 CREATEDB CREATEROLE;

2、對(duì)用戶(hù)授權(quán)

【例2.14】給“postgre01”用戶(hù)添加創(chuàng)建數(shù)據(jù)表和創(chuàng)建角色的權(quán)限，SQL語(yǔ)句如下：

ALTER USER postgre01 CREATEDB CREATEROLE;

3、收回組角色權(quán)限

【例2.15】將“post1”角色的創(chuàng)建數(shù)據(jù)表和創(chuàng)建角色權(quán)限收回，SQL語(yǔ)句如下：

ALTER ROLE post1 NOCREATEDB NOCREATEROLE;

4、收回用戶(hù)權(quán)限

【例2.16】將“postgre01”用戶(hù)的創(chuàng)建數(shù)據(jù)表和創(chuàng)建角色權(quán)限收回，SQL語(yǔ)句如下：

ALTER USER postgre01 NOCREATEDB NOCREATEROLE;

2.5 數(shù)據(jù)庫(kù)權(quán)限管理

1、修改數(shù)據(jù)庫(kù)的擁有者

【例2.17】將“mytest”數(shù)據(jù)庫(kù)的所有者修改為post1。SQL語(yǔ)句如下：

ALTER DATABASE mytest OWNER TO post2;

2、增加用戶(hù)的數(shù)據(jù)表權(quán)限

【例2.18】其中ppo1是一個(gè)現(xiàn)有的數(shù)據(jù)表，postgres是一個(gè)現(xiàn)有的用戶(hù)，允許postgres更新ppo1數(shù)據(jù)表，用下面的命令更新表的權(quán)限：

GRANT UPDATE ON ppo1 TO postgres;

如果將上面的語(yǔ)句修改為：

GRANT UPDATE ON ppo1 TO PUBLIC;

則表示將數(shù)據(jù)表ppo1的更新權(quán)限賦予系統(tǒng)中的所有角色。

如果將上面的語(yǔ)句修改為：

GRANT ALL ON ppo1 TO postgres;

則表示把適用于該對(duì)象的所有權(quán)限都賦予用戶(hù)postgres。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。