mysql 自定义角色_MySQL的账户设置

最新推荐文章于 2024-07-30 03:51:08 发布

兰艳知己

最新推荐文章于 2024-07-30 03:51:08 发布

阅读量502

点赞数

文章标签： mysql 自定义角色

本文链接：https://blog.csdn.net/weixin_30562025/article/details/113280682

版权

本文详细介绍了MySQL的账户设置，包括如何使用docker安装MySQL并连接，查看和创建账户，以及账户的资源限制和密码管理。此外，还讨论了MySQL8引入的新特性——角色管理，解释了如何创建、更新和授权角色，帮助用户更有效地管理数据库权限。

摘要由CSDN通过智能技术生成

MySQL的账户设置

使用 docker 安装 MySQL 并快速启动，现在我们进入docker容器。➜ ~ docker exec -it mysql8 /bin/bash

root@dedd71769326:/#

MySQL数据库连接

MySQL命令语法

用户名是你登录的用户，主机名或者IP地址为可选项，如果是本地连接则不需要设置，远程连接服务端则需要填写，密码是对应用户的密码。mysql –u用户名 [–h主机名或者IP地址,-P端口号] –p密码-u：登录的用户名。

-h：远程主机名或IP地址，不填写则默认本地地址。

-P：MySQL端口号，默认为3306。

-p：该登录用户对应的登录密码。root@dedd71769326:/# mysql -u root -p

Enter password:

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

Your MySQL connection id is 9

Server version: 8.0.21 MySQL Community Server - GPL

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.

MySQL账户查看

由于 root 权限很高，所以一般项目上会分配不同的账户和权限供程序员操作。

查看已有账户mysql> select user from mysql.user;

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

| user |

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

| root |

| mysql.infoschema |

| mysql.session |

| mysql.sys |

| root |

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

5 rows in set (0.03 sec)

为什么有两条 root 信息？我们来详细看一下。mysql> select user, host from mysql.user;

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

| user | host |

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

| root | % |

| mysql.infoschema | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

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

5 rows in set (0.00 sec)

这里host字段代表允许任意ip地址登录MySQL。目前root账户允许远程和本地登录。

查看当前账户mysql> select current_user;

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

| current_user |

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

| root@localhost |

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

1 row in set (0.00 sec)

如果我们使用外部电脑连接mysql> select current_user;

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

| current_user |

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

| root@% |

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

1 row in set (0.00 sec)

则表示当前登陆root账户允许远程和本地登录。

MySQL账户创建

MySQL命令语法CREATE USER [IF NOT EXISTS]

user [auth_option] [, user [auth_option]] ...

DEFAULT ROLE role [, role ] ...

[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

[WITH resource_option [resource_option] ...]

[password_option | lock_option] ...

user:

(see Section 6.2.4, “Specifying Account Names”)

auth_option: {

IDENTIFIED BY 'auth_string'

| IDENTIFIED WITH auth_plugin

| IDENTIFIED WITH auth_plugin BY 'auth_string'

| IDENTIFIED WITH auth_plugin AS 'hash_string'

}

tls_option: {

SSL

| X509

| CIPHER 'cipher'

| ISSUER 'issuer'

| SUBJECT 'subject'

}

resource_option: {

MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

}

password_option: {

PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]

| PASSWORD HISTORY {DEFAULT | N}

| PASSWORD REUSE INTERVAL {DEFAULT | N DAY}

| PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]

}

lock_option: {

ACCOUNT LOCK

| ACCOUNT UNLOCK

}user ：账户名称，语法是 'user_name'@'host_name' ，其中主机地址可以写为 %表示接受任何地址的连接。

auth_option ：身份验证方式，可以指定密码以及认证插件(mysql_native_password、sha256_password、caching_sha2_password)。

tls_option：加密连接选项。

resource_option：用户资源限制，比如每小时最大连接数。

password_option：密码额外的控制，比如设定失效时间。

lock_option：账户锁定选项，由管理员上锁或者解锁 (ACCOUNT LOCK | ACCOUNT UNLOCK)。

最简单的就是指定账户名+密码CREATE USER 'tian'@'localhost' IDENTIFIED BY 'password';

加上认证插件CREATE USER 'tian'@'localhost' IDENTIFIED WITH sha256_password BY 'password';

