权限作用
用户对于数据库对象，有哪些 管理能力。
权限的表现方式
具体的命令。
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 | | | SET_USER_ID | Server Admin | | | ROLE_ADMIN | Server Admin | | | RESOURCE_GROUP_USER | Server Admin | | | RESOURCE_GROUP_ADMIN | Server Admin | | | BINLOG_ADMIN | Server Admin | | | SYSTEM_VARIABLES_ADMIN | Server Admin | | | GROUP_REPLICATION_ADMIN | Server Admin | | | CONNECTION_ADMIN | Server Admin | | | REPLICATION_SLAVE_ADMIN | Server Admin | | | ENCRYPTION_KEY_ADMIN | Server Admin | | | BACKUP_ADMIN | Server Admin | | | PERSIST_RO_VARIABLES_ADMIN | Server Admin | | +----------------------------+---------------------------------------+-------------------------------------------------------+ 46 rows in set (0.00 sec) mysql>
授权，回收权限操作
语法：
8.0以前：
grant 权限 on 对象 to 用户 identified by '密码'；
8.0+：
create user 用户 identified by '密码'；
grant 权限 on 对象 to 用户 identified by ''密码 ；
grant 权限1，权限2，权限3，···· on 对象 to 用户 identified by '密码'；
权限：
| ALL | 管理员 |
| 权限1，权限2，权限3，··· | 普通用户，并发用户 |
| grant option | 给别的用户授权 |
对象：库，表
*.* : chmod -R 755 / 管理员
guest.* : chmod -R 755 /guest
guest.t1 : chmod -R 755 /guest/t1
授权的例子
例子1：创建并授权管理员用户guest，能够通过localhost登录并管理数据库
mysql> create user guest identified by '123';
Query OK, 0 rows affected (0.02 sec)
mysql> grant all on *.* to guest;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user
-> ;
+------------------+-----------+
| user | host |
+------------------+-----------+
| guest | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> select * from mysql.user\G
*************************** 1. row ***************************
Host: %
User: guest
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: caching_sha2_password
.Zt6*#')3 :NauMJFbUzw/8xQV2iUpCcqW.j256RejsBAGeKZlQcd72
password_expired: N
password_last_changed: 2020-04-27 07:01:58
password_lifetime: NULL
account_locked: N
Create_role_priv: Y
Drop_role_priv: Y
Password_reuse_history: NULL
Password_reuse_time: NULL
mysql授权表：
| user | *.* |
| db | app.* |
| tables_priv | app.t1 |
| columns_priv | 列 |
回收权限
mysql中不能通过重复的授权，修改权限，只能 通过回收权限
revoke create on app.* from 'app'@'10.0.0.%'; show grants for app@'10.0.0.%';
超级管理员密码忘记，应该如何处理
--skip-grant-tables #跳过授权表 --skip-networking #跳过tcp/ip链接 mysqld_safe --skip-grant-tables --skip-networking flush privileges; #手工加载授权表
mysql参数列表
-u 用户名 -p 密码 -s 本地socket配置 -h 数据库IP地址 -P 数据库端口号 -e 免交互还行数据库命令 < 导入sql脚本
socket：
前提：数据库中必须实现授权
mysql -uguest -p123 -S /tmp/mysql.sock mysql -uguest -p -S /tmp/mysql.sock
tcp/ip：
前提：必须提前创建好，可以远程链接的用户
mysql -uguest -p123 -h 10.0.0.51 -P 3306 mysql -uguest -p -h 10.0.0.51 -P 3306
例子
授权guest管理用户，可以通过本地socket登录
grant all on *.* to guest@'localhost' identified by '123'; #如果是远程@后加为可允许地址
查看是远程还是本地登录
show processlist;
mysql -e的应用
mysql -uroot -p123 -e "select @@innodb_flush_log_at_trx_commit;" mysql -uroot -p123 < /root/world.sql
1324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
