程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 我的MYSQL學習心得(十三),mysql學習心得

我的MYSQL學習心得(十三),mysql學習心得

編輯:MySQL綜合教程

我的MYSQL學習心得(十三),mysql學習心得


我的MYSQL學習心得(十三)

我的MYSQL學習心得(一)

我的MYSQL學習心得(二)

我的MYSQL學習心得(三)

我的MYSQL學習心得(四)

我的MYSQL學習心得(五)

我的MYSQL學習心得(六)

我的MYSQL學習心得(七)

我的MYSQL學習心得(八)

我的MYSQL學習心得(九)

我的MYSQL學習心得(十)

我的MYSQL學習心得(十二)

 

這一篇《我的MYSQL學習心得(十三)》將會講解MYSQL的用戶管理

 

在mysql數據庫中,有mysql_install_db腳本初始化權限表,存儲權限的表有:

1、user表

2、db表

3、host表

4、table_priv表

5、columns_priv表

6、proc_priv表

 

MySQL存取控制包含2個階段:

  • 階段1:服務器檢查你是否允許連接。
  • 階段2:假定你能連接,服務器檢查你發出的每個請求。看你是否有足夠的權限實施它。例如,如果你從數據庫中一個表精選(select)行或從數據庫拋棄一個表,服務器確定你對表有select權限或對數據庫有drop權限。

服務器在存取控制的兩個階段使用在mysql的數據庫中的userdbhost表,在這些授權表中字段如下:

表名稱 user db host 范圍字段 Host Host Host   User Db Db   Password User   權限字段 Select_priv Select_priv Select_priv   Insert_priv Insert_priv Insert_priv   Update_priv Update_priv Update_priv   Delete_priv Delete_priv Delete_priv   Index_priv Index_priv Index_priv   Alter_priv Alter_priv Alter_priv   Create_priv Create_priv Create_priv   Drop_priv Drop_priv Drop_priv   Grant_priv Grant_priv Grant_priv   Reload_priv       Shutdown_priv       Process_priv       File_priv    

對存取控制的第二階段(請求證實),如果請求涉及表,服務器可以另外參考tables_privcolumns_priv表。這些表的字段如下:

表名稱 tables_priv columns_priv 范圍字段 Host Host   Db Db   User User   Table_name Table_name     Column_name 權限字段 Table_priv Column_priv   Column_priv   其他字段 Timestamp Timestamp   Grantor  

每個授權表包含范圍字段和權限字段。

 

user表主要分為:用戶列、權限列、安全列、資源控制列

host表主要分為:用戶列、權限列

這裡美中不足的是mysql.user 沒有一個列是保存用戶創建時間的

有時候排查用戶問題的時候,比如某個客戶在某個時間說連接不上數據庫,我們在user表裡只能查到是否存在那個用戶

但是不知道這個用戶的創建時間,也就是說客戶說的那個時間究竟用戶是否已經創建我們是不知道的

 

 


帳戶管理

MYSQL提供許多語句用來管理用戶帳號,這些語句可以用來包括登錄和退出MYSQL服務器、創建用戶、刪除用戶、密碼管理、權限管理

MYSQL數據庫的安全性,需要通過帳戶管理來保證

 

登錄和退出MYSQL

mysql命令的常用參數

-h:主機名或ip,默認是localhost,最好指定-h參數

-u:用戶名

-p:密碼,注意:該參數後面的字符串和-p不能有空格

-P:端口號,默認為3306

數據庫名:可以在命令最後指定數據庫名

-e:執行SQL語句,如果指定該參數,將在登錄後執行-e後面的命令或sql語句並退出

 

 

 命令執行完之後返回book表的結構,查詢返回之後會自動退出MYSQL


用戶

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] 

 

新建普通用戶

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

用戶名部分為“jeffrey”,主機名默認為“%”(即對所有主機開放權限)

如果指定用戶登錄不需要密碼,則可以省略identified BY部分

 

對於使用插件認證連接的用戶,服務器調用指定名稱的插件,客戶端需要提供驗證方法所需要的憑據。

如果創建用戶時或者連接服務器時,服務器找不到對應的插件,將返回一個錯誤

identified with語法

CREATE user 'jeffrey'@'localhost' identified with my_auth_plugin;

identified with只能在MYSQL5.5.7及以上版本使用。

identified with和identified by是互斥的,所以對一個帳戶來說只能使用一個驗證方法。

CREATE USER語句的操作會被記錄到服務器日志文件或者操作歷史文件中

