修改mysql中普通用户的密码

为mysql建立普通用户的连接，在mysql数据库的mysql库中，在user表中建立了新用户后，一般是空密码，需要修改密码，最简单的方法是：
1.用root登录mysql:mysql -u root -p root的密码，进入mysql>状态；
2.mysql> UPDATE user SET password=PASSWORD('新密码') WHERE user='已创建的普通用户名';
3.mysql> grant select on 要授权使用的数据库名.* to '普通用户名'@'%';
4.mysql> flush privileges;
 
mysql用户管理
给mysql的管理员设了密码后，登陆和关闭
[root@test81 mysql]# bin/mysqladmin -uroot -p123 shutdown
mysql只有启动后，才可以登陆       
[root@test81 mysql]# bin/mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
启动后

匿名用户都可以登陆，执行相关操作。具有information_schema和test库的相关权限.

[root@test81 mysql]# bin/mysql -ux3
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.44-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

------------------
删除匿名用户的办法
    让匿名用户只能登陆information_schema库
mysql> drop user ''@'localhost';
mysql> drop user ''@'%';
   直接删除用户
mysql> delete from user where user='';
Query OK, 2 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@test81 mysql]# bin/mysql -ux3
ERROR 1045 (28000): Access denied for user 'x3'@'localhost' (using password: NO)
------------------------

以root用户登陆时，必须需要密码。
----------
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
通过grant命令创建用户。
mysql> grant select,insert,update,delete on test1.* to 'z2'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
直接操作权限表。因db表存在于mysql库中，所以要先选择库
mysql> insert into db (host,db,user,select_priv,insert_priv,update_priv,delete_priv) values('%','test1','z2','Y','Y','Y','Y');
ERROR 1046 (3D000): No database selected
mysql> use mysql
Database changed
mysql> insert into db (host,db,user,select_priv,insert_priv,update_priv,delete_priv) values('%','test1','z2','Y','Y','Y','Y');
Query OK, 1 row affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
记得执行以上的命令，否则不会立即生效。
-----------查看或更改账户权限
show grants for user@host;
mysql> show grants for z2@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for z2@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'z2'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'z2'@'%'                                      |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test1`.* TO 'z2'@'%'                                     |
+---------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

----看如下的命令所返回的值，直接user，user后默认是@‘%’，只有这种情况是可以的。相对于z5用户就会报错了。
mysql> show grants for z2;
+---------------------------------------------------------------------------------------------------+
| Grants for z2@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'z2'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'z2'@'%'                                      |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test1`.* TO 'z2'@'%'                                     |
+---------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for z1;
+---------------------------------------------------------------------------------------------------+
| Grants for z1@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'z1'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for z5;
ERROR 1141 (42000): There is no such grant defined for user 'z5' on host '%'
mysql>

新版本的mysql-5.0以后的版本，也可以通过information_schema库进行权限的查看。
mysql> use information_schema;
Database changed
mysql> select * from SCHEMA_PRIVILEGES where grantee="'z1'@'localhost'";
+------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE          | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+------------------+---------------+--------------+-------------------------+--------------+
| 'z1'@'localhost' | NULL          | test         | SELECT                  | YES          |
| 'z1'@'localhost' | NULL          | test         | INSERT                  | YES          |
| 'z1'@'localhost' | NULL          | test         | UPDATE                  | YES          |
| 'z1'@'localhost' | NULL          | test         | DELETE                  | YES          |
| 'z1'@'localhost' | NULL          | test         | CREATE                  | YES          |
| 'z1'@'localhost' | NULL          | test         | DROP                    | YES          |
| 'z1'@'localhost' | NULL          | test         | REFERENCES              | YES          |
| 'z1'@'localhost' | NULL          | test         | INDEX                   | YES          |
| 'z1'@'localhost' | NULL          | test         | ALTER                   | YES          |
| 'z1'@'localhost' | NULL          | test         | CREATE TEMPORARY TABLES | YES          |
| 'z1'@'localhost' | NULL          | test         | LOCK TABLES             | YES          |
| 'z1'@'localhost' | NULL          | test         | EXECUTE                 | YES          |
| 'z1'@'localhost' | NULL          | test         | CREATE VIEW             | YES          |
| 'z1'@'localhost' | NULL          | test         | SHOW VIEW               | YES          |
| 'z1'@'localhost' | NULL          | test         | CREATE ROUTINE          | YES          |
| 'z1'@'localhost' | NULL          | test         | ALTER ROUTINE           | YES          |
| 'z1'@'localhost' | NULL          | test         | EVENT                   | YES          |
| 'z1'@'localhost' | NULL          | test         | TRIGGER                 | YES          |
+------------------+---------------+--------------+-------------------------+--------------+
18 rows in set (0.00 sec)

