IT420: Database Management and Organization

1. IT420: Database Management and Organization Database Security 5 April 2006 Adina Crăiniceanu www.cs.usna.edu/~adina

2. Database Security Rights Enforced • Database security - only authorized users can perform authorized activities • Developing database security • Determine users’ rights and responsibilities • Enforce security requirements using security features from both DBMS and application programs Responsibilities Not Enforced Kroenke, Database Processing

3. DBMS Security • DBMS products provide security facilities • They limit certain actions on certain objects to certain users or groups (also called roles) • Almost all DBMS products use some form of user name and password security • Examples? Kroenke, Database Processing

4. GRANT and REVOKE • GRANT – create users and grant them privileges • REVOKE – remove privileges • Privileges: • ALL • SELECT • INSERT, DELETE, UPDATE • CREATE, ALTER, DROP • USAGE //no privileges Kroenke, Database Processing

5. GRANT Syntax GRANT privilege_type ON object TO user [IDENTIFIED BY 'password'] [WITH GRANT OPTION] Example: GRANT ALL ON dbmusic.* TO adina Kroenke, Database Processing

6. REVOKE Syntax REVOKE priv_type ON object FROM user [, user] Example: REVOKE INSERT ON dbmusic.* FROM adina Kroenke, Database Processing

7. Class exercise • Create database vp5fund and tables • Log in MySQL from command line as root • Grant select privileges on table Items to user mxxx with password mxxx • Logout • Log in MySQL as mxxx with password mxxx • SELECT * FROM Items • INSERT into Items VALUES(‘aa’,5) – What happens? Kroenke, Database Processing

8. DBMS Security Model With Roles Kroenke, Database Processing

9. DBMS Security Guidelines • Run DBMS behind a firewall, but plan as though the firewall has been breached • Apply the latest operating system and DBMS service packs and fixes • Use the least functionality possible • Protect the computer that runs the DBMS Kroenke, Database Processing

10. DBMS Security Guidelines • Manage accounts and passwords • Use a low privilege user account for the DBMS service • Protect database accounts with strong passwords • Monitor failed login attempts • Frequently check group and role memberships • Audit accounts with null passwords • Assign accounts the lowest privileges possible • Limit DBA account privileges • Planning • Develop a security plan for preventing and detecting security problems • Create procedures for security emergencies and practice them Kroenke, Database Processing

11. Application Security • If DBMS security features are inadequate, additional security code could be written in application program • Example In Project 2? • Use the DBMS security features first • Less chance for infiltration • Faster • Cheaper • Higher quality results than developing your own Kroenke, Database Processing

12. SQL Injection Attack • SQL injection attack occurs when data from the user is used to modify a SQL statement • User input that can modify a SQL statement must be carefully edited to ensure that only valid input has been received and that no additional SQL syntax has been entered • Example: users are asked to enter their names into a Web form textbox • User input: Benjamin Franklin ' OR TRUE ' SELECT * FROM EMPLOYEE WHERE EMPLOYEE.Name = 'Benjamin Franklin' OR TRUE; • Result: every row of the EMPLOYEE table will be returned Kroenke, Database Processing

13. Class exercise • Write PHP code to check user input, so SQL injection attack not possible Kroenke, Database Processing

14. 12 Week Exam • SQL • SQL Views • SQL Triggers • SQL Stored Procedures • PHP/MySQL • Database Administrator tasks • Manage database structure • Concurrency control Kroenke, Database Processing