数据库常用授权和授权回收参数配置

标题索引

• 官方帮助

• 常用案例

官方帮助

在使用数据库时必不可少的即是查看help帮助，通过help帮助再次寻找常用命令及参数，如下为help grant信息：

MariaDB [(none)]> help grant;Name: 'GRANT'Description:Syntax:GRANT    priv_type [(column_list)]      [, priv_type [(column_list)]] ...    ON [object_type] priv_level    TO user_specification [, user_specification] ...    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]    [WITH with_option ...]GRANT PROXY ON user_specification    TO user_specification [, user_specification] ...    [WITH GRANT OPTION]object_type:    TABLE  | FUNCTION  | PROCEDUREpriv_level:    *  | *.*  | db_name.*  | db_name.tbl_name  | tbl_name  | db_name.routine_nameuser_specification:    user    [        IDENTIFIED BY [PASSWORD] 'password'      | IDENTIFIED WITH auth_plugin [AS 'auth_string']    ]Normally, a database administrator first uses CREATE USER to create anaccount, then GRANT to define its privileges and characteristics. Forexample:CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';GRANT ALL ON db1.* TO 'jeffrey'@'localhost';GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

常用配置

1.授权单位普通DBA或运维人员增删改查命令

MariaDB [(none)]> create table performance( id int, server_name varchar(20), Adress_City varchar(30) );MariaDB [(none)]> grant select,update,delete,insert on cluster.performance to root@'172.18.0.%' identified by 'jncsy';Query OK, 0 rows affected (0.00 sec)MariaDB  [(none)]> flush privileges;

2.授权某个用户某张表的某几列查询命令

MariaDB [(none)]> grant select(id,server_name) on cluster.performance to root@'172.18.0.%' identified by 'jncsy';Query OK, 0 rows affected (0.00 sec)MariaDB  [(none)]> flush privileges;

3.撤销某用户的授权权限

MariaDB [(none)]> show grants for root@'172.18.0.%';+--------------------------------------------------------------------------------------------------------------+| Grants for root@172.18.0.%                                                                                   |+--------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'root'@'172.18.0.%' IDENTIFIED BY PASSWORD '*429B3FB345D35EC8C8B54230AD060C07E846494A' || GRANT SELECT, INSERT, UPDATE, DELETE ON `cluster`.`performance` TO 'root'@'172.18.0.%'                       |+--------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)MariaDB [(none)]> revoke select on cluster.performance from root@'172.18.0.%';Query OK, 0 rows affected (0.00 sec)