Understand Database Security Concepts

1. Understand Database Security Concepts LESSON 5.1 98-364 Database Administration Fundamentals

2. Lesson Overview • Security is a major concern for database administrators. There are hackers and external attacks, but security must include problems with local access. Without security measures in place, valuable data can be damaged or stolen. • In this lesson, you will learn about: • Security plans • Physical security • Access control • Common attacks • User accounts • Roles

3. Security Plans A security plan must identify which users can perform which action(s) to which data in the database. The plan involves external and internal methods. Physical security A secure location with documentation of who has access Backups and operational continuity Run backups regularly and periodically store offsite. Test the restore capability periodically.

4. Security Plans (continued) Internal security Access control ensures and restricts who can connect and what they can do to the database. Users should be limited to only the data they need. All users should have strong passwords. Use the administrator or root account only when absolutely necessary. Disable or delete old or unused accounts that belong to people who no longer need access.

5. Types of Attacks Brute—the forced cracking of weak or default user names/passwords  Privilege escalation—a user is granted more access and privileges than needed. Exploiting unused and unnecessary database services and functionality Targeting unpatched database vulnerabilities (software security holes) Stolen backup (unencrypted) tapes Inference SQL injection 

6. Inference Attack  • A data mining technique in which, by analyzing data, the user illegitimately gains knowledge about a subject or database. • Inference occurs when users are able to piece together information at a low security level that should be available only to a higher security level. • Protocols, such as cryptography, can prevent users from inferring data. • Careful database design and user access control are also used.

7. SQL Injection Allows a user to execute arbitrary Structured Query Language (SQL) code to access the database. Occurs when user input is not filtered for escape characters or executes unexpectedly. For example, at the login screen for user name and password, a hacker provides a SQL statement or database command (instead of the login name) that goes directly to the database. To protect against SQL injection attacks: Check parameters. When asking for a customer number, check that input is the proper data type, length, etc., before executing the query. Limit the permissions of the account that executes SQL queries. Use stored procedures (or similar techniques) to prevent users from directly interacting with SQL code.

8. User Accounts Database administrators protect their data from unauthorized outsiders and insiders attempting to exceed their authority by locking access to the database with required user names and passwords. This feature is built into SQL. Server-based databases all have user accounts similar to computer operating systems (such as Windows Vista or Windows 7). Create individual database user accounts for each person who will be accessing your database. Use strong passwords witheight or more characters and combine letters, numbers, and symbols. With a small number of users, creating user accounts and assigning permissions directly to them will be sufficient for your needs in most cases.

9. User Accounts (continued) The SQLGRANT statement grants appropriate database permissions to users and roles. Example:GRANT permissions ON tableTO user/roleWITH GRANT OPTION GRANT—table permissions (SELECT, INSERT, UPDATE, DELETE)or database permissions (CREATE TABLE, ALTER DATABASE,GRANT) More than one can be granted in a single GRANT statement. Table-level and database-level permissions cannot be used in a single statement.  ON—is the affected table for table-level permissions. TO—is the user or role that is being granted permissions.  WITH GRANT OPTION—the user (not roles) is permitted to grant the same permissions to other users.

10. Database Roles With a large number of users, the task of maintaining accounts and proper permissions can be overwhelming. A group user account or single account can be assigned to a role or roles. Permissions are then assigned to the role rather than the individual user. We could create a SuperUser role and then add the user accounts of our teachers to this role We can then assign a specific permission to all present (and future) users by simply assigning the permission to the role, such as the right to use a special color printer. CREATE ROLE SuperUser AUTHORIZATION Administrator

11. Using Roles in a GRANT Statement First, create user accounts for each operator and then add them all to a new role called DataEntry. A group of teachers will be adding grades to the student records. They need to be able to access the Class Info table to modify or add new records to the table. They are not able to delete a record from the database. Using the role (DataEntry) for this group of teachers lets the teacher accomplish the tasks of adding grades. GRANT SELECT, INSERT, UPDATEON Class InfoTO DataEntry

12. Lesson Review • What is a security plan? • What types of security must be considered? • What types of attacks can occur? • What are inference attacks? • What is SQL injection? • Distinguish between user accounts and database roles.