Accessing MySQL on VB.NET using MySQL Connector/Net, Part 4: Create & Grant MySQL User Account

Create & Grant MySQL User Account

By default, the root account on MySQL Server has all privileges on every tables on MySQL Server but only localhost can have accessed (remote access is not allowed) and it is recommend to use other user account rather than root account to perform operations on MySQL Server (for security issue). Therefore, you should create a new user account on MySQL and grant at least privileges for the account as possible.

You can see index of this series at Accessing MySQL on VB.NET using MySQL Connector/Net, Part I: IntroductionThis post, I’m going to show how to create a new user account “worldUser” on MySQL Server and grant privileges to the user account. Mostly usage privileges are SELECT, INSERT, DELETE and UPDATE. Also, I’ll allow remote connection from any host so that I can develop an application from remote PC to the database PC.
Note: If you are in development in a single environment, it’s OK to use root account. But Don’t in production.

Create New MySQL User Account

1. On the Database PC, connect to MySQL Server. Open Command-line and type

   mysql -u root -p

   mysql -u root -p

   It’ll ask for the password. Type root’s password.
   

2. To Create a User Account on MySQL Server, use this format:

   CREATE USER username IDENTIFIED BY 'password'

   CREATE USER username IDENTIFIED BY 'password'

3. I’ll create MySQL User Account “worldUser” with password “worldpassword”. I use this account to connect to MySQL Server from remotely PC in later post.

   CREATE USER worldUser IDENTIFIED BY 'worldpassword';

   CREATE USER worldUser IDENTIFIED BY 'worldpassword';

   

Grant Privileges on User Account

1. To grant privileges on MySQL User Account, use this format:

   GRANT privileges ON database.table TO 'username'@'host'

   GRANT privileges ON database.table TO 'username'@'host'

2. I’m going to grant privileges on “worldUser” to allow SELECT, INSERT, UPDATE and DELETE on world database from any machine.

   GRANT SELECT,INSERT,UPDATE,DELETE ON world.* TO 'worldUser'@'%';

   GRANT SELECT,INSERT,UPDATE,DELETE ON world.* TO 'worldUser'@'%';

   

3. Another grant example. Grant SELECT privilege to mysql.proc on the certain user. This is not required to run, just an example.

   GRANT SELECT ON mysql.proc TO 'worldUser'@'%';

   GRANT SELECT ON mysql.proc TO 'worldUser'@'%';