文档介绍：该【mysql编写用户等级表MySQL的用户管理】是由【鼠标】上传分享，文档一共【11】页，该文档可以免费在线阅读，需要了解更多关于【mysql编写用户等级表MySQL的用户管理】的内容，可以使用淘豆网的站内搜索功能，选择自己适合的文档，以下文字是截取该文章内的部分文字，如需要获得完整电子版，请下载此文档到您的设备，方便您编辑和打印。:..mysql编写?户等级表_MySQL的?户管理MySQL在使??户时?较独特,它的?户账号由两部分组成,即?户名和主机(******@hostname),这个username我们可以?定义,?hostname可以是?个?段,也可以是?个主机名,hostname?持通配符。2>.?户账号管理的常?命令createuser#创建?户dropuser#删除?户renameuser#修改?户账号名称setpassword#设置?户密码3>.权限管理的命令grant#授权revoke#取消权限4>.查看?户能够使?的权限:mysql>showgrantsforusername@'hostname'以下是MySQL实例:mysql>createuserchensiting@'%'identifiedby'123';#注意,这?使?的是允许远程访问的IP可以访问QueryOK,0rowsaffected()mysql>flushprivileges;#通知MySQL服务器重读授权表QueryOK,0rowsaffected()#进?以上操作后,我们新建?个终端进?以下验证:[******@10e0e0e12~]#mysql-uchensiting--p123;mandsendwith;or\:(GPL)Copyright(c)2000,2018,Oracle,'help;'or'\h''\c'[(none)]>MySQL[(none)]>MySQL[(none)]>showdatabases;+--------------------+|Database|+--------------------+:..|information_schema|+--------------------+1rowinset()MySQL[(none)]>createdatabasetestdb;#很明显,我们?户并没有权限创建数据库,只有?些查看的权限ERROR1044(42000):Accessdeniedforuser'chensiting'@'%'todatabase'testdb'MySQL?户的创建案例(createuser)展?mysql>;createuserchensiting@'%'identifiedby'123';QueryOK,0rowsaffected()mysql>flushprivileges;QueryOK,0rowsaffected()mysql>renameuserchensiting@'%'to'yhh'@'%';#由于MySQL?户是有?户和主机名组成,因此我们在修改MySQL?户的时候,既可以修改?户的字符也可以修改mysql的字符,使?我们修改的?户即可正常登录QueryOK,0rowsaffected()[******@10e0e0e12~]#mysql-uyhh--p123;#我们会发现当你再次?你之前创建的?户登录数据库是会报错,但是更换新的修改后的?户,即可成功登录mandsendwith;or\:(GPL)Copyright(c)2000,2018,Oracle,'help;'or'\h''\c'[(none)]>MySQL的改名(renameuser)案例展?mysql>renameuserchensiting@'%'to'yhh'@'%';#改名操作QueryOK,0rowsaffected()mysql>setpasswordfor'yhh'@'%'=password('666');#对改名后的MySQL?户重新设置登录密码QueryOK,0rowsaffected()[******@10e0e0e12~]#mysql-uyhh--p123;#发现?之前的数据库密码?法登录ERROR1045(28000):Accessdeniedforuser'yhh'@'cobbler'(usingpassword:YES)[******@10e0e0e12~]#mysql-uyhh--p666;#但是我们?修改后的密码就可以完成登录操作mandsendwith;or\:(GPL)Copyright(c)2000,2018,Oracle,'help;'or'\h''\c'toclearthecurrentinputstatement.:..MySQL[(none)]>MySQL?户重置密码(setpassword)案例展??.Mysql的权限类型1>.管理类权限createtemporarytables#临时表createuser #创建?户file#允许?户读或者写某些?件locktables #添加显式锁process:#查看?户的线程reload: #相当于执?flush和resetreplicationclient #查询有哪些复制客户端replicationslave #赋予?户复制权限showdatabases #查看数据库权限shutdown #关闭MySQL服务super #杂项管理类命令2>.数据库访问权限(库级别)alter #修改表的权限alterroutine #修改存储历程create #可以穿件表和库的createroutine #创建存储过程,存储函数createview #创建视图delete #删除表中的?drop #删除数据库或者表的execute #是否能执?存储过程或存储函数的grantoption #将??的权限复制给别的?户index #创建或删除索引showview #查看?个视图是如何被创建的3>.数据操作类权限(表级别)select#在表中执?查询操作insert #在表中插?数据update #修改表中的数据delete #删除表中的数据4>.字段级别select(col1,....)#查询update(col1,....)#修改字符:..insert(col1,....)#插?字符5>.所有权限ALL[PRIVILEGES] #?(grant)帮助信息mysql>helpgrantName:'GRANT'Description:Syntax:GRANTpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_level#如果[object_type]不指定,默认类型为表;TOuser[auth_option][,user[auth_option]]...#指定授权?户,可以?次性指定多个,?逗号(,)分隔;[REQUIRE{NONE|tls_option[[AND]tls_option]...}]#要求基于ssl_option进?连接,需要在前?加REQUIRE关键字,如果不加默认不启?该功能;[WITH{GRANTOPTION|resource_option}...]GRANTPROXYONuserTOuser[,user]...[WITHGRANTOPTION]object_type:{(指定授权类型,如果不指定,默认为表)TABLE #表|FUNCTION #函数|PROCEDURE #过程}priv_level:{(指定授权对象)*#指定所有数据对象|*.*#指定所有库的所有表|db_name.*#指定库的所有表|#指定库的某个表|tbl_name#指定表名|#指定库的存储历程}user: #可以直接跟?户,不需要指定密码,但是前提是你之前已经创建过该?户并配置的有密auth_option:{#如果之前创建过?户,则这?是为该?户修改密码IDENTIFIEDBY[PASSWORD]'auth_string'|IDENTIFIEDWITHauth_plugin|IDENTIFIEDWITHauth_pluginAS'auth_string'}tls_option:{SSL#基于SSL协议,该选项?的较频繁|X509#基于X509的证书格式|CIPHER'cipher'#指定使?的加密机制|ISSUER'issuer'#指定颁发的证书|SUBJECT'subject'#指定证书的承受着}resource_option:{ #将??的权限转赠给别?,不建议使?,这种操作很危险;|MAX_QUERIES_PER_HOURcount#每?时允许执?的最?查询次数|MAX_UPDATES_PER_HOURcount#每?时允许执?的最?更新次数|MAX_CONNECTIONS_PER_HOURcount#每?时允许执?的最?连接次数|MAX_USER_CONNECTIONScount#使?同?个账号可以同时连接的次数:..},youmusthavetheGRANTOPTIONprivilege,andyoumusthavetheprivilegesthatyouaregranting.(Alternatively,ifyouhavetheUPDATEprivilegeforthegranttablesinthemysqlsystemdatabase,ountanyprivilege.)Whentheread_onlysystemvariableisenabled,[HELPREVOKE].example:GRANTALLONdb1.*TO'jeffrey'@'localhost';ount,ifomitted,defaultsto'%'.Normally,adatabaseadministratorfirstusesCREATEUSERtocreateanaccount,:CREATEUSER'jeffrey'@'localhost'IDENTIFIEDBY'password';GRANTALLONdb1.*TO'jeffrey'@'localhost';'jeffrey'@'localhost';GRANTUSAGEON*.*TO'jeffrey'@'localhost'WITHMAX_QUERIES_PER_HOUR90;*Note*:-,,GRANTrespondswithQueryOK,,[HELPSHOWGRANTS].以下是授权的实例仅供参考:mysql>@'%';#值运?MySQL?户'yhh'@'%'在chensiting这个库中创建名字为student的表名:..QueryOK,0rowsaffected()#进?以上操作后,我们新建?个终端进?以下验证:MySQL[(none)]>usechensitingpletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedMySQL[chensiting]>showgrantsfor'yhh'@'%';#查看授权信?+-----------------------------------------------------------------+|Grantsforyhh@%|+-----------------------------------------------------------------+|GRANTUSAGEON*.*TO'yhh'@'%'IDENTIFIEDBYPASSWORD||GRANTCREATEON`chensiting`.`students`TO'yhh'@'%'|+-----------------------------------------------------------------+2rowsinset()#这?的授权是只能创建?个表MySQL[chensiting]>createtablestudents(Namevarchar(30)notnull)engine=innodbdefaultcharset=utf8;#我们发现可以在chensiting库中创建tb1的表名ERROR1050(42S01):Table'students'alreadyexistsMySQL[chensiting]>createtabletb1(Namevarchar(30)notnull)engine=innodbdefaultcharset=utf8;#但是我们却没有权限创建其他名称的表名ERROR1142(42000):manddeniedtouser'yhh'@'cobbler'fortable'tb1'MySQL[chensiting]>授权只能创建指定数据库的指定表名称mysql>grantdroponchensiting.*to'yhh'@'%';QueryOK,0rowsaffected()#进?以上操作后,我们新建?个终端进?以下验证:******@10e0e0e12~]#mysql-uyhh--p666;mandsendwith;or\:(GPL)Copyright(c)2000,2018,Oracle,'help;'or'\h''\c'[(none)]>MySQL[chensiting]>showgrantsfor'yhh'@'%';+-----------------------------------------------------------------+|Grantsforyhh@%|+-----------------------------------------------------------------+|GRANTUSAGEON*.*TO'yhh'@'%'IDENTIFIEDBYPASSWORD|:..|GRANTDROPON`chensiting`.*TO'yhh'@'%'||GRANTCREATEON`chensiting`.`students`TO'yhh'@'%'|+-----------------------------------------------------------------+3rowsinset()MySQL[chensiting]>showtables;+----------------------+|Tables_in_chensiting|+----------------------+|Classes||score||students||tb1||tearchers|+----------------------+5rowsinset()MySQL[chensiting]>droptabletb2;ERROR1051(42S02):Unknowntable''MySQL[chensiting]>MySQL[chensiting]>droptablestudents;QueryOK,0rowsaffected()MySQL[chensiting]>droptablescore;#你会发现你是可以可劲的删除表QueryOK,0rowsaffected()MySQL[chensiting]>lasses;#但是仍然是没有select权限哟!因此不能查看表结构,想要获得此权限授权即可ERROR1142(42000):manddeniedtouser'yhh'@'cobbler'fortable'Classes'MySQL[chensiting]>授权MySQL?户拥有某个库的删除权限mysql>grantselect,insert,update,deleteonchensiting.*to'yhh'@'%';QueryOK,0rowsaffected()[******@10e0e0e12~]#mysql-uyhh--p123;mandsendwith;or\:(GPL)Copyright(c)2000,2018,Oracle,'help;'or'\h''\c'[(none)]>usechensitingpletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-A:..DatabasechangedMySQL[chensiting]>showtables;+----------------------+|Tables_in_chensiting|+----------------------+|Classes||score||students||tearchers|+----------------------+4rowsinset()MySQL[chensiting]>descstudents;+-----------+--------------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-----------+--------------------+------+-----+---------+----------------+|StudentID|mediumint(9)|NO|PRI|NULL|auto_increment||Name|varchar(50)|NO|PRI|NULL|||Age|tinyint(4)|NO|MUL|NULL|||Gender|enum('boy','girl')|YES||NULL|||ClassID|tinyint(4)|YES||NULL|||TeacherID|tinyint(4)|YES||NULL||+-----------+--------------------+------+-----+---------+----------------+6rowsinset()授权MySQL?户多个权限案例展??法帮助信息mysql>helprevokeName:'REVOKE'Description:Syntax:REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_levelFROMuser[,user]...#我们可以指定从某个?户收回个别的权限REVOKEALLPRIVILEGES,GRANTOPTION#?也可以?次性收回所有的权限FROMuser[,user]...REVOKEPROXYONuser:..FROMuser[,user]...以下收回权限的?个案例,仅供参考,通过以下案例,你也可以做其他操作:mysql>revokecreate,select,insert,update,deleteonchensiting.*from'yhh'@'%';QueryOK,0rowsaffected()mysql>FLUSHPRIVILEGES;#刷新授权信息,让在线的?户也能重读授权表,这样?户不需要退出当前客户端就能读取到最新的授权信息QueryOK,0rowsaffected()mysql>showgrantsfor'yhh'@'%';+----------------------------------------------------------------------------------------------------+|Grantsforyhh@%|+----------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'yhh'@'%'IDENTIFIEDBYPASSWORD'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'|+----------------------------------------------------------------------------------------------------+1rowinset()mysql>grantselect,insert,update,deleteonchensiting.*to'yhh'@'%';QueryOK,0rowsaffected()mysql>showgrantsfor'yhh'@'%';+----------------------------------------------------------------------------------------------------+|Grantsforyhh@%|+----------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'yhh'@'%'IDENTIFIEDBYPASSWORD'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'||GRANTSELECT,INSERT,UPDATE,DELETEON`chensiting`.*TO'yhh'@'%'|+----------------------------------------------------------------------------------------------------+2rowsinset()mysql>revokecreate,select,insert,update,deleteonchensiting.*from'yhh'@'%';QueryOK,0rowsaffected()mysql>FLUSHPRIVILEGES;QueryOK,0rowsaffected()3.?个跟?户授权相关的表:mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||chensiting||mysql|:..|performance_schema||yanhuihuang|+--------------------+5rowsinset()mysql>usemysqlpletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+---------------------------+|Tables_in_mysql|+---------------------------+|columns_priv|#列(字段)级别的权限|db|#库级别的权限|event||func||general_log||help_category||help_keyword||help_relation||help_topic||innodb_index_stats||innodb_table_stats||ndb_binlog_index||plugin||proc|#存储过程和存储函数相关的权限|procs_priv||proxies_priv||servers||slave_master_info||slave_relay_log_info||slave_worker_info||slow_log||tables_priv|#表级别权限|time_zone||time_zone_leap_second||time_zone_name||time_zone_transition||time_zone_transition_type|:..|user|+---------------------------+28rowsinset()补充:proxies_priv表是存储代理?>.?络内