34_03MySQL系列之十一－MySQL用户和权限管理

2015-07-06

用户和权限管理

Information about account privileges is stored in the user,db,host,tables_priv,columns_priv and procs_priv tables in the mysql database,The MySQL server reads the contents of these tables into memory when it starts and reloads them under the circumstances,Access-control decision are based on the in-memory copies of the grant tables;

user : Contains user accounts.global privileges ,and other non-privilege columns;
用户帐号、全局权限
db : Contains database-level privileges;

列库级别权限
host : Obsolete
废弃
tables_priv : Contains table-level privileges;
表级别权限
columns_priv : Contains column-level privileges;
列级别权限
procs_priv : Contains stored procedure and function privileges;
存储过程和存储函数相关的权限
proxies_priv : Contains proxy-user privileges
代理用户权限

There are several distinctions between the way user names and passwords are userd by MySQL and the way they are used by your oprating system;

   User names, as used by MySQL for authentication purposes, have nothing to do with user names (login names) as userd by windows or Unix.
   MySQL user names can be up to 16 characters long.
   The server uses MySQL passwords stored in the user table to authenticate client connections using MySQL native authentication (against passwords stored in the mysql user table).
   MySQL encrypts passwords stored in the user table using its own algorithm. This encryption is the same as that implemented by the PASSWORD() SQL function but differs , It is possible to connect to the server regardless of character set settings if the user name and password contain only ASCII characters;


用户帐号:
   用户名@主机
       用户名:16字符以内
       主机:
           主机名:www.test.com,mysql
           IP: 192.168.0.109
           网络地址: 172.16.0.0/255.255.0.0

       通配符: %, _

启动MySQL服务器时:
–skip-name-resolve:略过名称解析,会提高用户连接速度

权限级别:
   全局级别: CREATE TABLESPACE,CREATE USER, PROCESS,PROXY,BELOAD,REPLICATION CLIENT,REPLICATION SLAVE,SHOW DATABASES,SHUTDOWN,SUPER,ALL[PRIVILEGES],USAGE
   库
   表
   存储过程和存储函数



临时表:内存表
   heap:16MB

触发器:主动数据库
INSERT，DELETE，UPDATE

!!!!!!!!!!!!!更多自行help!!!!!!!
创建用户:
1、mysql>CREATE USER ‘jeffrey‘@’localhost’ [IDENTIFIED BY ‘mypass’];现在会默认加密
2、mysql>GRANT
3、mysql>INSERT INTO mysql.user
   这种方式需要FLUSH PRIVILEGES;

设置密码:
SET PASSWORD FOR ‘bob‘@’%.example.org’ = PASSWORD(‘cleartext password’);

查看授权:
mysql>SHOW GRANTS FOR ‘jeffrey‘@’localhost’;
| GRANT USAGE ON . TO ‘dragon‘@’localhost’ IDENTIFIED BY PASSWORD ‘*08E03012EB25C19BCBB8BF6E12EB104AED8DE2C8’ |(USAGE默认没权限)

进行授权:
object_type
   TABLE
   FUNCTION
   PROCEDURE

priv_level:
   *
   .
   db_name.*
   db_name.tb1.name
   tb1_name
   db_name.routine_name

ssl_option:
   SSL
   X509
   CIPHER ‘cipher’
   ISSUER ‘issuer’
   SUBJECT ‘subject’

with_option:
   MAX_QUERIES_PER_HOUR count 每小时最大查询请求个数
   MAX_UPDATES_PER_HOUR count 每小时最大使用更新次数
   MAX_CONNECTIONS_PER_HOUR count 每小时最大连接次数
   MAX_USER_CONNECTIONS count 最大并发连接次数(同一个账号最大连接次数)
   当不想进行限定时，只要将值改为0即可；

mysql>GRANT ALL PRIVILEGES  ON *.* TO 'username'@'host' IDENTIFIED BY 'mypass'; 
     
mysql>GRANT CREATE ON cactidb.* TO 'jeffrey'@'%'; 
 
mysql>GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; 
 
mysql>GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'; 
 
mysql>GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;     
     
mysql>GRANT CREATE INSERT UPDATE ON *.* TO 'username'@'localhost'; 
 
mysql>GRANT UPDATE(Age) ON TEST.TEST TO 'jeffrey'@'localhost'; 
     
mysql>GRANT SUPER ON *.* TO 'username'@'localhost';这个权限类似root 
```   
 
删除用户:     

```sql
DROP USER 'username'@'host'

重命名:

1	RENAME USER old_name TO new_name

取消授权:

1 2	REVOKE INSERT ON . FROM 'jeffrey'@'localhost'; REVOKE SELECT ON 'cactidb'.* FROM 'cactiuder'@'%';

修改密码:

通过更新授权表的方式直接修改其密码，而后移除此两个选项重启服务器；

1
2
3

#service mysqld stop 
 
#vim /etc/init.d/mysqld

找到start 那项:
添加成如下:
$bindir/mysqld_safe –skip-grant-tables –skip-networking (添加这两个) –datadir….

–skip-grant-tables : 跳过授权表
–skip-networking : 跳过网络(因为如果此时有人通过网络连接你的数据库的话，那就比较悲剧了，只能本地登陆，所以一定要添加这一项)

1	mysql>update user SET PASSWORD=PASSWORD('mypass') WHERE User='root' ;

由于跳过了授权表，所以使用set就不会起作用，只有使用update;

然后重新修改/etc/init.d/mysqld,重启即可；

;