例如 ~/.mysql_history。這意味著對這些文件有讀取權限的人,都可以讀取到新添加用戶的明文密碼

 

一個辦法就是新建用戶的時候使用password關鍵字

CREATE user 'tom'@'localhost' identified BY password'*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

SELECT password('mypass');

SELECT * FROM `mysql`.`user` WHERE `User` ='tom';

先查出你的密碼的哈希值,然後在新建用戶的時候輸入哈希值

那麼在日志裡面就只能看到哈希值

 


使用GRANT語句創建新用戶

GRANT USER語句可以用來創建帳戶,通過該語句可以在user表中添加一條新記錄

比起CREATE USER語句創建的新用戶,還需要使用GRANT語句賦予用戶權限

使用GRANT語句創建新用戶時必須有GRANT權限。

語法

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER 'cipher' [AND]]
        [ISSUER 'issuer' [AND]]
        [SUBJECT 'subject']]
    [WITH with_option [with_option] ...]

 

使用GRANT語句創建一個新用戶testUser,密碼為testpwd,並授予用戶對所有數據表的SELECT和UPDATE權限

GRANT SELECT ,UPDATE ON *.* TO 'testUser'@'localhost' identified BY 'testpwd'
SELECT `Host` ,`User` ,`Select_priv` ,`Update_priv`  FROM mysql.user WHERE `User` ='testUser';

執行結果顯示執行成功,使用SELECT語句查詢用戶testUser的權限

 查詢結果顯示SELECT和UPDATE權限字段均為Y

注意:User表中的user和host字段區分大小寫,在查詢的時候要指定正確的用戶名或主機名


直接操作MYSQL用戶表

不管是CREATE USER還是GRANT USER,在創建用戶時,實際上都是在user表中添加一條新記錄。

使用INSERT語句向mysql.user表INSERT一條記錄來創建一個新用戶

插入的時候必須要有INSERT權限

INSERT INTO mysql.user(host,user,password,[privilegelist])
VALUES ('host','username',password('password'),privilegevaluelist)

 

使用INSERT創建一個新用戶,其用戶名稱為customer1,主機名為localhost,密碼為customer1

INSERT INTO mysql.user(host,user,password)
VALUES ('localhost','customer1',password('customer1'))

語句執行失敗,查看警告信息如下:

show WARNINGS ;

因為ssl_cipher這個字段在user表中沒有定義默認值,所以在這裡提示錯誤信息。

影響insert語句的執行,使用SELECT語句查看user表中的記錄

可以看到,插入失敗


刪除普通用戶

使用DROP USER語句刪除用戶,也可以直接通過DELETE從mysql.user表中刪除對應的記錄來刪除用戶

DROP USER語句用於刪除一個或多個MYSQL帳戶。要使用DROP USER,必須擁有MYSQL數據庫的全局

CREATE USER 權限或DELETE權限。

 

刪除testUser這個用戶

DROP user 'testUser'@'localhost';

 

 可以發現testUser這個用戶已經刪除了

 

使用delete語句刪除用戶

DELETE FROM mysql.user WHERE `Host`='localhost' and `User`='testUser'

root用戶修改自己的密碼

修改root密碼的方式有多種

 

1、使用mysqladmin命令在命令行指定新密碼

mysqladmin -u root -p password"rootpwd"

 

 

2、修改mysql數據庫的user表

UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'

password('')函數用來加密用戶密碼。執行update之後需要執行flush privileges語句重新加載用戶權限

 

3、使用SET語句修改root用戶的密碼

SET PASSWORD語句可以用來重新設置其他用戶的登錄密碼或者自己使用的帳戶密碼

語法

SET PASSWORD=PASSWORD("ROOTPWD")

 

新密碼必須用PASSWORD函數加密

 

使用root用戶登錄到mysql之後執行下面語句

SET password=password('123456')

執行之後需要使用執行flush privileges語句或者重啟MYSQL重新加載用戶權限

 


root用戶修改普通用戶密碼

1、使用SET語句修改普通用戶的密碼

SET PASSWORD FOR 'USER'@'HOST' =PASSWORD("ROOTPWD")

 

2、使用update語句修改普通用戶的密碼

UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'

執行完畢之後需要使用flush privileges語句或者重啟MYSQL重新加載用戶權限

 

3、使用GRANT語句修改普通用戶密碼

GRANT USAGE ON *.* TO 'someuser'@'%'  IDENTIFIED BY 'somepwd'

 

使用下面語句把testUser用戶的密碼改為123456

grant USAGE ON *testUser*TO 'localhost' identified BY '123456';

 

