: Today Database Security and Access Control Midsemester Exam Inspection Today (Wednesday 28-09-2005) 11 - 1 CSIT N329
COMP2400/6240: Lecture 21
2005
1
Database Security
Reading
E & N Ch 23 Oracle9i Database Concepts Ch 22, Ch 23 Oracle9i Administrators Guide Ch 25 Oracle9i SQL Reference see commands GRANT REVOKE CREATE ROLE
COMP2400/6240: Lecture 21 2005 2
Breaches of security may be deliberate or accidental and may result in
Theft and fraud Loss of condentiality Loss of privacy Loss of integrity Loss of availability
COMP2400 concentrating on security aspects of DBMS
COMP2400/6240: Lecture 21
2005
3
Note other security issues also important
Physical security of buildings and equipment wrt breakin, ood, re, electrical
faults, etc
honesty of staff competence of staff and awareness of various security issues and procedures Network security Encryption Ethical considerations and legal requirements Cost/benet wrt level of security
COMP2400/6240: Lecture 21
2005
4
Some events reported in the Forum on Risks to the Public in Computers and Related Systems (This grew out of a newsgroup comp.risks) See The Risks Digest at
http://catless.ncl.ac.uk/Risks/
Articles from Volume 23 Issues 93, 94 Also from Volume 24 Issue 4 See especially the real horror story on identity theft of victim Michael Berry in Volume 22 Issue 84; This is scarier than the movie The Net.
http://catless.ncl.ac.uk/Risks/22.84.html#subj1
and click on the Washington Post article.
COMP2400/6240: Lecture 21
2005
5
Database Access Control
Database privileges
managed by a DBA
GRANT / REVOKE database privileges Oracle ROLEs Object privileges
managed by owner and other users granted privileges with grant option
GRANT / REVOKE object privileges on objects objects are tables, views, columns object privileges are SELECT, REFERENCE, INDEX, UPDATE, INSERT,
DELETE, ALTER
Effective use of views
COMP2400/6240: Lecture 21 2005 6
GRANT of object privileges by owner of object (or by a user given a grant option) Syntax :
GRANT {object_priv | ALL}[(column_list)] {object_priv [, | ALL}[(column_list)] . . . ] ON object TO {user | role | PUBLIC} [, {user | role | PUBLIC} . . . ] [WITH GRANT OPTION]
See Oracle9i SQL Reference Table 17.3 for list of Object Privileges
COMP2400/6240: Lecture 21
2005
7
Examples Suppose the Student, Course, Enrolment tables are university admin tables. To grant any database user read-only access to the Course table :
GRANT SELECT ON Course TO PUBLIC
To allow course managers to enter marks into the Enrolment table for their course only : Dene COMP2400Enrol (for 2005) as
CREATE VIEW COMP2400Enrol(StudentId, CourseCode, Year, Sem, Mark, Grade) AS SELECT StudentId, CourseCode, Year, Sem, Mark, Grade FROM Enrolment WHERE CourseCode = COMP2400 AND Year = 2005 WITH CHECK OPTION
COMP2400/6240: Lecture 21 2005 8
Then grant select on the view, and update of the Mark and Grade columns to the user COMP2400.
GRANT SELECT, UPDATE(Mark, Grade) ON COMP2400Enrol TO COMP2400 /* Oracle user COMP2400 */ WITH GRANT OPTION
The WITH GRANT OPTION allows user comp2400 to grant the privileges to other users (e.g. read-only access to comp2400 tutors) :
GRANT SELECT ON COMP2400Enrol TO OPS$U8100582, OPS$9606272
COMP2400/6240: Lecture 21
2005
9
To allow COMP2400 student...