Before we get into doing queries, we need to add a regular user with less permissions than root. Why do we want this? We don't want web sites having access to root permissions. In the security world, we want to give the website the least amount of privileges we can give it to do their job.

You can think of it like this: We wouldn't give the person in the mail room access to the main bank account of the company. We only give financial people access to that. However, we don't give access to the financial people to the mail room. Each situation requires it's own unique access to various functions.

In MySQL, we'll emulate that function.

To create MySQL users, we need to first log in as root. If you have the ~/.bashrc my alias set up correctly, just type:

my

Otherwise, type:

mysql -u root -p
Enter password:

The first thing I like to do is open a notepad and do my user creation statements on paper first. Basically, you'll want to copy the next steps into a notepad and customize it to your needs.

Before we can add a new user, we need to create a new database. The user is assigned to that database and won't have access to any other databases.

create database [database name]

[database name] is any name. You'll want to name it something that is associated with your project. For example, you wouldn't want to call a database "wordpress". You'd instead want to call it something like, "personalblog".

To create a MySQL user, we need to use the create user statement and then we'll use the grant statement.

create user is what adds the user to the system.

grant then gives that new user specific permissions.

So, to do a complete new user, we'll execute the following MySQL commands:

create user '[username]'@'[ip or domain]' identified by '[password]';
grant [permissions] on [database].* to '[username]'@'[ip or domain]';

[username] - the new username for this account. can be anything like, ashley, user1, etc.

[ip or domain] - the ip address or domain that the user is connecting from.

[password] - A 32 character password.

[permissions] - a comma separated list of permissions that the user can use.

[database] - The name of the database that we created ealier.

Let's create a database and a new user:

create database employees;
create user 'bobrocker'@'localhost' identified by '7PzoBROs7D1zwW6Qna3mvWNDMIwktCBe';
grant select, insert, update, delete on employees.* to 'bobrocker'@'localhost';

This will give bobrocker access to the employees database. Bob's new password will be 7PzoBROs7D1zwW6Qna3mvWNDMIwktCBe. Bob can only execute select, insert, update, and delete statements within MySQL.