This content originally appeared on DEV Community and was authored by Antonio Silva
Access MySQL
sudo mysql -u root -p
The sudo mysql -u root -p command is used to access MySQL as the root user with administrative privileges. After running the command, you will be prompted to enter the MySQL root user password.
-
sudo: Runs the command with superuser privileges. -
mysql: Starts the MySQL client. -
-u root: Specifies that you are connecting as the "root" user. -
-p: Requests MySQL to ask for the password of the specified user.
If you have not set a password for the MySQL root user, the command may fail. If this is the case, you can either set a password or access MySQL without the -p (no password) option.
Create a new user
The SQL CREATE USER command is used to create a new user in MySQL with a username and password.
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
-
username: The name of the new user you are creating. -
localhost: Specifies that the user will only be able to connect to MySQL from the server where MySQL is running. If you want to allow remote connections, you can replacelocalhostwith%or a specific IP address. -
password: The password that will be associated with this user.
After creating the user, you need to grant permissions to it.
Grant permissions to the user
- Grant All Privileges for a Database
If you want to grant all permissions for a specific database, use:
GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'localhost';
- Specific Permissions
You can also specify permissions, such as SELECT, INSERT, UPDATE, DELETE, etc.
GRANT permission ON database_name.* TO 'user_name'@'localhost';
- Permissions on a Specific Table
To grant permissions only on a specific table
GRANT ALL PRIVILEGES ON database_name.table_name TO 'user_name'@'localhost';
- Grant Global Permissions
To grant permissions across all databases
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost';
- Allow the user to grant permissions to other users
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' WITH GRANT OPTION;
Update privileges
The FLUSH PRIVILEGES command is used in MySQL to reload the permission tables, making effective the changes you made to user permissions, whether with the GRANT, REVOKE, or CREATE USER command.
FLUSH PRIVILEGES;
Check Permissions
SHOW GRANTS FOR 'username'@'localhost';
The SHOW GRANTS FOR username'@'localhost; command displays the permissions associated with the specified user in MySQL. It is useful for checking the privileges a user has over the database.
Revoke Permissions
The REVOKE command is used to remove specific privileges from a user in MySQL.
REVOKE ALL PRIVILEGES ON database_name.* FROM 'user_name'@'localhost';
- After revoking privileges, the user will still exist, but without the permissions in the specified database.
- It is recommended to run the
FLUSH PRIVILEGEScommand after revoking privileges to ensure that the changes are applied immediately.
List users
SELECT User, Host FROM mysql.user;
The SELECT User, Host FROM mysql.user; command is used to query the mysql.user table in MySQL, which stores information about all users created in the system.
Know which user is connected
SELECT USER();
The SELECT USER(); command in MySQL returns the username and hostname you are using in the current session. It is a function that shows which user account was used to connect to the database, in the format user@host.
This content originally appeared on DEV Community and was authored by Antonio Silva
Antonio Silva | Sciencx (2024-10-17T19:54:09+00:00) MySQL Terminal: Login, Users and Permissions. Retrieved from https://www.scien.cx/2024/10/17/mysql-terminal-login-users-and-permissions/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.