mysql学习笔记之十四（安全性机制）
来源：互联网 发布：手机陀螺仪水平仪软件 编辑：程序博客网 时间：2024/05/23 16:55
root和普通用户权限机制 mysql系统数据库 show databases; use mysql; show tables; +---------------------------+ | 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 | +---------------------------+ mysql.user +-----------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | 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 | | | Reload_priv | enum('N','Y') | NO | | N | |重新加载权限表 | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | |服务器管理 | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | |Grand Option 数据库，表，存储过程函数 | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_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 | | | Create_user_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | +-----------------------+-----------------------------------+------+-----+---------+-------+ Host 主机名 User 用户名 Password 上述三个字段都验证成功，才允许用户登录 权限字段，以_priv结尾的字段，决定了用户权限 高级权限 对用数据库进行管理 普通权限 对数据库操作 安全字段 ssl*~x509_su*，主要用来实现加密 mysql通常不支持ssl标准 show variables like 'have_openssl' 上句可以用来查询是否支持ssl +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | +---------------+----------+ mysql.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 | | +-----------------------+---------------+------+-----+---------+-------+ 用户字段 Host，User，Db 权限字段 形如*_priv mysql.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 | | +-----------------------+---------------+------+-----+---------+-------+ 用户字段 Host,Db 权限字段 其他权限表 tables_priv 对单表进行权限设置 *************************** 1. row *************************** Field: Host Type: char(60) Null: NO Key: PRI Default: Extra: *************************** 2. row *************************** Field: Db Type: char(64) Null: NO Key: PRI Default: Extra: *************************** 3. row *************************** Field: User Type: char(16) Null: NO Key: PRI Default: Extra: *************************** 4. row *************************** Field: Table_name Type: char(64) Null: NO Key: PRI Default: Extra: *************************** 5. row *************************** Field: Grantor Type: char(77) Null: NO Key: MUL Default: Extra: *************************** 6. row *************************** Field: Timestamp Type: timestamp Null: NO Key: Default: CURRENT_TIMESTAMP Extra: *************************** 7. row *************************** Field: Table_priv Type: set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view') Null: NO Key: Default: Extra: *************************** 8. row *************************** Field: Column_priv Type: set('Select','Insert','Update','References') Null: NO Key: Default: Extra: columns_priv 对单列进行权限设置 +-------------+----------------------------------------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------------------------------+------+-----+-------------------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Column_name | char(64) | NO | PRI | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+----------------------------------------------+------+-----+-------------------+-------+ procs_priv 系统表 +--------------+----------------------------------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------------------------+------+-----+-------------------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Routine_name | char(64) | NO | PRI | | | | Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | | | Grantor | char(77) | NO | MUL | | | | Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | | +--------------+----------------------------------------+------+-----+-------------------+-------+用户机制 用户管理机制包括登陆退出mysql，创建用户，修改用户密码，删除用户，为用户赋予权限 1、登陆或退出mysql mysql -h hostname|hostIP -p port -u username -pPassword DatabaseName -e "SQL语句" 登陆数据库服务器，如果直接接密码，则密码password直接写在-p之后，中间不能有空格 mysql -u root -p 登陆本地的数据库，则隐藏输入密码 exit|quit：退出数据库 2、创建普通用户 root用户的权限太大，应该严格杜绝使用root登陆mysql （a）create user create user username [identifield by [password] 'password'] [,username [identifield by [password] 'password']] .... password关键字主要用来对密码进行加密 create user qionghua identified by '123456'; （b）insert mysql.user存储了关于用户账户的信息 insert into user(host,user,password) values('hostname','username',password('password')); 在具体创建用户账户时，由于表user中字段ssl_cipher，x509_issuer,x509_subject没有默认值，所以还需要设置这些字段的值，对password字段，一定要使用函数PASSWORD()进行加密 创建用户成功后如果不能登陆，使用 flush privileges 刷新下权限 （c）grant 上述两种创建用户的方式不方便给用户赋予权限 grant priv_type on databasename.tablename to username[identified by [password]'password'] [,username[identified by [password]'password']] [,username[identified by [password]'password']] .... 2、修改用户密码 两种方式：通过超级权限用户root和通过普通用户 root用户密码修改 1、mysqladmin 修改root用户密码 mysqladmin -u username -p oldpassword "new_password" 2、set 登陆mysql服务器后，可以通过set命令修改root用户密码 set password=password('new password') 3、更改系统表mysql.user数据记录修改 update user set password=password('new_password') where user='root' and host='localhost' 普通用户密码修改 1、仿照创建用户时的grant方式 grant priv_type on databasename.tablename to username[identified by [password]'password'] 2、set命令 通过root账号登陆mysql后使用set命令 set password for 'username'@'hostname'=password("newpassword") 通过普通账户登陆mysql后使用set命令修改自己的密码 set password=password("new_password") 3、更改系统表mysql.user update user set password=password("password") where user='username' and host="localhost" 如果没有设置主机，则不需要匹配host="localhost" 3、删除普通用户账户 1、drop user drop user user1[,usre2,...] 2、删除系统表mysql.user数据记录 delete from user where user="username" and host="hostname" 权限管理 授权权限 在进行授权操作之前，需要用户具有grant权限 grant priv_type[(column_list)] on databasename.tablename to user [identified by [password] "password"] [,user [identified by [password] "password"]] ... [with with_option[with_option]....] with_option的取值 grant option max_queries_per_hour count max_update_per_hour count max_connections_per_hour count max_user_connections count 单个用户可以同时具有count个连接 查看权限 1、查看系统表mysql.user的数据记录 grant select,update,create,drop on *.* to 'qionghua'; select * from mysql.user where user='qionghua'\G *************************** 1. row *************************** Host: % User: qionghua Password: *00A51F3F48415C7D4E8908980D443C29C69B60C9 Select_priv: Y Insert_priv: N Update_priv: Y Delete_priv: N Create_priv: Y Drop_priv: Y ........ 后面为N的字段省略。 同这个查询，可以看出该用户所具有的权限 收回权限 revoke priv_type[(column_list)] on databasename.tablename from user1 [identified by [password] "password"] [,user2 [identified by [password] "password"]] ... [with with_option[with_option]....] 收回全部权限 revoke all privilege,grant option from user1 [identified by [password] "password"] [,user2 [identified by [password] "password"]] ... mysql> show grants for "qionghua"\G *************************** 1. row *************************** Grants for qionghua@%: GRANT SELECT, UPDATE, CREATE, DROP ON *.* TO 'qionghua'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74 329105EE4568DDA7DC67ED2CA2AD9' 1 row in set (0.00 sec) mysql> revoke select on *.* from "qionghua"; Query OK, 0 rows affected (0.00 sec) mysql> show grants for "qionghua"\G *************************** 1. row *************************** Grants for qionghua@%: GRANT UPDATE, CREATE, DROP ON *.* TO 'qionghua'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE 4568DDA7DC67ED2CA2AD9' 1 row in set (0.00 sec) 0 0
- mysql学习笔记之十四（安全性机制）
- MySQL学习笔记（十四）自定义函数
- MYSQL学习笔记（二十四）安全管理
- MySQL学习笔记之二十四 设置用户并授权
- 学习笔记（十四）
- MYSQL学习笔记（十四）使用全文本搜索
- MySQL学习笔记十四：优化(1)
- PYTHON框架之DJANGO学习笔记（十四）
- PHP学习笔记十四之异常（进阶篇）
- Android笔记（二十四）广播机制
- java学习笔记（十四）
- Linux学习笔记（十四）
- 学习笔记（二十四）
- Java学习笔记（十四）
- Java学习笔记（十四）
- ArcGIS学习笔记（十四）
- Java学习笔记（十四）
- python 学习笔记（十四）
- 艱難完成 nginx + puma 部署 rails 4的詳細記錄
- 欢迎使用CSDN-markdown编辑器
- ruby線程實現生產者消費者問題示例(隊列Queue實現線程同步)
- POJ 1177 Picture [离散化+扫描线+线段树]
- rudy 繼承 概念
- mysql学习笔记之十四（安全性机制）
- the environment variable java_home (with the value of ) does not point to a valid jvm installation
- Rails命令行常用操作命令簡明總結
- #301 (div.2) A. Combination Lock
- ruby 過程對象 解析
- NIN-Network In Network阅读笔记
- Ruby簡明教程之數組和Hash介紹
- win7中用virtualbox不能安装虚拟机的问题
- 黑马程序员——Static关键词的用法与单例设计模式
