{"id":790,"date":"2019-01-30T07:08:03","date_gmt":"2019-01-30T07:08:03","guid":{"rendered":"https:\/\/www.copahost.com\/blog\/?p=790"},"modified":"2020-05-31T14:12:32","modified_gmt":"2020-05-31T14:12:32","slug":"mysql-grant-all-privileges-how-to-manage-user-privileges","status":"publish","type":"post","link":"https:\/\/www.copahost.com\/blog\/mysql-grant-all-privileges-how-to-manage-user-privileges\/","title":{"rendered":"MySQL Grant All Privileges: How to manage user privileges"},"content":{"rendered":"

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.<\/p>\n

How to grant all privileges in MySQL via cPanel?<\/h3>\n

Log in to your cPanel account and then on Databases section select MySQL Databases.
\n<\/strong><\/p>\n

Also, scroll down and you will see Add User to Database section.\u00a0 Then, select user and database, on which you want to give privileges to the selected user.<\/p>\n

\"\"<\/p>\n

Then click on the ‘Add’ button.<\/p>\n

Now you will be redirected to a new page as shown below:<\/p>\n

\"\"<\/p>\n

Here you can select sufficient privileges that you want to give that user.<\/p>\n

I give all privileges to the user here.<\/p>\n

\"\"<\/p>\n

Then click on the ‘Make Changes’ button at the bottom of the page.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

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

How to grant all privileges in mysql via command line?<\/h3>\n

First of all, it is very easy to give privileges on a database to a user via command line.<\/p>\n

Login to MySQL<\/a> using the following command:<\/p>\n

mysql -u user<\/a> -p<\/pre>\n
Then, use appropriate username in place of ‘user’.<\/p>\n
Enter the password for the user when prompted.<\/p>\n
Use the following query to give All privileges on a database to a specific user.<\/p>\n
GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'localhost';<\/pre>\n
Next, replace ‘db_name’ and ‘user_name’ with appropriate values.<\/p>\n
I will show an example below:<\/p>\n
mysql> GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost';\r\nQuery OK, 0 rows affected (0.00 sec)<\/pre>\n
The above command is to give all privileges on the database ‘test’ to the user ‘test’.<\/p>\n
We can see granted privileges using the below query:<\/p>\n
SHOW GRANTS FOR 'user_name'@'localhost';<\/pre>\n
Replace ‘user_name’ with your value.<\/p>\n
The grants of the user ‘test’ are shown below:<\/p>\n
\"\"<\/p>\n
How to grant specific privileges via command line?<\/h3>\n
This is the general syntax of the query. It actually provides specific privileges on a database to a user:<\/p>\n
GRANT specific_permission ON db_name.* TO \u2018user_name\u2019@'localhost\u2019;<\/pre>\n
Using the above command you give that user specific privileges to all tables<\/strong> of that specific database. To give specific permission on only a table<\/strong> of the database you can use the following example.<\/p>\n
GRANT specific_permission ON db_name.tbl_name TO \u2018user_name\u2019@'localhost\u2019;<\/pre>\n
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.<\/p>\n
Some\u00a0common privileges are below. These can be in place of ‘specific_permission’ in the above commands’:<\/p>\n
 <\/p>\n