分类:
2010-05-25 11:26:14
$ 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 privileges (columns)
ON what
TO user IDENTIFIED BY "password"
WITH GRANT OPTION
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)
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)
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)
$ 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'
$ mysql -u user1 -p123456 test
mysql> revoke update on test.* from user2; # 移除 UPDATE 权限
Query OK, 0 rows affected (0.00 sec)
$ 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'
$ 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)
$ mysql -u user2 -p123456 test
ERROR 1045 (28000): Access denied for user 'user2'@'localhost' (using password: YES)
$ 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)
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;