This Lecture General Database Security Privileges Database - PDF document

This Lecture • General Database Security • Privileges Database Security • Granting • Revoking • Views Database Systems • SQL Insertion Attacks Michael Pound • Further Reading • The Manga Guide to Databases, Chapter 5 • Database Systems, Chapter 20 Database Security DBMS Security Support • Database security is • Many aspects to • DBMSs can provide • The DBMS verifies about controlling access consider for security some security password and checks to information • Physical security • Each user has an a user’s permissions • Some information • OS/Network security account, username when they try to should be available • Encryption and and password • Retrieve data freely passwords • These are used to • Other information • Modify data • DBMS security should only be available identify a user and • This lecture we will • Modify the database to certain people or control their access focus mainly on DBMS structure groups to information security Permissions and Privilege Privileges in SQL • SQL uses privileges to • The owner (creator) of a GRANT <privileges> • <users> is a list of ON <object> control access to tables database has all user TO <users> and other database privileges on all objects [WITH GRANT OPTION] objects. E.g. in the database, and • <object> is the can grant these to • SELECT privilege name of a table or view others • INSERT privilege <privileges> is a list of • The owner (creator) of SELECT (<columns>), • UPDATE privilege • WITH GRANT INSERT (<columns>), • CREATE privilege an object has all OPTION means that DELETE, privileges on that object • In MySQL there are the users can pass their UPDATE (<columns>), and can pass them on actually 30 distinct privileges on to others or simply ALL to others privileges 1

Privileges Examples Removing Privileges • For example: GRANT ALL ON GRANT SELECT, • If you want to remove a Employee UPDATE(Salary) privilege you have REVOKE TO Manager ON Employee granted you use UPDATE(Salary) WITH GRANT OPTION; TO Finance; ON Employee REVOKE FROM Finance • The user ‘Manager’ can • The user ‘Finance’ can <privileges> do anything to the view the entire Employee REVOKE ALL table, and can change Employee table, and can ON <object> PRIVILEGES, GRANT allow other users to do Salary values, but cannot FROM <users>; OPTION FROM the same (by using change other values or Manager GRANT statements) pass on their privilege Removing Privileges Removing Privileges • Example • Manager’ revokes ALL • ‘Admin’ grants ALL from ‘Personnel’ Admin Admin privileges to ‘Manager’, • ‘Personnel’ still has and SELECT to ‘Finance’ SELECT ALL SELECT privileges from SELECT ALL with grant option ‘Finance’ • ‘Manager’ grants ALL to Finance Manager Finance Manager Personnel • ‘Finance’ grants SELECT SELECT ALL SELECT to Personnel Personnel Personnel Removing Privileges Views • Manager’ revokes ALL • Privileges work at the • Views provide ‘virtual’ from ‘Personnel’ level of tables tables Admin • ‘Personnel’ still has • You can restrict access • A view is the result of a SELECT privileges from ALL by column SELECT statement which ‘Finance’ is treated like a table • You cannot restrict Finance Manager • You can SELECT from • ‘Admin’ revokes SELECT access by row (and sometimes UPDATE • Views, along with from ‘Finance’ etc) views just like tables • Personnel also loses privileges, allow for SELECT customised access Personnel 2

