Lecture 20 - JMU CS Department - James Madison University

frightenedfroggeryΔιαχείριση Δεδομένων

16 Δεκ 2012 (πριν από 4 χρόνια και 7 μήνες)

124 εμφανίσεις

SQL Authorization and Database Security
FCDB 10.1
Dr.Chris Mayeld
Department of Computer Science
James Madison University
Apr 09,2012
Database security 101
I
Access control,users/groups
I
Views (for limiting access)
I
Encryption (e.g.,passwords)
I
Denial of service attacks
I
Fault tolerance (hot standby)
I
Privacy of user's information
I
Audit trail (using triggers?)
Apr 09,2012 SQL Authorization and Database Security 2 of 13
Privileges
POSIX le system:
I
fUser,Group,Otherg may f4=Read,2=Write,1=Executeg
I
Example:chmod 755 myfile.txt
SQL database:
I
SELECT,INSERT,UPDATE,DELETE
I
TRUNCATE,REFERENCES,TRIGGER
I
CREATE,CONNECT,TEMPORARY
I
EXECUTE,USAGE,ALL PRIVILEGES
http://www.postgresql.org/docs/9.1/static/sql-grant.html
Apr 09,2012 SQL Authorization and Database Security 3 of 13
Granting privileges
GRANT <privilege list> ON <database element> TO <user list>
GRANT SELECT,INSERT ON Studio
TO kirk,picard WITH GRANT OPTION;
-- PostgreSQL syntax is slightly different from the book
GRANT SELECT (title),UPDATE (title) ON Movies TO sisko;
-- PUBLIC means any user
GRANT INSERT ON films TO PUBLIC;
http://www.postgresql.org/docs/9.1/static/ddl-priv.html
Apr 09,2012 SQL Authorization and Database Security 4 of 13
Grant diagrams
Directed graph:
I
Nodes = user and privilege
I
** = owner of element
I
* = with grant option
I
Edges = who granted privilege
Fundamental rule:
I
User C has privilege Q as long as
I
path from XP   to CQ,CQ,or CQ  
I
and P is a superprivilege of Q
I
Remember that P could be Q,and X could be C
Apr 09,2012 SQL Authorization and Database Security 5 of 13
Example grant diagram
I
A owns the object for which P is a privilege
I
User A:GRANT P TO B WITH GRANT OPTION;
I
User B:GRANT P TO C WITH GRANT OPTION;
I
User A:GRANT P TO C;
Apr 09,2012 SQL Authorization and Database Security 6 of 13
Example revoke cascade
User A:REVOKE P FROM B CASCADE;
I
Both B and C lose P
I
However,C still has P
Apr 09,2012 SQL Authorization and Database Security 7 of 13
Revoking privileges
REVOKE <privilege list> ON <database element> FROM <user list>
[ CASCADE j RESTRICT ]
Note:RESTRICT by default
I
Cannot revoke if has any dependent privileges
REVOKE SELECT,INSERT ON Studio
FROM picard CASCADE;
-- PostgreSQL has additional options
REVOKE ALL PRIVILEGES ON Studio FROM picard;
See practice problems on page 436
Apr 09,2012 SQL Authorization and Database Security 8 of 13
Creating initial privileges
How I created your databases:
CREATE DATABASE mayfiecs OWNER = mayfiecs;
REVOKE ALL ON DATABASE mayfiecs FROM public;
And made\postgres"DB read-only:
REVOKE CREATE ON DATABASE postgres FROM public;
REVOKE TEMP ON DATABASE postgres FROM public;
REVOKE CREATE ON SCHEMA public FROM public;
Apr 09,2012 SQL Authorization and Database Security 9 of 13
Privilege-checking process
1.Is the user the owner?
2.Is the object public?
3.Does the user have access?
Normal user:
CREATE ROLE mayfiecs LOGIN NOSUPERUSER INHERIT
NOCREATEDB NOCREATEROLE NOREPLICATION;
-- Each user has a set of authorization IDs
GRANT students TO mayfiecs;
Group role:
CREATE ROLE students NOSUPERUSER INHERIT
NOCREATEDB NOCREATEROLE NOREPLICATION;
Super user:
CREATE ROLE postgres LOGIN SUPERUSER INHERIT
CREATEDB CREATEROLE REPLICATION;
Apr 09,2012 SQL Authorization and Database Security 10 of 13
SQL Injection
Why is this still a problem?
Examples of SQL injection
In Java:
String sql ="SELECT * FROM users"
+"WHERE name ='"+ userName +"';"
Hello,my name is:'OR'1'='1
SELECT * FROM users
WHERE name =''OR'1'='1';
Better yet,don't check my password:'OR 1=1 --
SELECT * FROM users
WHERE name =''OR 1=1 --';
Or try to get someone red:';DROP TABLE users;--
SELECT * FROM users
WHERE name ='';DROP TABLE users;--';
Apr 09,2012 SQL Authorization and Database Security 12 of 13
SQL injection methods
I
Adding or modifying data
I
Denial of service
I
Privilege escalation
I
Bypassing authentication
I
Evading detection
I
Executing remote commands
I
Extracting data
I
Identifying injectable parameters
I
Inferring sensitive information
http://en.wikipedia.org/wiki/SQL
injection
http://www.unixwiz.net/techtips/sql-injection.html
Apr 09,2012 SQL Authorization and Database Security 13 of 13