注意:使用GRANT語句和MYSQLADMIN設置密碼,他們均會加密密碼,這種情況下,不需要使用PASSWORD()函數


普通用戶修改密碼

使用SET語句修改自己的密碼

SET password=password('newpassword');

 

比如修改testUser這個用戶的密碼,需要使用testUser這個用戶登錄到mysql,然後執行

SET password=password('123456');

root用戶密碼丟失的解決辦法

使用--skip-grant-tables選項啟動MYSQL服務

使用--skip-grant-tables選項啟動MYSQL時,服務器將不加載權限判斷,任何用戶都能訪問數據庫

 

 

LINUX下

使用mysqld_safe來啟動MYSQL服務,也可以使用/etc/init.d/mysql命令來啟動mysql

mysqld_safe --skip-grant-tables user=mysql

或者

/etc/init.d/mysql start-mysqld --skip-grant-tables

啟動MYSQL服務後,就可以使用root用戶登錄了

 

Windows下

詳細可以看一下這篇文章

Windows mysql提示:1045 access denied for user 'root'@'localhost' using password yes


權限管理

MYSQL中的各種權限

對於GRANT和REVOKE語句,priv_type可以被指定為以下任何一種:

權限

意義

ALL [PRIVILEGES]

設置除GRANT OPTION之外的所有簡單權限

ALTER

允許使用ALTER TABLE

ALTER ROUTINE

更改或取消已存儲的子程序

CREATE

允許使用CREATE TABLE

CREATE ROUTINE

創建已存儲的子程序

CREATE TEMPORARY TABLES

允許使用CREATE TEMPORARY TABLE

CREATE USER

允許使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。

CREATE VIEW

允許使用CREATE VIEW

DELETE

允許使用DELETE

DROP

允許使用DROP TABLE

EXECUTE

允許用戶運行已存儲的子程序

FILE

允許使用SELECT...INTO OUTFILE和LOAD DATA INFILE

INDEX

允許使用CREATE INDEX和DROP INDEX

INSERT

允許使用INSERT

LOCK TABLES

允許對您擁有SELECT權限的表使用LOCK TABLES

PROCESS

允許使用SHOW FULL PROCESSLIST

REFERENCES

未被實施

RELOAD

允許使用FLUSH

REPLICATION CLIENT

允許用戶詢問從屬服務器或主服務器的地址

REPLICATION SLAVE

用於復制型從屬服務器(從主服務器中讀取二進制日志事件)

SELECT

允許使用SELECT

SHOW DATABASES

SHOW DATABASES顯示所有數據庫

SHOW VIEW

允許使用SHOW CREATE VIEW

SHUTDOWN

允許使用mysqladmin shutdown

SUPER

允許使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL語句,mysqladmin debug命令;允許您連接(一次),即使已達到max_connections。

UPDATE

允許使用UPDATE

USAGE

“無權限”的同義詞

GRANT OPTION

允許授予權限

當從舊版本的MySQL升級時,要使用EXECUTE, CREATE VIEW, SHOW VIEW, CREATE USER, CREATE ROUTINE和ALTER ROUTINE權限


授權

授權就是為某個用戶授予權限

 

授予的權限可以分為多個層級:

·         全局層級

全局權限適用於一個給定服務器中的所有數據庫。這些權限存儲在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤銷全局權限。

·         數據庫層級

數據庫權限適用於一個給定數據庫中的所有目標。這些權限存儲在mysql.db和mysql.host表中。GRANT ALL ONdb_name.*和REVOKE ALL ON db_name.*只授予和撤銷數據庫權限。

·         表層級

表權限適用於一個給定表中的所有列。這些權限存儲在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表權限。

·         列層級

列權限適用於一個給定表中的單一列。這些權限存儲在mysql.columns_priv表中。當使用REVOKE時,您必須指定與被授權列相同的列。

·         子程序層級

CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT權限適用於已存儲的子程序。這些權限可以被授予為全局層級和數據庫層級。而且,除了CREATE ROUTINE外,這些權限可以被授予為子程序層級,並存儲在mysql.procs_priv表中。

當後續目標是一個表、一個已存儲的函數或一個已存儲的過程時,object_type子句應被指定為TABLE、FUNCTION或PROCEDURE。當從舊版本的MySQL升級時,要使用本子句,您必須升級您的授權表

 

使用GRANT語句創建一個新用戶grantUser,密碼為“grantpwd”

用戶對所有的數據有查詢、插入權限,並授予GRANT權限

