河北省建设信息中心网站,ui设计网课一般多少钱,wordpress更换图标,网站建设亿玛酷技术19.用户与权限管理
19.1 用户管理
MySQL用户可以分为普通用户和root用户。root用户是超级管理员#xff0c;拥有所有权限#xff0c;包括创建用户、删除用户和修改用户的密码等管理权限#xff1b;普通用户只拥有被授予的各种权限。
MySQL提供了许多语句用来管理用户账号…19.用户与权限管理
19.1 用户管理
MySQL用户可以分为普通用户和root用户。root用户是超级管理员拥有所有权限包括创建用户、删除用户和修改用户的密码等管理权限普通用户只拥有被授予的各种权限。
MySQL提供了许多语句用来管理用户账号这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。
MySQL数据库的安全性需要通过账户管理来保证。
19.1.1 创建用户
在MySQL数据库中官方推荐使用CREATE USER语句创建新用户。
使用CREATE USER语句来创建新用户时必须拥有CREATEUSER权限。每添加一个用户CREATE USER语句会在MySQL.user表中添加一条新记录但是新创建的账户没有任何权限。如果添加的账户已经存在CREATE USER语句就会返回一个错误。
CREATE USER 用户名 [ IDENTIFIED BY ‘密码 ] [ ,用户名 [ IDENTIFIED BY ‘密码]]; CREATE USER zhangsan IDENTIFIED BY 111111;
用户名参数表示新建用户的账户由用户(User)和主机名(Host)构成。[]表示可选也就是说可以指定用户登录时需要密码验证也可以不指定密码验证这样用户可以直接登录。不过不指定密码的方式不安全不推荐使用。如果指定密码值这里需要使用IDENTIFIED BY指定明文密码值。CREATE USER语句可以同时创建多个用户。
19.1.2 修改用户(修改用户名)
UPDATE mysql.user SET USER114 WHERE USERwang5; FLUSH PRIVILEGES;
19.1.3 删除用户
在MySQL数据库中可以使用DROP USER语句来删除普通用户也可以直接在mysql.user表中删除用户。
使用DROP方式删除(推荐)使用DROP USER语句来删除用户时必须用于DROP USER权限。DROP USER语句的基本语法形式如下:DROP USER user[,user]...;其中user参数是需要删除的用户由用户的用户名(User)和主机名(Host)组成DROP USER语句可以同时删除多个用户各用户之间用逗号隔开。
19.1.4 设置当前用户密码
适用于root用户修改自己的密码以及普通用户登录后修改自己的密码。
root用户拥有很高的权限因此必须保证root用户的密码安全。root用户可以通过多种方式来修改密码使用ALTER USER修改用户密码是MySQL官方推荐的方式。此外也可以通过SET语句修改密码由于MySQL8.0中已移除了PASSWORD函数因此不再使用UPDATE语句直接操作用户表修改密码。
使用ALTER USER命令来修改当前用户密码用户可以使用ALTER命令来修改自身密码如下语句代表修改当前登录用户的密码。基本语法如下: ALTER USER USER() IDENTIFIED BY new_password ;使用SET语句来修改当前用户密码使用root用户登录MySQL后可以使用SET语句来修改密码具体SQL语句如下:SET PASSWORDnew_password;。该语句会自动将密码加密后再赋给当前用户。
19.1.5 修改其它用户密码
root用户不仅可以修改自己的密码还可以修改其它普通用户的密码。root用户登录MySQL服务器后可以通过ALTER语句和SET语句来修改普通用户的密码。
使用ALTER语句来修改普通用户的密码可以使用ALTER USER语句来修改普通用户的密码。基本语法形式如下: ALTER USER user [ IDENTIFIED BY ‘新密码] [, user[ IDENTIFIED BY 新密码]] ...;。其中user参数表示新用户的账户由用户名和主机名构成IDENTIFIED BY关键字用来设置密码。使用SET命令来修改普通用户的密码使用root用户登录到MySQL服务器后可以使用SET语句来修改普通用户的密码。SET语句的代码如下SET PASSWORD FOR username hostname new_password;。其中,username参数是普通用户的用户名; hostname参数是普通用户的主机名; new_password是新密码。
19.2 权限管理
19.2.1 权限列表 19.2.2 授予权限
给用户授权的方式有2种分别是通过把角色赋予用户给用户授权和直接给用户投权。用户是数据库的使用者我们可以通过给用户授予访问数据库中资源的权限来控制使用者对数据库的访问消除安全隐患。
授权命令GRANT 权限1,权限2...权限n ON 数据库名称.表名称 TO 用户名用户地址 [ IDENTIFIED BY ‘密码口令 ];
该权限如果发现没有该用户则会直接新建一个用户。
19.2.3 查看权限
查看当前用户权限 SHOW GRANTS; 查看某用户的全局权限 SHOW GRANTS FOR user 主机地址;
19.2.4 收回权限
收回权限就是取消已经赋予用户的某些权限收回用户不必要的权限可以在一定程度上保证系统的安全性。 MySQL中使用REVOKE语句取消用户的某些权限。使用REVOKE收回权限之后用户账户的记录将从db、host、tables_priv和columns_priv表中删除但是用户账户记录仍然在user表中保存。
注意在将用户账户从user表删除之前应该收回相应用户的所有权限。 REVOKE 权限1权限2...权限n ON 数据库名称.表名称 FROM 用户名用户地址;
19.3 权限表
MySQL服务器通过权限表来控制用户对数据库的访问权限表存放在mysql数据库中。MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表。除此之外还有table_priv表、 column_priv表和proc_priv表等。在MySQL启动时服务器将这些数据库表中权限信息的内容读入内存。
19.4 角色管理
角色是在MySQL8.0中引入的新功能。在MySQL中角色是权限的集合可以为角色添加或移除权限。用户可以被赋予角色同时也被授予角色包含的权限。对角色进行操作需要较高的权限。并且像用户账户一样角色可以拥有授予和撤消的权限。
引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定可以确保数据的安全性这是至关重要的。
19.4.1 创建角色
在实际应用中为了安全性需要给用户授予权限。当用户数量较多时为了避免单独给每一个用户授予多个权限可以先将权限集合放入角色中。再赋予用户相应的角色。 创建角色使用CREATE ROLE语句语法如下: CREATE ROLE role_name [ host_name ] [ , role_name[ host_name] ]...
角色名称的命名规则和用户名类似。如果host_name省略默认为%role_name不可省略不可为空。
19.4.2 给角色赋予权限
创建角色之后默认这个角色是没有任何权限的我们需要给角色授权。给角色授权的语法结构是: GRANT privileges ON table_name TO role_name [ host_nane ] ;
上述语句中privileges代表权限的名称多个权限以逗号隔开。可使用SHOW PRIVILEGES查询权限名称。
19.4.3 查看角色的权限
赋予角色权限之后我们可以通过 SHOW GRANTS语句来查看权限是否创建成功。
19.4.4 回收角色的权限
角色授权后可以对角色的权限进行维护对权限进行添加或撤销。添加权限使用GRANT语句与角色授权相同。撤销角色或角色权限使用REVOKE语句。修改了角色的权限会影响拥有该角色的账户的权限。撤销角色权限的SQL语法如下: REVOKE privileges ON tablename FROM rolename;
19.4.5 删除角色
当需要对业务重新整合的时候可能就需要对之前创建的角色进行清理删除一些不会再使用的角色。语法格式如下 DROP ROLE role [,rolc2] ... 注意如果删除了角色那么用户也就失去了通过这个角色所获得的所有权限。
19.4.6 给用户赋予角色
角色创建并授权后要赋给用户并处于激活状态才能发挥作用。给用户添加角色可使用GRANT语句语法形式如下: GRANT role [ ,role2, ...] TO user [ , user2,... ] ;
注意MySQL中创建了角色之后默认都是没有被激活的必须手动激活后用户才能拥有角色对应的权限。
19.4.5 激活角色
使用set default role命令激活角色。用户需要退出重新登录才能看到赋予的角色。将active all roles on login设置为onSET GLOBAL activate_all_roles_ib_loginON;即对所有角色永久激活。
19.4.6 撤销用户的角色
REVOKE role FROM user;
19.4.7 设置强制角色(mandatory role)
给每个创建账户的默认角色不需要手动设置强制角色无法被REVOKE或DROP。
服务启动前设置mandatory_roles‘role1,role2’运行时设置 SET PERSIST mandatory_rolesrole1,role2; 系统重启后仍然有效。SET GLOBAL mandatory_rolesrole1,role2; 系统重启后失效。
20.逻辑架构
20.1 逻辑架构剖析
20.1.1 服务器处理客户端请求
首先 MySQL是典型的C/S架构即Client/Server架构服务器端程序使用的mysqld。 不论客户端进程和服务器进程是采用哪种方式进行通信最后实现的效果都是客户端进程向服务器进程发送一段文本(SQL语句)服务器进程处理后再向客户端进程发送一段文本(处理结果。
20.1.2 Connectors
Connectors指的是不同语言中与SQL的交互。MySQL首先是一个网络程序在TCP之上定义了自己的应用层协议。所以要使用MySQL我们可以编写代码跟MySQL Server建立TCP连接之后按照其定义好的协议进行交互。或者比较方便的办法是调用SDK比如Native C API、JDBC、PHP等各语言MySQL Connector或者通过ODBC。但通过SDK来访问MySQL本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互。
20.1.3 第一层连接层
系统(客户端)访问MySQL服务器前做的第一件事就是建立TCP连接。经过三次握手建立连接成功后MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。
用户名或密码不对会收到一个Access denied for user错误。客户端程序结束执行。用户名密码认证通过会从权限表查出账号拥有的权限与连接关联之后的权限判断逻辑都将依赖于此时读到的权限。 为了解决TCP无限创建与TCP频繁创建销毁带来的资源耗尽、性能下降问题。MySQL服务器里有专门的TCP连接池限制连接数采用长连接模式复用TCP连接。
20.1.4 第二层服务层
第二层架构主要完成大多数的核心眼务功能如SQL接口并完成缓存的查询SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现如过程、函数等。
在该层服务器会解析查询并创建相应的内部解析树并对其完成相应的优化∶如确定查询表的顺序是否利用索引等最后生成相应的执行操作。
如果是SELECT语句服务器还会查询内部的缓存。如果缓存空间足够大这样在解决大量读操作的环境中能够很好的提升系统的性能。
20.1.5 第三层引擎层
插件式存储引挛层Storage Engines)真正的负责了MySQL中数据的存储和提取对物理服务器级别维护的底层数据执行操作服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同这样我们可以根据自己的实际需要进行选取。
20.1.6 存储层
所有的数据数据库、表的定义表的每一行的内容索引都是存在文件系统上以文件的方式存在的。并完成与存储引擎的交互。
20.2 SQL执行流程 查询缓存Server如果在查询缓存中发现了这条SQL语句就会直接将结果返回给客户端;如果没有就进入到解析器阶段。需要说明的是因为查询缓存往往效率不高所以在MySQL8.0之后就抛弃了这个功能。解析器在解析器中对SQL语句进行语句分析、语义分析。优化器在优化其中会确定SQL语句的执行路径比如是根据全表检索还是根据索引检索等。经过了解析器MySQL就知道你要做什么了在开始执行之前还要先经过优化器的处理一条查询可以有很多种执行方式最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。执行器在执行之前需要判断该用户是否具备权限。如果没有就会返回权限错误。如果具备权限就执行SQL查询并返回结果。在MySQL8.0以下的版本如果设置了查询缓存这时会将查询结果进行缓存。
20.3 数据库缓冲池Buffer Pool
InnoDB存储引擎是以页为单位来管理存储空间的我们进行的增删改查操作其实本质上都是在访问页面包括读页面、写页面、创建新页面等操作)。而磁盘I/O需要消耗的时间很多而在内存中进行操作效率则会高很多为了能让数据表或者索引中的数据随时被我们所用DBMS 会申请占用内存来作为数据缓冲法在真正访问页面之前需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。
这样做的好处是可以让磁盘活动最小化从而减少与磁盘直接进行I/O的时间。要知道这种策略对提升SQL语句的查询性能来说至关重要。如果索引的数据在缓冲池里那么访问的成本就会降低很多。
20.3.1 缓冲池VS查询缓存
缓冲池和查询缓存不是一个东西。
缓冲池 InnoDB缓冲池中包括了数据页、索引页、插入缓存、锁信息、自适应Hash和数据字典信息等。查询缓存 查询缓存是提前把查询结果缓存起来这样下次不需要执行就可以直接拿到结果。需要说明的是在MySQL中的查询缓存不是缓存查询计划而是查询对应的结果。因为命中条件苛刻而且只要数据表发生变化直询缓存就会失效因此命中率低。’
缓冲池和查询缓存都服务于数据库整体的I/O操作它们的共同点都是通过缓存的机制来提升效率。
21.存储引擎
为了管理方便人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能把真实存取数据的功能划分为存储引擎的功能。所以在MySQL server完成了查询优化后只需按照生成的执行计划调用底层存储引擎提供的API获取到数据后返回给客户端即可。
MySQL中提到了存储引擎的慨念。简而言之存储引擎就是指表的类型。它的功能就是接收上层传下来的指令然后对表中的数据进行提取或写入操作。
21.1 查看存储引擎
show engines; 查询结果显示MySQL共存在9中存储引擎。即MEMORY、MRG_MYISAM、FEDERATED、InnoDB默认使用、CSV、BLACKHOLE、ARCHIVE、MyISAM、PERFORMANCE_SCHEMA。
21.2 设置系统默认的存储引擎
查看默认的存储引擎 show variables like %storage_engine%;或SELECT %default_storagr_engine;修改默认的存储引擎 如果在创建表的语句中没有显式指定表的存储引导的话那就会默认使用InnoDB作为表的存储引擎。如果我们想改变表的默认存储引擎的话可以这样写启动服务器的命令行: SET DEFAULT_STORAGE_ENGINEMyISAM;
21.3 设置表的存储引擎
存储引擎是负责对表中的数据进行提取和写入工作的我们可以为不同的表设置不同的存储引擎也就是说不同的表可以有不同的物理存储结构不同的提取和写入方式。
21.3.1 创建表时指定存储引擎
我们之前创建表的语句都没有指定表的存储引擎那就会使用默认的存储引擎InnoDB。如果我们想显式的指定一下表的存储引擎那可以这么写: CREATE TABLE 表名( 建表语句; ENGINE存储引擎名称;
21.3.2 修改表的存储引擎
如果表已经创建完成我们也可以使用该语句来修改表的存储引擎ALTER TABLE 表名 ENGINE存储引擎名称;
21.4 引擎介绍
21.4.1 InnoDB引擎具备外键支持功能的事务存储引擎
MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后默认采用InnoDB引擎。lnnoDB是MySQL的默认事务型引擎它被设计用来处理大量的短期事务。可以确保事务的完整提交(Commit)和回滚(Rolltack)。除了增加和查询外还需要更新、删除操作操作应优先选择InnoDB存储引擎。除非有非常特别的原因要使用其他的存储引擎否则应该优先考虑InndDB引擎。数据文件结构 表名.frm 存储表结构MySQL8.0时合并在表名.ibd中)表名.ibd 存储数据和索引 lnnoDB是为处理巨大数据量的最大性能设计。 在以前的版本中字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如: .frm,.par,.trn ,.isl,.db. opt等都在MySQL8.0中不存在了。 对比MyISAM的存储引擎InnoDB写的处理效率差些并且会占用更多的磁盘空间以保存数据和索引。MyISAM只缓存索引不缓存真实数据InnoDB不仅缓存索引还要缓存真实数据对内存要求较高而且内存大小对性能有决定性的影响。
21.4.2 MyISAM引擎主要的非事务处理存储引擎
MylSAM提供了大量的特性包括全文索引、压缩、空间函数(GIS)等但MyISAM不支持事务、行级锁、外键有一个毫无疑问的缺陷就是崩溃后无法安全恢复。5.5之前默认的存储引擎。优势是访问的速度快对事务完整性没有要求或者SELECT、INSERT为主的应用。针对数据统计有额外的常数存储故而count(*)的查询效率很高。数据文件结构 表名.frm 存储表结构表名.MYD 存储数据(MYData)表名.MYI 存储索引(MYIndex) 应用场景只读应用或者以读为主的业务。
21.4.3 Archive引擎用于数据存档
archive是归档的意思仅仅支持插入和查询两种功能〔行被插入后不能再修改。在MySQL5.5以后支持索引功能。拥有很好的压缩机制使用zlib压缩库在记录请求的时候实时的进行压缩经常被用来作为仓库使用。创建ARCHIVE表时存储引擎会创建名称以表名开头的文件。数据文件的扩展名为.ARZ。根据英文的测试结论来看同样数据量下Archive表比MyISAM表要小大约75%比支持事务处理的InnoDB表小大约83%。ARCHIVE存储引擎采用了行级锁。该ARCHIVE引擎支持AUTO_INCREMENT列属性。AUTO_INCREMENT列可以具有唯一索引或非唯一索引。尝试在任何其他列上创建索引会导致错误。Archive表适合日志和数据采集档案类应用适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度但是对查询的支持较差。
21.4.4 Blackhole引擎丢弃写操作读操作会返回空内容
Blackhole引擎没有实现任何存储机制它会丢弃所有插入的数据不做任何保存。服务器会记录Blackhole表的日志所以可以用于复制数据到备库或者简单地记录到日志。但这种应用方式会碰到很多问题因此并不推荐。
21.4.5 CSV引擎存储数据时以逗号分隔各个数据项
CSV引擎可以将普通的CSV文件作为MySQL的表来处理但不支持索引。CSV引擎可以作为一种数据交换的机制非常有用。CSV存储的数据直接可以在操作系统里用文本编辑器或者excel读取。对于数据的快速导入、导出是有明显优势的。
创建CSV表时服务器会创建一个纯文本数据文件其名称以表名开头并带有.CSV扩展名。当你将数据存储到表中时存储引擎将其以逗号分隔值格式保存到数据文件中。
21.4.6 Memory引擎置于内存的表
Memory采用的逻辑介质是内存响应速度很快但是当mysqld守护进程崩溃的时候数据会丢失。另外要求存储的数据是数据长度不变的格式比如Blob和Text类型的数据不可用(长度不固定的)。
Memory同时支持哈希HASH索引和B树索引。 哈希索引相等的比较快但是对于范围的比较慢很多。默认使用哈希HASH索引其速度要比使用B型树(BTREE索引快。如果希望使用B树索引可以在创建索引时选择使用。 Memory表至少比MyISAM表要快一个数量级。MEMORY表的大小是受到限制的。表的大小主要取决于两个参数分别是max_rows和max_heap_table_size。其中max_rows可以在创建表时指定max_heap_table_size的大小默认为16MB可以按需要进行扩大。数据文件与索引文件分开存储。 每个基于MEMORY存储引擎的表实际对应一个磁盘文件该文件的文件名与表名相同类型为frm类型该文件中只存储表的结构而其数据文件都是存储在内存中的。这样有利于数据的快速处理提供整个表的处理效率。 缺点其数据易丢失生命周期短。基于这个缺陷选择MEMORY存储引擎时需要特别小心。使用Memory存储引擎的场景 目标数据比较小而且非常频繁的进行访问在内存中存放数据如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小限制Memory表的最大的大小。如果数据是临时的而且必须立即可用得到那么就可以放在内存中。存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。
21.4.7 Federated引擎访问远程表
Federated引擎是访问其他MySQL服务器的一个代理尽管该引擎看起来提供了一种很好的跨服务器的灵活性但也经常带来问题因此默认是禁用的。
21.4.8 Merge引擎管理多个MyISAM表构成的表集合
21.4.9 NDB引擎MySQL集群专用存储引擎
也叫做NDB Cluster存储引擎主要用于MySQL cluster分布式集群环境类似于Oracle的RAC集群。
