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
- You can name multiple users in the same GRANT satement. Each of the users named will get the same permission granted to them.
- WITH GRANT OPTION gives users permission to give other users the permission they were just given. So if a user was given permission to SELECT from a specific table, then this command allows them to give someone else the ability to SELECT from the same table.
- A specific column, or columns in a table can be used instead of the entire table.
- You can specify more than one permission on a table, just list each permission you want to grant on a table using a comma after each.
- GRANT ALL gives users permission to SELECT, UPDATE, INSERT and DELETE from the specified table.
- You can specify every table in a database with database_name.* - the * wildcard in a SELECT statement specifies all the tables in a database.
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);
?>
<?php
tells the web server that php code follows.$conn = mysql_connect("localhost", "greg", "gr3gzpAs");
connects to gregs_list database by telling the web server where the RDBMS is located, what our username is and what our password is. We store this connection string in a variable called 'conn' which stands for 'connection'. The PHP function 'mysql_connect()' takes this information and reaches out with the RDBMS to see if it can communicate with it.if(!$conn) { die('Did not connect: ' . mysql_error()); }
returns an error message telling us why we couldn't connect if there was a problem.mysql_select_db("my_db", $conn);
tells PHP which database we are interested in using.$result = mysql_query("SELECT first_name, last_name FROM my_contacts");
is where we write our query, execute it and then store the results of that query in a variable called 'result'.while($row = mysql_fetch_array($result)) {
gets all of the rows from our 'result' variable and displays them on a web page using a while loop.echo $row['first_name'] . " " . $row['last_name'];
this echo statement prints out the values contained in the 'first_name' and 'last_name' columns, seperated by a space.echo "<br />";
adds a new line at the end of the row so that each row is printed on seperate lines.mysql_close($conn);
closes the connection to the database when we are done with it, like logging out.?>
ends the PHP script