GRANT SELECT ,INSERT ON *.*TO 'grantUser'@'localhost' identified BY '123456' WITH GRANT OPTION ;

 

查詢顯示grantUser被創建成功,並賦予了SELECT、INSERT、GRANT權限,其相應字段值為Y

 

被授予GRANT權限的用戶可以登錄MYSQL並創建其他用戶帳戶,在這裡是grantUser的用戶


收回權限

收回權限就是取消已經賦予用戶的某些權限。收回用戶不必要的權限可以在一定程度上保證系統的安全性。

使用REVOKE收回權限之後,用戶帳戶的記錄將從db、host、tables_priv、columns_priv表中刪除,但是用戶帳號記錄依然

在user表中保存。

語法

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

使用REVOKE語句,必須擁有mysql數據庫的全局CREATE權限或UPDATE權限

 

使用REVOKE語句取消用戶grantUser的INSERT權限

REVOKE INSERT ON *.* FROM 'grantUser'@'localhost';

 

可以看到grantUser用戶的INSERT權限已經被收回了

注意:當從舊版本的MYSQL升級時,如果要使用EXECUTE、CREATE VIEW、SHOW VIEW、CREATE USER、CREATE ROUTINE、ALTER ROUTINE

權限,必須先升級授權表


查看權限

SHOW GRANT語句可以顯示用戶的權限信息

語法

show grants FOR 'user'@'host';

 

使用SHOW GRANT語句查詢用戶grantUser的權限信息

show grants FOR 'grantUser'@'localhost';

 

 返回結果顯示了user表中的帳戶信息;接下來以為GRANT SELECT ON關鍵字開頭,表示用戶被授予了SELECT權限;

*.*表示SELECT權限作用於所有數據庫的所有數據表;

IDENTIFIED BY 後面的為用戶加密後的密碼

 

在這裡,只是定義了個別的用戶權限,GRANT可以顯示更加詳細的權限信息,包括全局級的和非全局級的權限

如果表層級或者列層級的權限被授予用戶的話,他們也能在結果中顯示出來。


查看MYSQL裡面匿名用戶

如果有匿名用戶,那麼客戶端就可以不用密碼登錄MYSQL數據庫,這樣就會存在安全隱患

檢查匿名用戶的方法

SELECT * FROM mysql.user WHERE `User`='';

如果查找到user字段值為空的那條記錄,說明存在匿名用戶,需要把這條記錄刪除

如果用匿名用戶登錄MYSQL就可以看到用戶名是空的

刪除語句

DELETE FROM mysql.user WHERE `User`='';

SELECT * FROM mysql.user WHERE `User`='';

總結

本文簡單的闡述了MYSQL的用戶管理和權限方面的內容,希望對大家有幫助

如果大家想更深入學習MYSQL訪問控制方面的知識

可以參考這篇文章:MySQL權限的架構體系

核心就是兩個圖

1、客戶端連接請求認證階段

 

 

2、客戶端操作請求認證階段

 

 

 

 

最最後說一下,我開了一個MYSQL走起的微博,希望大家支持一下o(∩_∩)o 

歡迎關注MYSQL官方微博:MYSQL走起

在我的博客的左邊欄就會看到微博鏈接,大家也可以通過點擊我的博客左邊欄的微博鏈接進入MYSQL走起微博o(∩_∩)o 

 

如有不對的地方,歡迎大家拍磚o(∩_∩)o 


我想學習數據庫,問MYSQL怎

與其他的大型數據庫例如Oracle、DB2、SQL Server等相比,MySQL自有它的不足之處,如規模小、功能有限(MySQL Cluster的功能和效率都相對比較差)等,但是這絲毫也沒有減少它受歡迎的程度。對於一般的個人使用者和中小型企業來說,MySQL提供的功能已經綽綽有余,而且由於MySQL是開放源碼軟件,因此可以大大降低總體擁有成本。 LAMP
目前Internet上流行的網站構架方式是LAMP(Linux+Apache+MySQL+PHP/Perl/Python)和LNMP(Linux+Nginx+MySQL+php/perl/Python)。不過SQL語言都是大同小異的,只要你把Mysql學精了,其他SQL語言都能很快上手~~
 

Java學習心得

先要聽老師講的,理解他的思路,然後試著寫老師講的代碼,不會的時候可一看看老師的代碼,關鍵是要知道代碼為什麼這樣寫,還要學會檢查異常,解決異常,這一點也很重要。

我覺得我學習那會,就是寫代碼,改錯。

代碼寫的多了,自然就知道該怎麼寫了。
純屬個人意見,希望對你有幫助。
 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved