IT 420 Database Management and Organization Database Security

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

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

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 3

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 4

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 5

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

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

DBMS Security Model With Roles Kroenke, Database Processing 8

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 9

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 10

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 11

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 12

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

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