root和普通用戶
權限機制
mysql系統數據庫
show databases;
use mysql;
show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
mysql.user
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |重新加載權限表
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |服務器管理
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |Grand Option 數據庫，表，存儲進程函數
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
Host 主機名
User 用戶名
Password
上述3個字段都驗證成功，才允許用戶登錄
權限字段，以_priv結尾的字段，決定了用戶權限
高級權限
對用數據庫進行管理
普通權限
對數據庫操作
安全字段
ssl*~x509_su*，主要用來實現加密
mysql通常不支持ssl標準
show variables like 'have_openssl'
上句可以用來查詢是不是支持ssl
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
+---------------+----------+
mysql.db
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
用戶字段
Host，User，Db
權限字段
形如*_priv
mysql.host
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
用戶字段
Host,Db
權限字段
其他權限表
tables_priv
對單表進行權限設置
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: Db
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: User
Type: char(16)
Null: NO
Key: PRI
Default:
Extra:
*************************** 4. row ***************************
Field: Table_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 5. row ***************************
Field: Grantor
Type: char(77)
Null: NO
Key: MUL
Default:
Extra:
*************************** 6. row ***************************
Field: Timestamp
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra:
*************************** 7. row ***************************
Field: Table_priv
Type: set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view')
Null: NO
Key:
Default:
Extra:
*************************** 8. row ***************************
Field: Column_priv
Type: set('Select','Insert','Update','References')
Null: NO
Key:
Default:
Extra:
columns_priv
對單列進行權限設置
+-------------+----------------------------------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-------+
procs_priv
系統表
+--------------+----------------------------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------------------------+------+-----+-------------------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Routine_name | char(64) | NO | PRI | | |
| Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | |
| Grantor | char(77) | NO | MUL | | |
| Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | |
+--------------+----------------------------------------+------+-----+-------------------+-------+
用戶機制
用戶管理機制包括登陸退出mysql，創建用戶，修改用戶密碼，刪除用戶，為用戶賦予權限
1、登陸或退出mysql
mysql -h hostname|hostIP -p port -u username -pPassword DatabaseName -e "SQL語句"
登陸數據庫服務器，如果直接接密碼，則密碼password直接寫在-p以后，中間不能有空格
mysql -u root -p
登陸本地的數據庫，則隱藏輸入密碼
exit|quit：退出數據庫
2、創建普通用戶
root用戶的權限太大，應當嚴格杜絕使用root登陸mysql
（a）create user
create user username [identifield by [password] 'password']
[,username [identifield by [password] 'password']]
....
password關鍵字主要用來對密碼進行加密
create user qionghua identified by '123456';
（b）insert
mysql.user存儲了關于用戶賬戶的信息
insert into user(host,user,password) values('hostname','username',password('password'));
在具體創建用戶賬戶時，由于表user中字段ssl_cipher，x509_issuer,x509_subject沒有默許值，所以還需要設置這些字段的值，對password字段，1定要使用函數PASSWORD()進行加密
創建用戶成功后如果不能登陸，使用
flush privileges
刷新下權限
（c）grant
上述兩種創建用戶的方式不方便給用戶賦予權限
grant priv_type on databasename.tablename
to username[identified by [password]'password']
[,username[identified by [password]'password']]
[,username[identified by [password]'password']]
....
2、修改用戶密碼
兩種方式：通過超級權限用戶root和通過普通用戶
root用戶密碼修改
1、mysqladmin
修改root用戶密碼
mysqladmin -u username -p oldpassword "new_password"
2、set
登陸mysql服務器后，可以通過set命令修改root用戶密碼
set password=password('new password')
3、更改系統表mysql.user數據記錄修改
update user set password=password('new_password')
where user='root' and host='localhost'
普通用戶密碼修改
1、仿照創建用戶時的grant方式
grant priv_type on databasename.tablename
to username[identified by [password]'password']
2、set命令
通過root賬號登陸mysql后使用set命令
set password for 'username'@'hostname'=password("newpassword")
通過普通賬戶登陸mysql后使用set命令修改自己的密碼
set password=password("new_password")
3、更改系統表mysql.user
update user set password=password("password") where user='username' and host="localhost"
如果沒有設置主機，則不需要匹配host="localhost"
3、刪除普通用戶賬戶
1、drop user
drop user user1[,usre2,...]
2、刪除系統表mysql.user數據記錄
delete from user where user="username" and host="hostname"
權限管理
授權權限
在進行授權操作之前，需要用戶具有grant權限
grant priv_type[(column_list)] on databasename.tablename
to user [identified by [password] "password"]
[,user [identified by [password] "password"]]
...
[with with_option[with_option]....]
with_option的取值
grant option
max_queries_per_hour count
max_update_per_hour count
max_connections_per_hour count
max_user_connections count 單個用戶可以同時具有count個連接
查看權限
1、查看系統表mysql.user的數據記錄
grant select,update,create,drop on *.* to 'qionghua';
select * from mysql.user where user='qionghua'G
*************************** 1. row ***************************
Host: %
User: qionghua
Password: *00A51F3F48415C7D4E8908980D443C29C69B60C9
Select_priv: Y
Insert_priv: N
Update_priv: Y
Delete_priv: N
Create_priv: Y
Drop_priv: Y
........
后面為N的字段省略。
同這個查詢，可以看出該用戶所具有的權限
收回權限
revoke priv_type[(column_list)] on databasename.tablename
from user1 [identified by [password] "password"]
[,user2 [identified by [password] "password"]]
...
[with with_option[with_option]....]
收回全部權限
revoke all privilege,grant option
from user1 [identified by [password] "password"]
[,user2 [identified by [password] "password"]]
...
mysql> show grants for "qionghua"G
*************************** 1. row ***************************
Grants for qionghua@%: GRANT SELECT, UPDATE, CREATE, DROP ON *.* TO 'qionghua'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74
329105EE4568DDA7DC67ED2CA2AD9'
1 row in set (0.00 sec)
mysql> revoke select on *.* from "qionghua";
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for "qionghua"G
*************************** 1. row ***************************
Grants for qionghua@%: GRANT UPDATE, CREATE, DROP ON *.* TO 'qionghua'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE
4568DDA7DC67ED2CA2AD9'
1 row in set (0.00 sec)
上一篇 ArcGIS教程：3D 面要素
下一篇 szrz工具安裝
程序員人生，我編程，我富裕，記住wfuyu網，php教程，php學習，php手冊，CMS模版制作
聲明：本站大部分內容是作者原創，少部分收集于互聯網供大家一起學習，原版權很多不明，如有侵權請聯系本站，謝謝！
粵ICP備14040726號-1?? 2015-2020 程序員人生 版權所有
