1、添加用户
本机访问权限:
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
让服务器重读授权表。
insert into
user(host,user,password,ssl_cipher,x509_issuer,x509_subject)
values(‘localhost’,'xff’,password(‘xff’),”,”,”);
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张表实现用户权限控制,均可以通过直接对这些表的操作以达到对用户的管理
解决MySQL不允许从远程访问的方法:
1。 改表法。
可能是你的帐号不允许从远程登陆,只能在localhost。这个时候只要在localhost的那台电脑,登入mysql后,更改
"mysql" 数据库里的 "user" 表里的 "host" 项,从"localhost"改称"%"
mysql -u root -pvmwaremysql>use mysql;
mysql>update user set host = '%' where user =
'root';
mysql>select host, user from user;
2. 授权法。
例如,你想myuser使用mypassword从任何主机连接到mysql服务器的话。
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY
'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器,并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY
'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器的dk数据库,并使用mypassword作为密码
GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED
BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
我用的第一个方法,刚开始发现不行,在网上查了一下,少执行一个语句 mysql>FLUSH
RIVILEGES 使修改生效.就可以了
2、删除用户
drop user admin@localhost;(@不加默认为“%”)
3、权限回收
revoke delete on test.* from
admin@'localhost';
4、创建用户授权一起实现
grant select,insert,update,delete on *.*
to 'admin2′@'%' identified by ‘admin2′ with grant option;
note:在mysql中,如果@后面的登录范围不同,帐号可以一样
5、限制用户资源
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;
6、用户密码设置
使用mysqladmin:
shell> mysqladmin -u
user_name -h host_name password "newpwd"
或在mysql里执行语句:
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;
修改root密码:
update mysql.user set
password=password(‘passw0rd’) where user=’root’;
FLUSH PRIVILEGES;
7、关于加密
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 (安全散列算法)中所述。
8、授权精确到列
grant select (cur_url,pre_url) on test.abc
to admin@localhost;