例子: GRANT SELECT, INSERT ON test.user TO 'pig'@'%'; GRANT ALL ON *.* TO 'pig'@'%';
注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
mysql> GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
Step5、设置与更改用户密码
命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword");
例子: SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
Step6、撤销用户权限
命令: REVOKE privilege ON databasename.tablename FROM 'username'@'host';
说明: privilege, databasename, tablename - 同授权部分.
例子: REVOKE SELECT ON *.* FROM 'pig'@'%';
Step7、删除用户
命令: DROP USER 'username'@'host';
附表:在MySQL中的操作权限
ALTER
Allows use of ALTER TABLE.
ALTER ROUTINE
Alters or drops stored routines.
CREATE
Allows use of CREATE TABLE.
CREATE ROUTINE
Creates stored routines.
CREATE TEMPORARY TABLE
Allows use of CREATE TEMPORARY TABLE.
CREATE USER
Allows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW
Allows use of CREATE VIEW.
DELETE
Allows use of DELETE.
DROP
Allows use of DROP TABLE.
EXECUTE
Allows the user to run stored routines.
FILE
Allows use of SELECT... INTO OUTFILE and LOAD DATA INFILE.
INDEX
Allows use of CREATE INDEX and DROP INDEX.
INSERT
Allows use of INSERT.
LOCK TABLES
Allows use of LOCK TABLES on tables for which the user also has SELECT privileges.
PROCESS
Allows use of SHOW FULL PROCESSLIST.
RELOAD
Allows use of FLUSH.
REPLICATION
Allows the user to ask where slave or master
CLIENT
servers are.
REPLICATION SLAVE
Needed for replication slaves.
SELECT
Allows use of SELECT.
SHOW DATABASES
Allows use of SHOW DATABASES.
SHOW VIEW
Allows use of SHOW CREATE VIEW.
SHUTDOWN
Allows use of mysqladmin shutdown.
SUPER
Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.
UPDATE
Allows use of UPDATE.
USAGE
Allows connection without any specific privileges.