User and rights management ：

grammar

grant  jurisdiction  on  database . Data sheet  to ' user ' @ ' Host name ';

example ： to  xiaogang  Assign all permissions

grant all on *.* to 'xiaogang'@'%';

This is the time  xiaogang  You have   All rights are granted

Permission list

·   Administrative authority （ Such as  super, process, file etc. ） You can't specify one database ,on It has to be followed by  *.*

·    Someone will ask. truncate Authority , Actually truncate Authority is create+drop, This is something to be aware of

View user authorization information

mysql> show grants for bzfys;

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

| Grants for [email protected]%                                                                                   |

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

| GRANT USAGE ON *.* TO [email protected]'%' IDENTIFIED BY PASSWORD '*A399693A49F7EC7C548D0FC376FA52AD293A552F' |

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

1 row in set (0.00 sec)

The authority granted by general circumstances

User management

mysql>use mysql;

One 、 see

mysql> select host,user,password from user ;

Two 、 establish

mysql> create user  bzfys   IDENTIFIED by 'xxxxx';   //identified by  The plain text password is encrypted and stored as a hash value

3、 ... and 、 modify

mysql>rename   user  bzfys to   buzaifengyaosha;//mysql 5 Can be used later , You need to use update  to update user surface

Four 、 Delete

mysql>drop user buzaifengyaosha;   //mysql5 Before deleting a user, you must first use revoke  Delete user privileges , Then delete the user ,mysql5 after drop  The command can delete the user's related permissions at the same time

5、 ... and 、 Change password （ If you find it back root The password has to be in this way ）

mysql> set password for bzfys =password('xxxxxx');

 mysql> update  mysql.user  set  password=password('xxxx')  where user=’bzfys’;5.8 The columns that need to be modified in the future are authentication_string Column update  mysql.user  set  authentication_string=password('xxxx')  where user=’bzfys’

6、 ... and 、 View user permissions

mysql> show grants for bzfys;

7、 ... and 、 To give permission

mysql> grant select on bzfys_db.*  to bzfys;

Recycling permissions

mysql> revoke  select on bzfys_db.*  from  bzfys;  // If the permission does not exist, an error will be reported

  The above command can also use multiple permissions to grant and reclaim at the same time , Permissions are separated by commas

mysql> grant select,update,delete  ,insert  on bzfys_db.*  to  bzfys;

If you want to see the results immediately, use

flush  privileges ;

The command to update  

The following information must be given when setting permissions

1, Permission to be granted

2, A database or table to which access is granted

3, user name

grant and revoke Access can be controlled at several levels

1, Entire server , Use  grant ALL   and revoke  ALL

2, Entire database , Use on  database.*

3, Characteristics table , Use on  database.table

4, Specific columns

5, Specific stored procedures

user In the table host The meaning of the value of the column

%               Match all hosts

localhost    localhost It won't be resolved into IP Address , Directly through UNIXsocket Connect

127.0.0.1       Will pass TCP/IP Protocol connection , And can only be accessed locally ;

::1                 ::1 It's compatibility support ipv6 Of , It means the same as ipv4 Of 127.0.0.1

grant  Ordinary data users , Inquire about 、 Insert 、 to update 、 Delete   Rights to all table data in the database .

grant select on testdb.* to [email protected]’%’

grant insert on testdb.* to [email protected]’%’

grant update on testdb.* to [email protected]’%’

grant delete on testdb.* to [email protected]’%’

perhaps , Use one  MySQL  Order to replace ：

grant select, insert, update, delete on testdb.* to [email protected]’%’

9>.grant  Database developers , Create table 、 Indexes 、 View 、 stored procedure 、 function ... Such as permissions .

grant  establish 、 modify 、 Delete  MySQL  Data table structure permission .

grant create on testdb.* to [email protected]’192.168.0.%’;

grant alter on testdb.* to [email protected]’192.168.0.%’;

grant drop on testdb.* to [email protected]’192.168.0.%’;

grant  operation  MySQL  Foreign key permissions .

grant references on testdb.* to [email protected]’192.168.0.%’;

grant  operation  MySQL  Temporary table permission .

grant create temporary tables on testdb.* to [email protected]’192.168.0.%’;

grant  operation  MySQL  Index permission .

grant index on testdb.* to [email protected]’192.168.0.%’;

grant  operation  MySQL  View 、 View view source code   jurisdiction .

grant create view on testdb.* to [email protected]’192.168.0.%’;

grant show view on testdb.* to [email protected]’192.168.0.%’;

grant  operation  MySQL  stored procedure 、 function   jurisdiction .

grant create routine on testdb.* to [email protected]’192.168.0.%’; -- now, can show procedure status

grant alter routine on testdb.* to [email protected]’192.168.0.%’; -- now, you can drop a procedure

grant execute on testdb.* to [email protected]’192.168.0.%’;

10>.grant  Ordinary  DBA  Manage someone  MySQL  Database permissions .

grant all privileges on testdb to [email protected]’localhost’

among , keyword  “privileges”  It can be omitted .

11>.grant  senior  DBA  management  MySQL  Permissions for all databases in .

grant all on *.* to [email protected]’localhost’

12>.MySQL grant  jurisdiction , They can work at multiple levels .

1. grant  It works on the whole  MySQL  Server ：

grant select on *.* to [email protected]; -- dba  You can query  MySQL  All the tables in the database in .

grant all on *.* to [email protected]; -- dba  Can manage  MySQL  All databases in

2. grant  Acting on a single database ：

grant select on testdb.* to [email protected]; -- dba  You can query  testdb  In the table .

3. grant  Acting on a single data table ：

grant select, insert, update, delete on testdb.orders to [email protected];

4. grant  Acting on columns in a table ：

grant select(id, se, rank) on testdb.apache_log to [email protected];

5. grant  It works on stored procedures 、 On the function ：

grant execute on procedure testdb.pr_add to ’dba’@’localhost’

grant execute on function testdb.fn_add to ’dba’@’localhost’

Be careful ： After modifying the permissions   Be sure to refresh the service , Or restart the service , Refresh the service with ：FLUSH PRIVILEGES.

grant create routine, alter routine, execute ON `blacklist`.* TO 'blacklist'@'%';

create routine Create stored procedure

alter routine,  Modify stored procedure

execute： Execute stored procedures

(adsbygoogle = window.adsbygoogle || []).push({});