Database
Mysql Tutorial
Detailed introduction to the sample code of mysql5.7 to create user authorization, delete user and revoke authorization
Detailed introduction to the sample code of mysql5.7 to create user authorization, delete user and revoke authorization
This article mainly introducesmysql5.7 Creating user authorizationDeletingThe method of user revoking authorization is very good and has reference value. Friends who need it can refer to it
1. Create user:
Command:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
Description: username - the username you will create, host - specify the On which host can the user log in? If it is a local user, localhost can be used. If you want the user to log in from any remote host, you can use the wildcard character %. password - the user's login password. The password can be empty. , if it is empty, the user can log in to the server without a password.
Example:
CREATE USER 'dog'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'pig'@'192.168.1.100' IDENDIFIED BY 'password';
CREATE USER 'pig'@'192.168.1.%' IDENDIFIED BY 'password';
CREATE USER 'pig'@'%' IDENTIFIED BY 'password';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';2, Authorization:
Command:
GRANT privileges ON databasename.tablename TO 'username'@'host'
Description: privileges - user’s operation permissions, such as SELECT, INSERT, UPDATE, etc. (see the end of this article for a detailed list). If you want to grant all permissions, then Use ALL.;databasename - database name, tablename - table name. If you want to grant the user corresponding operation permissions on all databases and tables, you can use * to indicate it, such as *.*.
Example:
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';Note: A user authorized with the above command cannot authorize other users. If you want the user to be able to authorize, use the following command:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
Permission information Tables with user, db, host, tables_priv and columns_priv are stored in MySQL database (that is, in a database named mysql).
Permission column Context
select Select_priv table
insert Insert_priv table
update Update_priv table
delete Delete_priv table
index Index_priv table
alter Alter_priv table
create Create_priv database, table or index
drop Drop_priv database or table
grant Grant_priv database or table
references References_priv database or table
reload Reload_priv server management
shutdown Shutdown_priv server management
process Process_priv server management
file File_priv file access on the server
3. Set and change user password
Command:
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');如果是当前登陆用户用
SET PASSWORD = PASSWORD("newpassword");Example:
SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");4. Revoke user permissions
Command:
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
Description: privilege, databasename, tablename - same Authorization part.
Example:
REVOKE SELECT ON *.* FROM 'pig'@'%';
Note: If you authorize user 'pig'@'%' like this (or similar): GRANT SELECT ON test.user TO 'pig'@'%', then using the REVOKE SELECT ON *.* FROM 'pig'@'%'; command cannot revoke the user's access to the user in the test database SELECT operation of the table. On the contrary, if the authorization is GRANT SELECT ON *.* TO 'pig'@'%'; then REVOKE SELECT ON test.user FROM 'pig'@'% ';The command cannot revoke the user's Select permission on the user table in the test database.
Detailed information can be viewed with the command SHOW GRANTS FOR 'pig'@'%'; .
##5. Delete user
Command:DROP USER 'username'@'host';
6 View user authorization
mysql> show grants for 'test01'@'localhost'; +--------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for test01@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test01'@'localhost' | | GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test001`.* TO 'test01'@'localhost' | +--------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.01 sec) mysql> show grants for 'test02'@'localhost'; +-------------------------------------------------------------+ | Grants for test02@localhost | +-------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test02'@'localhost' | | GRANT ALL PRIVILEGES ON `test001`.* TO 'test02'@'localhost' | +-------------------------------------------------------------+ 2 rows in set (0.00 sec)
The above is the detailed content of Detailed introduction to the sample code of mysql5.7 to create user authorization, delete user and revoke authorization. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
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
How to use Xiaohongshu account to find users? Can I find my mobile phone number?
Mar 22, 2024 am 08:40 AM
With the rapid development of social media, Xiaohongshu has become one of the most popular social platforms. Users can create a Xiaohongshu account to show their personal identity and communicate and interact with other users. If you need to find a user’s Xiaohongshu number, you can follow these simple steps. 1. How to use Xiaohongshu account to find users? 1. Open the Xiaohongshu APP, click the "Discover" button in the lower right corner, and then select the "Notes" option. 2. In the note list, find the note posted by the user you want to find. Click to enter the note details page. 3. On the note details page, click the "Follow" button below the user's avatar to enter the user's personal homepage. 4. In the upper right corner of the user's personal homepage, click the three-dot button and select "Personal Information"
Local users and groups are missing on Windows 11: How to add it
Sep 22, 2023 am 08:41 AM
The Local Users and Groups utility is built into Computer Management and can be accessed from the console or independently. However, some users find that local users and groups are missing in Windows 11. For some people who have access to it, the message suggests that this snap-in may not work with this version of Windows 10. To manage user accounts for this computer, use the User Accounts tool in Control Panel. The issue has been reported in previous iterations of Windows 10 and is usually caused by issues or oversights on the user's side. Why are local users and groups missing in Windows 11? You are running Windows Home edition, local users and groups are available on Professional edition and above. Activity
Log in to Ubuntu as superuser
Mar 20, 2024 am 10:55 AM
In Ubuntu systems, the root user is usually disabled. To activate the root user, you can use the passwd command to set a password and then use the su- command to log in as root. The root user is a user with unrestricted system administrative rights. He has permissions to access and modify files, user management, software installation and removal, and system configuration changes. There are obvious differences between the root user and ordinary users. The root user has the highest authority and broader control rights in the system. The root user can execute important system commands and edit system files, which ordinary users cannot do. In this guide, I'll explore the Ubuntu root user, how to log in as root, and how it differs from a normal user. Notice
Explore Windows 11 guide: How to access user folders on your old hard drive
Sep 27, 2023 am 10:17 AM
Certain folders are not always accessible due to permissions, and in today’s guide we will show you how to access user folders on your old hard drive on Windows 11. The process is simple but can take a while, sometimes even hours, depending on the size of the drive, so be extra patient and follow the instructions in this guide closely. Why can't I access my user folders on my old hard drive? User folders are owned by another computer, so you cannot modify them. You don't have any permissions on the folder other than ownership. How to open user files on old hard drive? 1. Take ownership of the folder and change permissions Find the old user directory, right-click on it and select Properties. Navigate to "An
Tutorial: How to delete a normal user account in Ubuntu system?
Jan 02, 2024 pm 12:34 PM
Many users have been added to the Ubuntu system. I want to delete the users that are no longer in use. How to delete them? Let’s take a look at the detailed tutorial below. 1. Open the terminal command line and use the userdel command to delete the specified user. Be sure to add the sudo permission command, as shown in the figure below. 2. When deleting, be sure to be in the administrator directory. Ordinary users do not have this permission. , as shown in the figure below 3. After the delete command is executed, how to judge whether it has been truly deleted? Next we use the cat command to open the passwd file, as shown in the figure below 4. We see that the deleted user information is no longer in the passwd file, which proves that the user has been deleted, as shown in the figure below 5. Then we enter the home file
How to upgrade win10 enterprise version 2016 long-term service version to professional version
Jan 03, 2024 pm 11:26 PM
When we no longer want to continue using the current Win10 Enterprise Edition 2016 Long-Term Service Edition, we can choose to switch to the Professional Edition. The method is also very simple. We only need to change some contents and install the system image. How to change win10 enterprise version 2016 long-term service version to professional version 1. Press win+R, and then enter "regedit" 2. Paste the following path directly in the address bar above: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WindowsNT\CurrentVersion3 , then find the EditionID and replace the content with "professional" to confirm
Windows 11 KB5031455 fails to install, causing other issues for some users
Nov 01, 2023 am 08:17 AM
Microsoft began rolling out KB2 to the public as an optional update for Windows 503145511H22 or later. This is the first update to enable Windows 11 Moment 4 features by default, including Windows Copilot in supported areas, preview support for items in the Start menu, ungrouping of the taskbar, and more. Additionally, it fixes several Windows 11 bugs, including potential performance issues that caused memory leaks. But ironically, the optional update for September 2023 will be a disaster for users trying to install the update, or even for those who have already installed it. Many users will not install this Wi
What is sudo and why is it important?
Feb 21, 2024 pm 07:01 PM
sudo (superuser execution) is a key command in Linux and Unix systems that allows ordinary users to run specific commands with root privileges. The function of sudo is mainly reflected in the following aspects: Providing permission control: sudo achieves strict control over system resources and sensitive operations by authorizing users to temporarily obtain superuser permissions. Ordinary users can only obtain temporary privileges through sudo when needed, and do not need to log in as superuser all the time. Improved security: By using sudo, you can avoid using the root account during routine operations. Using the root account for all operations may lead to unexpected system damage, as any mistaken or careless operation will have full permissions. and
