Chinaunix首页 | 论坛 | 博客
  • 博客访问: 35368
  • 博文数量: 9
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 50
  • 用 户 组: 普通用户
  • 注册时间: 2019-05-08 16:04
文章分类

全部博文(9)

文章存档

2019年(9)

我的朋友

分类: Mysql/postgreSQL

2019-05-08 16:05:09

原文地址:修改mysql中普通用户的密码 作者:zeuls

有时候,为了小程序,要为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用户管理
2010-08-14 23:55

给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
;
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
(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
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
;
mysql> show grants for
;
+---------------------------------------------------------------------------------------------------+
| Grants for
                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO
IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO
                                      |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test1`.* TO
                                     |
+---------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

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

mysql> show grants for z1;
+---------------------------------------------------------------------------------------------------+
| Grants for
                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO
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="
";
+------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE          | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+------------------+---------------+--------------+-------------------------+--------------+
|
| NULL          | test         | SELECT                  | YES          |
|
| NULL          | test         | INSERT                  | YES          |
|
| NULL          | test         | UPDATE                  | YES          |
|
| NULL          | test         | DELETE                  | YES          |
|
| NULL          | test         | CREATE                  | YES          |
|
| NULL          | test         | DROP                    | YES          |
|
| NULL          | test         | REFERENCES              | YES          |
|
| NULL          | test         | INDEX                   | YES          |
|
| NULL          | test         | ALTER                   | YES          |
|
| NULL          | test         | CREATE TEMPORARY TABLES | YES          |
|
| NULL          | test         | LOCK TABLES             | YES          |
|
| NULL          | test         | EXECUTE                 | YES          |
|
| NULL          | test         | CREATE VIEW             | YES          |
|
| NULL          | test         | SHOW VIEW               | YES          |
|
| NULL          | test         | CREATE ROUTINE          | YES          |
|
| NULL          | test         | ALTER ROUTINE           | YES          |
|
| NULL          | test         | EVENT                   | YES          |
|
| NULL          | test         | TRIGGER                 | YES          |
+------------------+---------------+--------------+-------------------------+--------------+
18 rows in set (0.00 sec)

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

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

ERROR:
No query specified
mysql> show grants for
\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
;     
+----------------------------------------+
| Grants for
                |
+----------------------------------------+
| GRANT USAGE ON *.* TO
|
+----------------------------------------+
1 row in set (0.00 sec)

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

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

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

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

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

mysql> show grants for ;        
+----------------------------------------+
| Grants for
                |
+----------------------------------------+
| GRANT USAGE ON *.* TO
|
+----------------------------------------+
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
= password('1234');
修改自己的密码可以这么做
mysql> set password = password('1234');
Query OK, 0 rows affected (0.00 sec)

法三:
mysql> grant usage on *.* to
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
;
+-------------------------------------------------------+
| Grants for
                               |
+-------------------------------------------------------+
| GRANT PROCESS, FILE, SUPER ON *.* TO
|
+-------------------------------------------------------+
1 row in set (0.00 sec)

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

mysql> show grants for ;
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
;
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() |
+----------------+
|
|
+----------------+
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
;
mysql> show grants for
;
+----------------------------------------------------------------------------+
| Grants for
                                                    |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO
                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.`user` TO
|
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> exit
[root@test81 mysql]# bin/mysql -uz3
ERROR 1045 (28000): Access denied for user
(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
(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.


阅读(3513) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~