DBA Hub

📋Steps in this guide1/12

Grant/Revoke Privileges in MySQL

Grant/Revoke Privileges in MySQL

mysql configurationintermediate
by MYSQL
13 views
1

Overview

Grant/Revoke Privileges in MySQL 1. Overview Global Level : Database Level:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
Global Level :
A privilege granted at this level applies to all databases on the server. Privileges granted at the global level stored in "mysql.user" table.
Database Level:
A privilege granted at this level applies to all tables in the specified database. Privileges granted at the global level stored in "mysql.db" table.
Table Level:
A privilege granted at this level applies to all columns in the specified table. Privileges granted at the global level stored in "mysql.tables_priv" table.
Column Level:
A privilege granted at this level applies to only the specified column. Privileges granted at the global level stored in "mysql.columns_priv" table.
Routine Level:
A privilege granted at this level applies to only the specified stored function procedure. Privileges granted at the global level stored in "mysql.procs_priv" table.
Proxy Level:
Grant proxy access to users
2

Section 2

Table Level: Column Level: Routine Level: Proxy Level: 2. What Are User Privileges?
3

Section 3

3. How to List Own User Privileges? SHOW GRANTS FOR CURRENT_USER(); show grants;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
ALL 	- All privileges.
CREATE 	- Allows the user to use CREATE TABLE commands.
ALTER 	- Allows the user to use ALTER TABLE commands.
DROP 	- Allows the user to use DROP TABLE commands.
DELETE	- Allows the user to use DELETE commands.
INSERT	- Allows the user to use INSERT commands.
UPDATE 	- Allows the user to use UPDATE commands.
SELECT 	- Allows the user to use SELECT commands.
SHUTDOWN - Allows the user to use "mysqladmin shutdown".
INDEX 	- Allows the user to create and drop indexes.
CREATE USER - Allows the user to manage user accounts.
CREATE VIEW - Allows the user to user "CREATE VIEW" commands.
CREATE ROUTINE - Allows the user to CREATE PROCEDURE and CREATE FUNCTION
USAGE - No privileges.

[root@rac1 ~]#
mysql -u raj -pRaj_12345%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 current_user();
+----------------+
| current_user() |
+----------------+
| raj@%          |
+----------------+
1 row in set (0.00 sec)

mysql>

mysql>
SHOW GRANTS FOR CURRENT_USER();
+---------------------------------+
| Grants for raj@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `raj`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

mysql>

---------------- OR -----

mysql>
show grants;
+---------------------------------+
| Grants for raj@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `raj`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

mysql>
4

Section 4

4. How to List Other User Privileges? SHOW GRANTS FOR 'raj'@'%'; 5. How To Grant User Privileges at the Global Level?

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql>
select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>
SHOW GRANTS FOR 'raj'@'%';
+---------------------------------+
| Grants for raj@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `raj`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

mysql>
5

Section 5

GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost'; GRANT CREATE ON *.* TO 'raj'@'localhost'; create table test_raj ( -> trans_id smallint, -> trans varchar(5), -> primary key (trans_id)) -> ENGINE=InnoDB; INSERT INTO test_raj values(1,'SUGI'); ERROR 1142 (42000): INSERT command denied to user 'raj'@'localhost' for table 'test_raj'

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
To grant all privileges to a user account over all databases, use the following command:
GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';
mysql>
create user raj@localhost identified by 'Rajasekhar_123%';
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> SHOW GRANTS FOR 'raj'@'localhost';
+-----------------------------------------+
| Grants for raj@localhost                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `raj`@`localhost` |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>


mysql>
GRANT CREATE ON *.* TO 'raj'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>
SHOW GRANTS FOR 'raj'@'localhost';
+------------------------------------------+
| Grants for raj@localhost                 |
+------------------------------------------+
| GRANT CREATE ON *.* TO `raj`@`localhost` |
+------------------------------------------+
1 row in set (0.00 sec)

mysql>


[root@rac1 ~]#
mysql -u raj -h localhost -pRajasekhar_123%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>
select current_user();
+----------------+
| current_user() |
+----------------+
| raj@localhost  |
+----------------+
1 row in set (0.00 sec)

mysql>
SHOW GRANTS FOR CURRENT_USER();
+------------------------------------------+
| Grants for raj@localhost                 |
+------------------------------------------+
| GRANT CREATE ON *.* TO `raj`@`localhost` |
+------------------------------------------+
1 row in set (0.00 sec)

mysql>
show databases;
+--------------------+
| Database           |
+--------------------+
| dell               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql>
use test;
Database changed

mysql>
select database ();
+-------------+
| database () |
+-------------+
| test        |
+-------------+
1 row in set (0.00 sec)

mysql> 

