plsql能管理mysql吗_MySQL的用户管理

1# 创建用户的一些限制和注意点

用户名长度必须不超过16个字符

用户名是大小写敏感的

2# 创建用户

语法：

(root@localhost)[(none)]> help create user

Name: 'CREATE USER'

Description:

Syntax:

CREATE USER user_specification [, user_specification] ...

user_specification:

user [ identified_option ]

auth_option: {

IDENTIFIED BY 'auth_string'

| IDENTIFIED BY PASSWORD 'hash_string'

| IDENTIFIED WITH auth_plugin

| IDENTIFIED WITH auth_plugin AS 'hash_string'

}

The CREATE USER statement creates new MySQL accounts. An error occurs

if you try to create an account that already exists.

按照语法，最简答的创建用户的方法：

c(root@localhost)[mysql]> create user test1;

Query OK, 0 rows affected (0.00 sec)

(root@localhost)[mysql]> select user,host,password from user;

+-------+-----------+-------------------------------------------+

| user | host | password |

+-------+-----------+-------------------------------------------+

| root | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B |

| test1 | % | |

+-------+-----------+-------------------------------------------+

2 rows in set (0.00 sec)

2 rows in set (0.00 sec)reate user test1;

这个时候其实密码是空的，可以空密码登录的。

[mysql@mysql01 ~]$ mysql -S /data/mysqldata/3306/mysql.sock -utest1

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.31-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(test1@localhost)[(none)]>

但是没有任何权限：(USAGE这个权限，是代表废物的意思！嗯，就是这样)

(test1@localhost)[(none)]> show grants;

+-----------------------------------+

| Grants for test1@% |

+-----------------------------------+

| GRANT USAGE ON *.* TO 'test1'@'%' |

+-----------------------------------+

1 row in set (0.00 sec)

3# 给用户设置密码：

命令

(root@localhost)[mysql]> help set password

Name: 'SET PASSWORD'

Description:

Syntax:

SET PASSWORD [FOR user] = password_option

password_option: {

PASSWORD('auth_string')

| OLD_PASSWORD('auth_string')

| 'hash_string'

}

给test1设置一个密码：

(root@localhost)[mysql]> set password for test1=password('passwordtest');

Query OK, 0 rows affected (0.00 sec)

(root@localhost)[mysql]> select user,host,password from user where user='test1';

+-------+------+-------------------------------------------+

| user | host | password |

+-------+------+-------------------------------------------+

| test1 | % | *A76A397AE758994B641D5C456139B88F40610926 |

+-------+------+-------------------------------------------+

1 row in set (0.00 sec)

至于OLD_PASSWORD()函数，是为了兼容老版本的密码而存在，古老的mysql4。

然而，set password for =password('string'); 这种修改方式已经被设置为要弃用，所以需要使用标准的修改密码方式：

(root@localhost)[mysql]> alter user test1 identified by 'password4test1';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'password4test1'' at line 1

(root@localhost)[mysql]>

以上可见报错了。原因是5.6还不支持这种密码修改方式：

(root@localhost)[mysql]> help alter user;

Name: 'ALTER USER'

Description:

Syntax:

ALTER USER user_specification [, user_specification] ...

user_specification:

user PASSWORD EXPIRE

这里只有一个子句，就是设置密码过期

3# 账号的密码过期：

(root@localhost)[mysql]> alter user test1 password expire;

Query OK, 0 rows affected (0.00 sec)

(root@localhost)[mysql]> select user,host,password,password_expired from user;

+-------+-----------+-------------------------------------------+------------------+

| user | host | password | password_expired |

+-------+-----------+-------------------------------------------+------------------+

| root | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B | N |

| test1 | % | *A76A397AE758994B641D5C456139B88F40610926 | Y |

+-------+-----------+-------------------------------------------+------------------+

2 rows in set (0.00 sec)

可以看到账号密码已经过期。

但是过期以后还是可以登录，但是什么都干不了，会提示马上更改密码：

[mysql@mysql01 ~]$ mysql -S /data/mysqldata/3306/mysql.sock -utest1 -p'passwordtest'

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.31-log

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(test1@localhost)[(none)]> select 1

-> ;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

(test1@localhost)[(none)]>

#修改当前账户的密码：

(test1@localhost)[(none)]> set password = password('password4test1');

Query OK, 0 rows affected (0.00 sec)

(test1@localhost)[(none)]>

#再次尝试登录，并做查询测试

[mysql@mysql01 ~]$ mysql -S /data/mysqldata/3306/mysql.sock -utest1 -p'password4test1'

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.6.31-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(test1@localhost)[(none)]> select 1;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

#查询成功，说明密码更改成功。用管理账号查询use表查看账号状态：

(root@localhost)[mysql]> select user,host,password,password_expired from user;

+-------+-----------+-------------------------------------------+------------------+

| user | host | password | password_expired |

+-------+-----------+-------------------------------------------+------------------+

| root | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B | N |

| test1 | % | *CFA887C680E792C2DCF622D56FB809E3F8BE63CC | N |

+-------+-----------+-------------------------------------------+------------------+

2 rows in set (0.00 sec)

4# 远程登录

在user表中，test1的host列值为%，代表可以从任意位置登录mysql

[mysql@mysql01 ~]$ mysql -utest1 -p'password4test1' -h 192.168.199.101 -P 3306

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 5.6.31-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(test1@192.168.199.101)[(none)]>

5# 比较完整方式创建用户

(root@localhost)[mysql]> create user test2@'%' identified by 'password4test2';

Query OK, 0 rows affected (0.00 sec)

(root@localhost)[mysql]> create user test2@'192.168.199.101' identified by 'test2local';

Query OK, 0 rows affected (0.00 sec)

(root@localhost)[mysql]> select user,host,password from user where user='test2';

+-------+-----------------+-------------------------------------------+

| user | host | password |

+-------+-----------------+-------------------------------------------+

| test2 | 192.168.199.101 | *74F386E8F5EEC7648BABDD0FCBA4524B97344856 |

| test2 | % | *5AB2E18AD9EE76F76E1C02E4DBF97BC7C3B4588B |

+-------+-----------------+-------------------------------------------+

2 rows in set (0.00 sec)

(root@localhost)[mysql]>

建立了两个test2，这两个test2是不同的，实际上应该说，用户test2@'192.168.199.101' 和用户test2@'%' 是两个不同的用户。

[mysql@mysql01 ~]$ mysql -utest2 -p'test2local' -h 192.168.199.101 -P 3306

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 14

Server version: 5.6.31-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(test2@192.168.199.101)[(none)]>

[mysql@mysql01 ~]$ mysql -utest2 -S /data/mysqldata/3306/mysql.sock -p'password4test2'

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 21

Server version: 5.6.31-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(test2@localhost)[(none)]>

5# 修改密码：

1，set password 方式：

(root@localhost)[mysql]> set password for test1=password('password4test1');

Query OK, 0 rows affected (0.00 sec)

2，直接update系统表user，这种方式需要刷新权限列表

(root@localhost)[mysql]> update user set password=password('password4test1') where user='test1';

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 0

(root@localhost)[mysql]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

3，grant 方式

(root@localhost)[mysql]> grant usage on *.* to test1 identified by 'password4test1';

Query OK, 0 rows affected (0.01 sec)