MySQL 用户管理

2011-3-29 Jerry MySQL

添加用户

本机访问权限:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username
'@'localhost'

    ->     IDENTIFIED BY 'password' WITH GRANT OPTION;
远程访问权限:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%'

    ->     IDENTIFIED BY 'password' WITH GRANT OPTION;
另外还有一种方法是直接Insert INTO user,注意这种方法之后需要 FLUSH PRIVILEGES 让服务器重读授权表。
删除用户
DROP USER user;
限制用户资源
mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
    ->     IDENTIFIED BY 'frank'
    ->     WITH MAX_QUERIES_PER_HOUR 20 
    ->          MAX_UPDATES_PER_HOUR 10
    ->          MAX_CONNECTIONS_PER_HOUR 5 
    ->          MAX_USER_CONNECTIONS 2;
设置用户密码
命令行:
shell> mysqladmin -u user_name -h host_name password "newpwd"
或执行语句:
mysql> SET PASSWORD FOR 'username'@'%'
 = PASSWORD('password');
如果只是更改自己的密码,则直接:
mysql> SET PASSWORD = PASSWORD(‘password’);
在全局级别使用GRANT USAGE语句(在*.*)来指定某个账户的密码:
mysql> GRANT USAGE ON *.* TO 'username'@'%'
 IDENTIFIED BY 'password';
或直接修改MySQL库表:
mysql> UPDATE user SET Password = PASSWORD('bagel')
       -> WHERE Host = '%' AND User = 'francis';
mysql> FLUSH PRIVILEGES;

关于加密

mysql> select PASSWORD('password');
+-------------------------------------------+
| PASSWORD('password')                      |
+-------------------------------------------+
| *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select MD5('hello');
+----------------------------------+
| MD5('hello')                     |
+----------------------------------+
| 5d41402abc4b2a76b9719d911017c592 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select SHA1('abc');

-> 'a9993e364706816aba3e25717850c26c9cd0d89d'

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

标签: MySQL


发表评论:

Powered by emlog sitemap