学习笔记-mysql_用户管理

权限表-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',

 `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

 `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

 `Create_tablespace_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',

 `plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password',

 `authentication_string` text COLLATE utf8_bin,

 `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

 PRIMARY KEY (`Host`,`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

1.用户列 包括host user password 分别表示主机名，用户名和密码

 其中host和user为user表的联合组件 且这三列值同时匹配 则可以登陆

2.权限列 权限列决定了用户的权限 包含普通权限及高级权限  普通权限用于操作数据库 超级权限用户数据库管理

 权限列字段类型为 enum  默认为N 可以使用grant语句或者update语句修改对应的权限

3.安全列  安全列有6个字段 其中2个是ssl相关的  2个是x509相关的 另外2个是授权插件相关的

 ssl用于加密  x509标准可用于标识用户  plugin字段标识可以用于验证用户身份的插件(如果为空 则服务器使用内建授权验证用户身份)

 可以通过show variables like 'have_openssl'  语句查询服务器是否支持ssl 功能

 

4.资源控制列

 max_questions  用户每小时允许执行的查询操作次数

 max_updates 用户每小时允许执行的更新操作次数

 max_connections 用户每小时允许执行的连接操作次数

 max_user_connections 用户允许同时建立的连接次数

 如果说超出限制 用户将被锁定  只有等待下一个小时 才能执行对应操作  可使用grant语句更新这些字段

 

权限表-db表和host表

db表存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取那个数据库

Create Table: CREATE TABLE `db` (

 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

 `User` char(16) COLLATE utf8_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',

 `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',

 `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',

 `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',

 `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

 `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

 `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

 PRIMARY KEY (`Host`,`Db`,`User`),

 KEY `User` (`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'

1.用户列

host user db 从某个主机连接某个用户对某个数据库的操作权限 

这3个字段组合狗吃db表的主键

2.权限列

和user表一样 不过唯一不同的是 user表示针对用户和主机是否对表有权限或者其他  而db表针对的是库

权限表-tables_priv表 和 columns_priv表

tables_priv 表用来对表设置权限

columns_priv 表用来对表的某一列设置权限

CREATE TABLE `tables_priv` (

 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

 `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

 `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',

 `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',

 PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),

 KEY `Grantor` (`Grantor`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges';

1.host db user table_name 分表表示主机名，数据库名，用户名和表名

    2.grantor 表示修改该记录的用户

3.timestamp 修改该记录的时间

4.Table_priv 表示对表的操作权限

5.Column_priv 表示对列的操作权限

CREATE TABLE `columns_priv` (

 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

 `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

 `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',

 PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges'

雷同tables_priv表 Column_name表示具体的列

权限表-procs_priv表

对存储过程和存储函数设置操作权限

CREATE TABLE `procs_priv` (

 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

 `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Routine_name` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

 `Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL,

 `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',

 `Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '',

 `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

 PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),

 KEY `Grantor` (`Grantor`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges'

1.host db user 分表表示主机名，数据库名，用户名

2.routine_name 表示存储过程或者函数的名称

3.routine_type 表示是存储过程还是函数 ('FUNCTION','PROCEDURE')

4.grantor 表示修改该记录的用户

5.proc_priv 表示拥有的权限 ('Execute','Alter Routine','Grant')

6.timestamp 修改该记录的时间

账户管理-登陆和退出

mysql命令常用参数:

1. -h 主机名

    2. -u 用户名

3. -p 密码

4. -P 端口

5. 数据库名 可以在命令的最后指定

6. -e 执行sql语句 并退出

账户管理-新建普通用户

1.使用create语句

create user user_specification[,user_specification]...

user_specification： 

user@host

[

identified by [PASSWORD] 'password' |

identified with auth_plugin [as 'auth_string']

]

2.使用grant语句

grant privileges on db.db.table to user@host [identified by [PASSWORD] 'password'] [,user@host [identified by [PASSWORD] 'password']]

[with grant option];

privileges 表示赋予用户的权限

    3.直接操作mysql.user表

使用操作数据表需要 使用 flush privileges; 刷新权限

账户管理-删除普通用户

1.使用drop命令

drop user user@host[,user@host];

2.使用对mysql.user表删除用户

账户管理-密码修改

1.通过mysqladmin修改

mysqladmin -u username -h localhost -p password "newpwd"

2.直接操作mysql.user表

使用操作数据表需要 使用  flush prvileges; 刷新权限

3,对当前用户使用set命令修改

set password=password('newpwd');

账户管理-有超级权限的用户修改普通用户的密码

1.使用set语句修改

set password for 'user'@'host'=password('newpwd');

2.直接操作mysql.user表

使用操作数据表需要 使用  flush prvileges; 刷新权限

3.使用grant语句

grant usage on *.* to 'user'@'host' identified by password "newpwd";

账户管理-密码丢失

1.在启动mysqld 的时候 加入 --skip-grant-tables 参数及可以跳过用户认证 然后使用用户更改密码即可

权限管理-mysql的各种权限

mysql 的所有权限 可以通过 show privileges;查看

 1.create和drop权限 用于数据库 及表 可执行创建删除(移掉)操作

 2.select，inster，update，delete 允许在一个数据库上 现有的表上操作

 3.index权限允许创建或删除索引

 4.alter 更改表结构或者重命名

 5.create routine  创建函数和存储过程

 6.alter routine 更改和删除存储过程和函数

 7.execute 允许执行函数和存储过程

 8.grant 权限允许授权用户

 9.file权限 给予用户使用 load data infile 和select ... into outfile 语句读或者写服务器上的文件

select value into outfile "路径" from 表;

load data infile "路径" into table 表 fields terminated by ',' optionally enclosed by '"' lines terminated by '\n'

FIELDS TERMINATED BY ',' 字段间分割符

OPTIONALLY ENCLOSED BY '"' 将字段包围 对数值型无效

LINES TERMINATED BY '\n' 换行符

其他权限用于管理性：

 1.reload  允许执行的命令 flush-hosts，flush-logs，flush-privileges。flush—status，flush-tables，flush-threads，refresh，reload

用于将授权表重新读入内存其实 refresh 情况所以表并关闭/打开记录文件

 2.shutdown  允许可以使用shutdown命令关闭服务器

 3.processlist  允许可以使用processlist显示服务器执行的线程信息

 4.kill  允许可以使用kill杀死服务器线程

 

权限管理-授权

1.全局层级

grant all on *.* 和revoke all on *.* 用于全局授予和撤销

2.数据库层级

grant all on db_name.* 和revoke all on db_name.* 用于数据库授予和撤销

3.表层级

grant all on db_name.tbl_name 和revoke all on db_name.tbl_name 用于对某数据库某张表授予和撤销

4.列层级

grant select(columns) on db_name.tbl_name 和 revoke select(columns) on db_name.tbl_name 以查询为例子

在grant 都需要 to 'user'@'host' 指定

在revoke 都需要 from 'user'@'host' 指定

权限管理-查看权限

show grant for 'user'@'host';

权限管理-访问控制

1.连接核实阶段(同时匹配user表中 host user password 则该阶段完成)

2.请求核实阶段(登陆后通过检查用户执行的操作 是否有权限去执行它 才能受理请求)

user表权限->db权限表->tables_priv->columns_priv (匹配就受理并执行 优先级)