MySQL Grant All Privileges: How to manage user privileges

January 30, 2019 / MySQL / 3 min

The primary function of the MySQL privilege system is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, DELETE etc. In general, Mysql Grant All Privileges ensures that one user has access to one or more databases and/or table.

How to grant all privileges in MySQL via cPanel?

Also, scroll down and you will see Add User to Database section. Then, select user and database, on which you want to give privileges to the selected user.

Then click on the ‘Add’ button.

Now you will be redirected to a new page as shown below:

Here you can select sufficient privileges that you want to give that user.

I give all privileges to the user here.

Then click on the ‘Make Changes’ button at the bottom of the page.

As shown in the above image, you will get a message: Success: You saved “user_name”‘s privileges on the database “database_name”.

How to grant all privileges in mysql via command line?

First of all, it is very easy to give privileges on a database to a user via command line.

mysql -u user -p

Then, use appropriate username in place of ‘user’.

Enter the password for the user when prompted.

Use the following query to give All privileges on a database to a specific user.

GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'localhost';

Next, replace ‘db_name’ and ‘user_name’ with appropriate values.

I will show an example below:

mysql> GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

The above command is to give all privileges on the database ‘test’ to the user ‘test’.

We can see granted privileges using the below query:

SHOW GRANTS FOR 'user_name'@'localhost';

Replace ‘user_name’ with your value.

The grants of the user ‘test’ are shown below:

How to grant specific privileges via command line?

This is the general syntax of the query. It actually provides specific privileges on a database to a user:

GRANT specific_permission ON db_name.* TO ‘user_name’@'localhost’;

Using the above command you give that user specific privileges to all tables of that specific database. To give specific permission on only a table of the database you can use the following example.

GRANT specific_permission ON db_name.tbl_name TO ‘user_name’@'localhost’;

Replace ‘specific_permission’ with the type of privilege you want to give. And, ‘db_name’ with your database name and ‘user_name’ with your MySQL username.

Some common privileges are below. These can be in place of ‘specific_permission’ in the above commands’:

CREATE – allows the user to create databases and tables
DROP – allows the user to drop databases and tables
DELETE – allows the user to delete rows from specific MySQL table
INSERT – allows the user to insert rows into a specific MySQL table
SELECT – allows the user to read the database
UPDATE – allows the user to update table rows

“mysql.users” TABLE

The ‘mysql.users’ table contains information about users that have permission to access the MySQL server and their global privileges. The table can be queried and although it is possible to directly update it. For this reason, it is best to use CREATE USER and GRANT for adding users and privileges. We suggest this other article about managing mysql users.

PRIVILEGES in MySQL

ALL, ALL PRIVILEGES – These privilege specifiers are shorthand for “all privileges available at a given privilege level” (except GRANT OPTION).
CREATE – Enables use of statements that create new databases and tables.
INSERT – Enables rows to be inserted into tables in a database.
ALTER – Enables use of the ALTER_TABLE statement to change the structure of tables. ALTER_TABLE also requires the CREATE and INSERT privileges.
DELETE – Enables rows to be deleted from tables in a database.
DROP – Enables use of statements that drop (remove) existing databases, tables, and views.
SELECT -Enables rows to be selected from tables in a database. SELECT statements require the SELECT privilege only if they actually access tables.
SHOW DATABASES – Enables the account to see database names by issuing the SHOW DATABASE statement. Accounts that do not have this privilege see only databases for which they have some privileges. This way, you cannot use the statement at all if the server was started with the –skip-show-database option.
UPDATE – Enables rows to be updated in tables in a database.

GRANT OPTION

In summary, it enables you to grant to or revoke from other users those privileges that you yourself possess. That means you can give or remove privileges that you have, from other users with GRANT statement. Also, to give a user GRANT OPTION privilege we have to specify WITH GRANT OPTION at the end of the GRANT query.

The syntax is:

GRANT ALL ON db_name.* TO 'user_name'@'localhost' WITH GRANT OPTION;

Was this helpful?

Thanks for your feedback!