mysql安全、访问控制和权限怎么配置

这篇文章主要介绍“MySQL安全、访问控制和权限怎么配置”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“mysql安全、访问控制和权限怎么配置”文章能帮助大家解决问题。

成都创新互联主要从事网站制作、网站设计、网页设计、企业做网站、公司建网站等业务。立足成都服务姑苏,10余年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:028-86922220

创建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安全、访问控制和权限怎么配置”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注创新互联行业资讯频道，小编每天都会为大家更新不同的知识点。