什么是權(quán)限管理
不同的身份, 可以干不同的事情
新增加的用戶, 權(quán)限很少
我們先新建用戶dog
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
image.png
image.png
image.png
image.png
直接實(shí)例
1. 對新建用戶dog在library.reader表上授予select和delete權(quán)限
沒有權(quán)限時, 直接查詢會報(bào)錯
> 1142 - SELECT command denied to user 'dog'@'localhost' for table 'reader'
> 時間: 0s
賦予權(quán)限
use library;
grant select,DELETE on reader to dog@localhost;
image.png
但是insert操作依然報(bào)錯, 因?yàn)闆]有權(quán)限
INSERT INTO `library`.`reader`
( `readerid`, `readername`, `readerpass`, `retypeid`, `readerdate`, `readerstatus` )
VALUES
( '0017', '蘇小東', '123456', 1, '1999-09-09 00:00:00', '有效' );
> 1142 - INSERT command denied to user 'dog'@'localhost' for table 'reader'
> 時間: 0s
2. 授予dog在library.reader上的姓名和密碼的update權(quán)限
grant update(readername,readerpass) on library.reader to dog@localhost;
image.png
3. 授予dog用戶在library數(shù)據(jù)庫中的所有表的select權(quán)限
grant select on library.* to dog@localhost;
image.png
4. 授予dog在library數(shù)據(jù)庫中所有的表操作權(quán)限
grant all on library.* to dog@localhost;
image.png
5. 授予dog對所有數(shù)據(jù)庫所有表的操作權(quán)限
image.png
grant insert, delete, update, select on *.* to dog@localhost;
image.png
6. 授予dog創(chuàng)建新用戶的權(quán)利
mysql> CREATE USER 'cat'@'localhost' IDENTIFIED BY '123456';
1227 - Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
mysql>
一開始, dog用戶是沒有創(chuàng)建用戶的權(quán)限的
grant create user on *.* to dog@localhost;
之后, 可以成功
mysql> CREATE USER 'cat'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql>
image.png
7. 回收用戶dog在library.reader表上的select權(quán)限
revoke select on library.reader from dog@localhost;
我不喜歡被人收回權(quán)限, 所以就不演示了...
