Exploring the Demo

Exploring the Demo

Accessing the Demo Database

Using your favourite tool connect to the database veildemo as the user vdemo_user.

You will be able to see all of the demo views, both the secured views and the helpers. But you will not initially be able to see any records: each view will appear to contain no data. To gain some privileges you must identify yourself using the connect_person() function.

There are 6 persons in the demo. You may connect as any of them and see different subsets of data. The persons are

• 1 Deb (the DBA). Deb has global privileges on everything. She needs them as she is the DBA.
• 2 Pat (the PM). Pat has the manager role globally, and is the project manager of project 102. Pat can see all but the most confidential personal data, and all data about her project.
• 3 Derick (the director). Derick can see all personal and project data. He is also the project manager for project 101, the secret project.
• 4 Will (the worker). Will has been assigned to both projects. He has minimal privileges and cannot access project confidential data.
• 5 Wilma (the worker). Will has been assigned to project 101. She has minimal privileges and cannot access project confidential data.
• 6 Fred (the fired DBA). Fred has all of the privileges of Deb, except for can_connect privilege. This prevents Fred from being able to do anything.

Here is a sample session, showing the different access enjoyed by different users.

veildemo=> select connect_person(4);
 connect_person 
----------------
 t
(1 row)

veildemo=> select * from persons;
 person_id |    person_name    
-----------+-------------------
         4 | Will (the worker)
(1 row)

veildemo=> select * from person_details;
 person_id | detail_type_id |    value     
-----------+----------------+--------------
         4 |           1003 | 20050105
         4 |           1002 | Employee
         4 |           1004 | 30,000
         4 |           1005 | 19660102
         4 |           1006 | 123456789
         4 |           1007 | Subservience
(6 rows)

veildemo=> select * from project_details;
 project_id | detail_type_id |  value   
------------+----------------+----------
        102 |           1001 | 20050101
        102 |           1002 | Ongoing
(2 rows)

veildemo=> select connect_person(2);
 connect_person 
----------------
 t
(1 row)

veildemo=> select * from person_details;
 person_id | detail_type_id |       value       
-----------+----------------+-------------------
         1 |           1003 | 20050102
         2 |           1003 | 20050103
         3 |           1003 | 20050104
         4 |           1003 | 20050105
         5 |           1003 | 20050106
         6 |           1003 | 20050107
         1 |           1002 | Employee
         2 |           1002 | Employee
         3 |           1002 | Employee
         4 |           1002 | Employee
         5 |           1002 | Employee
         6 |           1002 | Terminated
         2 |           1004 | 50,000
         1 |           1005 | 19610102
         2 |           1005 | 19600102
         3 |           1005 | 19650102
         4 |           1005 | 19660102
         5 |           1005 | 19670102
         2 |           1006 | 123456789
         1 |           1007 | Oracle, C, SQL
         2 |           1007 | Soft peoply-stuff
         3 |           1007 | None at all
         4 |           1007 | Subservience
         5 |           1007 | Subservience
(24 rows)

veildemo=> select * from project_details;
 project_id | detail_type_id |  value   
------------+----------------+----------
        102 |           1001 | 20050101
        102 |           1002 | Ongoing
        102 |           1008 | $100,000
(3 rows)

veildemo=>

Next: The Demo Code

---