MySQL常用用户管理命令

mysql_logo

1、添加用户
本机访问权限:

[php]mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;[/php]

远程访问权限:

[php]mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;[/php]

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

[php]insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject) values(‘localhost’,'xff’,password(‘xff’),”,”,”);
FLUSH PRIVILEGES;[/php]

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、删除用户

[php]drop user admin@localhost;(@不加默认为“%”)[/php]

3、权限回收

[php]revoke delete on test.* from admin@'localhost';[/php]

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

[php]grant select,insert,update,delete on *.* to 'admin2′@'%' identified by ‘admin2′ with grant option;[/php]

note:在mysql中,如果@后面的登录范围不同,帐号可以一样
5、限制用户资源

[php]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;[/php]

6、用户密码设置
使用mysqladmin:

[php]shell> mysqladmin -u user_name -h host_name password "newpwd"[/php]

或在mysql里执行语句:

[php]mysql> SET PASSWORD FOR 'username'@'%' = PASSWORD('password');[/php]

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

[php]mysql> SET PASSWORD = PASSWORD(‘password’);[php]

在全局级别使用GRANT USAGE语句(在*.*)来指定某个账户的密码:
[php]mysql> GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY 'password';[/php]

或直接修改MySQL库表:

[php]mysql> UPDATE user SET Password = PASSWORD('bagel') WHERE Host = '%' AND User = 'francis';
mysql> FLUSH PRIVILEGES;[/php]

修改root密码:

[php]update mysql.user set password=password(‘passw0rd’) where user=’root’;
FLUSH PRIVILEGES;[/php]

7、关于加密

[php]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'[/php]

SHA1()是为字符串算出一个 SHA1 160比特检查和,如RFC 3174 (安全散列算法)中所述。
8、授权精确到列
grant select (cur_url,pre_url) on test.abc to admin@localhost;

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

还没有评论,快来抢沙发!

发表评论

  • 😉
  • 😐
  • 😡
  • 😈
  • 🙂
  • 😯
  • 🙁
  • 🙄
  • 😛
  • 😳
  • 😮
  • emoji-mrgree
  • 😆
  • 💡
  • 😀
  • 👿
  • 😥
  • 😎
  • ➡
  • 😕
  • ❓
  • ❗
  • 67 queries in 0.409 seconds