Adding Users

ClassDB Home | Table of Contents

---

Removing Users

Authors: Andrew Figueroa, Steven Rollo

Users can be removed (also referred to as "dropped") by calling the appropriate ClassDB function for the type of user being removed. These are ClassDB.dropStudent(), ClassDB.dropInstructor(), and ClassDB.dropDBManager(), for students, instructors, and DB managers respectively. However, there remain two important decisions that have to be made when removing a user:

• What should be done with the server role associated with their user name?
• What should be done with their assigned schema and any other objects they may own?

ClassDB allows the end-user to decide what should be done for each of these two questions. Regardless of the decision made, successful execution of one of the drop user functions will mean that the user is no longer registered as a user in ClassDB's records.

Before dropping a user, it is a good idea to ensure that they do not have any connections to the database that ClassDB is installed in. See Managing User Connections for information on viewing currently connected users.

Functions

The following are partial definitions of the three functions used to revoke users from ClassDB. Data types of parameters have been modified from their internal referential representation to their effective types.

ClassDB.dropStudent(userName VARCHAR(63),
                    dropFromServer BOOLEAN DEFAULT FALSE,
                    okIfRemainsClassDBRoleMember BOOLEAN DEFAULT TRUE,
                    objectsDisposition VARCHAR DEFAULT 'assign_i',
                    newObjectsOwnerName VARCHAR(63) DEFAULT NULL)

ClassDB.dropInstructor(userName VARCHAR(63),
                       dropFromServer BOOLEAN DEFAULT FALSE,
                       okIfRemainsClassDBRoleMember BOOLEAN DEFAULT TRUE,
                       objectsDisposition VARCHAR DEFAULT 'assign_i',
                       newObjectsOwnerName VARCHAR(63) DEFAULT NULL)

ClassDB.dropDBManager(userName VARCHAR(63),
                      dropFromServer BOOLEAN DEFAULT FALSE,
                      okIfRemainsClassDBRoleMember BOOLEAN DEFAULT TRUE,
                      objectsDisposition VARCHAR DEFAULT 'assign_i',
                      newObjectsOwnerName VARCHAR(63) DEFAULT NULL)

Removing From Server

Although under all circumstances, successfully dropping a user removes them from ClassDB's records, it may be desired to keep their server-level roles. This can be chosen by through the value of the dropFromServer parameter. If FALSE, the default value, then the server-level role for the user being dropped is kept. Otherwise, if it set to TRUE, then the server-level role is dropped in addition to the user being removed from ClassDB's records.

If the dropFromServer option is TRUE, then is not possible to drop the role that is currently being used to perform the drop. This means that to remove the last ClassDB user (which will be an instructor or DB manager), it is necessary to call the appropriate drop function from the role that ran the setup script or a superuser.

Removing the server-level role from a user also restricts the options available for object disposition, namely, the as_is and drop options are no longer available, since those require the server-level role to continue existing. Instead drop_c or one of the assign options must be used.

In some cases it may not be possible to also drop the server-level role from the user, such as if their role owns objects located in other databases.

Object Disposition

Rather than always dropping all of the objects owned by the user being dropped, ClassDB offers several options to decide what to do with these objects. These are specified with the objectDisposition parameter when calling one of the drop user functions, and the newObjectsOwnerName parameter if assign is used as the object disposition option.

Option	Description
as_is	Do nothing with the objects that are owned by the user, leaving them as the owner of these objects. This can only be used if dropFromServer is FALSE.
drop	Restrictively drops the objects owned by the user, meaning objects which are depended on by any other database objects are not dropped. This can only be used if dropFromServer is FALSE
drop_c	Drops objects owned by the user in a "cascading" manner. In addition to dropping objects that the user owns, also drops objects that depend on any objects that the user owns
assign	Reassigns objects owned by the user being dropped to the role specified by the newObjectsOwnerName parameter
assign_i	Reassigns objects owned by the user being dropped to the classdb_instructor role
assign_m	Reassigns objects owned by the user being dropped to the classdb_dbmanager role

Underscores (_) can be replaced with dashes (-) for any of the options that contain them. Additionally, assign can be replaced with xfer. For example, drop_c and drop-c are equivalent and assign_i and xfer_i are equivalent.

Note when using drop_c: Due to the nature of the cascade option and the fact that the object disposition is carried out under the execution context of the classdb role, using the drop_c option can lead to the possibility of dropping of objects that might want to be maintained.

Examples

To drop a student from ClassDB that has a user name of bell001:

SELECT ClassDB.dropStudent('bell001');

This would remove the corresponding entry in ClassDB, and reassign ownership of the objects owned by the bell001 role to the classdb_instructor role, but leave a server-level role named bell001.

To also remove the bell001 role from the server, the following should be run instead:

SELECT ClassDB.dropStudent('bell001', TRUE);

All functions follow the case sensitivity rules described in the Adding Users documentation.

---