mysql> select * from SCHEMA_PRIVILEGES where grantee="'z2'@'%'";       
+----------+---------------+--------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------+---------------+--------------+----------------+--------------+
| 'z2'@'%' | NULL          | test         | SELECT         | NO           |
| 'z2'@'%' | NULL          | test         | INSERT         | NO           |
| 'z2'@'%' | NULL          | test         | UPDATE         | NO           |
| 'z2'@'%' | NULL          | test         | DELETE         | NO           |
| 'z2'@'%' | NULL          | test1        | SELECT         | NO           |
| 'z2'@'%' | NULL          | test1        | INSERT         | NO           |
| 'z2'@'%' | NULL          | test1        | UPDATE         | NO           |
| 'z2'@'%' | NULL          | test1        | DELETE         | NO           |
+----------+---------------+--------------+----------------+--------------+
8 rows in set (0.00 sec)
附：必须使用表内的相关描述才能返回，以下z2@‘%’就为空。
mysql> select * from SCHEMA_PRIVILEGES where grantee="z2@'%'";
Empty set (0.00 sec)

----------更改权限
mysql> show grants for z1@localhost \G;
*************************** 1. row ***************************
Grants for z1@localhost: GRANT ALL PRIVILEGES ON *.* TO 'z1'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION
*************************** 2. row ***************************
Grants for z1@localhost: GRANT ALL PRIVILEGES ON `test`.* TO 'z1'@'localhost' WITH GRANT OPTION
2 rows in set (0.00 sec)

ERROR:
No query specified
mysql> show grants for z2@localhost \G;
ERROR 1141 (42000): There is no such grant defined for user 'z2' on host 'localhost'
ERROR:
No query specified
可能看出Z2对localhost没有权限
----赋予z2对localhost上的所有库的两个权限，执行后只列出了select 权限。
mysql> show grants for z2@localhost;     
+----------------------------------------+
| Grants for z2@localhost                |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'z2'@'localhost' |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> grant select on *.* to z2@localhost;        
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for z2@localhost;      
+-----------------------------------------+
| Grants for z2@localhost                 |
+-----------------------------------------+
| GRANT SELECT ON *.* TO 'z2'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)

继续给z2赋予权限，进行select,insert的合并
mysql> grant select,insert on *.* to z2@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for z2@localhost;             
+-------------------------------------------------+
| Grants for z2@localhost                         |
+-------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'z2'@'localhost' |
+-------------------------------------------------+
1 row in set (0.00 sec)
-----
收回权限，使用revoke，注意，这里是from而不是to了
mysql> revoke select,insert on *.* from z2@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for z2@localhost;
+----------------------------------------+
| Grants for z2@localhost                |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'z2'@'localhost' |
+----------------------------------------+
1 row in set (0.00 sec)
只剩下USAGE的权限了，revoke只进行权限的收回，并不会删除用户。

但USAGE的权限却不能被收回。
mysql> revoke usage on *.* from z2@localhost;             
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for z2@localhost;        
+----------------------------------------+
| Grants for z2@localhost                |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'z2'@'localhost' |
+----------------------------------------+
1 row in set (0.00 sec)

