SQL 12: Security: Protecting your assets

Exercise 1: Decide exactly what the user needs.

1. GRANT INSERT ON magic_animals TO doc; Allows doc to INSERT into the magic_animals table. 2. GRANT DELETE ON chores TO happy, sleepy; Allows happy and sleepy to DELETE from the chores table. grants multiple users! 3. GRANT DELETE ON chores TO happy, sleepy WITH GRANT OPTION; WITH GRANT OPTION Allows happy and sleepy to DELETE from the chores table and give others the same permission. 4. GRANT SELECT (chore_name) ON chores TO dopey; Hint: It's a column name. (chore_name) Allows dopey to SELECT from just the chore_name column in the chores table. 5. GRANT SELECT, INSERT ON talking_animals TO sneezy; Allows sneezy to SELECT and INSERT into the talking_animals table. 6. GRANT ALL ON talking_animals TO bashful; ALL Allows bashful to SELECT, UPDATE, INSERT and DELETE on the talking_animals table.

Exercise 2: Write some of your own GRANT statements.

7. Gives Doc permission to SELECT from chores. GRANT SELECT ON chores TO doc; 8. Gives Sleepy permission to DELETE from talking_animals, and it also gives Sleepy permission to GRANT the DELETE from talking_animals to anyone else. GRANT DELETE ON talking_animals TO sleepy WITH GRANT OPTION; 9. Gives ALL of the users all permissions on chores. GRANT ALL ON chores TO bashful, doc, dopey, grumpy, happy, sleepy, sneezy; 10. This allows you to set the SELECT privilege for Doc all at once for every table in the woodland_cottage database. GRANT SELECT ON woodland_cottage.* TO doc

Q: Is there anything in addition to tables and columns that I can use GRANT and REVOKE with?

A: You can use them with views in exactly the same way you would a table, unless the view is non-updatable. In that case, you wouldn't be able to INSERT if you had permission to. And just like a table, you can grant access to specific columns in a view.

Using a role

Before creating our role, we could have given our data-entry users privileges directly using the GRANT statements, like so: GRANT SELECT, INSERT ON talking_animals TO doc; This is the old way. Now all we need to do is substitute the GRANT operation for our new role and apply it to doc. We don't need to mention the privileges or table because that's all stored in the data_entry role: GRANT data_entry TO doc;

A note on determining accessibility for users.

Database name: woodland_cottage Tables: talking_animals, chores Only root can INSERT, UPDATE, and DELETE chores. Only some users may modify particular tables. Only the person in charge should be able to add new chores to the chores table. Happy is in charge of the talking_animals table and may ALTER the structure of it, as well as perform any other operations on it.

The problem with shared accounts

Not safe.

REVOKing with RESTRICT

RESTRICT when you want to remove a privilege from a user will return an error if that user has granted privileges to anyone else. Both retain privileges, and root receives an error. She's stopped from making the change and gets an error because it will also have an effect on sneezy.

Revoking your role

Revoking a role works much like revoking a grant: REVOKE data_entry FROM doc;

With the restrictions, what is the user's capacity to manipulate data?

SELECT, UPDATE, INSERT data. Or anything else until we've had time to do extensive background checks for more accessibility?

Add a new user

SQL keeps a database of data about itself. It includes user ids, usernames, passwords, and what each user is allowed to do to each database. To create a new user, we can start with a username and a password. There's no actual SQL command to create a user, but most RDBMSs will use something like this: CREATE USER employee name IDENTIFIED BY 'password'; However: User creation varies from RDBMS to RDBMS. You need to check your documentation to find the correct way to create a user in your RDBMS.

When you created an user account, couldn't you have restricted Elsie from certain tables at the same time?

The advantage to knowing how to grant access independently of creating a user is that: it gives us the ability to make changes to user access later as our database changes. We'll create a user and then grant him the specific access he needs. And then we'll put it all together before we're finished.

Using your role WITH ADMIN OPTION

This option allows anyone with that role to grant that role to anyone else. GRANT role_name TO user_name WITH ADMIN OPTION; GRANT data_entry TO doc WITH ADMIN OPTION;

DROP ROLE data_entry;

You can drop roles that are in use. Be very careful when dropping a role that you don't cut users off from the permissions that they need. If a user has a role that is then dropped, he loses those permissions

Classes of users

You might have 10 people who are devoted to data entry, and only need to insert and select from certain tables. You might also have three power users who need to be able to do anything, and lots of users who just need to SELECT. You may even have software and web applications that connect to your database and need to query specific views in specific ways.

Someone keeps giving Elsie the wrong privileges. Write the appropriate REVOKE statements to return her to her safe SELECT-only status.

