Granting MySQL DB Permissions
Granting Permissions to User:
Let’s take a look at how the MySQL db engine relates to a MySQL database. The MySQL database is actually a folder on the hard disk drive. Hence, when the create database command is used MySQL creates a folder on the hard disk with the same name as the database name used in the create database command.
MySQL uses tables to store user data. Hence, when tables are created with the create table command files are created within a specific folder. Tables can only be accessed via the MySQL db engine.
Hence, MySQL provides appropriate DBA syntax that permits binding users to multiple databases so that can access and use the data held within tables which in turn reside within a specific database.
The syntax is very granular, very specific user rights can be assigned to resources held within a MySQL database.
Let’s look at an example. Earlier on, a user named prerna was created who was given complete access to the database SMS. Let’s take a look at granting specific permissions to database resources to prerna instead of blanket permissions.
mysql>GRANT SELECT, INSERT, UPDATE, DELETE ON sms.* TO prerna@localhost identified by ‘@prerna12#’;
Using this syntax, the user prerna has been granted permission to as shown in Diagram 1.
Insert data into all the tables belonging to the database SMS. This permission is granted using the attribute SMS.
Here SMS is the name of the database
.* Here * translates to a Meta character expands to represent all tables within the db SMS. The appropriate expansion of * being done by the MySQL db engine.
The period being used to separate the database name from the Meta character.
This means that the user prerna cannot:
Create a table within the SMS database
Modify the structure of a table that exists in the SMS database
And a ton of other stuff.
All that the user prerna can do is:
Select data within SMS – tables
Insert data within SMS – tables
Update data within SMS – tables
Delete data within SMS – table
That is all.
Diagram 1: Granting some permission to the user
Let’s say the MySQL administrator wishes to grant all permissions to the resources held within the SMS database to the user prerna. This is how it can be done as shown in Diagram 2:
mysql>GRANT ALL ON SMS TO prerna@localhost identified by ‘@prerna12#’;
NOTE: Do not forget to enclose the password in single quotes.
Diagram 2: Granting all permissions to the user
Now, you can login to MySQL through User as shown in Diagram 3.
Diagram 3: Logging into MySQL as User