Summary
In the previous article we have learned how to create a user in mysql and in this article we will learn how to grant all privileges on database in mysql.
Start to grant mysql privileges first we need to login our mysql server using the below command.
$mysql -u root -h localhost -p
Here -u switch indicates the user name and -h switch denotes the hostname and -p indicates the password.
How to grant all privileges on database
We can restrict a user to operate by grant all privileges on database. Account privileges is very important task for a database admin to restrict a user to operate on database.Accidentally normal user can delete any records from the database. So a DBA should specify privileges to a normal user.
Case 1) suppose we have a normal database user techrideradmin and we want to give him full privileges by which this user can do any operational tasks on all databases. For this we will use grant all privileges statement.operational task means he or she can do any DML,DDL operation like insert,update ,delete any records from any database.
To set privileges on database we need to execute the below command.
mysql> grant all privileges on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';
Query OK, 0 rows affected (0.00 sec)
In the above command first * denotes that List of all databaes and the second * denotes list of all table name for all databases . we are using * that means this user has full privileges (can access all databases and tables).
Localhost identified by password means we create mysql user in local server and we set password using identified by statement.
After executing above command we need to execute flush privileges command.
mysql>FLUSH PRIVILEGES;
Case 2) suppose we have one database that is techrideradmin and have one table on this database that is student . If we want to give permission for the particular database and table we need to execute the below command.
mysql> grant all privileges on techrideradmin.student to 'techrideradmin'@'localhost' identified by 'newpassword';
Query OK, 0 rows affected (0.01 sec)
Here we have grant all privileges on database techrideradmin on student table for techrideradmin user.
Case 3) If we want to allow this user for the remote host we can use the below command. Suppose my server ip address 192.168.137.5 and my workstation ip address 192.168.137.1 . we want to access the database using my workstation for do this we need to execute the below command.
mysql> grant all privileges on techrideradmin.student to 'techrideradmin'@'192.168.137.5' identified by 'newpassword';
Query OK, 0 rows affected (0.00 sec)
There are different type of privileges
1)ALL PRIVILEGES----Permit full permission to user.
mysql> grant all privileges on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';
From the above query we have learned how to grant all privileges on database for all objects.
2)CREATE--------------Give permission to create database and table.
mysql> grant create on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';
From the above query we have learned how to grant create privileges to create database and table of the existing database.
3)SELECT--------------Give permission to view the details of the table data.
mysql> grant select on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';
From the above query we have learned how to create a readonly user in mysql and how to grant select privileges to view of all table data of any databases.
4)UPDATE--------------Give permission to update the data into the table
mysql> grant update on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';
From the above query we have learned how to grant update privileges to update data into all tables of all databases.
5)INSERT---------------Give permission to insert data into table.
mysql> grant insert on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';
From the above query we have learned how to grant insert privileges to insert data into all tables of all databases.
6)DELETE---------------Give permission to delete data from table.
mysql> grant delete on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';
From the above query we have learned how to grant delete privileges to delete data into all tables of all databases.
7)DROP-----------------Give permission to drop table and database.
mysql> grant drop on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';
From the above query we have learned how to grant drop privileges to drop any tables and databases.
Show grants for mysql user
Show grants command is used to find all provided grant information for a user.Here my username is techrideradmin and hostname is localhost so to view all grant info for that user using below command.
mysql> show grants for ‘techrideradmin’@’localhost’;
How to revoke privileges for MySQL user
If a user has leave from any organozation we need to revoke his or her privileges. To revoke the privileges for the mysql user we need to execute the below command.
mysql> revoke all privileges on *.* from 'techrideradmin'@'localhost' identified by 'newpassword';
Query OK, 0 rows affected (0.00 sec)
After executing the above command all privileges has been revoked from this user.
Conclusion
In this article we have learned how to grant all privileges on database in mysql.
0 comments:
Post a Comment