Mysql创建用户赋权操作及修改密码

ゝ一纸荒年。 2022-07-14 08:09 195阅读 0赞

创建用户：

mysql> create user 'username'@'ipaddress' identified by 'password';
    #注：如果ipaddress为'%'，代表任何地址都可以用新建的用户名连接
    
    mysql> create user 'myroot'@'123.123.12.123' identified by 'mypass';
    mysql> create user 'myroot'@'%' identified by 'mypass';
    mysql> create user 'myroot'@'%' identified by '';
    mysql> create user 'myroot'@'%';

创建之后，查询用户权限：

mysql> use mysql;
    mysql> show grants for myroot@123.123.12.123; +------------------------------------------------+
    | Grants for 'myroot'@'123.123.12.123' | +------------------------------------------------+
    | GRANT USAGE ON *.* TO 'myroot'@'123.123.12.123' | +------------------------------------------------+

授权：

给用户myroot赋予所有数据库的所有权限

mysql> grant all privileges on *.* to 'myroot'@'123.123.12.123' identified by 'mypass' with grant option;

给用户myroot赋予数据库db\_school的所有权限

mysql> grant all privileges on db_school.* to 'myroot'@'123.123.12.123' identified by 'mypass' with grant option;

指定IP段用户赋权

mysql> grant all privileges on db_school.* to 'myroot'@'123.123.%' identified by 'mypass' with grant option;

给用户myroot赋予数据库db\_school中表stu1的查询和新增的权限

mysql> grant select,update on db_school.stu1 to 'myroot'@'123.123.12.123' identified by 'mypass' with grant option;

解除权限：

mysql> revoke select on db_school.stu1 from 'myroot'@'123.123.12.123' with grant option;
    mysql> show grants for 'myroot'@'123.123.12.123';
    +----------------------------------------------------------------------------------+
    | Grants for 'myroot'@'123.123.12.123'                                             |
    +----------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'myroot'@'123.123.12.123'                                  |
    | GRANT UPDATE ON 'db_school'.'stu1' TO 'myroot'@'123.123.12.123' WITH GRANT OPTION|
    +----------------------------------------------------------------------------------+

删除用户

mysql> drop user 'myroot'@'123.123.12.123';

修改密码

mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
    
    mysql
    
    use mysql;
    
    update user set authentication_string=password('root123') where user='root';
    
    flush privileges;
    
    exit;
    
    service mysqld restart;