云服务器能放网站吗,做h5的网站哪个好,4399小游戏大全网页版,设计软件图标权限管理 创建用户 create user user1localhost identified by QianFeng123;
select * from mysql.user; 或者select * from mysql.user\G进行分行显示
密码要求#xff1a;
1组成#xff1a; 由小写字母、大写字母、数字、字符 中的三项组成 #xff0c;也就是3/4
2长度…权限管理 创建用户 create user user1localhost identified by QianFeng123;
select * from mysql.user; 或者select * from mysql.user\G进行分行显示
密码要求
1组成 由小写字母、大写字母、数字、字符 中的三项组成 也就是3/4
2长度8位
登录mysql
mysql -uroot -p‘QianFeng123’
show databases;
[rootlocalhost ly]# mysql -uuser1 -pQianFeng123
mysql: [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 16
Server version: 8.0.36 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.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 show databases;
--------------------
| Database |
--------------------
| information_schema |
| performance_schema |
--------------------
2 rows in set (0.00 sec)mysql
使用root用户登录能看到如下数据库
mysql show databases;
--------------------
| Database |
--------------------
| company |
| discuz |
| haha |
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
--------------------
8 rows in set (0.00 sec)mysql
由于user1的用户没有权限看不到company和school、haha等其他的数据库。
删除用户 drop user
mysql drop user user1localhost;
Query OK, 0 rows affected (0.00 sec)
改用户密码
方法1系统命令行下使用mysqladmin来改密码。
mysqladmin -uuser1 -pQianFeng123 password QianFeng123456
[rootlocalhost ~]# mysqladmin -uuser1 -pQianFeng123 password QianFeng123456
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
有个警告因为我们使用-pQianFeng123 这里密码明文了
我们可以使用
mysqladmin -uuser1 -p password QianFeng123456 然后根据提示输入密码就不会有这个告警了
登录测试一下
[rootlocalhost ~]# mysql -uuser1 -pQianFeng123456
mysql: [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 28
Server version: 8.0.36 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.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
方法2数据库命令行下
mysql select user();查看当前登录用户
mysql select user();
-----------------
| user() |
-----------------
| user1localhost |
-----------------
1 row in set (0.00 sec)mysql
mysql ALTER USER user1localhost IDENTIFIED BY QianFeng456;
mysql ALTER USER user1localhost IDENTIFIED BY QianFeng456;
Query OK, 0 rows affected (0.01 sec)
flush privileges
方法3mysql5.7 可以使用set password来修改密码。 忘记密码了怎么进行破解
1.vi /etc/my.cnf
添加行skip-grant-tables
表示跳过用户授权
2.systemctl restart mysqld 重启
此时系统不支持mysqladmin 来修改密码
3.mysql -uroot -p 不需要输入密码
此时使用alter user 来改密码也是不支持的
4.正确的方法update mysql.user set authentication_string wherer userroot; 把root的密码认证清空
select user,authentication_string from mysql.user
此时密码为空
5.vi /etc/my.cnf
注销#skip-grant-tables
6.mysqladmin -uroot -p passord QianFeng12345 改root密码成功。
登录mysql的其他参数
mysql -uroot -p‘QianFeng456’ -P 3306 如果数据库的端口号改了不再是默认的3306了就需要用-P来制定端口号进行连接
mysql -uroot -p‘QianFeng456’ company 制定连接后使用company数据库默认登录后需要use 数据库才能跳入某个数据库内部来使用。现在可以在登录时制定使用过的数据库
select databases();查看使用的是哪个数据库。 使用该命令可以展示出mysql中存在的权限。
show privileges;
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 | |
| TELEMETRY_LOG_ADMIN | Server Admin | |
| SYSTEM_VARIABLES_ADMIN | Server Admin | |
| SHOW_ROUTINE | Server Admin | |
| SET_USER_ID | Server Admin | |
| SENSITIVE_VARIABLES_OBSERVER | Server Admin | |
| RESOURCE_GROUP_USER | Server Admin | |
| AUTHENTICATION_POLICY_ADMIN | Server Admin | |
| PASSWORDLESS_USER_ADMIN | Server Admin | |
| SERVICE_CONNECTION_ADMIN | Server Admin | |
| CLONE_ADMIN | Server Admin | |
| RESOURCE_GROUP_ADMIN | Server Admin | |
| REPLICATION_SLAVE_ADMIN | Server Admin | |
| GROUP_REPLICATION_ADMIN | Server Admin | |
| BACKUP_ADMIN | Server Admin | |
| AUDIT_ABORT_EXEMPT | Server Admin | |
| SESSION_VARIABLES_ADMIN | Server Admin | |
| ROLE_ADMIN | Server Admin | |
| BINLOG_ENCRYPTION_ADMIN | Server Admin | |
| APPLICATION_PASSWORD_ADMIN | Server Admin | |
| BINLOG_ADMIN | Server Admin | |
| GROUP_REPLICATION_STREAM | Server Admin | |
| AUDIT_ADMIN | Server Admin | |
| SYSTEM_USER | Server Admin | |
| FLUSH_STATUS | Server Admin | |
| ENCRYPTION_KEY_ADMIN | Server Admin | |
| FIREWALL_EXEMPT | Server Admin | |
| FLUSH_OPTIMIZER_COSTS | Server Admin | |
| TABLE_ENCRYPTION_ADMIN | Server Admin | |
| FLUSH_TABLES | Server Admin | |
| CONNECTION_ADMIN | Server Admin | |
| INNODB_REDO_LOG_ENABLE | Server Admin | |
| FLUSH_USER_RESOURCES | Server Admin | |
| PERSIST_RO_VARIABLES_ADMIN | Server Admin | |
| INNODB_REDO_LOG_ARCHIVE | Server Admin | |
| REPLICATION_APPLIER | Server Admin | |
----------------------------------------------------------------------------------------------------------------------------
69 rows in set (0.00 sec)mysql
给用户授权的方式有 2 种分别是通过把角色赋予用户给用户授权mysql8.0的特性 和 直接给用户授权。用户是数据库的使用者我们可以通过给用户授予访问数据库中资源的权限来控制用户对数据库的访问消除安全隐患。授权命令该权限如果发现没有该用户则会直接新建一个用户。
方式1.把角色赋予用户给用户授权mysql8.0的特性
方式2直接给用户授权
GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名用户地址 [IDENTIFIED BY ‘密码口令’];举例 1、给li4用户用本地命令行方式授予shool这个库下的所有表的插删改查的权限。
GRANT SELECT,INSERT,DELETE,UPDATE ON school.* TO li4localhost ;school是数据库名
2、授予通过网络方式登录的user1用户 对所有库所有表的全部权限密码设为123。如果需要赋予包括grant权限添加参数“WITH GRANT OPTION”这个选项即可表示该用户可以将自己拥有的权限授权给其他用户可以使用grant重复给用户添加权限。
GRANT ALL PRIVILEGES ON *.* TO user2% IDENTIFIED BY 123 with grant option;user1% %是允许网络方式登录 ‘localhost’是只允许本地登录
报错
mysql GRANT ALL PRIVILEGES ON *.* TO user2% IDENTIFIED BY 123 with grant option;
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 123 with grant option at line 1
mysql
42000的错误就是命令的语法错误
主要原因是MySQL版本8.0后不能再使用原来的方式不能再授权的时候创建用户和配置密码
查询MySQL版本
SELECT version();
1.再用MySQL 8试一下
create user test1localhost identified by QianF123456;
create database testdb
grant all privileges on testdb.* to test1localhost;
flush privileges; 2.再用MySQL 5.7试一下mysql grant all privileges on test.* to test% identified by 123456;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql flush privileges;
那么在MySQL8.0版本及以后先创建用户再赋予授权。
#mysql8不允许授权并创建用户了,要分开使用
创建远程访问新用户并授权
use mysql;
# mysql8 以前
grant all privileges on *.* to 新用户名% identified by 密码 with grant option;
grant all privileges on *.* to 新用户名指定ip identified by 密码 with grant option;
例如:以前使用
grant all privileges on *.* to root% identified by 123456 with grant option;#mysql8不允许授权并创建用户了,要分开使用
mysql8分开操作为:
CREATE USER root%; #创建角色CREATE USER root% IDENTIFIED BY 123456;
ALTER USER root% IDENTIFIED with mysql_native_password by 123456; #修改密码
grant all privileges on *.* to root%; #给角色授权
flush privileges; #刷新权限#另外修改主机
update mysql.user set host具体要指定的主机ip where userroot;#重命名
RENAME USER jeffreylocalhost TO jeff127.0.0.1; flush privileges; #刷新权限
select user,host,authentication_string from user;授予数据库权限时权限类型可以指定为以下值
SELECT表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
INSERT表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
DELETE表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
UPDATE表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
REFERENCES表示授予用户可以创建指向特定的数据库中的表外键的权限。
CREATE表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
ALTER表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
SHOW VIEW表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
CREATE ROUTINE表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
ALTER ROUTINE表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
INDEX表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
DROP表示授予用户可以删除特定数据库中所有表和视图的权限。
CREATE TEMPORARY TABLES表示授予用户可以在特定数据库中创建临时表的权限。
CREATE VIEW表示授予用户可以在特定数据库中创建新的视图的权限。
EXECUTE ROUTINE表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
LOCK TABLES表示授予用户可以锁定特定数据库的已有数据表的权限。
ALL 或 ALL PRIVILEGES表示以上所有权限。
授予表权限时权限类型可以指定为以下值
SELECT授予用户可以使用 SELECT 语句进行访问特定表的权限。
INSERT授予用户可以使用 INSERT 语句向一个特定表中添加数据行的权限。
DELETE授予用户可以使用 DELETE 语句从一个特定表中删除数据行的权限。
DROP授予用户可以删除数据表的权限。
UPDATE授予用户可以使用 UPDATE 语句更新特定数据表的权限。
ALTER授予用户可以使用 ALTER TABLE 语句修改数据表的权限。
REFERENCES授予用户可以创建一个外键来参照特定数据表的权限。
CREATE授予用户可以使用特定的名字创建一个数据表的权限。
INDEX授予用户可以在表上定义索引的权限。
ALL 或 ALL PRIVILEGES所有的权限名。
授予列权限时权限类型的值
只能指定为 SELECT、INSERT 和 UPDATE同时权限后面需要加上列名列表 column-list
最有效率的权限是用户权限。授予用户权限时
权限类型除了可以指定为授予数据库权限时的所有值之外还可以是下面这些值
CREATE USER表示授予用户可以创建和删除新用户的权限。SHOW DATABASES表示授予用户可以使用 SHOW DATABASES 语句查看所有已有的数据库的定义的权限。
WITH GRANT OPTION的作用
数据库添加用户语句
WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。 再次说明mysql的用户授权信息存放在mysql的user表中。 分配表权限
grant all on school.t1 to admint1localhost;
[rootbogon ~]# mysql -uadmint1 -pQianF123456mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| performance_schema |
| school |
--------------------
3 rows in set (0.00 sec)mysql use school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql show tables- ;
------------------
| Tables_in_school |
------------------
| t1 |
------------------
1 row in set (0.01 sec)mysql select * from school.t1;
--------------
| id | name |
--------------
| 1 | liming |
--------------
1 row in set (0.00 sec)mysql
列权限分配
[rootbogon ~]# mysql -uroot -pRoot098 mysql create user adminl1localhost identified by QianF123456;
Query OK, 0 rows affected (0.02 sec)mysql grant select(id),insert(name) on school.t1 to adminl1localhost;
Query OK, 0 rows affected (0.01 sec)mysql [rootbogon ~]# mysql -uadminl1 -pQianF123456 mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| performance_schema |
| school |
--------------------
3 rows in set (0.00 sec)mysql use school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql show tables;
------------------
| Tables_in_school |
------------------
| t1 |
------------------
1 row in set (0.00 sec)mysql select * from school.t1- ;
ERROR 1142 (42000): SELECT command denied to user adminl1localhost for table t1
mysql select id from school.t1 - ;
------
| id |
------
| 1 |
------
1 row in set (0.00 sec)
mysql select name from school.t1;
ERROR 1143 (42000): SELECT command denied to user adminl1localhost for column name in table t1mysql insert into school.t1(name) values (wang4);
Query OK, 1 row affected (0.00 sec)
mysql insert into school.t1 values (2,wang4);
ERROR 1142 (42000): INSERT command denied to user adminl1localhost for table t1
with grant option 包含授权选项
[rootbogon ~]# mysql -uroot -pRoot098 mysql create user adminalllocalhost identified by QianF123456;
Query OK, 0 rows affected (0.02 sec)mysql grant all on *.* to adminalllocalhost with grant option;
Query OK, 0 rows affected (0.01 sec)mysql \q
Bye
[rootbogon ~]# mysql -uadminall -pQianF123456mysql show databases;
--------------------
| Database |
--------------------
| company |
| discuz |
| haha |
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| testdb |
--------------------
9 rows in set (0.01 sec)mysql grant select,insert on school.t1 to adminl1localhost;
Query OK, 0 rows affected (0.00 sec)mysql \q
Bye
[rootbogon ~]# mysql -uadminl1 -pQianF123456 mysql use school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql select * from t1;
--------------
| id | name |
--------------
| 1 | liming |
| NULL | wang4 |
--------------
2 rows in set (0.00 sec)mysql
远程登录
mysql -u用户名 -p‘密码’ -h IP地址
例如 mysql -uadmint1 -p‘QianF123456’ -h 10.23.0.209
mysql create user test102.168.0.% identified by QianF123456;
Query OK, 0 rows affected (0.01 sec)mysql select user,host from user;
ERROR 1046 (3D000): No database selected
mysql select user,host from mysql.user;
-------------------------------
| user | host |
-------------------------------
| ly | % |
| test | 102.168.0.% |
| adminall | localhost |
| adminl1 | localhost |
| admint1 | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| test | localhost |
| test1 | localhost |
| user1 | localhost |
-------------------------------
12 rows in set (0.01 sec)
user host | test | 102.168.0.% | | test | localhost |
我们不难发现用户名test有两个 但是他们对应的host是不同的
权限查看
show grants\G
mysql select user();
----------------
| user() |
----------------
| rootlocalhost |
----------------
1 row in set (0.00 sec)mysql show grants\G
*************************** 1. row ***************************
Grants for rootlocalhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO rootlocalhost WITH GRANT OPTION
*************************** 2. row ***************************
Grants for rootlocalhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO rootlocalhost WITH GRANT OPTION
*************************** 3. row ***************************
Grants for rootlocalhost: GRANT PROXY ON TO rootlocalhost WITH GRANT OPTION
3 rows in set (0.00 sec)
show grants for 用户名
mysql show grants for testlocalhost;
---------------------------------------------------------
| Grants for testlocalhost |
---------------------------------------------------------
| GRANT USAGE ON *.* TO testlocalhost |
| GRANT ALL PRIVILEGES ON test.* TO testlocalhost |
| GRANT ALL PRIVILEGES ON test1.* TO testlocalhost |
---------------------------------------------------------
3 rows in set (0.00 sec)mysql
revoke 权限回收
revoke 权限 on 数据库.表 from 用户地址
mysql show grants for testlocalhost;
---------------------------------------------------------
| Grants for testlocalhost |
---------------------------------------------------------
| GRANT USAGE ON *.* TO testlocalhost |
| GRANT ALL PRIVILEGES ON test.* TO testlocalhost |
| GRANT ALL PRIVILEGES ON test1.* TO testlocalhost |
---------------------------------------------------------
3 rows in set (0.00 sec)mysql revoke all on school.* from testlocalhost;
ERROR 1141 (42000): There is no such grant defined for user test on host localhostmysql revoke all on test.* from testlocalhost;
Query OK, 0 rows affected (0.01 sec)mysql
mysql show grants for testlocalhost;
---------------------------------------------------------
| Grants for testlocalhost |
---------------------------------------------------------
| GRANT USAGE ON *.* TO testlocalhost |
| GRANT ALL PRIVILEGES ON test1.* TO testlocalhost |
---------------------------------------------------------
2 rows in set (0.00 sec)mysql
删除用户
drop user 用户名
mysql select user();
----------------
| user() |
----------------
| rootlocalhost |
----------------
1 row in set (0.00 sec)mysql select user,host from mysql.user;
-------------------------------
| user | host |
-------------------------------
| ly | % |
| test | 102.168.0.% |
| adminall | localhost |
| adminl1 | localhost |
| admint1 | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| test | localhost |
| test1 | localhost |
| user1 | localhost |
-------------------------------
12 rows in set (0.00 sec)mysql drop user user1;
ERROR 1396 (HY000): Operation DROP USER failed for user1%
mysql drop user user1localhost;
Query OK, 0 rows affected (0.00 sec)mysql
注意在5.7和8.0之前的版本删除用户前必须先用revoke把权限都回收完才能删除用户。