指定密码过期，以便用户第一次使用的时候需要修改密码CREATE USER 'tian'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

也可以指定每隔一段时间修改一次新密码CREATE USER 'tian'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE INTERVAL 180 DAY;

可以指定加密连接-- 不使用加密连接

CREATE USER 'tian'@'localhost' REQUIRE NONE；

-- 使用加密连接

CREATE USER 'tian'@'localhost' REQUIRE SSL;

-- 使用加密连接，并要求客户端提供有效证书

CREATE USER 'tian'@'localhost' REQUIRE X509;

CREATE USER 'tian'@'localhost' REQUIRE ISSUER 'CA颁发的有效X.509证书';

CREATE USER 'tian'@'localhost' REQUIRE SUBJECT '包含主题的有效X.509证书';

CREATE USER 'tian'@'localhost' REQUIRE CIPHER '指定的加密方法';

可以指定资源控制-- 单位小时内，账户被允许查询500次，更新100次，单位小时内最大连接数不限制。最大并发连接数不限制

CREATE USER 'tian'@'localhost' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100 MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0；

可以锁定账户-- 锁定

CREATE USER 'tian'@'localhost' ACCOUNT LOCK

-- 解锁

ALTER USER 'tian'@'localhost' ACCOUNT UNLOCK

最后完整的命令选项大概这个样子CREATE USER 'user_name'@'host_name' IDENTIFIED [WITH auth_plugin] BY 'auth_string' [REQUIRE NONE(SSL,X509)] [WITH MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count] [PASSWORD EXPIRE] [ACCOUNT LOCK]

如果你要删除账户DROP USER 'tian'@'localhost';

如果你要修改名称RENAME USER 'tian'@'localhost' TO 'tina'@'127.0.0.1';

MySQL角色创建

MySQL8里新加入了对于角色的管理，下面就简单的说一下如何使用：

角色可以理解为一组权限的集合，然后将角色赋给某个帐户，该帐户就拥有了角色对应的权限，每个帐户可以拥有多个角色，就像游戏里，你可以有很多称号一样。-- 名字规范

'role_name'@'host_name'

-- 通常仅使用用户名部分指定角色名称，并隐式使用主机名部分 '%'，主机名部分没有任何意义

'admin'

创建角色-- 省略主机名，默认为 '%'

CREATE ROLE 'admin', 'dev';

-- 这种也可以，但是没意义

CREATE ROLE 'app'@'localhost';

移除角色DROP ROLE 'admin', 'dev';

MySQL账户更新

MySQL命令语法ALTER USER [IF EXISTS]

user [auth_option] [, user [auth_option]] ...

[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

[WITH resource_option [resource_option] ...]

[password_option | lock_option] ...

ALTER USER [IF EXISTS] USER() user_func_auth_option

ALTER USER [IF EXISTS]

user DEFAULT ROLE

{NONE | ALL | role [, role ] ...}

user:

(see Section 6.2.4, “Specifying Account Names”)

auth_option: {

IDENTIFIED BY 'auth_string'

[REPLACE 'current_auth_string']

[RETAIN CURRENT PASSWORD]

| IDENTIFIED WITH auth_plugin

| IDENTIFIED WITH auth_plugin BY 'auth_string'

[REPLACE 'current_auth_string']

[RETAIN CURRENT PASSWORD]

| IDENTIFIED WITH auth_plugin AS 'auth_string'

| DISCARD OLD PASSWORD

}

user_func_auth_option: {

IDENTIFIED BY 'auth_string'

[REPLACE 'current_auth_string']

[RETAIN CURRENT PASSWORD]

| DISCARD OLD PASSWORD

}

tls_option: {

SSL

| X509

| CIPHER 'cipher'

| ISSUER 'issuer'

| SUBJECT 'subject'

}

resource_option: {

MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

}

password_option: {

PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]

| PASSWORD HISTORY {DEFAULT | N}

| PASSWORD REUSE INTERVAL {DEFAULT | N DAY}

| PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]

}

lock_option: {

ACCOUNT LOCK

| ACCOUNT UNLOCK

}

参数选项参考创建账户。

修改自己当前的密码ALTER USER USER() IDENTIFIED BY 'new_password';

