当前位置:首页 » Mysql技术教程

MySQL - 用户及权限管理

2010-11-23 14:49 本站整理 浏览(4)

可以用 CREATE USER 或 GRANT 创建用户,后者还同时分配相关权限。而 REVOKE 则用于删除用户权限,DROP USER 删除账户。

$ mysql -u root -p

password:

mysql> create database test; # 创建数据库

Query OK, 1 row affected (0.00 sec)

mysql> show databases; # 查看数据库是否创建成功

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

| Database           |

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

| information_schema |

| mysql              |

| test               |

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

3 rows in set (0.00 sec)

mysql> grant all on test.* to user1@'%' identified by '123456' with grant option; # 创建特权管理用户

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user; # 查看用户创建是否成功

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

| user             | host      |

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

| user1            | %         |

| root             | 127.0.0.1 |

| debian-sys-maint | localhost |

| root             | localhost |

| root             | server    |

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

5 rows in set (0.00 sec)

mysql> show grants for user1; # 查看用户权限

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

| Grants for user1@%                                                                               |

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

| GRANT USAGE ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD '*6BB...2CA2AD9'                        |

| GRANT ALL PRIVILEGES ON `test`.* TO 'user1'@'%' WITH GRANT OPTION                                |

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

2 rows in set (0.00 sec)
GRANT 语法:

GRANT privileges (columns)

    ON what

    TO user IDENTIFIED BY "password"

    WITH GRANT OPTION
权限列表:

ALTER: 修改表和索引。

CREATE: 创建数据库和表。

DELETE: 删除表中已有的记录。

DROP: 抛弃(删除)数据库和表。

INDEX: 创建或抛弃索引。

INSERT: 向表中插入新行。

REFERENCE: 未用。

SELECT: 检索表中的记录。

UPDATE: 修改现存表记录。

FILE: 读或写服务器上的文件。

PROCESS: 查看服务器中执行的线程信息或杀死线程。

RELOAD: 重载授权表或清空日志、主机缓存或表缓存。

SHUTDOWN: 关闭服务器。

ALL: 所有权限,ALL PRIVILEGES同义词。

USAGE: 特殊的 "无权限" 权限。

户账户包括 "username" 和 "host" 两部分,后者表示该用户被允许从何地接入。user1@'%'

表示任何地址,默认可以省略。还可以是 "user1@192.168.1.%"、"user1@%.abc.com" 等。数据库格式为

db@table,可以是 "test.*" 或 "*.*",前者表示 test 数据库的所有表,后者表示所有数据库的所有表。

子句 "WITH GRANT OPTION" 表示该用户可以为其他用户分配权限。

我们用 root 再创建几个用户,然后由 test 数据库的管理员 user1 为他们分配权限。

mysql> create user user2 identified by '123456', user3 identified by 'abcd';

Query OK, 0 rows affected (0.00 sec)

mysql> select user, host from mysql.user;

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

| user             | host      |

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

| user1            | %         |

| user2            | %         |

| user3            | %         |

| root             | 127.0.0.1 |

| debian-sys-maint | localhost |

| root             | localhost |

| root             | server    |

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

7 rows in set (0.00 sec)
好了,我们退出改用 user1 登录并针对 test 数据库进行操作。

mysql> quit # 退出

Bye

$ mysql -u user1 -p123456 test # 使用新用户登录

mysql> select database(); # 确认当前工作数据库

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

| database() |

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

| test       |

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

1 row in set (0.00 sec)

mysql> select current_user(); # 确认当前工作账户

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

| current_user() |

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

| user1@%        |

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

1 row in set (0.00 sec)
继续,创建一个数据表。

mysql> create table table1 # 创建表

    -> (

    ->    name varchar(50),

    ->    age integer

    -> );

Query OK, 0 rows affected (0.02 sec)

mysql> show tables; # 查看表是否创建成功

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

| Tables_in_test |

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

| table1         |

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

1 row in set (0.00 sec)

mysql> describe table1; # 查看表结构

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

| Field | Type        | Null | Key | Default | Extra |

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

| name  | varchar(50) | YES  |     | NULL    |       |

| age   | int(11)     | YES  |     | NULL    |       |

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

2 rows in set (0.00 sec)

mysql> insert into table1 values('Tom', 20); # 插入记录

Query OK, 1 row affected (0.00 sec)

mysql> select * from table1; # 查询记录

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

| name | age  |

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

| Tom  |   20 |

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

1 row in set (0.00 sec)
接下来我们为 user2, user3 分配权限。

mysql> grant select on test.* to user2; # 为 user2 分配 SELECT 权限。

Query OK, 0 rows affected (0.00 sec)

mysql> grant select on test.* to user3; # 为 user3 分配 SELECT 权限。

