linux用命令怎么修改mysql用户的权限

mysql更改用户权限

This entry was posted by admin Monday, 26 April, 2010

1.“grant all on *.* to root@’%’ identified by ‘yourpassword’”——这个还可以顺带设置密码。

2.“flush privileges”——刷新一下，让权限生效。

mysql的一些其他的管理，可以用mysqladmin命令。可以用来设置密码什么的。

grant方面的详细信息可以看我下面的转载：

本文实例，运行于 MySQL 5.0 及以上版本。

MySQL 赋予用户权限命令的简单格式可概括为：

grant 权限 on 数据库对象 to 用户

一、grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。

grant select on testdb.* to common_user@’%’

grant insert on testdb.* to common_user@’%’

grant update on testdb.* to common_user@’%’

grant delete on testdb.* to common_user@’%’

或者，用一条 MySQL 命令来替代：

grant select, insert, update, delete on testdb.* to common_user@’%’

hi 楼主，在数据库中创建包含很多，视图，索引，临时表的创建权限都能分开赋予，你可以执行 show privileges 来查看权限参数，我这边就以创建表为例，只包含查询表功能，其他修改，删除，备份没有权限；以下是步骤：

1，create user 'tom'@'%' identified by '123456'---创建用户，无权限；

2， grant create,select on wangxh2.* to tom-----把wangxh2库的所有表的创建和查询赋予tom

3，flush privileges-----刷新权限表才能起效

接下来是测试：

mysql>show databases

+--------------------+

| Database |

+--------------------+

| information_schema |

| test |

| wangxh2|

+--------------------+

3 rows in set (0.06 sec)

mysql>use wangxh2

Database changed

mysql>show tables

+-------------------+

| Tables_in_wangxh2 |

+-------------------+

| test |

+-------------------+

1 row in set (0.00 sec)

mysql>drop test

ERROR 1064 (42000): You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near 'test' at line 1

mysql>drop table test

ERROR 1142 (42000): DROP command denied to user 'tom'@'localhost' for table 'test'

mysql>select count(*) from test

+----------+

| count(*) |

+----------+

| 33554432 |

+----------+

1 row in set (0.01 sec)

mysql>insert into test values(1)

ERROR 1142 (42000): INSERT command denied to user 'tom'@'localhost' for table 'test'

mysql>delete from test

ERROR 1142 (42000): DELETE command denied to user 'tom'@'localhost' for table 'test'

mysql>update test set id=1

ERROR 1142 (42000): UPDATE command denied to user 'tom'@'localhost' for table 'test'

mysql>create table test1 (id int)

Query OK, 0 rows affected (0.02 sec)

mysql>insert into test1 values(1)

ERROR 1142 (42000): INSERT command denied to user 'tom'@'localhost' for table 'test1'

[mysql@localhost ~]$ mysqldump -u tom -paidengshan wangxh2 >/home/mysql/aa.sql

mysqldump: Got error: 1044: Access denied for user 'tom'@'%' to database 'wangxh2' when using LOCK TABLES

[mysql@localhost ~]$

-----------------------------------------------------------------------------------------

以上测试发现，tom对wangxh2有建表，查询表的权限，但是修改，删除，新增，备份都没有权限，达到你的需求了

今天安装lnmp环境后用mysql -u root -p连接密码后出现错误

Access denied for user 'root'@'localhost' (using password: YES)

然后我就用 /etc/init.d/mysql stop 先去停止数据库

然后用安全模式 mysqld_safe --user=mysql --skip-grant-tables --skip-networking & 进入数据库

然后用SQL语句修改root权限 UPDATE user SET Password=PASSWORD('我的密码') where USER='root'

改完了后 重新刷新权限 FLUSH PRIVILEGES然后quit退出

然后重启数据库就行了 /etc/init.d/mysql restart

[root@967b0e11e627 default]# mysql -u root -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@967b0e11e627 default]# /etc/init.d/mysql stop

Shutting down MySQL. SUCCESS!

[root@967b0e11e627 default]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

[1] 91761

[root@967b0e11e627 default]# 160618 11:54:54 mysqld_safe Logging to '/usr/local/mysql/var/967b0e11e627.err'.

160618 11:54:55 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var

[root@967b0e11e627 default]# mysql -u root mysql

Welcome to the MySQL monitor. Commands end with or \g.

Your MySQL connection id is 1

Server version: 5.5.48-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

---