Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Basic Relational Concepts by JWN19Nb

VIEWS: 10 PAGES: 41

									               Computer Science



                  CSC 405
     Introduction to Computer Security


            Topic 6. Database Security



CSC 405                Dr. Peng Ning     1
Agenda
• Discretionary access control in DBMS
• Mandatory access control and multi-level
  databases
• Database inference control




           Computer Science   CSC 405   Dr. Peng Ning   2
Computer Science   CSC 405   Dr. Peng Ning   3
               Computer Science




          Topic 6.1 DAC in DBMS




CSC 405                Dr. Peng Ning   4
Outline
•   Relational model
•   Grant and revoke
•   Extension to the basic model
•   Questions/comments in reviews




            Computer Science   CSC 405   Dr. Peng Ning   5
Basic Relational Concepts
• Data is organized as a collection of tables, called
  RELATIONS
    – Example: two relations - EMP, DEPT
    – EMP: name, title, department
    – DEPT: department, location
•   Each row (or record) of a relation is called a TUPLE
•   Each relation has a unique name
•   Each attribute has a unique name within a relation
•   All values in a relation are atomic (indecomposable)
    – As a consequence , we have two tuples for a user


               Computer Science   CSC 405     Dr. Peng Ning   6
Examples
EMP    Name     Title       Dept
       Tom      Prof        ECE
       Tom      Prof        CS
       Adams    Prof        ECE
       Smith    Inst        CS




DEPT   Name       Location
       CS         Wither Hall
       ECE        Daniels Hall
       Math       Harrelson Hall




               Computer Science    CSC 405   Dr. Peng Ning   7
Relation Schemes
•   A relational database consists of 2 relation schemes:
•                EMP(Name, Title, Dept)
•                DEPT(Name, Location)
•   Schemes: structure of the database
•   Structured Query Language (SQL)
•   SQL "data definition" statements are used to create relations

CREATE TABLE EMP                            CREATE TABLE DEPT
    (Name CHAR(15) NOT NULL,                      (Name CHAR(10) NOT NULL,
    Title CHAR(4),                                Location CHAR (15),
    Dept CHAR(10),                                PRIMARY KEY (Name))
    PRIMARY KEY (Name))



                     Computer Science   CSC 405              Dr. Peng Ning   8
SQL
• The SELECT statement
  SELECT Name                            Tom
  FROM     EMP                           Adams
  WHERE Dept = `ECE'

• Joins
  SELECT   *                  Tom      Prof           CS         Wither Hall
  FROM     EMP, DEPT          Smith    Inst           CS         Wither Hall

  WHERE    EMP.Dept= DEPT.Name
  AND      Dept.Location = `Wither Hall'



            Computer Science   CSC 405           Dr. Peng Ning             9
Views
 CREATE VIEW EMP_LOCATION
    AS SELECT    Name, Dept, Location
    FROM         EMP, DEPT
    WHERE        EMP.Dept = DEPT.Name


EMP_LOCATION              Name Dept          Location
                          Tom ECE            Daniels Hall
                          Tom CS             Wither Hall
                          Abrams             ECE Daniels Bldg
                          Smith CS           Wither Hall

• Views are "virtual" relations. They can be used to customize
  relations and to provide security
                Computer Science   CSC 405         Dr. Peng Ning   10
Discretionary Access Controls
• Decentralized administration
  – Users can protect what they own
  – The owner may grant access to others
  – The owner may define the type of access
    (read/write/execute) given to others




           Computer Science   CSC 405   Dr. Peng Ning   11
Access Control Mechanisms
•   Identification and Authentication (I&A)
•   Security through Views
•   Stored Procedures
•   Grant and Revoke
•   Query Modification




             Computer Science   CSC 405   Dr. Peng Ning   12
Identification and Authentication
• Identification provided by DBMS can be
  distinct from that provided by the underlying
  OS
  – Example: MS SQL server
     • Two options
        – I&A through the OS
        – Separate I&A




           Computer Science   CSC 405   Dr. Peng Ning   13
Security Through Views
EMP

