MySQL用户管理与权限管理

1/11MySQL用户管理与权限管理MySQL的权限系统主要用来对连接到数据库的用户进行权限的验证，以此来判断此用户是否属于合法的用户，如果是合法用户则赋予相应的数据库权限。1权限表安装mysql时会自动安装一个名为mysql的数据库。Mysql数据库下面存储的都是权限表。用户登陆以后，mysql数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。查询可以用：showdatabases;语句查看；Mysql中的表:User：权限表包括字段用户列、权限列、安全列和资源控制列；User中用户列包括host(主机名)user（用户名）password（密码），他们决定了用户是否可以登录。User中的权限列包括了一些以例如select_priv等以priv结尾的字段，这些字段决定了用户所拥有的权限。User表中安全列有四个字段，主要用于加密。User表中资源控制列中字段规定了每小时最多可执行多少次查询、更新、建立连接等权限。Db:存储某个用户对一个数据库的权限用户列（hostdbhost）权限列Host：扩展dbTable_priv：对单个表进行权限设置Columns_priv：对单个数据进行权限设置Procs_priv：对存储过程和存储函数进行权限设置增加一个Creatroutinepriv字段和Alter_rountine_priv字段创建和修改存储过程2权限系统的工作原理MySQL权限系统通过下面两个阶段进行认证：2/11（1）对连接的用户进行身份认证，合法的用户通过认证，不合法的用户拒绝连接；（2）对通过认证的合法用户赋予相应的权限，用户可以在这些权限范围内对数据库做相应的操作。对于身份的认证，MySQL是通过IP地址和用户名联合进行确认的，例如MySQL安装后默认创建的用户root@localhost表示用户root只能从本地（localhost）进行连接才可以通过认证，此用户从其他任何主机对数据库进行的连接都将被拒绝。也就是说，同样的一个用户名，如果来自不同的IP地址，则MySQL将其视为不同的用户。MySQL的权限表在数据库启动的时候就载入内存，当用户通过身份认证后，就在内存中进行相应权限的存取，这样，此用户就可以在数据库中做权限范围内的各种操作了。3权限表的存取在权限存取的两个过程中，系统会用到“mysql”数据库中user、host和db这3个最重要的权限表，user中的列主要分为4个部分：用户列、权限列、安全列和资源控制列。其中，通常用得最多的是用户列和权限列，其中权限列有分为普通权限和管理权限。普通权限主要用于数据库的操作，而管理权限主要用来对数据库进行管理的操作。权限表的存取过程先从user表中的host、user和password这3个字段中判断连接的IP、用户名和密码是否存在于表中，如果存在，则通过身份验证，否则拒绝连接。如果通过身份验证，则按照以下权限表的顺序得到数据库权限：user-db-tables_priv-columns_priv。当用户表通过权限认证，进行权限分配时，将按照user-db-tables_priv-columns_priv的顺序进行权限分配，即先检查全局权限表user，如果user中对应权限为“Y”，则此用户对所有数据库的权限都为“Y”，将不再检查db、tables_priv和columns_priv；如果为“N”，则到db表中检查此用户对应的具体数据库，并得到db中为“Y”的权限；如果db中相应权限为“N”，则检查tables_priv中此数据库对3/11应的具体表，取得表中为“Y”的权限；如果tables_priv中相应权限为“N”，则检查columns_priv中此表对应的具体列，取得列中为“Y”的权限。4修改密码刚刚安装完的Mysql，只一有个root用户，密码为空，而且只能在本机登录！不安全，所以呢，我们要做的第一件事就是修改密码。i.使用mysqladmin。输入：mysqladmin-uroot–p111111password新密码注：111111为原mysql密码，后面的'passwordnewpasswd'是连接数据库成功后要执行的命令。如果p和111111之间存在空格，将会报111111不是执行命令的错误。执行这个命令后，需要输入root的原密码，这样root的密码将改为newpasswd。同样，把命令里的root改为你的用户名，你就可以改你自己的密码了。当然如果你的mysqladmin连接不上mysqlserver，或者你没有办法执行mysqladmin，那么这种方法就是无效的，而且mysqladmin无法把密码清空。ii.用setpassword命令iii.输入：mysqlinsertintomysql.user(Host,User,Password)VALUES(\'%\',\'system\',PASSWORD(\'manager\'));mysqlFLUSHPRIVILEGES//刷新确切地说这是在增加一个用户，用户名为system，密码为manager。注意要使用PASSWORD函数，然后还要使用FLUSHPRIVILEGES来执行确认。而4/11%指这个system用户可以在任意主机上登陆。flushprivileges的意思是强制刷新内存授权表，否则用的还是缓冲中的口令，这时非法用户还可以用root用户及空口令登陆，直到重启MySQL服务器。iv.使用grant...identifiedby语句，来进行授权。mysqlgrantusageon*.*tosystem@\%\identifiedby\'manager\';这里password()函数是不必要的，也不需要使用flushprivileges;来执行确认。password()函数作用是为口令字加密，在程序中MySql自动解释。flushprivileges的意思是强制刷新内存授权表，否则用的还是缓冲中的口令，这时非法用户还可以用root用户及空口令登陆，直到重启MySQL服务器。注：上面的方法中除第一种方法外其它都在mysql提示符下使用，且必须有mysql的root权限：mysql的信息数据存在mysql数据库中。因此，可以直接对此库进行操作。5创建用户a)获取有关权限管理的帮助root@localhost[(none)]helpAccountManagementCREATEUSER：用于创建一个新用户；DROPUSER：用于删除用户；GRANT：grant命令进行进一步授权，使用grant命令也可以直接来创建账户。5/11RENAMEUSER：重命名操作REVOKE：取消或撤销SETPASSWORD：输入密码或确定密码b)查看帮助createuser命令会创建一个新帐户，同时也可以为其指定密码。该命令将添加一条记录到user表。该命令仅仅授予usage权限。需要再使用grant命令进行进一步授权。也可以使用grant命令直接来创建账户。c)查看当前演示环境mysqlshowvariableslike'version';d)创建新用户创建新用户stu1@localhost，并赋予所有数据库上的所有表的select权限。mysqlgrantselecton*.*tostu1@localhost;创建一个只允许从本地登录的超级用户jack，并允许将权限赋予别的用户，密码为：jack.Mysqlgrantallprivilegeson*.*tojack@‘localhost’identifiedby”jack”withgrantoption;6/11GRANT命令说明：ALLPRIVILEGES是表示所有权限，你也可以使用select、update等权限。ON用来指定权限针对哪些库和表。*.*中前面的*号用来指定数据库名，后面的*号用来指定表名。TO表示将权限赋予某个用户。jack@'localhost'表示jack用户，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。注意：这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。IDENTIFIEDBY指定用户的登录密码。WITHGRANTOPTION这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITHGRANTOPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。6查看已添加的用户mysqlselecthost,user,passwordfromuser;注意：stu1密码为空，不安全；7/111)查看刚刚添加的账户mysqlselecthost,user,passwordfrommysql.userwhereuserlike'jack%';7使用grant授予权限Mysqlhelpgrant注：以上只是部分截图；如何授权a、需要指定授予哪些权限b、权限应用在那些对象上(全局，特定对象等)c、授予给哪个帐户d、可以指定密码(可选项,用此方式会自动创建用户)授权权限的范围：ON*.*ONdb_name.*ONdb_name.table_nameONdb_name.table_name.column_nameONdb_name.routine_name查看权限一览表，我们直接查询root账户所有的权限，如下：8/11mysql的权限相对于oracle而言，相对简单，而且也没有涉及到角色方面的定义与配置mysqlselect*frommysql.userwhereuser='root'andhost='localhost';8查看当前的连接用户mysqlselectcurrent_user();9刷新权限使用这个命令使权限生效，尤其是你对那些权限表user、db、host等做了update或者delete更新的时候。以前遇到过使用grant后权限没有更新的情况，9/11只要对权限做了更改就使用FLUSHPRIVILEGES命令来刷新权限。10查看用户权限mysqlshowgrants;下面使用root账户给fred赋予权限allprivilegesmysqlgrantallprivilegeson*.*to'stu1'@'localhost';查看stu1的权限11基于对象列级别的授权语句首先revoke之前的select权限mysqlrevokeselectonmysql.*from'stu1'@'%';mysqlcreatetabletb_userasselect*frommysql.user;mysqlgrantselect(user,host),update(host)onmysql.tb_userto'jack'@'%';下面使用root来授权mysqlgrantselect(user,host),update(host)onmysql.tb_userto'stu1'@'%';查看db的授权表注意：如果你的对象创建在test相关数据库下，权限限制可能会失效。10/11下面这个查询用于查看db的授权表mysqlselecthost,db,userfrommysql.db;12撤销权限撤销权限使用的是revoke关键字，撤销与授权的权限方式基本类似，其次有哪些权限可以授予，相应地就有哪些权限可以撤销，原来的to子句呢则变成了from子句。如下面的示例mysqlrevokedeleteon*.*from‘jack’@’localhost’;mysqlrevokeSELECT(user,host),UPDATE(host)ON`mysql`.`tb_user`from'jack'@'%';mysqlrevokeallprivileges,grantoptionfrom'jack'@'%';如果root用户密码丢失则使用Skip-grant-tables使服务器