mysql 授权 all_mysql使用grant ALL privileges命令给用户授权

mysql用户想连到mysql,需要使用grant命令给用户授权,有一个简单的办法,可以使用grant ALL privileges 命令给用户授予所有权限, 你想myuser使用mypassword从任何主机连接到mysql服务器的话。

主要使用:GRANT ALL PRIVILEGES ON *.* to 'root'@'%'; 命令授权.

--grant给root使用root密码从任何主机连接到mysql服务器。

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

FLUSH PRIVILEGES;

--或者

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root';

FLUSH PRIVILEGES;

--grant给root用户授予操作mysql所有数据库的所有权限

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'root';

FLUSH PRIVILEGES;

--或者

GRANT ALL ON *.* TO 'root'@'%' ;

FLUSH PRIVILEGES;

--grant给root用户授予操作test_db数据库的所有权限

允许用户root从ip为192.168.1.100的主机连接到mysql服务器，并使用root作为密码

GRANT ALL PRIVILEGES ON db.* TO 'root'@'192.168.1.100' IDENTIFIED BY "root";

FLUSH PRIVILEGES;

--允许用户testuser从ip为192.168.1.100的主机连接

--到mysql服务器的dk数据库，并使用root作为密码

GRANT ALL PRIVILEGES ON test_db.* TO 'testuser'@'192.168.1.100' IDENTIFIED BY 'root' WITH GRANT OPTION;

FLUSH PRIVILEGES;

--grant 操作 MySQL 外键权限

grant references on root.* to 'root'@'192.168.1.%';

--grant 操作 MySQL 临时表权限

grant create temporary tables on root.* to 'root'@'192.168.1.%';

FLUSH PRIVILEGES;

--grant 操作 MySQL 索引权限

grant index on root.* to 'root'@'192.168.1.%';

FLUSH PRIVILEGES;

--grant 操作 MySQL 视图、查看视图源代码权限

grant create view on root.* to 'root'@'192.168.1.%';

grant show view on root.* to 'root'@'192.168.1.%';

FLUSH PRIVILEGES;

--grant 操作 MySQL 存储过程、函数权限

grant create routine on root.* to 'root'@'192.168.1.%';

grant alter routine on root.* to 'root'@'192.168.1.%';

grant execute on root.* to 'root'@'192.168.1.%';

FLUSH PRIVILEGES;

GRANT的语法格式

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 ...]

object_type:

TABLE

| FUNCTION

| PROCEDURE

priv_level:

*

| *.*

| db_name.*

| db_name.tbl_name

| tbl_name

| db_name.routine_name

user_specification:

user [IDENTIFIED BY [PASSWORD] 'password']

ssl_option:

SSL

| X509

| CIPHER 'cipher'

| ISSUER 'issuer'

| SUBJECT 'subject'

with_option:

GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count