新建用户

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']    [, user [IDENTIFIED BY [PASSWORD] 'password'］

  CREATE USER 'dog' @ 'localhost' IDENTIFIED BY '123456' ;

  CREATE USER 'pig' @ '192.168.1.101_' IDENDIFIED BY '123456' ;   CREATE USER 'pig' @ '%' IDENTIFIED BY '123456' ;   CREATE USER 'pig' @ '%' IDENTIFIED BY '' ;   CREATE USER 'pig' @ '%' ;

create user test identified by password "1234"

== insert into mysql.user(Host,User,Password) values("localhost","test",password("1234"));

删除用户

drop user user@host==delete from user where condition

 

重命名用户

rename user username@host to username@host==update user set password=password('a123456') where user='mysqladm';

设置用户密码

SET PASSWORD [FOR user] =    {        PASSWORD('some password')      | OLD_PASSWORD('some password')      | 'encrypted password'    }

set password for mysqladm = {password('a123456')}

== update user set password=password('a123456') where user='mysqladm';

 

赋予用户权限

GRANT    priv_type [(column_list)]      [, priv_type [(column_list)］ ...    ON [object_type] priv_level    TO user [IDENTIFIED BY [PASSWORD] 'password']        [, user [IDENTIFIED BY [PASSWORD] 'password'］ ...    [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

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

grant privileges ON databasename.tablename TO 'username' @ 'host'

== insert into user(host,user,password) values ("%","userName",password("userPassword"));

 

显示用户权限

SHOW GRANTS;SHOW GRANTS FOR CURRENT_USER;SHOW GRANTS FOR CURRENT_USER();SHOW GRANTS FOR user@host;

 

移除用户权限

REVOKE    priv_type [(column_list)]      [, priv_type [(column_list)］ ...    ON [object_type] priv_level    FROM user [, user] ...REVOKE ALL PRIVILEGES, GRANT OPTION    FROM user [, user] ...

 

权限数据表

1. Global Level--mysql.user--:变更后,需先KILL重连接后生效 2. Database Level--mysql.db--: db_name.*,变更后,需先KILL重连接后生效 3. Table Level--mysql.table_priv--: db_name.table_name, 变更立即生效 4. Column Level--mysql.column_priv--: 变更立即生效 SELECT(Column_name,...),INSERT(Column_name,...),UPDATE(Column_name,...)

 

权限清单

  ALL [PRIVILEGES],设置除GRANT OPTION之外的所有简单权限   ALTER,  允许使用ALTER TABLE.   ALTER ROUTINE, 允许更改或取消已存储的子程序.   CREATE, 允许使用CREATE TABLE.   CREATE ROUTINE, 创建已存储的子程序.   CREATE TEMPORARY TABLE, 允许使用CREATE TEMPORARY TABLE.   CREATE USER, 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES   CREATE VIEW, 允许使用CREATE VIEW.   DELETE, 允许使用DELETE.   DROP, 允许使用DROP TABLE.   EXECUTE, 允许用户运行已存储的子程序.   FILE, 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE.   INDEX, 允许使用CREATE INDEX和DROP INDEX.   INSERT, 允许使用INSERT .   LOCK TABLES, 允许对您拥有SELECT权限的表使用LOCK TABLES.   PROCESS, 允许使用SHOW FULL PROCESSLIST.   REFERENCES,未被实施   RELOAD, 允许使用FLUSH.   REPLICATION, 允许用户询问从属服务器或主服务器的地址.   REPLICATION SLAVE, 用于复制型从属服务器（从主服务器中读取二进制日志事件）.   SELECT, 允许使用SELECT   SHOW DATABASES, 允许显示所有数据库   SHOW VIEW, 允许使用SHOW CREATE VIEW   SHUTDOWN, 允许使用 mysqladmin shutdown   SUPER, 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句, mysqladmin debug command.命令；允许连接（一次），即使已达到max_connections。   UPDATE, 允许使用UPDATE   USAGE, “无权限”的同义词   GRANT OPTION,允许授予权限

 

ROOT

#set admin passwordsudo mysqladmin -u root password NEWPASSWORD#change admin passwordsudo mysqladmin -u root -p'OLDPASSWORD' password NEWPASSWORD#recover admin passwordsudo service mysql stopsudo mkdir /var/run/mysqld;sudo chown mysql:mysql /var/run/mysqld;sudo -u mysql  mysqld_safe --skip-grant-tables —skip-networking --pid-file=/var/run/mysqld/mysqld.pidmysql -u root>select host,user from mysql.user;>update mysql.user set authentication_string=password('zhaomeng') where user='root';>--update mysql.user set authentication_string=password('zhaomeng') where user='root' and host='localhost';>flush privileges;>exitstop mysqlsudo service mysql restartmysql -uroot -pzhaomeng

ALTER

1、修改用户的口令 ALTER USER SCOTT IDENTIFIED BY NEW_PASSWORD           alter user user() identified by 'zhaomeng';2、设置用户口令过期 ALTER USER SCOTT PASSWORD EXPIRE3、锁定用户 ALTER USER SCOTT ACCOUNT LOCK4、解锁用户 ALTER USER SCOTT ACCOUNT UNLOCK