----------------修改密码
法一：
mysqladmin -u user_name -h host_name password "newpwd"
修改root密码
[root@test81 mysql]# bin/mysqladmin -u root -p123456 password '123'

法二：
mysql> set password for 'user'@'host_name'= password('1234');
修改自己的密码可以这么做
mysql> set password = password('1234');
Query OK, 0 rows affected (0.00 sec)

法三：
mysql> grant usage on *.* to 'root'@'localhost' identified by '12345'；

法四：
mysql> use mysql
Database changed
mysql> update user set password=password('123456') where Host='localhost' and User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
也可以先建立用户
mysql> insert into user
    -> (Host,User,Password)
    -> VALUES('','',PASSWORD(''))
    -> ;
Query OK, 1 row affected, 3 warnings (0.00 sec)


---------删除用户
mysql> show grants for z3@localhost;
+-------------------------------------------------------+
| Grants for z3@localhost                               |
+-------------------------------------------------------+
| GRANT PROCESS, FILE, SUPER ON *.* TO 'z3'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop user z3@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for z3@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'z3' on host 'localhost'

Mysql的安全问题

一。严格控制操作系统账号和权限，

锁定mysql
其他用户都采用独立方式，管理员通过专有用户或通过root su到mysql用户下管理
mysql用户目录下除了数据文件外，其他文件和目录都属主为root
二。避免用root运行mysql
   [root@test81 mysql]# bin/mysql --user=root &
不能使用该命令。
   一般使用--user=mysql
   注：测试时，使用root,再无法使用mysql 用户启动，原因是
100813 23:36:05 [ERROR] Failed to open log (file './mysql-bin.000017', errno 13)
100813 23:36:05 [ERROR] Could not open log file
100813 23:36:05 [ERROR] Can't init tc log
100813 23:36:05 [ERROR] Aborting
修改file './mysql-bin.000017'的属主属组为mysql即可。

三。mysql的相关安全问题
   删除匿名用户：
   删除匿名用户的办法
    让匿名用户只能登陆information_schema库
mysql> use msyql
mysql> select * from user \G'
mysql> select * from db \G'
mysql> drop user ''@'localhost';
mysql> drop user ''@'%';
   直接删除用户
mysql> delete from user where user='';
Query OK, 2 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
给root账户设置口令
设置安全密码
   密码直接写在命令行
   交互式登陆
   用户名和密码写在配置文件中/etc/my.cnf
[client]
user            = root
password        = 123456
    [root@test81 mysql]# bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.44-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
只授予必须的权限
all privileges的权限除了grant外的全部
一般的权限如select,insert,update,delete

除root外，其他用户不应该有mysql库中的user表的存取权限
用root登陆数据库后，赋予z3的相关权限
mysql> grant select,insert, update,delete on mysql.user to z3@localhost;
mysql> show grants for z3@localhost;
+----------------------------------------------------------------------------+
| Grants for z3@localhost                                                    |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'z3'@'localhost'                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO 'z3'@'localhost' |
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> exit
[root@test81 mysql]# bin/mysql -uz3
ERROR 1045 (28000): Access denied for user 'z3'@'localhost' (using password: YES)
[root@test81 tmp]# vi /etc/my.cnf
    [client]
#user           = root
#password       = 123456
[root@test81 mysql]# bin/mysql -uz3
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.44-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| user            |
+-----------------+
1 row in set (0.00 sec)

mysql> update user set password=password('abcd') where user='root' and host='localhost';  
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
ERROR 1227 (42000): Access denied; you need the RELOAD privilege for this operation
不能使用该选项，为root所有。
----使用root 登陆还是可以的，但刷新数据库后，再登陆就报错了
[root@test81 mysql]# bin/mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.44-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
[root@test81 mysql]# bin/mysql -uroot -p123456
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@test81 mysql]# bin/mysql -uroot -pabcd
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.44-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.