mysql安全、訪問控制和權限怎么配置

這篇文章主要介紹“mysql安全、訪問控制和權限怎么配置”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“mysql安全、訪問控制和權限怎么配置”文章能幫助大家解決問題。

創建mysql數據庫后系統會自動創建test庫和mysql庫
test中有測試實驗數據
mysql庫相當于oracle的系統表空間，記錄庫相關的內容；

當前明月三千里版本為5.0.18
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.18-nt |
+-----------+
1 row in set (0.00 sec)

版本5.0.18下系統表包括如下表格
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

--------

user表

| user  |CREATE TABLE `user` (
  `Host` char(60) collate utf8_bin NOT NULL default '',
  `User` char(16) collate utf8_bin NOT NULL default '',
  `Password` char(41) character set latin1 collate latin1_bin NOT NULL default '',
  `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Reload_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Shutdown_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Process_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `File_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `References_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Show_db_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Super_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Execute_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Repl_slave_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Repl_client_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Show_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Alter_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_user_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') character set utf8 NOT NULL default '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL default '0',
  `max_updates` int(11) unsigned NOT NULL default '0',
  `max_connections` int(11) unsigned NOT NULL default '0',
  `max_user_connections` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' |

`Host`,`User`,`Password`三個字段記錄允許訪問數據庫的用戶機器口令以及可以連接的主機
mysql> select host,user,password from user;                    
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *B22DF64775852C409540CACB81399E4D2E7F93AC |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)                                        

其他字段包括權限控制，是否對口令加密等信息

------------

db表和host表

兩個表一起使用，記錄用戶可以對那些數據庫進行什么樣的操作

mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
20 rows in set (0.00 sec)

mysql> desc host;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
19 rows in set (0.01 sec)

------------------

tables_priv表和columns_priv表

記錄dba分配和限制的權限
分別對應的是表一級的權限以及字段

在獲取權限時首先讀取user表，確定用戶有鏈接權限后才進一步驗證是否有其他權限
所以之后訪問的是db表和host表
最后才是tables_priv表和column_priv表

特殊情況：
 本地用戶localhost可以以root形勢連接系統中所有數據庫
 windows允許本地連接訪問所有數據庫，unix允許本地完全訪問test數據庫
 對來自其他主機的用戶默認拒絕訪問

----------------

授權、廢除核瀏覽用戶權限

本地連接
mysql -u root -p

全表授權：
mysql> grant select on an.a  to test@localhost identified by 'anbaisheng';
字段授權：
mysql> grant select (id,name) on an.a to test@localhost;
回收權限：
mysql> revoke select on an.a from test@localhost;

mysql還提供了all權限等級作為全部權限的快捷方式

將an用戶的全部權限授予test：
mysql> grant all on an.* to test@localhost;

mysql還提供了usage權限等級，只擁有創建用戶但不授予其他任何權限
mysql> grant usage on an.* to test@localhost;
Query OK, 0 rows affected (0.00 sec)

------------------

限制資源的使用

user表中有字段
max_questions
max_updates
max_connections
用來限制具體用戶每個小時內的查詢、表或者記錄更新和新連接的數量
這些限制可以寫在grant語句中，用with關鍵字寫就可以

也可以直接向user表中插入數據來進行權限管理
插入數據后需要flush privileges來使其生效

--------------------

查詢權限

show grants for user@server;

mysql> show grants for test@localhost;
+-------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*B22DF64775852C409540CACB81399E4D2E7F93AC' |
| GRANT ALL PRIVILEGES ON `an`.* TO 'test'@'localhost'                                                        |
| GRANT SELECT (name, id) ON `an`.`a` TO 'test'@'localhost'                                                   |
+-------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

---------------------

重新加載授權表

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

/usr/local/mysql/bin/mysqladmin -u root reload

---------------------

重置授權表

1/usr/local/mysql/support-files/mysql.server stop
2rm -rf /usr/local/mysql/data/mysql
3/usr/local/mysql/scripts/mysql_install_db
4\chown -R mysql:mysql /usr/local/mysql/data/mysql
5/usr/local/mysql/support-files/mysql.server start

----------------------

更改用戶口令

mysql -h localhost -u logger -p

腳本中可以使用如下語法
mysql -h localhost -u logger -ptimber

如果直接在user表中修改密碼必須使用password函數將明文密碼轉換后才可以

update user set password =password('anbaisheng');
flush privileges;

mysql> set password for test@localhost=password('anbaisheng');

------------------------

設置root口令

立即生效
/usr/local/mysql/bin/mysqladmin -u root password 'new_password'

也可以使用常規的更改方式來修改

-------------------------

重置root口令

1、/usr/local/mysql/support-files/mysql.server stop
2、/usr/local/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking
3、mysql
  use mysql
  update user set password =password('new-password') where user='root';
4、/usr/local/mysql/support-files/mysql.server stop
   /usr/local/mysql/support-files/mysql.server start  

關于“mysql安全、訪問控制和權限怎么配置”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注億速云行業資訊頻道，小編每天都會為大家更新不同的知識點。