next up previous contents
Next: Grouping access rights Up: Method for the public Previous: Definition of the access   Contents

Creating views:

Each user view is created separately. Always for each table one view is created. At the beginning list of all allowed table names is taken from the user access rights view (only these table names on which user can execute SELECT statement).

Then the following steps have to be accomplished to create view for each table from the list:

  1. Creating list of basic columns for the view.
    The algorithm takes from the user access view all column names for the table which is currently treated. The column names are taken from the each policy definition and then they are merged together in to the one list (duplicates of columns are removed). This list is needed to create basic view structure.

  2. Creating basic SQL statement needed to produce view.
    This first part of the SQL statement is defined on the basis of the columns which we got in the previous step.

    CREATE VIEW user_schema.treated_table AS SELECT list of basic columns FROM meta_user_schema.treated_table WHERE oid=NULL ......
    The ''where clause'' is needed here to create empty view structure. Now we have to add the filtration for the columns and the records according to the descriptor definitions.

  3. Defining filtering extensions for the basic SQL statement.
    The records are filtered by the additionall SELECT statements which have to be defined separately for the each unique set of columns. SELECTS are created one by one and for each of them the following actions are effected:

    Example:

    CREATE VIEW user\_schema.breeds as 
        SELECT breed\_id, mcname, country\_id, tax\_id, dailygain FROM breeds
      UNION
        SELECT breed\_id, mcname, country\_id, tax\_id, NULL FROM breeds
          WHERE (tax\_id=1 or tax\_id=2) 
                  and (carcassweight>=300 and carcassweight<=400)
                    and (owner='PL' or owner='DE') 
      UNION
        SELECT breed\_id, mcname, NULL, NULL, NULL FROM breeds
          WHERE (owner='FR') and (tax\_id=3)
      UNION
        SELECT breed\_id, mcname, NULL, tax\_id, dailygain FROM breeds
          WHERE (dailygain>=24 and dailygain<=56) and not((tax\_id=1 and so on ...);
    
    In result we get a following view:

    Table 1.12: View for the breeds table
    breed_id mcname country_id tax_id dailygain
    33 Polish Red 50000091 1 NULL
    45 Angler 50000009 1 NULL
    67 Wollschwein 50000009 2 NULL
    56 Pulawska 50000091 2 NULL
    23 Duck de la France NULL NULL NULL
    78 Lanka NULL 5 350
    24 Florina NULL 6 315


    The symbolic schema of reading data is shown on Figure 1.4.

    Figure 1.4: Reading data from the database
    \includegraphics[scale=1]{readingdata.eps}


next up previous contents
Next: Grouping access rights Up: Method for the public Previous: Definition of the access   Contents
Marek Imialek 2006-06-22