修改账户密码ALTER USER 'tian'@'localhost' IDENTIFIED BY 'new_password';

修改认证插件ALTER USER 'tian'@'localhost' IDENTIFIED WITH mysql_native_password;

修改密码和插件ALTER USER 'tian'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

修改角色-- 授予自定义角色

ALTER USER 'tian'@'localhost' DEFAULT ROLE your_role_name;

-- 无角色

ALTER USER 'tian'@'localhost' DEFAULT ROLE NONE;

-- 所有角色

ALTER USER 'tian'@'localhost' DEFAULT ROLE ALL;

修改加密方式-- 只有账户密码正确，无须加密连接

ALTER USER 'tian'@'localhost' REQUIRE NONE;

-- 需要加密连接

ALTER USER 'tian'@'localhost' REQUIRE SSL;

...

修改资源访问-- 单位小时内，最大查询数量和更新数量

ALTER USER 'tian'@'localhost' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;

指定密码过期ALTER USER 'tian'@'localhost' PASSWORD EXPIRE;

修改锁定解锁ALTER USER 'tian'@'localhost' ACCOUNT LOCK;

ALTER USER 'tian'@'localhost' ACCOUNT UNLOCK;

MySQL账户授权

MySQL命令语法GRANT

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

TO user_or_role [, user_or_role] ...

[WITH GRANT OPTION]

[AS user

[WITH ROLE

DEFAULT

| NONE

| ALL

| ALL EXCEPT role [, role ] ...

| role [, role ] ...

]

}

GRANT PROXY ON user_or_role

TO user_or_role [, user_or_role] ...

[WITH GRANT OPTION]

GRANT role [, role] ...

TO user_or_role [, user_or_role] ...

[WITH ADMIN OPTION]

object_type: {

TABLE

| FUNCTION

| PROCEDURE

}

priv_level: {

| *.*

| db_name.*

| db_name.tbl_name

| tbl_name

| db_name.routine_name

}

user_or_role: {

user

| role

}

user:

(see Section 6.2.4, “Specifying Account Names”)

role:

(see Section 6.2.5, “Specifying Role Names”)

GRANT语法使得管理员能够授予账户权限或者角色，但是GRANT不能再一个语句中同时授予权限和角色。有ON，是授予权限

无ON，是授予角色-- 授予数据库db1的所有权限给指定账户

GRANT ALL ON db1.* TO 'tian'@'localhost';

-- 授予角色给指定的账户

GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

-- 授予数据库world的SELECT权限给指定的角色

GRANT SELECT ON world.* TO 'role3';

基本语法GRANT [权限] ON [数据库名].[表名] TO 'user_name'@'localhost' ...;

-- 授予所有数据库的权限

GRANT [权限] ON *.* TO 'user_name'@'localhost' ...;

注：全局权限是管理或适用于给定服务器上的所有数据库。要分配全局权限，请使用 ON *.*语法

下面是权限列表mysql> show privileges;

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

| Privilege | Context | Comment |

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

| Alter | Tables | To alter the table |

| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |

| Create | Databases,Tables,Indexes | To create new databases and tables |

| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |

| Create role | Server Admin | To create new roles |

| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |

| Create view | Tables | To create new views |

| Create user | Server Admin | To create new users |

| Delete | Tables | To delete existing rows |

| Drop | Databases,Tables | To drop databases, tables, and views |

| Drop role | Server Admin | To drop roles |

| Event | Server Admin | To create, alter, drop and execute events |

| Execute | Functions,Procedures | To execute stored routines |

| File | File access on server | To read and write files on the server |

| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |

| Index | Tables | To create or drop indexes |

| Insert | Tables | To insert data into tables |

| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |

| Process | Server Admin | To view the plain text of currently executing queries |

| Proxy | Server Admin | To make proxy user possible |

| References | Databases,Tables | To have references on tables |

| Reload | Server Admin | To reload or refresh tables, logs and privileges |

| Replication client | Server Admin | To ask where the slave or master servers are |

| Replication slave | Server Admin | To read binary log events from the master |

| Select | Tables | To retrieve rows from table |

| Show databases | Server Admin | To see all databases with SHOW DATABASES |

| Show view | Tables | To see views with SHOW CREATE VIEW |