Creating Views View Example CREATE VIEW <name> • Example Student AS • We want each university sID sFirst sLast sYear <select statement>; tutor to be able to see marks of only those Enrolment students they actually • <name> is the name of teach sID mCode eMark eYearTaken the new view • We will assume our • <select statement> is a database is structured Module with Student, Enrolment, query that returns the Tutors and Module mCode mTitle mCredits rows and columns of tables similar to those the view seen in previous lectures Tutors Lecturers lID sID lID lName lDept View Example Database Integrity CREATE VIEW TuteeMarks • Database Security • Database Integrity AS • Database security makes • Ensures that authorised SELECT sID, sFirst, sLast, mCode, eMark sure that the user is users only input FROM Student INNER JOIN Enrolment USING(sID) authorised to access consistent data into the INNER JOIN Module USING (mCode) information database WHERE sID IN (SELECT sID FROM Tutors • Beyond security, checks • Usually consists of a WHERE lID = CURRENT_USER); should be made that series of constraints and user mistakes are assertions on data detected and prevented GRANT SELECT ON TuteeMarks TO 'user'@'%'; Note: You should grant for all Tutors in MySQL, in Oracle you can grant to PUBLIC. In Oracle CURRENT_USER is called USER Database Integrity Connections to a DBMS • Integrity constraints come in a number of • A major concern with database security forms: should be when your application connects to • CREATE DOMAIN can be used to create custom the DBMS types with specific values • The user doesn’t connect to the DBMS, the • CREATE ASSERTION can be used to check application does manipulation of tables against some test, that • This often happens with elevated privileges must always be true • CHECK constraints (more widely suppoted) are • If the application isn’t well secured, it could used to check row-level constraints provide a conduit for malicious code • Oracle supports CHECK constraints. MySQL can emulate them with triggers 3

SQL Injection Attacks SQL Injection Attacks • It is common for user input to be read, and form part of an SQL query. For example, in PHP: $query = "SELECT * FROM Products An SQL Injection attack is an exploit where a WHERE pName LIKE '%" . $searchterm . "%'"; user is able to insert malicious code into an SQL • If a user is able to pass the application malicious query, resulting in an entirely new query information, this information may be combined with regular SQL queries • The resulting query may have a very different effect SQL Injection Attacks SQL Injection Attacks • An application or website is vulnerable to an • Imagine a user login webpage that requests a injection attack if the programmer hasn’t user ID and password. These are passed from added code to check for special characters in a form to PHP via $_POST the input: • $_POST[‘id’] = ‘Michael’ • ' represents the beginning or end of a string • $_POST[‘pass’] = ‘password’ • ; represents the end of a command • /*...*/ represent comments • The ID is later used for a query: • -- represents a comment for the rest of a line SELECT uPass FROM Users WHERE uID = 'Michael'; SQL Injection Attacks SQL Injection Attacks • In PHP the code for any user might look • If the user enters Name , the command something like this: becomes: SELECT uPass FROM Users $query = "SELECT uPass FROM Users WHERE WHERE uID = 'Name'; uID = '" . $_POST['id'] . "'"; $result = mysql_query($query); • But what about if the user entered $row = mysql_fetch_row($result); $pass = row['uPass']; ';DROP TABLE Users;-- as their name? • The password would then be compared with the other field the user entered 4

SQL Injection Attacks SQL Injection Attacks • With the malicious code inserted, the • The website programmer intended to execute meaning of the SQL changes into two queries a single SQL query: and a comment: SELECT uPass FROM Users WHERE uID = ' Name ' SELECT uPass FROM Users WHERE uID = ' ';DROP TABLE Users;-- ' String Concatenation String Concatenation SELECT uPass FROM Users WHERE uID = 'Name' SELECT uPass FROM Users WHERE uID = ''; DROP TABLE Users; -- ' SQL Injection Attacks SQL Injection Attacks • Sometimes the goal isn’t sabotage, but • This attack is aimed at listing all accounts at a information bank. The SQL becomes a single, altered query: • Consider an online banking system: SELECT No, SortCode FROM Accounts WHERE No = '11244102 ' SELECT No, SortCode FROM Accounts WHERE No = '1' OR 'a' = 'a ' String Concatenation String Concatenation SELECT No, SortCode FROM Accounts WHERE No = '11244102' SELECT No, SortCode FROM Accounts WHERE No = '1' OR 'a' = 'a' This is particularly effective with weakly typed languages like PHP Defending Against Injection Attacks How To Write An SQL Injection Attack • Defending against SQL injection attacks is not • Data Protection Act 1998, Section 55(1): difficult, but a lot of people still don't A person must not knowingly or recklessly, without the • There are numerous ways you can improve consent of the data controller obtain or disclose security. You should be doing most of these at personal data or the information contained in any time where a user inputs variables that personal data. • Do not do this on a website you do not own will be used in an SQL statement • "I was just seeing if it would work" is not a • In essence, don't trust that all users will do valid defence what you expect them to do 5