国产av日韩一区二区三区精品,成人性爱视频在线观看,国产,欧美,日韩,一区,www.成色av久久成人,2222eeee成人天堂

Home Database Mysql Tutorial MYSQL教程:MySQL用戶帳號管理_MySQL

MYSQL教程:MySQL用戶帳號管理_MySQL

Jun 01, 2016 pm 01:58 PM
Tutorial database data sheet user

????? 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帳號的資源限制

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undress AI Tool

Undress AI Tool

Undress images for free

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

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

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

In summer, you must try shooting a rainbow In summer, you must try shooting a rainbow Jul 21, 2024 pm 05:16 PM

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: Simple Concepts for Easy Learning MySQL: Simple Concepts for Easy Learning Apr 10, 2025 am 09:29 AM

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's Role in the Business World Oracle's Role in the Business World Apr 23, 2025 am 12:01 AM

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: An Introduction to the World's Most Popular Database MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

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 vs. Other Databases: Comparing the Options MySQL vs. Other Databases: Comparing the Options Apr 15, 2025 am 12:08 AM

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: A Beginner-Friendly Approach to Data Storage MySQL: A Beginner-Friendly Approach to Data Storage Apr 17, 2025 am 12:21 AM

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.

Why Use MySQL? Benefits and Advantages Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

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: Structured Data and Relational Databases MySQL: Structured Data and Relational Databases Apr 18, 2025 am 12:22 AM

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.

See all articles