| Shutdown | Server Admin | To shut down the server |

| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |

| Trigger | Tables | To use triggers |

| Create tablespace | Server Admin | To create/alter/drop tablespaces |

| Update | Tables | To update existing rows |

| Usage | Server Admin | No privileges - allow connect only |

| XA_RECOVER_ADMIN | Server Admin | |

| SHOW_ROUTINE | Server Admin | |

| RESOURCE_GROUP_USER | Server Admin | |

| SET_USER_ID | Server Admin | |

| SESSION_VARIABLES_ADMIN | Server Admin | |

| CLONE_ADMIN | Server Admin | |

| PERSIST_RO_VARIABLES_ADMIN | Server Admin | |

| ROLE_ADMIN | Server Admin | |

| BACKUP_ADMIN | Server Admin | |

| CONNECTION_ADMIN | Server Admin | |

| RESOURCE_GROUP_ADMIN | Server Admin | |

| INNODB_REDO_LOG_ARCHIVE | Server Admin | |

| BINLOG_ENCRYPTION_ADMIN | Server Admin | |

| REPLICATION_SLAVE_ADMIN | Server Admin | |

| SYSTEM_VARIABLES_ADMIN | Server Admin | |

| GROUP_REPLICATION_ADMIN | Server Admin | |

| SYSTEM_USER | Server Admin | |

| APPLICATION_PASSWORD_ADMIN | Server Admin | |

| TABLE_ENCRYPTION_ADMIN | Server Admin | |

| SERVICE_CONNECTION_ADMIN | Server Admin | |

| AUDIT_ADMIN | Server Admin | |

| BINLOG_ADMIN | Server Admin | |

| ENCRYPTION_KEY_ADMIN | Server Admin | |

| INNODB_REDO_LOG_ENABLE | Server Admin | |

| REPLICATION_APPLIER | Server Admin | |

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

58 rows in set (0.00 sec)

权限范围示例-- 数据库权限

GRANT ALL ON mydb.* TO 'user_name'@'host_name';

-- 表权限

GRANT ALL ON mydb.mytable TO 'user_name'@'host_name';

-- 列权限

GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytable TO 'user_name'@'host_name';

-- 存储过程权限

GRANT CREATE ROUTINE ON mydb.* TO 'user_name'@'host_name';

GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'user_name'@'host_name';

授权之后可以使用flush命令使其立即生效FLUSH PRIVILEGES

FLUSH语法FLUSH [NO_WRITE_TO_BINLOG | LOCAL] {

flush_option [, flush_option] ...

| tables_option

}

flush_option: {

BINARY LOGS

| ENGINE LOGS

| ERROR LOGS

| GENERAL LOGS

| HOSTS

| LOGS

| PRIVILEGES

| OPTIMIZER_COSTS

| RELAY LOGS [FOR CHANNEL channel]

| SLOW LOGS

| STATUS

| USER_RESOURCES

}

tables_option: {

TABLES

| TABLES tbl_name [, tbl_name] ...

| TABLES WITH READ LOCK

| TABLES tbl_name [, tbl_name] ... WITH READ LOCK

| TABLES tbl_name [, tbl_name] ... FOR EXPORT

}

FLUSH PRIVILEGES 包含以下操作重新加载mysql系统数据库中的grant表中的权限信息，并清除caching_sha2_password身份验证插件使用的内存缓存。

服务器读取包含动态特权分配的global_grants表，并注册其中的任何未注册特权。

服务器通过GRANT、CREATE USER、CREATE SERVER和INSTALL PLUGIN语句将信息缓存到内存中。对应的REVOKE、DROP USER、DROP SERVER和UNINSTALL插件语句不会释放这些内存，因此对于执行许多导致缓存的语句实例的服务器，内存使用量将会增加。可以使用刷新特权释放此缓存内存。

FLUSH TABLES 包含以下操作

关闭所有打开的表，强制关闭所有正在使用的表，并刷新准备好的语句缓存。

REVOKE语法

既然可以授权，那么就可以撤销REVOKE

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

FROM user_or_role [, user_or_role] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION

FROM user_or_role [, user_or_role] ...

REVOKE PROXY ON user_or_role

FROM user_or_role [, user_or_role] ...