给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>
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.
|