Query OK, 0 rows affected (0.00 sec)

mysql> grant insert, update on test.* to user2; # 再为 user2 增加 INSERT, UPDATE 权限。

Query OK, 0 rows affected (0.00 sec)
好了,我们退出,切换成 user2 操作看看。

$ mysql -u user2 -p123456

mysql> use test; # 切换工作数据库

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select database(); # 验证当前工作数据库

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

| database() |

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

| test       |

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

1 row in set (0.00 sec)

mysql> select user(); # 验证当前账户

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

| user()          |

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

| user2@localhost |

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

1 row in set (0.00 sec)

mysql> show grants for user2; # 查看当前用户权限,显然后来添加的 INSERT, UPDATE 被添加了。

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

| Grants for user2@%                                                                               |

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

| GRANT USAGE ON *.* TO 'user2'@'%' IDENTIFIED BY PASSWORD '*6BB837....2C9'                        |

| GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'user2'@'%'                                          |

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

2 rows in set (0.00 sec)
进行操作测试。

mysql> insert into table1 values("Jack", 21); # INSERT 操作成功

Query OK, 1 row affected (0.00 sec)

mysql> update table1 set age=22 where name='Jack'; # UPDATE 操作成功

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from table1; # SELECT 操作成功

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

| name | age  |

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

| Tom  |   20 |

| Jack |   22 |

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

2 rows in set (0.00 sec)

mysql> delete from table1 where age=22; # DELETE 操作无权限

ERROR 1142 (42000): DELETE command denied to user 'user2'@'localhost' for table 'table1'
我们切换回 user1 管理账户,移除 user2 的 UPDATE 权限看看。

$ mysql -u user1 -p123456 test

mysql> revoke update on test.* from user2; # 移除 UPDATE 权限

Query OK, 0 rows affected (0.00 sec)
再次切换回 user2。

$ mysql -u user2 -p123456 test

mysql> show grants for user2; # UPDATE 权限被移除

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

| Grants for user2@%                                                                               |

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

| GRANT USAGE ON *.* TO 'user2'@'%' IDENTIFIED BY PASSWORD '*6B...2AD9'                            |

| GRANT SELECT, INSERT ON `test`.* TO 'user2'@'%'                                                  |

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

2 rows in set (0.00 sec)

mysql> update table1 set age=23 where name='Jack'; # 不在拥有 UPDATE 权限

ERROR 1142 (42000): UPDATE command denied to user 'user2'@'localhost' for table 'table1'
好了,到此我们基本完成了创建用户和分配权限的操作。接下来,我们回到 root 进行修改用户密码和删除用户操作。

$ mysql -u root -p123456

mysql> set password for user3=password('abcabc'); # 修改用户 user3 密码

Query OK, 0 rows affected (0.00 sec)

mysql>flush privileges; # 刷新权限表(通常只在直接修改相关管理数据表后需要该操作)

Query OK, 0 rows affected (0.00 sec)

mysql> revoke all on *.* from user2; # 移除 user2 在所有数据库上的权限 

Query OK, 0 rows affected (0.00 sec)

mysql> drop user user2; # 删除 user2 账户

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user; # 验证删除结果

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

| user             | host      |

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

| user1            | %         |

| user3            | %         |

| root             | 127.0.0.1 |

| debian-sys-maint | localhost |

| root             | localhost |

| root             | server    |

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

6 rows in set (0.00 sec)
用户 user2 无法再次使用。

$ mysql -u user2 -p123456 test

ERROR 1045 (28000): Access denied for user 'user2'@'localhost' (using password: YES)
试试 user3。

$ mysql -u user3 -pabc test # 连接失败!哦,对了,我们修改了密码。

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

$ mysql -u user3 -pabcabc test # 新密码成功

mysql> select * from table1; # SELECT 操作成功

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

| name | age  |

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

| Tom  |   20 |

| Jack |   22 |

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

2 rows in set (0.00 sec)
要修改自己的密码直接执行 "set password = password('new_password');" 即可。

------- 摘要 --------------------------------------

创建用户:

GRANT insert, update ON testdb.* TO user1@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

CREATE USER user2 IDENTIFIED BY 'password';
分配权限:

GRANT select ON testdb.* TO user2;
查看权限:

SHOW GRANTS FOR user1;
修改密码:

SET PASSWORD FOR user1 = PASSWORD('newpwd');

SET PASSWORD = PASSWORD('newpwd');
移除权限:

REVOKE all ON *.* FROM user1;
删除用户:

DROP USER user1;
数据库列表:

SHOW DATABASES;
数据表列表:

SHOW TABLES;
当前数据库:

SELECT DATABASE();
当前用户:

SELECT USER();
数据表结构:

DESCRIBE table1;
刷新权限:

FLUSH PRIVILEGES;