|
am;
+------+-------+
| id | value |
+------+-------+
| 1 | jack |
| 2 | robin |
+------+-------+
frank@172.16.6.89[(none)]> show grants;
+------------------------------------------------------------------------------------------------------+
| Grants for frank@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '*63DAA25989C7E01EB96570FA4DBE154711BEB361' |
| GRANT SELECT ON `tempdb`.* TO 'frank'@'%' --可以看到多出了select权限 |
+------------------------------------------------------------------------------------------------------+
--下面是一个授予最大权限的例子，授予的同时会自动创建用户，由于我们没有设置密码，所以password列查询结果为空
root@localhost[(none)]> grant all privileges on *.* to 'jack'@'localhost';
Query OK, 0 rows affected (0.00 sec) --第一个*号代表任意数据库，第二个*号代表数据库上的任意对象
root@localhost[(none)]> select user,host,Password from mysql.user where user='jack';
+------+-----------+----------+
| user | host | Password |
+------+-----------+----------+
| jack | localhost | |
+------+-----------+----------+
suse11b:~ # mysql -ujack -p -h localhost
Enter password:
jack@localhost[(none)]> show grants for current_user; --该方式等同于show grants，查看自身权限
+---------------------------------------------------+
| Grants for jack@localhost |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'jack'@'localhost' |
+---------------------------------------------------+
--在当前session下查看其它用户的权限，注，当前session登陆的用户也需要有权限才能查看其它用户权限
jack@localhost[(none)]> show grants for 'frank'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for frank@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '*63DAA25989C7E01EB96570FA4DBE154711BEB361' |
| GRANT SELECT ON `tempdb`.* TO 'frank'@'%' |
+------------------------------------------------------------------------------------------------------+
--下面演示基于对象列级别的授权
--首先revoke之前的select权限
root@localhost[(none)]> revoke select on tempdb.* from 'frank'@'%';
Query OK, 0 rows affected (0.00 sec)
fred@localhost[tempdb]> create table tb_user as select * from mysql.user;
Query OK, 9 rows affected (0.15 sec)
Records: 9 Duplicates: 0 Warnings: 0
fred@localhost[tempdb]> grant select(user,host),update(host) on tempdb.tb_user to 'frank'@'%';
ERROR 1142 (42000): GRANT command denied to user 'fred'@'localhost' for table 'tb_user' --授权失败
--下面使用root来授权
root@localhost[(none)]> grant select(user,host),update(host) on tempdb.tb_user to 'frank'@'%';
Query OK, 0 rows affected (0.00 sec)
root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
--下面检查一下frank所拥有的权限
root@localhost[(none)]> show grants for 'frank';
+------------------------------------------------------------------------------------------------------+
| Grants for frank@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'frank'@'%' IDENTIFIED BY PASS |