Skip to content

Adding Users

Jump to bottom
Andrew Figueroa edited this page Jan 15, 2018 · 32 revisions

ClassDB Home | Table of Contents

Adding Users

Authors: Andrew Figueroa, Sean Murthy

ClassDB provides three functions to add users to an instance of ClassDB: one for each group role ClassDB defines. The functions are located in the classdb schema and can be executed by instructors and DB managers. All three functions have an identical set of parameters.

Successful execution of each user-creation function ensures that the user being created:

  • Exists on the server in which ClassDB is installed
  • Has appropriate privileges for their group role
  • Has a schema that they own assigned to them
  • Is recorded internally by ClassDB.

Depending on the function arguments given, this may involve ClassDB creating a server role and database schema for the new user.

Regardless of the group role(s) they are a part of, every user name must be unique among all users/roles on the server, even among users/roles not created through ClassDB. By default, role names are not case sensitive internally, and are folded down to lowercase. A user name may be enclosed in double quotes to preserver case. See Postgres documentation for details on user names and other PostgreSQL identifiers. In either case, clients will require the case to match what is stored internally.

If the user being created does not exist on the server, then ClassDB will create a server role and set a password for the user. An initial password may be optionally supplied. If an initial password is not supplied, the user's password is set to the same value as the user name. Whether an explicit initial password is provided or a default initial password is used, every user should change their password soon after first log in.

To remove users that were added to ClassDB, see Removing Users. Otherwise, to only revoke the ClassDB group roles that have been assigned to them, see Revoking Roles.

Functions

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

ClassDB.createStudent(userName VARCHAR(63),
                      fullName VARCHAR,
                      schemaName VARCHAR(63) DEFAULT NULL,
                      extraInfo VARCHAR DEFAULT NULL,
                      okIfRoleExists BOOLEAN DEFAULT TRUE,
                      okIfSchemaExists BOOLEAN DEFAULT TRUE,
                      initialPwd VARCHAR(128) DEFAULT NULL)

ClassDB.createInstructor(userName VARCHAR(63),
                         fullName VARCHAR,
                         schemaName VARCHAR(63) DEFAULT NULL,
                         extraInfo VARCHAR DEFAULT NULL,
                         okIfRoleExists BOOLEAN DEFAULT TRUE,
                         okIfSchemaExists BOOLEAN DEFAULT TRUE,
                         initialPwd VARCHAR(128) DEFAULT NULL)

ClassDB.createDBManager(userName VARCHAR(63),
                        fullName VARCHAR,
                        schemaName VARCHAR(63) DEFAULT NULL,
                        extraInfo VARCHAR DEFAULT NULL,
                        okIfRoleExists BOOLEAN DEFAULT TRUE,
                        okIfSchemaExists BOOLEAN DEFAULT TRUE,
                        initialPwd VARCHAR(128) DEFAULT NULL)

Parameters

Parameter Name Effective Data Type Default Value Notes
userName VARCHAR(63) None - required parameter The username that the user will use to connect to the server instance. This value should follow the rules for SQL Identifiers in Postgres.

If a no server role matches userName, then a server role is created with this name.

If a server role matching userName does exists on the server, then if okIfRoleExists is false, an EXCEPTION is raised. Otherwise, if okIfRoleExists is true, the function will raise a NOTICE, but continue creating the user.

Regardless of the value of okIfRoleExists, if the role already exists on the server, the password for the role is not changed.
fullName VARCHAR None - required parameter The user's given name, or some other identifying information that should be stored for later reference.
schemaName VARCHAR(63) NULL The name of the schema that should be assigned to the user being created. This value should follow the rules for SQL Identifiers in Postgres. If NULL, the default value, then userName is used as the schema name.

If a schema matching schemaName does not exists in the database ClassDB is installed in, it is created and ownership of the schema is assigned to the role matching userName.

If a schema matching schemaName does exist in the databse, then if okIfSchemaExists is false, an EXCEPTION is raised. Otherwise, if okifSchemaExists is true, then it is verified that the user being created has ownership of that schema. If they do not, an EXCEPTION is raised.
extraInfo VARCHAR NULL Any additional information that is desired to be stored.
okIfRoleExists BOOLEAN TRUE If TRUE, then no EXCEPTION is raised by the function if a role matching userName already exists on the server.
okIfSchemaExists BOOLEAN TRUE If TRUE, then no EXCEPTION is raised by the function if a schema matching schemaName already exists on the server AND the role matching userName is the owner of the schema.
initialPwd VARCHAR(128) NULL An initial password to set for the role. If NULL, the default value, then the folded value of userName is used as the initial password. This value is only used if a server role is created by ClassDB, so if the role already existed on the server, then the password for the role is not modified.

Examples

Function createInstructor creates an Instructor. This function takes three parameters:

  • instructorUserName - Required - The username the instructor will use to connect to the server instance
  • instructorName - Required - The instructor's given name. This name is stored for later reference
  • initialPwd - Optional - The instructor's initial password

Execute the following query to create an instructor with username "caldwellj", given name "Jessica Caldwell", and the default initial password (their username).

SELECT classdb.createInstructor('caldwellj', 'Jessica Caldwell');

Execute the following query to create the same user as above, but with an initial password of "LV8jzugmfFBF":

SELECT classdb.createInstructor('caldwellj', 'Jessica Caldwell', 'LV8jzugmfFBF');

In both the examples shown above, the user name is not case sensitive. To make the user name case-sensitive instead, execute the following query. This instructor will need to use the exact string CaldwellJ as user name at log in:

SELECT classdb.createInstructor('"CaldwellJ"', 'Jessica Caldwell', 'LV8jzugmfFBF');

Students

Function createStudent creates a Student. This function takes four parameters:

  • studentUserName - Required - The username the student will use to connect to the server instance
  • studentName - Required - The student's given name. This name is stored for later reference
  • schoolID - Optional - The school issued ID assigned to the Student. If provided, it is stored for later reference
  • initialPwd - Optional - The student's initial password

Examples

Execute the following query to create a student with username "bell001", given name "Emmett Bell", no school-issued ID, and the default initial password (their username).

SELECT classdb.createStudent('bell001', 'Emmett Bell');

Execute the following query to create the same user as above, but with a school-issued ID "B584452" and the default initial password:

SELECT classdb.createStudent('bell001', 'Emmett Bell', 'B584452');

Execute the following query to create the same student, but with an initial password of "w18nwMcK&606":

SELECT classdb.createStudent('bell001', 'Emmett Bell', 'B584452', 'w18nwMcK&606');

Finally, execute the following query to create the same student with the same explicit initial password, but not provide a school-issued ID:

SELECT classdb.createStudent('bell001', 'Emmett Bell', NULL, 'w18nwMcK&606');

DBManagers

Function createDBManager creates a DBManager. This function takes two parameters:

  • managerUserName - Required - The username the dbmanager will use to connect to the server instance
  • initialPwd - Optional - The dbmanager's initial password

Examples

Execute the following query to create a dbmanager with username "martine", and the default initial password (their username).

SELECT classdb.createDBManager('martine');

Execute the following query to create the same user as above, but with an initial password of "Cid8&88#M8Y8":

SELECT classdb.createDBManager('martine', 'Cid8&88#M8Y8');

In both the examples shown above, the user name is not case sensitive. To make the user name case-sensitive instead, execute the following query. This dbmanager will need to use the exact string Martine as user name at log in:

SELECT classdb.createDBManager('"Martine"', 'Cid8&88#M8Y8');

Clone this wiki locally