Mysql学习积累之二[网摘收藏 个人学习参考]

  1. 云栖社区>
  2. 博客>
  3. 正文

Mysql学习积累之二[网摘收藏 个人学习参考]

ghost丶桃子 2016-05-20 16:04:41 浏览1106
展开阅读全文

继续前一篇,这里是一些常用的管理命令,也为转载,所有权归原作者所有,此处仅作参考学习.

详见:http://www.centos.bz/2011/09/mysql-user-management-commands/

MySQL常用用户管理命令

1、添加用户

本机访问权限:

  1. mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost'
  2. -> IDENTIFIED BY 'password' WITH GRANT OPTION;

远程访问权限:

  1. mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%'
  2. -> IDENTIFIED BY 'password' WITH GRANT OPTION;

另外还有一种方法是直接Insert INTO user,注意这种方法之后需要 FLUSH PRIVILEGES 让服务器重读授权表。

  1. insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject)
  2. values(‘localhost’,'xff’,password(‘xff’),”,”,”);
  3. FLUSH PRIVILEGES;

note:1)必须要加上ssl_cipher,x509_issuer,x509_subject三列,以为其默认值不为空(数据库版本为:5.0.51b)
2)FLUSH PRIVILEGES重载授权表,使权限更改生效
3)mysql是通过User表,Db表,Host表,Tables_priv 表,Columns_priv 表这5张表实现用户权限控制,均可以通过直接对这些表的操作以达到对用户的管理

2、删除用户

  1. drop user admin@localhost;(@不加默认为“%”)

3、权限回收

  1. revoke delete on test.* from admin@'localhost';

4、创建用户授权一起实现

  1. grant select,insert,update,delete on *.* to 'admin2′@'%'
  2. identified by ‘admin2′ with grant option;

note:在mysql中,如果@后面的登录范围不同,帐号可以一样

5、限制用户资源

  1. mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
  2. -> IDENTIFIED BY 'frank'
  3. -> WITH MAX_QUERIES_PER_HOUR 20
  4. -> MAX_UPDATES_PER_HOUR 10
  5. -> MAX_CONNECTIONS_PER_HOUR 5
  6. -> MAX_USER_CONNECTIONS 2;

6、用户密码设置

使用mysqladmin:

  1. shell> mysqladmin -u user_name -h host_name password "newpwd"

或在mysql里执行语句:

  1. mysql> SET PASSWORD FOR 'username'@'%'
  2. = PASSWORD('password');

如果只是更改自己的密码,则:

  1. mysql> SET PASSWORD = PASSWORD(‘password’);

在全局级别使用GRANT USAGE语句(在*.*)来指定某个账户的密码:

  1. mysql> GRANT USAGE ON *.* TO 'username'@'%'
  2. IDENTIFIED BY 'password';

或直接修改MySQL库表:

  1. mysql> UPDATE user SET Password = PASSWORD('bagel')
  2. -> WHERE Host = '%' AND User = 'francis';
  3. mysql> FLUSH PRIVILEGES;

修改root密码:

  1. update mysql.user set password=password(‘passw0rd’) where user=’root’;
  2. FLUSH PRIVILEGES;

7、关于加密

  1. mysql> select PASSWORD('password');
  2. +-------------------------------------------+
  3. | PASSWORD('password')                      |
  4. +-------------------------------------------+
  5. | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
  6. +-------------------------------------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> select MD5('hello');
  10. +----------------------------------+
  11. | MD5('hello')                     |
  12. +----------------------------------+
  13. | 5d41402abc4b2a76b9719d911017c592 |
  14. +----------------------------------+
  15. 1 row in set (0.00 sec)
  16.  
  17. mysql> select SHA1('abc');
  18.  
  19. -> 'a9993e364706816aba3e25717850c26c9cd0d89d'

SHA1()是为字符串算出一个 SHA1 160比特检查和,如RFC 3174 (安全散列算法)中所述。

8、授权精确到列

  1. grant select (cur_url,pre_url) on test.abc to admin@localhost;

文章来源:http://www.ha97.com/4109.html

转载请注明出处:http://www.cnblogs.com/haochuang/

网友评论

登录后评论
0/500
评论