A Live Developer Journal

SQL protecting your assets

Notes based on Head First SQL

You can prevent other people from inserting or updating something incorrectly or from deleting the wrong data.

Protect the root user account

By default, the first user - the root user - has complete control over everything in the database. This is important because the root user needs to be able to create user accounts for all other users. We don't want to limit what they can do but we do want to give them a password. We can do this with the following command:

SET PASSWORD FOR 'root'@'localhost = PASSWORD('b4dc10wnz');

In the command above, the username of our root user is 'root', 'localhost' indicates that this is where the SQL software is installed and running, and the password inside the parentheses is the password we chose for our root user.

Localhost means that the computer you are using to run your queries is the same computer that your SQL RDBMS is installed on. localhost is the default value for this parameter, so it is optional.

If you are using an SQL client on a machine somewhere else (remote access), then you will have to tell the query where the computer is. You can do that with an IP address or a hostname instead of localhost. If your SQL software was installed on a machine called unicorn on the rainbows network, you might use something like root@unicorn.rainbows.com

Adding a new user

SQL stares information about users in a table. SQL keeps a database of data about itself, including user ids, usernames, passwords and what each user is allowed to do in each database.


CREATE USER elsie
IDENTIFIED BY 'c13v3rp4s5w0rd';

New users by default have no permissions to do anything.

We could have restricted Elsie from certain tables at the same time that we created her as a user. However, sometimes we don't know exactly what access we need to grant from the very beginning. So we can do one thing at a time. We can grant the specific access they need, and then modify that access later if needed.

Deciding what the user needs

When we create a user for the first time, they have no permission to do anything. We have to use a GRANT statement to give them permission to even SELECT from a table.


GRANT SELECT ON
clown_info
TO elsie;

In the command above, we are granting elsie the permission to SELECT from the clown_info table

Elsie also needs SELECT permission on the other clown-tracking tables so that she can use joins and subqueries in here SELECT statements. So we need seperate GRANT permission statements for each of the child tables too.


GRANT SELECT ON activities TO elsie;
GRANT SELECT ON location TO elsie;
GRANT SELECT ON info_activities TO elsie;
GRANT SELECT ON info_location TO elsie;

GRANT variations

Revoke priviliges

The revoke syntab is almost identical to the GRANT statement. Instead of GRANT we use REVOKE, and instead of TO we use FROM.


REVOKE SELECT ON
clown_info
FROM elsie;

We can also revoke the ability for users to grant the same permissions they have to other users (GRANT OPTION), whilst still keeping their original priviliges intact:


REVOKE GRANT OPTION ON
DELETE ON chores
FROM happy, sleepy;

The code above revokes happy's and sleepy's abilities to give other users their ability to DELETE from the chores table, without removing their ability to delete from the chores table.

When you use this, you also revoke this ability from all of the users that happy and sleepy granted this privilige to. This is a side effect of the REVOKE statement. You can have more control over what happens when you revoke privilidges using the RESTRICK and CASCADE options.

REVOKE DELETE ON chores FROM sleepy CASCADE;

The cascade command removes the privilege from the user you are targeting (in this case Sleepy), as well as anyone else that user gave permission too. Hmm, that seems the same as revoking without the CASCADE keyword.. Looked it up, cascade is the default, but it can be useful to add as an extra declaration of intent.

REVOKE DELETE ON chores FROM sleepy RESTRICT;

Using RESTRICT when you want to remove a privilege from a user will return an error if that user has granted privileges to anyone else.

GRANT SELECT ON *.* TO elsie;

The code above grants global privileges. The first asterist refers to all databases, while the second asterisk refers to all tables.


GRANT SELECT, INSERT, DELETE
ON gregs_list.*
TO jim;

The statement above grants Jim select, insert and delete privileges to all of the tables contained in the 'gregs_list' database.

Roles

If you have a lot of users, you may have groups of users who are responsible for different things. You can give groups of people the privileges they need whilst at teh same time giving each user a seperate account with a role.

CREATE ROLE data_entry;

To add privileges to the role, you can treat it as you would a username:

GRANT SELECT, INSERT ON some_table TO data_entry;

To assign the role, we can do the following:

GRANT data_entry TO elsie;

To delete the role, we can do the following:

DROP ROLE data_enty;

To revoke a role, we can do the following:

REVOKE data_entry FROM elsie;

Using role WITH ADMIN OPTION

Just like the GRANT statement has WITH GRANT OPTION, a role has WITH ADMIN OPTION, which allows anyone with that role to grant that role to anyone else.

GRANT data_entry TO elsie WITH ADMIN OPTION;

Combining CREATE USER and GRANT


CREATE USER elsie
IDENTIFIED BY 'c13v3rp4s5w0rd';
GRANT SELECT ON
clown_info
TO elsie;

Connecting to a database with PHP


<?php
$conn = mysql_connect("localhost", "greg", "gr3gzpAs");
if(!$conn) {
  die('Did not connect: ' . mysql_error());
}

mysql_select_db("my_db", $conn);

$result = mysql_query("SELECT first_name, last_name FROM my_contacts");

while($row = mysql_fetch_array($result)) {
  echo $row['first_name'] . " " . $row['last_name'];
  echo "<br />";
}

mysql_close($conn);
?>