How to Change a MySQL Privilege Table
There will be times when multiple users need to access the same database, at which point you will need to change the MySQL privilege table. Depending on user needs, you may provide limited or full access to the database. MySQL provides a mechanism to implement that type of change in the privilege table using the GRANT command.
Other People Are Reading
Instructions
-
Change a MySQL Privilege Table
-
1
Log in as root or another admin user. To use the GRANT command in MySQL, it's necessary to be root or another privileged user. From the command-line MySQL client, connect to the database as root. Example:
mysql -u root -h localhost -
2
Replace localhost with your database host, unless you are running the MySQL server on the same computer you are using. Otherwise, use the hostname of your MySQL server. Example:
mysql -u root -h localhost -p -
Grant 'Read' Privileges
-
3
Grant read privileges to a user if she doesn't need to change any data. For example, if the marketing department wants access to your customer database, you could give them read-only access to the customer database.
-
4
Accomplish this with the three-part GRANT command from the root user. To change privileges, GRANT needs to command who has access privileges to which databases and tables, and what passwords they should use to login. In this example, the GRANT command grants permission to use the SELECT command on all tables in the customers_db database. It gives permission to the user 'marketing' logging in from 'localhost,' who is identified by the password 'some_password'. Example:
GRANT SELECT ON customers_db.*
TO 'marketing'@'localhost'
IDENTIFIED BY 'some_password';
Grant 'Write' Privileges
-
5
Grant write privileges to a user. This command takes the same form as granting read privileges but adds more commands with SELECT.
-
6
Identify what type of privileges the user will need. Here the command grants the accounting department permission to INSERT and UPDATE, as well as SELECT data from the customer databases. Example:
GRANT SELECT,INSERT,UPDATE ON customers_db.*
TO 'accounting'@'localhost'
IDENTIFIED BY 'some_password';
Grant 'Admin' Privileges
-
7
Grant admin privileges to a user. This command grants permissions to run all commands.
-
8
Use this command to give other admins full control over a database. Example:
GRANT ALL PRIVILEGES ON customers_db.*
TO 'admin'@'localhost'
IDENTIFIED BY 'some_password'
-
1
Tips & Warnings
On a default install, there is no password on the root user. Once one is set, you'll have to use the -p switch, as well.
Limiting user access guards against human error, faulty software and malicious users. This means that novice users or a software bug can't accidentally delete all the data. If someone gains control of a minor account, they can't do much damage.
