MYSQL教程:MySQL用戶帳號管理_MySQL
Jun 01, 2016 pm 01:58 PM????? MySQL用戶帳號管理主要用grant(授權)和revoke(撤權)兩個SQL指令來管理。這兩個指令實質是通過操作user(連接權限和全局權限)、db(數(shù)據(jù)庫級權限)、tables_priv(數(shù)據(jù)表級權限)、columns_priv(數(shù)據(jù)列級權限)四個權限表來分配權限的。host權限表不受這兩個指令影響。下面將會詳細介紹用戶權限管理的內容。
GRANT語法說明:
GRANT privileges (columns)????????? #privileges表示授予的權限,columns表示作用的列(可選)
????? ON what?????????????????????? #設置權限級別,全局級、數(shù)據(jù)庫級、數(shù)據(jù)表級和數(shù)據(jù)列級
????? TO account??????????????????? #權限授予的用戶,用"user_name"@"host_name"這種用戶名、主機名格式
????? IDENTIFIED BY 'password'????? #設置用戶帳號密碼
????? REQUIRE encryption requirements?????? #設置經由SSL連接帳號
????? WITH grant or resource management options;?? #設置帳號的管理和資源(連接服務器次數(shù)或查詢次數(shù)等)選項
示例:
mysql>grant all on db.* to 'test'@'localhost' identified by 'test';
上例運行后的效果是,test用戶只能通過‘test’密碼從本機訪問db數(shù)據(jù)庫
mysql>grant all on db.* to 'test'@'%' identified by 'test';
上例運行后的效果是,test用戶可通過‘test’密碼從任意計算機上訪問db數(shù)據(jù)庫?!?’代表任意字符,‘_’代表一個任意字符。主機名部份還可以是IP地址。
???
如果沒有給定主機部份,則默認為任意主機,也就是'test'和'test'@'%'是等價的。
Table 4.1. 訪問權限表
權限 權限說明
CREATE TEMPORARY TABLES 創(chuàng)建臨時數(shù)據(jù)表
EXECUTE 執(zhí)行存儲過程(暫不支持)
FILE 操作系統(tǒng)文件
GRANT OPTION 可把本帳號的權限授予其它用戶
LOCK TABLES 鎖定指定數(shù)據(jù)表
PROCESS 查看運行著的線程信息
RELOAD 重新加載權限表或刷新日志及緩沖區(qū)
REPLICATION CLIENT 可查詢主/從服務器主機名
REPLICATION SLAVE 運行一個鏡像從服務器
SHOW DATABASES 可運行SHOW DATABASES指令
SHUTDOWN 關閉數(shù)據(jù)庫服務器
SUPER 可用kill終止線程以及進行超級用戶操作
???
ALTER 可修改表和索引的結構
CREATE 創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表
DELETE 刪除數(shù)據(jù)表中的數(shù)據(jù)行
DROP 刪除數(shù)據(jù)表和數(shù)據(jù)行
INDEX 建立或刪除索引
INSERT 插入數(shù)據(jù)行
REFERENCES (暫時不支持)
SELECT 查詢數(shù)據(jù)行
UPDATE 更新數(shù)據(jù)行
???
ALL 所有權限,但不包括GRANT。
USAGE 無權限權限
Table 4.2. 權限作用范圍(由ON子句設置)
權限限定符 作用范圍
ON *.* 全局級權限,作用于所有數(shù)據(jù)庫
ON * 全局級權限,若未指定默認數(shù)據(jù)庫,其作用范圍是所有數(shù)據(jù)庫,否則,其作用范圍是當前數(shù)據(jù)庫
ON db_name.* 數(shù)據(jù)庫級權限,作用于指定數(shù)據(jù)庫里的所有數(shù)據(jù)表
ON db_name.tbl_name 數(shù)據(jù)表級權限,作用于數(shù)據(jù)表里的所有數(shù)據(jù)列
ON tbl_name 數(shù)據(jù)表級權限,作用于默認數(shù)據(jù)庫中指定的數(shù)據(jù)表里的所有數(shù)據(jù)列
USAGE權限的用法:修改與權限無關的帳戶項,如:
mysql>GRANT USAGE ON *.* TO account IDENTIFIED BY 'new_password';???? #修改密碼
mysql>GRANT USAGE ON *.* TO account REQUIRE SSL;????????????????????? #啟用SSL連接
mysql>GRANT USAGE ON *.* TO account WITH MAX_CONNECTIONS_PER_HOUR 10; #設置資源
擁有WITH GRANT OPTION權限的用戶可把自已所擁用的權限轉授給其他用戶,如:
mysql>GRANT ALL ON db.* TO 'test'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
這樣test用戶就有權把該權限授予其他用戶。
限制資源使用,如:
mysql>GRANT ALL ON db.* TO account IDENTIFIED BY 'password' WITH MAX_CONNECTIONS_PER_HOUR 10 MAX_QUERIES_PER_HOUR 200 MAX_UPDATES_PER_HOUR 50;
允許account用戶每小時最多連接20次服務器,每小時最多發(fā)出200條查詢命令(其中更新命令最多為50條)
默認都是零值,即沒有限制。FLUSH USER_RESOURCES和FLUSH PRIVILEGES可對資源限制計數(shù)器清零。
REVOKE語法說明:
mysql>REVOKE privileges (columns) ON what FROM account;
示例:
mysql>REVOKE SELECT ON db.* FROM 'test'@'localhost';
刪除test帳號從本機查詢db數(shù)據(jù)庫的權限
REVOKE可刪除權限,但不能刪除帳號,即使帳號已沒有任何權限。所以user數(shù)據(jù)表里還會有該帳號的記錄,要徹底刪除帳號,需用DELETE命令刪除user數(shù)據(jù)表的記錄,如:
% mysql -u root -p
mysql>use mysql
mysql>DELETE FROM user where User='test' and Host='localhost';
mysql fulsh privileges;
REVOKE不能刪除REQUIRE和資源占用的配置。他們是要用GRANT來刪除的,如:
GRANT USAGE ON *.* TO account REQUIRE NONE;????? #刪除account帳號的SSL連接選項
GRANT USAGE ON *.* TO account WITH MAX_CONNECTIONS_PER_HOUR 0 MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0;?????? #刪除account帳號的資源限制

Hot AI Tools

Undress AI Tool
Undress images for free

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics











After rain in summer, you can often see a beautiful and magical special weather scene - rainbow. This is also a rare scene that can be encountered in photography, and it is very photogenic. There are several conditions for a rainbow to appear: first, there are enough water droplets in the air, and second, the sun shines at a low angle. Therefore, it is easiest to see a rainbow in the afternoon after the rain has cleared up. However, the formation of a rainbow is greatly affected by weather, light and other conditions, so it generally only lasts for a short period of time, and the best viewing and shooting time is even shorter. So when you encounter a rainbow, how can you properly record it and photograph it with quality? 1. Look for rainbows. In addition to the conditions mentioned above, rainbows usually appear in the direction of sunlight, that is, if the sun shines from west to east, rainbows are more likely to appear in the east.

MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

Oracle is not only a database company, but also a leader in cloud computing and ERP systems. 1. Oracle provides comprehensive solutions from database to cloud services and ERP systems. 2. OracleCloud challenges AWS and Azure, providing IaaS, PaaS and SaaS services. 3. Oracle's ERP systems such as E-BusinessSuite and FusionApplications help enterprises optimize operations.

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.

MySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.