mysql>
create table test_raj (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>

mysql>
show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_raj       |
+----------------+
1 row in set (0.00 sec)

mysql>

mysql>
INSERT INTO test_raj values(1,'SUGI');
ERROR 1142 (42000): INSERT command denied to user 'raj'@'localhost' for table 'test_raj'
mysql>

mysql>
use dell;   <---- Login to other database and try to create table
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>
mysql>
select database ();
+-------------+
| database () |
+-------------+
| dell        |
+-------------+
1 row in set (0.00 sec)

mysql>
create table test_raj (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>
show tables;
+----------------+
| Tables_in_dell |
+----------------+
| test           |
| test_raj       |
+----------------+
2 rows in set (0.00 sec)

mysql>
create table test1 (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>
show tables;
+----------------+
| Tables_in_dell |
+----------------+
| test           |
| test1          |
| test_raj       |
+----------------+
3 rows in set (0.00 sec)

mysql>
6

Section 6

create table test1 ( -> trans_id smallint, -> trans varchar(5), -> primary key (trans_id)) -> ENGINE=InnoDB; 6. How To Grant User Privileges at the Database Level? GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost'; create table test_orcl ( -> trans_id smallint, -> trans varchar(5), -> primary key (trans_id)) -> ENGINE=InnoDB;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
To grant all privileges to a user account over a specific database, use the following command:
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
mysql>
create database orcl;
Query OK, 1 row affected (0.00 sec)

mysql>
create user lori@localhost identified by 'Lori123%';
Query OK, 0 rows affected (0.01 sec)

mysql>
grant create on orcl.* to lori@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql>

[root@rac1 ~]#
mysql -u lori -h localhost -pLori123%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>
select current_user ();
+-----------------+
| current_user () |
+-----------------+
| lori@localhost  |
+-----------------+
1 row in set (0.00 sec)

mysql>

mysql>
GRANT ALL ON orcl.* to lori@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql>


[root@rac1 ~]#
mysql -u lori -h localhost -pLori123%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>
select current_user ();
+-----------------+
| current_user () |
+-----------------+
| lori@localhost  |
+-----------------+
1 row in set (0.00 sec)

mysql>
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| orcl               |
+--------------------+
2 rows in set (0.00 sec)

mysql>
use orcl;
Database changed
mysql>
create table test_orcl (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>
show tables;
+----------------+
| Tables_in_orcl |
+----------------+
| test_orcl      |
+----------------+
1 row in set (0.00 sec)

mysql>
SHOW GRANTS FOR CURRENT_USER();
+--------------------------------------------------------+
| Grants for lori@localhost                              |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `lori`@`localhost`               |
| GRANT ALL PRIVILEGES ON `orcl`.* TO `lori`@`localhost` |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
7

Section 7

7. How To Grant User Privileges at the Table Level? GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.table_name TO database_user@'localhost'; GRANT SELECT, INSERT, UPDATE, DELETE ON `orcl`.`test_orcl` TO `lori`@`localhost` 8. How To Grant User Privileges at the Column Level?

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.table_name TO database_user@'localhost';
mysql>
SHOW GRANTS FOR lori@localhost;
+--------------------------------------------------------+
| Grants for lori@localhost                              |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `lori`@`localhost`               |
| GRANT ALL PRIVILEGES ON `orcl`.* TO `lori`@`localhost` |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql>
GRANT SELECT, INSERT, UPDATE, DELETE ON orcl.test_orcl TO 'lori'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>
SHOW GRANTS FOR lori@localhost;
+----------------------------------------------------------------------------------+
| Grants for lori@localhost                                                        |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `lori`@`localhost`                                         |
| GRANT ALL PRIVILEGES ON `orcl`.* TO `lori`@`localhost`                           |
|
GRANT SELECT, INSERT, UPDATE, DELETE ON `orcl`.`test_orcl` TO `lori`@`localhost`
|
+----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
8

Section 8

8. How To Grant User Privileges at the Column Level? GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; GRANT INSERT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; GRANT INSERT (`trans`) ON orcl.`test_orcl` to 'may'@`localhost`; show grants for 'may'@`localhost`; GRANT SELECT (`trans`, `trans_id`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost` GRANT SELECT (`trans`, `trans_id`), INSERT (`trans`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost`

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
GRANT INSERT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
GRANT INSERT (`trans`) ON orcl.`test_orcl` to 'may'@`localhost`;
mysql>
create user may@localhost identified by 'May1234%';
Query OK, 0 rows affected (0.01 sec)

mysql>
SELECT CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON orcl.`', TABLE_NAME, '` to ''may''@`localhost`;')
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_SCHEMA = 'orcl' AND TABLE_NAME = 'test_orcl';
+------------------------------------------------------------------------------------------------------------+
| CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON orcl.`', TABLE_NAME, '` to ''may''@`localhost`;') |
+------------------------------------------------------------------------------------------------------------+
| GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;                                |
| GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;                             |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
mysql>
GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
Query OK, 0 rows affected (0.00 sec)

mysql>
GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
Query OK, 0 rows affected (0.00 sec)

mysql>
show grants for 'may'@`localhost`;
+------------------------------------------------------------------------------------------+
| Grants for may@localhost                                                                 |
+------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `may`@`localhost`                                                  |
|
GRANT SELECT (`trans`, `trans_id`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost`
|
+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>

mysql>
select trans from orcl.test_orcl;
+-------+
| trans |
+-------+
| SUGI  |
| TEJU  |
| RAJ   |
| SOMU  |
| JANA  |
+-------+
5 rows in set (0.00 sec)

mysql>

mysql>
GRANT INSERT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql>
SHOW GRANTS FOR 'may'@`localhost`;
+------------------------------------------------------------------------------------------------------------+
| Grants for may@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `may`@`localhost`                                                                    |
|
GRANT SELECT (`trans`, `trans_id`), INSERT (`trans`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost`
|
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>

mysql>
GRANT INSERT (`trans`) ON orcl.`test_orcl` to 'may'@`localhost`;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
SHOW GRANTS FOR 'may'@`localhost`;
+------------------------------------------------------------------------------------------------------------+
| Grants for may@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `may`@`localhost`                                                                    |
|
GRANT SELECT (`trans`, `trans_id`), INSERT (`trans`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost`
|
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
9

Section 9

GRANT SELECT (`trans`, `trans_id`), INSERT (`trans`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost` 9. How To Grant User Privileges WITH GRANT OPTION? create table new_test ( -> trans_id smallint, -> trans varchar(5), -> primary key (trans_id)) -> ENGINE=InnoDB; grant select on orcl.* to 'bose'@'localhost' WITH GRANT OPTION ;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
mysql>
use orcl;
Database changed
mysql>
mysql>
create table new_test (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>

mysql>
INSERT INTO new_test values(1,'SUGI');
Query OK, 1 row affected (0.01 sec)

mysql>
INSERT INTO new_test values(2,'TEJU');
Query OK, 1 row affected (0.00 sec)

mysql>
INSERT INTO new_test values(3,'RAJ');
Query OK, 1 row affected (0.00 sec)

mysql>
INSERT INTO new_test values(4,'SOMU');
Query OK, 1 row affected (0.01 sec)

mysql>
INSERT INTO new_test values(5,'JANA');
Query OK, 1 row affected (0.00 sec)

mysql>
COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql>
create user bose@localhost identified by 'Bose_1234%';
Query OK, 0 rows affected (0.01 sec)

mysql>
grant select on orcl.* to 'bose'@'localhost'
WITH GRANT OPTION
;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql>
SHOW GRANTS FOR 'bose'@'localhost';
+------------------------------------------------------------------+
| Grants for bose@localhost                                        |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `bose`@`localhost`                         |
|
GRANT SELECT ON `orcl`.* TO `bose`@`localhost` WITH
GRANT OPTION
|
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
10

Section 10

GRANT SELECT ON `orcl`.* TO `bose`@`localhost` WITH GRANT OPTION 10. How to Revoke User Privileges? -- Global Level -- Database Level

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- Global Level
REVOKE ALL ON *.* FROM 'database_user'@'localhost';
REVOKE SELECT,INSERT,UPDATE,DELETE ON *.* FROM 'database_user'@'localhost';
-- Database Level
REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost';
REVOKE SELECT,INSERT,UPDATE,DELETE ON database_name.* FROM 'database_user'@'localhost';
-- Table Level
REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'database_user'@'localhost';
REVOKE SELECT,INSERT,UPDATE,DELETE ON database_name.table_name FROM 'database_user'@'localhost';
-- Column Level
REVOKE SELECT,INSERT (`trans`) ON orcl.`test_orcl` FROM 'may'@`localhost`;
REVOKE INSERT (`trans`) ON orcl.`test_orcl` FROM 'may'@`localhost`;
-- Revoke With Grant Option
mysql>
SHOW GRANTS FOR 'bose'@'localhost';
+------------------------------------------------------------------+
| Grants for bose@localhost                                        |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `bose`@`localhost`                         |
|
GRANT SELECT ON `orcl`.* TO `bose`@`localhost`
WITH GRANT OPTION
|
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

mysql>
revoke grant option on orcl.* from bose@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql>
SHOW GRANTS FOR 'bose'@'localhost';
+------------------------------------------------+
| Grants for bose@localhost                      |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `bose`@`localhost`       |
|
GRANT SELECT ON `orcl`.* TO `bose`@`localhost`
|
+------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
11

Section 11

-- Table Level -- Column Level -- Revoke With Grant Option WITH GRANT OPTION revoke grant option on orcl.* from bose@localhost; SHOW GRANTS FOR 'bose'@'localhost';
12

Section 12

GRANT SELECT ON `orcl`.* TO `bose`@`localhost` Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!