授予数据库账号dba权限_【MySQL】数据库管理：用户与权限

创建用户

mysql> create user 'tpcc'@'%' identified by 'tpcc';

Query OK, 0 rows affected (0.02 sec)

修改用户密码

mysql> set password for 'tpcc'@'%'=password('abcd.1234');

Query OK, 0 rows affected (0.03 sec)

查询用户权限

mysql> show grants for 'tpcc'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for tpcc@%                                                                                   |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*8E329B15E3C0FF9DDF7597B748CCE9473593BF60' |

+-----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

删除用户

mysql> drop user 'tpcc'@'%';

Query OK, 0 rows affected (0.07 sec)

权限级别分为全局，库，表，列四个层次，在指定级别授予的权限也必须在指定的级别撤销权限

授予列的权限

mysql> grant all (w_id) on tpcc100.warehouse to 'tpcc'@'%';

Query OK, 0 rows affected (0.02 sec

mysql> grant select (w_name) on tpcc100.warehouse to 'tpcc'@'%';

Query OK, 0 rows affected (0.03 sec)

mysql> grant insert (w_street_1) on tpcc100.warehouse to 'tpcc'@'%';

Query OK, 0 rows affected (0.04 sec)

mysql> select * from mysql.columns_priv;

+------+----------+------+------------+-------------+---------------------+-------------+

| Host | Db       | User | Table_name | Column_name | Timestamp           | Column_priv |

+------+----------+------+------------+-------------+---------------------+-------------+

| %    | tpcc100 | tpcc | warehouse  | w_id        | 0000-00-00 00:00:00 | Select      |

| %    | tpcc100 | tpcc | warehouse  | w_name      | 0000-00-00 00:00:00 | Select      |

| %    | tpcc100 | tpcc | warehouse  | w_street_1  | 0000-00-00 00:00:00 | Insert      |

+------+----------+------+------------+-------------+---------------------+-------------+

3 rows in set (0.00 sec)

mysql> show grants for 'tpcc'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for tpcc@%                                                                                   |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

| GRANT SELECT (w_id, w_name), INSERT (w_street_1) ON `tpcc1000`.`warehouse` TO 'tpcc'@'%'            |

+-----------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

撤销授予的列权限

mysql> revoke select (w_id, w_name), insert (w_street_1) on tpcc1000.warehouse from 'tpcc'@'%';

Query OK, 0 rows affected (0.03 sec)

mysql> show grants for 'tpcc'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for tpcc@%                                                                                   |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

+-----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

授予表的权限

mysql> grant all on tpcc100.warehouse to 'tpcc'@'%';

Query OK, 0 rows affected (0.03 sec)

mysql> show grants for 'tpcc'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for tpcc@%                                                                                   |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

| GRANT ALL PRIVILEGES ON `tpcc100`.`warehouse` TO 'tpcc'@'%'                                        |

+-----------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> select * from mysql.tables_priv where user='tpcc'\G;

*************************** 1. row ***************************

Host: %

Db: tpcc100

User: tpcc

Table_name: warehouse

Grantor: root@localhost

Timestamp: 0000-00-00 00:00:00

Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger

Column_priv:

1 row in set (0.00 sec)

撤销授予的表权限

mysql> revoke all on tpcc100.warehouse from 'tpcc'@'%';

Query OK, 0 rows affected (0.02 sec)

mysql> show grants for 'tpcc'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for tpcc@%                                                                                   |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

+-----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

授予库的权限

mysql> grant all on tpcc100.* to 'tpcc'@'%';

Query OK, 0 rows affected (0.02 sec)

mysql> show grants for 'tpcc'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for tpcc@%                                                                                   |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

| GRANT ALL PRIVILEGES ON `tpcc100`.* TO 'tpcc'@'%'                                                  |

+-----------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> select * from mysql.db where user='tpcc'\G;

*************************** 1. row ***************************

Host: %

Db: tpcc100

User: tpcc

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

1 row in set (0.00 sec)

撤销授予的库权限

mysql> revoke all on tpcc100.* from 'tpcc'@'%';

Query OK, 0 rows affected (0.02 sec)

mysql> show grants for 'tpcc'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for tpcc@%                                                                                   |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

+-----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

授予全局权限

mysql> grant all on *.* to 'tpcc'@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'tpcc'@'%';

+--------------------------------------------------------------------------------------------------------------+

| Grants for tpcc@%                                                                                            |

+--------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

+--------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from mysql.user where user='tpcc'\G;

*************************** 1. row ***************************

Host: %

User: tpcc

Password: *D2B995B159B5840C27690FC435C6ACEE7B9E1348

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string:

password_expired: N

1 row in set (0.00 sec)

撤销授予的全局权限

mysql> revoke all on *.* from 'tpcc'@'%';

Query OK, 0 rows affected (0.03 sec)

mysql>  show grants for 'tpcc'@'%';

+-----------------------------------------------------------------------------------------------------+

| Grants for tpcc@%                                                                                   |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'tpcc'@'%' IDENTIFIED BY PASSWORD '*D2B995B159B5840C27690FC435C6ACEE7B9E1348' |

+-----------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)