1. GRANT SELECT, INSERT, DELETE ON locations TO elsie; REVOKE INSERT, UPDATE, DELETE ON locations FROM elsie; 2. GRANT ALL ON clown_info TO elsie; REVOKE INSERT, UPDATE, DELETE ON activities FROM elsie; 3. GRANT SELECT, INSERT ON activities TO elsie; REVOKE INSERT ON activities FROM elsie; 4. GRANT DELETE, SELECT on info_location TO elsie WITH GRANT OPTION; REVOKE DELETE on info_location FROM elsie CASCADE; 5. GRANT INSERT(location), DELETE ON locations TO elsie; REVOKE GRANT INSERT (locations), DELETE ON locations FROM elsie; Looks like we could also use a GRANT here to make sure she can still SELECT locations. We want to leave her with SELECT privileges, so we are not REVOKING everything. Another way you could have done these is to REVOKE everything and then GRANT what you need to. How does this look like? REVOKE INSERT, UPDATE, DELETE ON *.* FROM elsie; GRANT SELECT ON *.* TO else;

GRANT variations

1. You can name multiple users in the same GRANT statement. Each of the users named will get the same permission granted to them. 2. WITH GRANT OPTION gives users permission to give other users the permission they were just given. It sounds confusing, but it simply means that if the user was given a SELECT on chores, he can give any other user that same permission to do SELECTs on chores. 3. A specific column, or columns, in a table can be used instead of the entire table. The permission can be given to only SELECT from a single column. The only output the user will see will be from that column. 4. 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. 5. GRANT ALL gives users permission to SELECT, UPDATE, INSERT, and DELETE from the specified table. It's simply a shorthand way of saying "give users permission to SELECT, UPDATE, INSERT, and DELETE from the specified table. 6. "You can specify every table in a database with database_name.* Much like you use the * wildcard in a SELECT statement, this specifies all the tables in a database.

Exercise 3

Across 1. GRANT ALL gives users permission to SELECT, UPDATE, INSERT, and DELETE from the specified table. 3. DISTINCT function returns each unique value only once, with no duplicates. 6. NORMAL tables won't have duplicate data, which will reduce the size of your database. 7. Granting a role WITH ADMIN OPTION allows a user to grant the role to someone else. 11. SET PASSWORD FOR 'root'@'localhost' = PASSWORD('b4dcl0wnZ'); 13. Values stored in CHAR or VARCHAR columns are known as STRINGS. 16. 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. 17. With an inner join, you're comparing rows from two tables, but the ORDER of those two tables doesn't matter. 18. We can use a SELF-JOIN to simulate having two tables. 20. If changing any of the non-key columns might cause any of the other columns to change, you have a transitive DEPENDENCY. 23. If the subquery stands alone and doesn't reference anything from the outer query, it is a NONCORRELATED subquery. 24. ATOMIC means that your data has been broken down into the smallest pieces of data that can't or shouldn't be divided. 25. To help you decide what steps in your SQL can be considered a transaction, remember the acronym ACID. 26. A LEFT OUTER JOIN takes all the rows in the left table and matches them to rows in the RIGHT table. 27. A CORRELATED subquery means that the inner query relies on the outer query before it can be resolved.

REVOKing with CASCADE

CASCADE, removes the privilege from the user you target (in this case, sleepy) as well as anyone else that that user gave permissions to. Some systems will not pay attention to where the GRANT came from when CASCADE is used, and some users will ignore it. Check the documentation on your particular software.

REVOKING a used GRANT OPTION

Consider this scenario. The root user gave sleepy DELETE privileges with GRANT OPTION on the chores table. Then sleepy gave sneezy DELETE privileges on chores, too. Suppose the root user changes her mind and takes the privilege away from sleepy. It will also be revoked from sneezy, even though she only revoked it from sleepy. A side effect of the REVOKE statement was that sneezy also lost the privilege. There are two keywords you can use that will let you control what you want to happen when you're revoking.

GRANT SELECT ON *.* TO elsie;

Depending on the RDBMS, grant a user to SELECT from all of the tables in all of my databases. The first asterisk refers to all database, the second to all tables.

How can a second user grant privileges of a new role to another user?

Doc now has admin privileges, and he can grant happy the data_entry role the same way it was granted to him: GRANT role_name TO third_user_name GRANT data_entry TO happy;

Exercise 4

