MySQL(User&Grant)

最新推荐文章于 2024-08-24 16:04:53 发布

weixin_34124577

最新推荐文章于 2024-08-24 16:04:53 发布

阅读量79

点赞数

原文链接：https://my.oschina.net/igooglezm/blog/861653

版权

2019独角兽企业重金招聘Python工程师标准>>>

新建用户

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

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 password
sudo mysqladmin -u root password NEWPASSWORD



#change admin password
sudo mysqladmin -u root -p'OLDPASSWORD' password NEWPASSWORD




#recover admin password
sudo service mysql stop

sudo 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.pid

mysql -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;
>exit
stop mysql

sudo service mysql restart
mysql -uroot -pzhaomeng

ALTER

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

转载于:https://my.oschina.net/igooglezm/blog/861653