NAME           DEPT SALARY MANAGER
Smith         Toy     10,000 Jones
Jones         Toy     15,000 Baker
Baker         Admin   40,000 Harding
Adams         Candy   20,000 Harding
Harding       Admin   50,000 None

Users are allowed to access partial information (such as
the Toy dept data), but not the detailed information.

             Computer Science   CSC 405   Dr. Peng Ning    14
Example
CREATE VIEW TOY_DEPT
 AS SELECT NAME, SALARY, MANAGER
 FROM     EMP
 WHERE DEPT = 'Toy'




   TOY_DEPT      NAME       SALARY         MANAGER


                 Smith      10,000         Jones
                 Jones      15,000         Baker




              Computer Science   CSC 405             Dr. Peng Ning   15
Example
 CREATE VIEW TOY_EMP_MGR
  AS SELECT EMP, MANAGER
  FROM     EMP
  WHERE DEPT = 'Toy'




TOY_EMP_MGR        NAME          MANAGER


                   Smith         Jones
                   Jones         Baker




              Computer Science     CSC 405   Dr. Peng Ning   16
Example
CREATE VIEW AVSAL(DEPT, AVG)
 AS SELECT DEPT, AVG(SALARY)
 FROM      EMP
 GROUP BY DEPT



  AVSAL      DEPT       AVG


             TOY        12,500
             CANDY 20,000
             ADMIN      45,000



          Computer Science    CSC 405   Dr. Peng Ning   17
Stored Procedures
• Right to execute compiled programs

• GRANT RUN ON program_A TO ADAMS

• Suppose program_A needs to access the relation EMP.
  Adams can execute program_A even though he does
  not have permission to access EMP




            Computer Science   CSC 405   Dr. Peng Ning   18
Query Modification
• Adams:
  GRANT SELECT ON EMP TO THOMAS WHERE SALARY < 15000
• THOMAS:
  SELECT *
  FROM EMP
• DBMS:
  SELECT *
  FROM EMP
  WHERE SALARY < 15000




             Computer Science   CSC 405   Dr. Peng Ning   19
The Grant Command
•   GRANT <privilege> ON <relation> TO <users>
    [WITH GRANT OPTION]
    –   GRANT SELECT ON EMP TO ADAMS

    –   GRANT SELECT ON EMP TO ADAMS WITH GRANT
        OPTION

    –   GRANT SELECT, UPDATE(SALARY) ON EMP TO
        JIM, JILL


•   Applied to base relations as well as views

              Computer Science   CSC 405   Dr. Peng Ning   20
The Revoke Command
•   REVOKE <privileges> [ON <relations>]
    FROM <users>
    –   REVOKE SELECT ON EMP FROM TOM

    –   REVOKE UPDATE ON EMP FROM SMITH

    –   REVOKE RESOURCE FROM ABRAMS

    –   REVOKE DBA FROM SMITH




             Computer Science   CSC 405   Dr. Peng Ning   21
Semantics of Revoke
• A sequence of grant command follow by a
  revoke operation
  – G1, G2, …, Gn, Rh


• Semantics
  – Equivalent to: G1, G2, …Gh-1, Gh+1, Gn




            Computer Science   CSC 405   Dr. Peng Ning   22
Time-stamped Authorizations


                       B            30g                         E
        10g
                                               40g

    A                                      D

        20g                                    60g                   F
                       C
                                    50g




              Computer Science   CSC 405             Dr. Peng Ning       23
Cascading Revocation

 Grant sequence:

   A                    B                      C                   D
           10g                      20g             30g



 B revokes privilege from C :


       A                    B
            10g




                 Computer Science    CSC 405       Dr. Peng Ning       24
Timestamps Make a Difference


                   B             30g                         E
       10g
                                              40g

   A                                      D

       20g                                    60g                   F
                   C
                                 50g




             Computer Science   CSC 405             Dr. Peng Ning       25
Timestamps Make a Difference


                 B             30g                        E
      10g
                                             50g

  A                                      D

      20g                                    60g              F
                 C             40g




            Computer Science   CSC 405             Dr. Peng Ning   26
