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
jurisdiction |
explain |
give an example |
---|---|---|
usage |
Connect ( land ) jurisdiction , Create a user , It's automatically granted to them usage jurisdiction ( By default ). |
mysql> grant usage on *.* to 'root′@'localhost' identified by '123'; |
This permission can only be used for database login , You can't do anything ; And usage Permissions cannot be reclaimed , That is to say REVOKE Users can't delete users . |
||
file |
Have file Only permissions can be executed select ..into outfile and load data infile… operation , But don't put file, process, super Permissions are granted to accounts other than administrators , There are serious security risks . |
mysql> grant file on *.* to root@localhost; |
mysql> load data infile '/home/mysql/pet.txt' into table pet; |
||
super |
This permission allows the user to terminate any query ; Modify global variables SET sentence ; Use CHANGE MASTER,PURGE MASTER LOGS. |
mysql> grant super on *.* to root@localhost; |
mysql> purge master logs before 'mysql-bin.000006′; |
||
select |
There has to be select Authority , Can be used select table |
mysql> grant select on pyt.* to 'root′@'localhost'; |
mysql> select * from shop; |
||
insert |
There has to be insert Authority , Can be used insert into ….. values…. |
mysql> grant insert on pyt.* to 'root′@'localhost'; |
mysql> insert into shop(name) values('aa'); |
||
update |
There has to be update Authority , Can be used update table |
mysql> update shop set price=3.5 where article=0001 and dealer='A'; |
delete |
There has to be delete Authority , Can be used delete from ….where….( Delete the records in the table ) |
mysql> grant delete on pyt.* to 'root′@'localhost'; |
mysql> delete from table where id=1; |
||
alter |
There has to be alter Authority , Can be used alter table |
mysql> alter table shop modify dealer char(15); |
alter routine |
Must possess alter routine Authority , Can be used {alter |drop} {procedure|function} |
mysql>grant alter routine on pyt.* to 'root′@' localhost ‘; |
mysql> drop procedure pro_shop; |
||
Query OK, 0 rows affected (0.00 sec) |
||
create |
There has to be create Authority , Can be used create table |
mysql> grant create on pyt.* to 'root′@'localhost'; |
drop |
There has to be drop Authority , To delete the library 、 surface 、 Indexes 、 View etc. |
mysql> drop database db_name; |
mysql> drop table tab_name; |
||
mysql> drop view vi_name; |
||
mysql> drop index in_name; |
||
create routine |
Must possess create routine Authority , Can be used {create |alter|drop} {procedure|function} |
mysql> grant create routine on pyt.* to 'root′@'localhost'; |
When granted create routine when , Automatically Grant EXECUTE, ALTER ROUTINE Permission to its Creator : |
||
create temporary tables |
( Notice that this is tables, No table) |
There has to be create temporary tables Authority , Can be used create temporary tables. |
mysql> grant create temporary tables on pyt.* to 'root′@'localhost'; |
||
[mysql@mydev ~]$ mysql -h localhost -u root -p pyt |
||
mysql> create temporary table tt1(id int); |
||
create view |
There has to be create view Authority , Can be used create view |
mysql> grant create view on pyt.* to 'root′@'localhost'; |
mysql> create view v_shop as select price from shop; |
||
create user |
To use CREATE USER, Must have mysql The whole database CREATE USER jurisdiction , Or have INSERT jurisdiction . |
mysql> grant create user on *.* to 'root′@'localhost'; |
or :mysql> grant insert on *.* to root@localhost; |
||
show database |
adopt show database You can only see the database with certain permissions you have , Unless you have the whole picture SHOW DATABASES jurisdiction . |
mysql> show databases; |
about [email protected] Users , No, right mysql Database permissions , So when you log in and query with this identity , Can't see mysql database : |
||
show view |
Must have show view jurisdiction , To perform show create view |
mysql> show create view name; |
index |
Must have index jurisdiction , To perform [create |drop] index |
mysql> grant index on pyt.* to [email protected]; |
mysql> create index ix_shop on shop(article); |
||
mysql> drop index ix_shop on shop; |
||
excute |
Execute what exists Functions,Procedures |
mysql> call pro_shoroot(0001,@a); |
event |
event It is recommended to use root Users create and maintain . |
mysql> show global variables like 'event_scheduler'; |
To make event Work ,MySQL The constant GLOBAL event_scheduler It has to be for on Or is it 1 |
||
lock tables |
Must have lock tables jurisdiction , Can be used lock tables |
mysql> grant lock tables on pyt.* to [email protected]; |
mysql> lock tables a1 read; |
||
mysql> unlock tables; |
||
references |
With REFERENCES jurisdiction , Users can use a field in other tables as a foreign key constraint for a table . |
|
reload |
Must have reload jurisdiction , To execute flush [tables | logs | privileges] |
mysql> grant reload on pyt.* to [email protected]; |
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES |
||
mysql> grant reload on *.* to 'root′@'localhost'; |
||
Query OK, 0 rows affected (0.00 sec) |
||
mysql> flush tables; |
||
replication client |
With this permission, you can query master server、slave server state . |
mysql> grant Replication client on *.* to [email protected]; |
or :mysql> grant super on *.* to [email protected]; |
||
mysql> show master status; |
||
replication slave |
With this permission, you can view the slave server , Read binary logs from the primary server . |
mysql> grant replication slave on *.* to [email protected]; |
mysql> show slave hosts; |
||
Empty set (0.00 sec) |
||
mysql>show binlog events; |
||
Shutdown |
close mysql jurisdiction |
[[email protected] ~]$ mysqladmin shutdown |
grant option |
Have grant option, You can give your own permissions to other users ( Limited to the rights you already have ) |
mysql> grant Grant option on pyt.* to [email protected]; |
mysql> grant select on pyt.* to [email protected]; |
||
process |
Through this authority , User can execute SHOW PROCESSLIST and KILL command . By default , Every user can execute SHOW PROCESSLIST command , But you can only query the process of the user . |
mysql> show processlist; |
all privileges |
All permissions .with grant option It can be authorized jointly and severally |
mysql> grant all privileges on pyt.* to [email protected] with grant option; |
· 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({});