ClassDB Home | Table of Contents
Schemas
Author: Andrew Figueroa, Steven Rollo
Every user in an instance of ClassDB receives their own schema, where they can perform operations on database objects that they have created. By default, this schema is created with the same name as the user’s database username. This “personal” schema is referred to as the $user schema.
The public schema is a special schema that can be read by any ClassDB user, but only modified by Instructors.
User Schemas
Every user owns their $user schema. They can create, modify, and delete objects within their schema without affecting any other user’s usage of the database. For Instructors and DBManagers, only the individual user that the $user schema corresponds to can create or access the objects within the schema. See the special note for Student users below.
Special note for Student schemas
$user schemas belonging to Students can be read by any Instructor (but not by DBManagers). The default privileges are also set such that Instructors can read tables created in this schema by the student. This allows instructors to view a student’s progress and review any assignments or projects. Student’s cannot read each other’s $user schemas.
Since students own their $user schemas, they have permission to DROP their schema. This is highly undesirable, and is prevented using an event trigger. If a student attempts to drop their own schema, the event trigger will throw an exception, causing the DROP to fail.
Public schema
The public schema is a schema that is configured so that it can be read by any user, but only have objects created or modified by Instructors. This allows read access on a table to be given to all users of an instance of ClassDB.
A user’s default search path is set to '$user, public', which means that database objects will first be searched for in their $user schema, followed by the public schema. This means that if an object with the same name exists in both the $user schema and the public schema, the one in the $user schema will be used.
For example, if there exists a table named Employee in both the $user schema and the public schema, then the following statement will use the one in the $user schema:
SELECT *
FROM Employee;
If the Employee table in the public schema needs to be accessed, then a fully qualified name must be used, like so:
SELECT *
FROM public.Employee;
Likewise, if an Instructor wishes to create a table in the public schema, then a fully qualified name must also be used. Otherwise, it will be created in their $user schema.