mysql用户和权限管理

yjhfgdfd

mysql用户和帐号，实现用户认证
1）用户公开，任何人都能看到；密码使用mysql中password函数生成2）帐号密码不能用登录linux系统
3）用户名@主机

---

用户和权限表
mysql启动后，此处的表从磁盘加载到内存，可提高mysql的性能
1）user: Contains user accounts, global privileges, and other non-privilege columns.
   user: 用户帐号、全局权限
用户帐号 用户名@主机
    用户名：16位以内

    主机: 1)主机名：www.hiyang.com

          2)IP或网络地址，IP/255.255.255.0

          3)通配符，192.168.%.%，%.hiyang.com

登录mysql时不反解析主机名，可以加快登录速度，登录时使用--skip-name-resolve选项


2）db: Contains database-level privileges.
   db: 库级别权限

3）host: Obsolete.
   host: 废弃，整合进入user

4）tables_priv: Contains table-level privileges.
   tables_priv：表级别权限

5）columns_priv: Contains column-level privileges.
columns_priv:列级别权限

6）procs_priv: Contains stored procedure and function privileges.
procs_priv: 存储过程和存储函数相关的权限

7）proxies_priv: Contains proxy-user privileges.
proxies_priv:代理用户权限



创建临时表，位于内存中，空间大小有限（heap：16M），不允许随意创建
CREATE TEMPORARY TABLES



触发器：主动数据库，和记录日志相关user: log

---

[size=1.143]创建用户
方式1，自动读入内存，不用flush
create user username@‘hostname’ [identified by ‘password’]

mysql> create user test@"192.168.8.0/24" identified by 'test';
mysql> show grants for test@"192.168.8.0/24";
+------------------------------------------------------------------------------------------------------------------+
| Grants for test@192.168.8.0/24                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'192.168.8.0/24' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+------------------------------------------------------------------------------------------------------------------+


方式2
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ...[REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}] [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user_specification TO user_specification [, user_specification] ...[WITH GRANT OPTION]

object_type: {
    TABLE | FUNCTION | PROCEDURE }



priv_level: {
    * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name
}


user_specification:
    user [ auth_option ]

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

tsl_option: {
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'
}

resource_option: { 资源使用限定
  | MAX_QUERIES_PER_HOUR count；限定每小时登录的次数
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}


方式3
insert into mysql.user

查看某个用户的权限
show grants for user@hostname

添加授权后，部分授权生效需要，如insert
1）flush privileges
2）用户会话重新连接

授权指定字段
mysql> grant update(name) on tdb.testdb to test1@"192.168.8.%";


super权限 *.*
mysql> grant super on *.* to test1@"192.168.8.%";

---

删除用户
drop user user@hostname
重命名
rename user old_user@hostname to new_user@hostname

---

取消授权revoke，不用flush
mysql> revoke select on tdb.* from test1@'192.168.8.%';