Transcript DB Security

IT420: 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 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
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
SQL Views
SQL Triggers
SQL Stored Procedures
PHP/MySQL
Database Administrator tasks
 Manage database structure
 Concurrency control
Kroenke, Database Processing
14