MySQL Terminal: Login, Users and Permissions

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 replace localhost with % 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 PRIVILEGES command 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.