MySQL使用GRANT授權某使用者瀏覽和修改表許可權操作一例
下面的例子是授權logger使用者查詢,插入和修改test資料庫表記錄,但不能刪除的一個例子:
C:\Users\duansf>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_shop |
| frag_test |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| workinfo |
+----------------+
1 row in set (0.03 sec)
mysql> select * from workinfo;
Empty set (0.12 sec) --表記錄為空
mysql> grant select,insert,update on test.workinfo to logger@localhost identified by 'timber'; --授權logger使用者訪問和修改test資料庫的workinfo表
Query OK, 0 rows affected (0.14 sec)
mysql> quit
Bye
C:\Users\duansf>mysql -h localhost -u logger -p --用logger使用者登入資料庫
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.13 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> select * from workinfo; --可以查詢
Empty set (0.12 sec)
mysql> desc workinfo;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| type | varchar(10) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| tel | varchar(20) | YES | | NULL | |
| wages | int(11) | YES | | NULL | |
| contents | tinytext | YES | | NULL | |
| extra | text | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
8 rows in set (0.02 sec)
mysql> insert into workinfo values(3,'chenfeng','type','zhengzhou','123456',22,1,1); --有插入許可權
Query OK, 1 row affected (0.13 sec)
mysql> select * from workinfo; --有查詢許可權
+----+----------+------+-----------+--------+-------+----------+-------+
| id | name | type | address | tel | wages | contents | extra |
+----+----------+------+-----------+--------+-------+----------+-------+
| 3 | chenfeng | type | zhengzhou | 123456 | 22 | 1 | 1 |
+----+----------+------+-----------+--------+-------+----------+-------+
1 row in set (0.00 sec)
mysql> update workinfo set name='duansf' where id=3; --有修改許可權
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from workinfo;
+----+--------+------+-----------+--------+-------+----------+-------+
| id | name | type | address | tel | wages | contents | extra |
+----+--------+------+-----------+--------+-------+----------+-------+
| 3 | duansf | type | zhengzhou | 123456 | 22 | 1 | 1 |
+----+--------+------+-----------+--------+-------+----------+-------+
1 row in set (0.00 sec)
mysql>
mysql> delete from workinfo where id=3; --無刪除許可權,以下是報錯資訊
ERROR 1142 (42000): DELETE command denied to user 'logger'@'localhost' for table
'workinfo'
C:\Users\duansf>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_shop |
| frag_test |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| workinfo |
+----------------+
1 row in set (0.03 sec)
mysql> select * from workinfo;
Empty set (0.12 sec) --表記錄為空
mysql> grant select,insert,update on test.workinfo to logger@localhost identified by 'timber'; --授權logger使用者訪問和修改test資料庫的workinfo表
Query OK, 0 rows affected (0.14 sec)
mysql> quit
Bye
C:\Users\duansf>mysql -h localhost -u logger -p --用logger使用者登入資料庫
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.13 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> select * from workinfo; --可以查詢
Empty set (0.12 sec)
mysql> desc workinfo;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| type | varchar(10) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| tel | varchar(20) | YES | | NULL | |
| wages | int(11) | YES | | NULL | |
| contents | tinytext | YES | | NULL | |
| extra | text | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
8 rows in set (0.02 sec)
mysql> insert into workinfo values(3,'chenfeng','type','zhengzhou','123456',22,1,1); --有插入許可權
Query OK, 1 row affected (0.13 sec)
mysql> select * from workinfo; --有查詢許可權
+----+----------+------+-----------+--------+-------+----------+-------+
| id | name | type | address | tel | wages | contents | extra |
+----+----------+------+-----------+--------+-------+----------+-------+
| 3 | chenfeng | type | zhengzhou | 123456 | 22 | 1 | 1 |
+----+----------+------+-----------+--------+-------+----------+-------+
1 row in set (0.00 sec)
mysql> update workinfo set name='duansf' where id=3; --有修改許可權
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from workinfo;
+----+--------+------+-----------+--------+-------+----------+-------+
| id | name | type | address | tel | wages | contents | extra |
+----+--------+------+-----------+--------+-------+----------+-------+
| 3 | duansf | type | zhengzhou | 123456 | 22 | 1 | 1 |
+----+--------+------+-----------+--------+-------+----------+-------+
1 row in set (0.00 sec)
mysql>
mysql> delete from workinfo where id=3; --無刪除許可權,以下是報錯資訊
ERROR 1142 (42000): DELETE command denied to user 'logger'@'localhost' for table
'workinfo'
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15498/viewspace-2089166/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql操作命令梳理(4)-grant授權和revoke回收許可權MySql
- 5. 建立使用者、授權以及修改密碼等許可權操作密碼
- MySQL使用者許可權控制一例MySql
- mysql 給了使用者所有許可權ALL PRIVILEGES,但是該使用者沒有grant許可權MySql
- mysql使用者和許可權MySql
- mysql 8.0.21使用者及許可權操作MySql
- MYSQL學習筆記13: DCL許可權控制(使用者許可權操作)MySql筆記
- mysql使用者許可權MySql
- oracle受權與回收許可權grant和revokeOracle
- Oracle 許可權(grant、revoke)Oracle
- Oracle 建立表空間、建立使用者以及授權、檢視許可權Oracle
- 批量修改資料夾及檔案使用者許可權和使用者組許可權 centosCentOS
- mysql使用者許可權管理MySql
- Oracle建立表空間、建立使用者、授權、授權物件的訪問以及檢視許可權集合Oracle物件
- Oracle users / 許可權 / grant privOracle
- mysql 使用者管理和許可權設定MySql
- MySQL建立使用者和授權MySql
- 淺談MySQL中授權(grant)和撤銷授權(revoke)用法詳解MySql
- oracle常見受權與回收許可權 grant和revokeOracle
- MySQL 使用者及許可權管理?MySql
- MySQL使用者及許可權管理MySql
- mysql匯出使用者許可權MySql
- 配置Mysql使用者許可權命令MySql
- MySQL使用者與許可權管理MySql
- MySql查詢使用者許可權MySql
- 如何檢查某個使用者是否具有某個許可權物件上定義的某種許可權物件
- 擁有GRANT ANY OBJECT PRIVILEGE許可權時的許可權回收Object
- Oracle建立使用者並給使用者授權查詢指定表或檢視的許可權Oracle
- mysql使用者建立、修改、刪除及授權操作的總結MySql
- mysql 新增、刪除使用者和許可權分配MySql
- MySQL-03.使用者管理和許可權管理MySql
- 1.7.6. 授權和撤銷管理許可權
- 選單許可權表sqlite和mysqlSQLiteMySql
- Oracle 使用者、物件許可權、系統許可權Oracle物件
- 使用者許可權繼承另一使用者的許可權繼承
- 入門MySQL——使用者與許可權MySql
- MySQL入門--使用者與許可權MySql
- MySQL 使用者管理與許可權管理MySql