Down 1. You can control exactly what users can do to tables and columns with the GRANT statement 2. A TRANSITIVE functional dependency means that a non-key column is related to any of the other non-key columns. 4. You can only have one AUTO_ INCREMENT field per table, it has to be an INTEGER data type. 5. A COMPOSITE KEY is a PRIMARY KEY composed of multiple columns, creating a unique key. 8. You can find the largest value in a column with the MAX function. 9. Assigning ROLE is a way you can group together specific privileges, and apply those to everyone in a group. 10. Use ORDER BY to alphabetically order your results based on a column you specify. 12. The non-equijoin returns any rows that are not EQUAL. 13. Use SET clause in your update statement to change a value. 14. A SELF-REFERENCING foreign key is the primary key of a table used in that same table for another purpose. 15. During a TRANSACTION, if all the steps can't be completed without interference, none of them should be completed. 19. A subquery is always a single SELECT statement. 21. NATURAL joins only work if the column you're joining by has the same name in both tables. 22. A CHECK constraint restricts what values you can insert into a column. 24. Our table can be given new columns with the ALTER statement and ADD COLUMN clause.

Write queries to grant access.

Everyone except grumpy can SELECT from the talking_animals table. Everyone except dopey can see the instructions column in the chores table (it just confuses him).

After the background checks, the users can also have the capability to DELETE accurate data. How to prevent this error?

Figure out the accessibility to manipulate data for the new employees. Write some queries that new employees should or should not be allowed to do. Include table names when possible. New employees should be allowed to: example: SELECT from activities SELECT from clown_info, info_activities, activities, info_location, location New employees should not be allowed to example: DROP TABLE on clown_info DROP TABLE on clown_info, info_activities, activities, info_location, location INSERT on clown_info, info_activities, activities, info_location, location UPDATE on clown_info, info_activities, activities, info_location, location ALTER on clown_info, info_activities, activities, info_location, location DELETE on clown_info, info_activities, activities, info_location, location

Read the descriptions of the jobs for each user and come up with multiple GRANT statements that give them the data they need while not letting them access anything they shouldn't.

Frank: "I'm responsible for finding job matches for prospective job openings. I never enter anything in the database, although I do delete job listings when I find matches or the opening is filled. I sometimes need to look up contact info in my_contacts as well." Jim: "I enter all the new data into the entire database. I've gotten really good at inserting, now that I can't accidentally enter an X for gender. I also update data. I'm learning to delete, but so far Greg tells me not to. Of course, what he doesn't know..." Joe: "I was just hired by Greg to manage the matchmaking side of things. He wants to integrate his contact info into a web site. I'm more a web developer than an SQL guy, but I can do simple selects. I don't do inserts. Or Windows. Sorry, bad joke." Take a look at the gregs_list database and give these guys some GRANTs before they damage some data. 1. Write the command to give the user currently known as "root" a password. SET PASSWORD FOR root@localhost = PASSWORD('gr3GRu1z'); 2. Write three commands to create user accounts for each of the three employees. CREATE USER frank IDENTIFIED BY 'jobM4tcH'; CREATE USER jim IDENTIFIED BY 'NOmOr3xs'; CREATE USER joe IDENTIFIED BY 's3LeCTdood'; Don't worry if your passwords are different. As long as you got the correct pieces of the commands in the right order, you're good to go! 3. Write GRANT statements for each new employee to give him the correct permissions. A. GRANT DELETE ON job_listings TO frank; GRANT SELECT ON my_contacts * TO frank; Frank needs to be able to remove job listings and look up (select) from my_contacts. B. GRANT SELECT, INSERT ON gregs_list * TO jim; Jim needs access to the SELECT and INSERT from the whole of gregs_list. For now, we'll keep him away from DELETE. C. GRANT SELECT ON my_contacts, profession, zip_code, status, contact_interest interests, contact_seeking, seeking TO joe; Meanwhile Joe needs to be able to select from all the orginial tables, but not the tables that deal with jobs.

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

GRANT SELECT, INSERT ON some_table TO role_name; created role, given privileges, then assign it to users.

Grant privileges for all the tables in a database.

GRANT SELECT, INSERT, DELETE ON gregs_list.* TO jim; Just name the database and use the * to assign the privileges to all the tables in that database.

GRANT statements that specify column names. GRANT SELECT (chore_name) ON table_name TO user_name; What happens if you grant with INSERT on a single column of a table?

Good question. It's actually a pretty useless privilege to have. If you can only put a value into a single column, you can't insert an actual row into the table. The only way it can work is if the table only has the one column specified in the GRANT. Almost all privileges by column are pretty useless, unless they are in conjunction with a SELECT in the GRANT.

CASCADE is the default if you don't specify how you want to REVOKE.

However, check your RDBMS for specifics.

REVOKE GRANT OPTION ON

Revoke with GRANT OPTION (cannot grant others privileges) but leave the privilege intact. REVOKE GRANT OPTION ON DELETE ON chores FROM happy, sleepy; In this example, happy and sleepy can still DELETE things from the chores table, but they can't give anyone else that privilege any longer:

REVOKE privileges

