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