Database common authorization and authorization collection parameter configuration

Title Index

• Official Help

• Common cases

Official Help

When working with a database, it is essential to view help assistance, and help to find common commands and parameters again, as follows:

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] &NBSP, ...}]     [WITH WITH_OPTION&NBSP, ...] 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;

Common configuration

1. Authorized Unit general DBA or operations personnel additions and deletions change the command

MariaDB [(None)]> CREATE TABLE performance (ID int, server_name varchar (), adress_city varchar (30)); MariaDB [(None)]> Grant Select,update,delete,insert on cluster.performance to [email protected] ' 172.18.0.% ' Identified by ' Jncsy '; Query OK, 0 rows Affected (0.00 sec) MariaDB [(none)]> flush privileges;

2. Authorizing a user to a certain number of columns of a table query command

MariaDB [(None)]> Grant Select (id,server_name) on cluster.performance to [e-mail protected] ' 172.18.0.% ' identified by ' Jncsy '; Query OK, 0 rows Affected (0.00 sec) MariaDB [(none)]> flush privileges;

3. Revoke a user's authorization rights

mariadb [(None)]> show grants for [email protected] ' 172.18.0.% '; +------------ --------------------------------------------------------------------------------------------------+|  grants for [email protected]%                                                                                       |+ --------------------------------------------------------------------------------------------------------------+ | 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 [email  Protected] ' 172.18.0.% '; query ok, 0 rows affected  (0.00 SEC)

Database common authorization and authorization collection parameter configuration