Suppose we decide to remove the SELECT privilege we gave to Elsie. To do that, we need the REVOKE statement. REVOKE SELECT ON clown_info FROM elsie;

Remote access: The root user is using an SQL client on a machine somewhere else.

Tell the query where the computer is. You can do that with an IP address or a hostname instead of localhost. SET PASSWORD FOR 'hostname' = PASSWORD ('b4dc10wnz'); as shown in the image. For example, if your SQL software was installed on a machine called kumquats on the O'Reilly network, you might use something like [email protected]. But that's not a real SQL server, so of course it won't work.

Greg's List has gone global!

Thanks to all your help, Greg is now so comfortable with using SQL—and teaching Jim, Frank, and Joe how to use it—that he's expanded Greg's List to include to include local classified advertisements and forums as well.And the best news of all? It's been such a success in Dataville that over 500 cities worldwide now have their own Greg's Lists, and Greg is front-page news! Do this and you will be okay!

GRANT statement

The GRANT statement can be used to give specific rights to users of our databases. Here's what the GRANT can allow us to do: Only some users may modify particular tables. You can control exactly what users can do to tables and columns. The data in a specific table may only be accessible to certain users. Even within tables there might need to be permissions: some users can see certain columns, but not others.

Combining CREATE USER and GRANT into one statement

The two statements that you have already seen: CREATE USER elsie IDENTIFIED BY 'cl3v3rp4s5w0rd'; GRANT SELECT ON clown_info TO elsie; Because the user elsie has to be created before she can have privileges granted to her, your RDBMS checks to see if she exists, and if not, it automatically creates her account. GRANT SELECT ON clown_info TO elsie IDENTIFIED BY 'cl3v3rp4s5w0rd';

First and foremost! Protect the root (first) user account

Up to this point, we've only had one user in our database, and no password. This poses a great risk because anyone with access to our terminal or graphical interface (the database system) to our database has complete control over the database. The root user has complete control over everything in the database, so that is why the first user must create user accounts for all other users. We don't want to limit what the root user can do, but we do want to give our root account a password. In MySQL, the command is simply: SET PASSWORD FOR 'root'@'localhost' = PASSWORD ('b4dc10wnz'); as shown in the image. Consult RDBMS-specific documentation for information on protecting the root account. Because other RDBMS, techniques vary. For example, Oracle uses: alter user root identified by new-password; If you're using a graphical interface to your database, you'll probably find a much easier dialog-driven way to change passwords. The important point is not so much how you do it, but that you definitely should do it.

REVOKE role with CASCADE

Used with CASCADE, the REVOKE affects everyone down the chain as well as the original target: REVOKE data_entry FROM doc CASCADE;

REVOKE role with 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. REVOKE data_entry FROM doc RESTRICT; Both retain privileges, and root receives an error. She's stopped from making the change because it will also have an effect on user happy.

GRANT SELECT ON

We know that Elsie has no permission to do anything at this point. She can sign in to the SQL software using her username and password, but that's it. She needs to be able to SELECT from the clown_info table, so we can give her that permission. We need to GRANT permission TO Elsie. We'll use this statement: GRANT SELECT ON table_name TO employee_name; Elsie also needs SELECT permission on the other clown-tracking tables so that she can use joins and subqueries in her SELECT statements. We need a separate GRANT statement for each table: 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;

How can you make your data more secure?

When the first user does not create user accounts for the employees to manipulate data on restrictions. Then you are worried if the user may have insert or update incorrectly, or delete accurate information For example, George have misspellings in the clown_info table. For the clown_id, he has misspellings for Snuggles: Snugles, and Snuggels. Then, the activities column under the activities table also has misspellings: Snuggles is a juggeler Snugles is a jugler Snuggels is a jugular.

Can the root user grant users the correct access without roles, in order, to limit the accessibility to grant privileges to others?

Yes, it's time to get Greg's employees set up to use gregs_list more securely. Greg will need to: 1. go through the steps in this chapter 2. protect the root account 3. figure out what his employees need 4. give them the correct privileges.

Can a user have more than one role at a time?

Yes. Just make sure they don't have conflicting permissions, or you might cause yourself some problems. The denied permissions take precedence over the granted ones.

Role

You can create a single user for each class of people in upper management and let themselves share the password. A role is a way you can group together specific privileges, and apply those to everyone in a group. CREATE ROLE role_name; Your role becomes an object in your database that you can change as needed when your database changes, without having to explicitly change every single user's privileges to reflect the database changes. With the group privileges, you can have individual accounts for each user. Note, no roles in MySQL, so assign privileges on a single user basis.

In setting the password, what is "localhost"? Please explain more in detail.

localhost means that the computer you're 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 including it is optional.