Further Extension
• Make cascading optional

• Permit negative authorizations




          Computer Science   CSC 405   Dr. Peng Ning   27
The Revoke Command
• REVOKE <privileges> [ON <relations>]
  FROM <users> [CASCADE]
  – REVOKE SELECT ON EMP FROM TOM
  – REVOKE UPDATE ON EMP FROM SMITH
    CASCADE
  – REVOKE RESOURCE FROM ADAMS
  – REVOKE DBA FROM SMITH CASCADE




          Computer Science   CSC 405   Dr. Peng Ning   28
Non-cascading Revocation

  A               B                         C                    D
      10g                      20g               30g




  A               B                                              D
      10g                                  30g




            Computer Science     CSC 405         Dr. Peng Ning       29
Why Non-cascading Revoke
• Reasons for revoke
  – Task is done. No need to have the privilege
    anymore

  – Task is still in progress. But a member left the
    project (e.g., promoted)




            Computer Science   CSC 405   Dr. Peng Ning   30
Example



                                            E
                                                     80     G
           B
     20              40                50
 A                            D

     30     C           60             70   F




          Computer Science   CSC 405        Dr. Peng Ning       31
Example

                                                E
                                                          80   G
                B
       20                 40              50
  A                                 D

       30        C             60         70    F


                                               After cascading
                 B                             revocation
       20
   A                                D

       30         C            60         70     F



            Computer Science    CSC 405        Dr. Peng Ning       32
Example

                                                 E
                                                           80   G
                 B
       20                 40               50
   A                                 D

        30        C             60         70    F


After non-cascading revocation
                            50                   E
                                                           80   G
               B
        20
                                           70
   A
                                     D
        30        C        60              70
                                                 F



             Computer Science    CSC 405        Dr. Peng Ning       33
Why Positive & Negative Authorization
• Closed world policy
  – Cannot access unless explicitly granted the right


• Negative authorization
  – User A should not be allowed to read table Emp
  – Need explicit deny policies




            Computer Science   CSC 405   Dr. Peng Ning   34
Positive & Negative Authorizations

                                             40 —
                        B                                           E
       10 + g

                                   30 + g
   A                                                                D


       20 —
                        C




                Computer Science   CSC 405          Dr. Peng Ning       35
Complication
• It is possible to have two authorizations
  – Grant A privilege p
  – Deny A privilege p


• Negative authorizations override positive
  authorizations




           Computer Science   CSC 405   Dr. Peng Ning   36
Problem 1
User B gives D negative authorization at time 50 :
                                               40 —
                          B                                             E
                                                      50 —
         10 + g

                                      30 + g
     A                                                                  D


         20 —
                          C



   In our model, positive authorization granted by A to D
   becomes blocked, but we do not delete the authorization.

                  Computer Science   CSC 405            Dr. Peng Ning       37
Problem 2
Suppose D receives negative authorization from B at time 60 :

                                              40 —
                       B                                             E
        10 + g                       60 —

                  30 + g                             50 +
    A                                           D                        F


        20 —
                       C


 What about the privilege given to F by D?
 Under our approach, it becomes blocked, but we do not delete it.


                 Computer Science   CSC 405          Dr. Peng Ning           38
Revocation When Negative Authorizations
Are Present
 Given :                                  40 —
                   B                                              E

  10 + g                           60 —

              30 + g                             50 +
  A                                          D                        F



  20 —
                 C



      Suppose A revokes B’ s privilege.


                Computer Science   CSC 405        Dr. Peng Ning           39
Cascading Revocation When Negative Authorizations
Are Present




              30 + g                           50 +
   A                                       D                   F


       20 —
                   C




              Computer Science   CSC 405       Dr. Peng Ning       40
Non-cascading Revocation When Negative
Authorizations Are Present



                                                                E
                                 40 —


                 60 —                           50 +
   A                                        D                       F
               30 + g

       20 —        C




              Computer Science    CSC 405       Dr. Peng Ning           41

								
To top