function adduser($course, $tmp_roles, $new_username, $mysqli)
{
$new_password = trim($_POST['new_password']);
$new_surname = StringUtils::my_ucwords(trim($_POST['new_surname']));
$new_title = $_POST['new_users_title'];
$new_email = trim($_POST['new_email']);
$new_first_names = StringUtils::my_ucwords(trim($_POST['new_first_names']));
$new_year = $_POST['new_year'];
$new_gender = $_POST['new_gender'];
$userid = UserUtils::create_user($new_username, $new_password, $new_title, $new_first_names, $new_surname, $new_email, $course, $new_gender, $new_year, $tmp_roles, '', $mysqli);
return $userid;
}
function update_module_enrolement($module, $idMod, $sms_api, $mysqli = 'NOTSET', $session = 'NOTSET', $demomode = false)
{
// run module enrolement for select code
if ($mysqli == 'NOTSET') {
global $mysqli;
}
if ($session == 'NOTSET') {
$session = date_utils::get_current_academic_year();
}
$session_parts = explode('/', $session);
$enrolements = 0;
$deletions = 0;
$enrolement_details = '';
$deletion_details = '';
// UoN code to strip off prefix codes.
//------------------------------------
$replaced_module = str_replace('_UNMC', '', $module);
$replaced_module = str_replace('_UNNC', '', $replaced_module);
//------------------------------------
// Get the currently enrolled students in Rogo for the module.
$current_users = array();
$student_data = $mysqli->prepare("SELECT modules_student.id, users.id, username, grade, title, surname, first_names, initials, roles, yearofstudy, auto_update, sid.student_id FROM (modules_student, users) LEFT JOIN sid ON users.id = sid.userID WHERE modules_student.userID = users.id AND calendar_year = ? AND idMod = ?");
$student_data->bind_param('si', $session, $idMod);
$student_data->execute();
$student_data->store_result();
$student_data->bind_result($sm_id, $uid, $username, $grade, $title, $surname, $first_names, $initials, $roles, $year, $auto_update, $student_id);
while ($student_data->fetch()) {
$current_users[$username]['delete'] = $auto_update;
// Set users to be deleted if added via SATURN, set otherwise lower down after checking with SMS
$current_users[$username]['smID'] = $sm_id;
$current_users[$username]['userID'] = $uid;
$current_users[$username]['grade'] = $grade;
$current_users[$username]['title'] = $title;
$current_users[$username]['surname'] = $surname;
$current_users[$username]['first_names'] = $first_names;
$current_users[$username]['initials'] = $initials;
$current_users[$username]['roles'] = $roles;
$current_users[$username]['year'] = $year;
$current_users[$username]['auto_update'] = $auto_update;
$current_users[$username]['student_id'] = $student_id;
}
$student_data->close();
$c_u = $current_users;
// Look up SMS
$returned_data = @file_get_contents($sms_api . "&code={$replaced_module}&year=" . $session_parts[0]);
$xml = false;
if ($returned_data !== false) {
$xml = new SimpleXMLElement($returned_data);
}
if (is_object($xml) and !isset($xml->ErrorMessage) and !isset($xml->Module->ModuleError)) {
foreach ($xml->Module->Membership->Student as $sms) {
$sms->Title = trim($sms->Title);
$sms->Surname = trim($sms->Surname);
$sms->Forename = trim($sms->Forename);
$sms->CourseCode = trim($sms->CourseCode);
$sms->Username = trim($sms->Username);
$sms->Email = trim($sms->Email);
$sms->Gender = trim($sms->Gender);
$sms->YearofStudy = trim($sms->YearofStudy);
$sms->StudentID = trim($sms->StudentID);
$lookup_username = trim($sms->Username);
// Make sure we have a proper username - it can sometimes be blank in SATURN data
if ($sms->Email != '') {
// Try to extract from email address
$un_parts = explode('@', $sms->Email);
$lookup_username = $un_parts[0];
}
if ($lookup_username != '') {
if (isset($current_users[$lookup_username]['delete'])) {
$current_users[$lookup_username]['delete'] = 0;
// Mark as being legitimate
} else {
// Student missing from Rogo module
$student_data = $mysqli->prepare("SELECT id, yearofstudy, initials, grade, title, surname, first_names, roles, email, COALESCE(sid.student_id,'SID_ERROR') FROM users LEFT JOIN sid ON users.id = sid.userID WHERE username = ? LIMIT 1");
// Do they have a Rogo user record?
$student_data->bind_param('s', $lookup_username);
$student_data->execute();
$student_data->store_result();
$student_data->bind_result($tmp_userID, $tmp_yearofstudy, $tmp_initials, $tmp_grade, $tmp_title, $tmp_surname, $tmp_first_names, $tmp_roles, $tmp_email, $tmp_student_id);
$student_data->fetch();
if ($student_data->num_rows == 0) {
// Going to have to create a whole new account for the user
$names = explode(' ', $sms->Forename);
$initials = '';
foreach ($names as $tmp_name) {
$initials .= $tmp_name[0];
}
if (!$demomode) {
$tmp_userID = UserUtils::create_user($lookup_username, '', $sms->Title, $sms->Forename, $sms->Surname, $sms->Email, $sms->CourseCode, $sms->Gender, $sms->YearofStudy, 'Student', $sms->StudentID, $mysqli);
if ($tmp_userID == false) {
echo 'ERROR: unable to establish surname for ' . $lookup_username . '<br />';
continue;
}
}
$current_users[$lookup_username]['userID'] = $tmp_userID;
$current_users[$lookup_username]['grade'] = $sms->CourseCode;
$current_users[$lookup_username]['title'] = $sms->Title;
$current_users[$lookup_username]['surname'] = $sms->Surname;
$current_users[$lookup_username]['first_names'] = $tmp_first_names;
$current_users[$lookup_username]['initials'] = $initials;
$current_users[$lookup_username]['roles'] = 'Student';
$current_users[$lookup_username]['email'] = $sms->Email;
$current_users[$lookup_username]['year'] = $sms->YearofStudy;
$current_users[$lookup_username]['student_id'] = $sms->StudentID;
$current_users[$lookup_username]['delete'] = 0;
} else {
$current_users[$lookup_username]['userID'] = $tmp_userID;
$current_users[$lookup_username]['grade'] = $tmp_grade;
$current_users[$lookup_username]['title'] = $tmp_title;
$current_users[$lookup_username]['surname'] = $tmp_surname;
$current_users[$lookup_username]['first_names'] = $tmp_first_names;
$current_users[$lookup_username]['initials'] = $tmp_initials;
$current_users[$lookup_username]['roles'] = $tmp_roles;
$current_users[$lookup_username]['email'] = $tmp_email;
$current_users[$lookup_username]['year'] = $tmp_yearofstudy;
$current_users[$lookup_username]['student_id'] = $tmp_student_id;
$current_users[$lookup_username]['delete'] = 0;
}
// Add student onto the module
$auto_update = 1;
//set auto_update to student module association
if (!$demomode) {
$success = UserUtils::add_student_to_module($tmp_userID, $idMod, 1, $session, $mysqli, $auto_update);
}
if ($success) {
$enrolements++;
if ($enrolement_details == '') {
$enrolement_details = $lookup_username;
} else {
$enrolement_details .= ',' . $lookup_username;
}
}
$student_data->close();
}
// Check to see if any details of the user account need updating.
if (strtoupper(substr($sms->ReasonForLeaving, 0, 3)) == 'W/D') {
$new_roles = 'left';
} elseif (stripos($sms->ReasonForLeaving, 'not permitted to progress') !== false) {
$new_roles = 'left';
} elseif ($sms->ReasonForLeaving == 'Successfully completed course') {
$new_roles = 'graduate';
} else {
$new_roles = $current_users[$lookup_username]['roles'];
// Keep the roles same as they were.
if ($new_roles != 'left' and $new_roles != 'graduate' and strpos($new_roles, 'Student') === false) {
$new_roles .= ',Student';
// Add in 'student' role if missing.
}
}
$names = explode(' ', $sms->Forename);
$tmp_initials = '';
foreach ($names as $tmp_name) {
if (isset($tmp_name[0])) {
$tmp_initials .= $tmp_name[0];
}
}
if ($current_users[$lookup_username]['year'] != $sms->YearofStudy or $tmp_initials != $current_users[$lookup_username]['initials'] or $current_users[$lookup_username]['grade'] != $sms->CourseCode or $current_users[$lookup_username]['title'] != $sms->Title or $current_users[$lookup_username]['surname'] != $sms->Surname or $current_users[$lookup_username]['first_names'] != $sms->Forename or $current_users[$lookup_username]['roles'] != $new_roles or isset($current_users[$lookup_username]['email']) and $current_users[$lookup_username]['email'] != $sms->Email) {
$result = $mysqli->prepare("UPDATE users SET yearofstudy = ?, roles = ?, grade = ?, title = ?, surname = ?, first_names = ?, initials = ?, email = ? WHERE username = ?");
$result->bind_param('issssssss', $sms->YearofStudy, $new_roles, $sms->CourseCode, $sms->Title, $sms->Surname, $sms->Forename, $tmp_initials, $sms->Email, $lookup_username);
if (!$demomode) {
$result->execute();
}
$result->close();
}
// Check if SID needs updating - rare but could happen
if ($current_users[$lookup_username]['student_id'] != $sms->StudentID) {
if ($current_users[$lookup_username]['student_id'] == 'SID_ERROR') {
$result = $mysqli->prepare("INSERT INTO sid VALUES (?, ?)");
$result->bind_param('si', $sms->StudentID, $current_users[$lookup_username]['userID']);
$result->execute();
$result->close();
} else {
$result = $mysqli->prepare("UPDATE sid SET student_id = ? WHERE userID = ?");
$result->bind_param('si', $sms->StudentID, $current_users[$lookup_username]['userID']);
$result->execute();
$result->close();
}
}
} else {
echo 'ERROR: unable to establish username for ' . $sms->Title . ' ' . $sms->Surname . ', ' . $sms->Forename . ' (' . $sms->StudentID . ')<br />';
}
}
// Check for any extra students in Rogo but not in SATURN for module
foreach ($current_users as $username => $individual_user) {
if ($individual_user['delete'] == 1 and $individual_user['auto_update'] == 1) {
$result = $mysqli->prepare("DELETE FROM modules_student WHERE id = ?");
// Delete using primary key of 'modules_student'
$result->bind_param('i', $individual_user['smID']);
if (!$demomode) {
$result->execute();
}
$result->close();
$deletions++;
if ($deletion_details == '') {
$deletion_details = $username;
} else {
$deletion_details .= ',' . $username;
}
}
}
}
$import_type = '';
if ($enrolements > 0 or $deletions > 0) {
if ($sms_api == 'http://saturn-exports.nottingham.ac.uk/touchstone.ashx?campus=malaysia') {
$import_type = 'SATURN Malaysia';
} elseif ($sms_api == 'http://saturn-exports.nottingham.ac.uk/touchstone.ashx?campus=china') {
$import_type = 'SATURN China';
} else {
$import_type = 'SATURN UK';
}
$result = $mysqli->prepare("INSERT INTO sms_imports VALUES (NULL, NOW(), ?, ?, ?, ?, ?, ?, ?)");
$result->bind_param('sisisss', $idMod, $enrolements, $enrolement_details, $deletions, $deletion_details, $import_type, $session);
$result->execute();
$result->close();
}
$this->set_enrolement_no($enrolements, $module);
$this->set_deletion_no($deletions, $module);
$expdata = array();
if ($demomode) {
// Write out to temp
$dir = sys_get_temp_dir();
$expdata['status'] = $this->errorinfo;
$expdata['students'] = $c_u;
$expdata['moduledata'] = $xml;
$expdata['studentsa'] = $current_users;
file_put_contents($dir . '/' . 'uon-' . $module . '.txt', var_export($expdata, true));
file_put_contents($dir . '/' . 'sum-uon-' . $module . '.txt', "{$enrolements}, {$deletions}\r\n{$import_type}\r\n{$enrolement_details}\r\n{$deletion_details}\r\n");
}
}
public function createAccount()
{
$userObject = UserObject::get_instance();
if (!$userObject->has_role('SysAdmin')) {
return 'AccessDenied';
}
if (!isset($_POST['data'])) {
return 'No data';
}
$xml = new SimpleXMLElement($_POST['data']);
$fields = array('username', 'password', 'firstnames', 'title', 'surname', 'email', 'course', 'gender', 'yearofstudy', 'roles');
foreach ($fields as $field) {
if (isset($xml->{$field}) and $xml->{$field} != '') {
${$field} = $xml->{$field};
} else {
return 'Missing data: ' . $field;
}
}
if (isset($xml->studentid)) {
$studentid = $xml->studentid;
} else {
$studentid = '';
}
if ($roles != 'Student' and $roles != 'Staff' and $roles != 'Staff,Admin' and $roles != 'Staff,SysAdmin') {
return 'Incorrect value for roles: ' . $roles;
}
$success = UserUtils::create_user($username, $password, $title, $firstnames, $surname, $email, $course, $gender, $yearofstudy, $roles, $studentid, $this->db);
if ($success === false) {
return false;
} else {
return $success;
}
}
/**
* create the database and users if they do not exist
*
*/
static function createDatabase($dbname, $dbcharset)
{
global $string;
$res = self::$db->prepare("SHOW DATABASES LIKE '{$dbname}'");
$res->execute();
$res->store_result();
@ob_flush();
@flush();
if ($res->num_rows > 0) {
self::displayError(array('010' => sprintf($string['displayerror1'], $dbname)));
}
$res->close();
switch ($dbcharset) {
case 'utf8':
$collation = 'utf8_general_ci';
break;
default:
$collation = 'latin1_swedish_ci';
}
self::$db->query("CREATE DATABASE {$dbname} CHARACTER SET = {$dbcharset} COLLATE = {$collation}");
//have to use query here oldvers of php throw an error
if (self::$db->errno != 0) {
self::displayError(array('011' => $string['displayerror2']));
}
//select the newly created database
self::$db->change_user(self::$db_admin_username, self::$db_admin_passwd, self::$cfg_db_name);
//create tables
$tables = new databaseTables($dbcharset);
self::$db->autocommit(false);
while ($sql = $tables->next()) {
$res = self::$db->query($sql);
@ob_flush();
@flush();
if (self::$db->errno != 0) {
self::displayError(array('012' => $string['displayerror3'] . self::$db->error . "<br /> {$sql}"));
try {
$err = self::$db->error;
$mess = self::$db->errno;
throw new Exception("MySQL error {$err}", $mess);
} catch (Exception $e) {
echo "Error No: " . $e->getCode() . " - " . $e->getMessage() . "<br />";
}
self::$db->rollback();
}
}
self::$db->commit();
self::$cfg_db_username = self::$cfg_db_basename . '_auth';
self::$cfg_db_password = gen_password() . gen_password();
self::$cfg_db_student_user = self::$cfg_db_basename . '_stu';
self::$cfg_db_student_passwd = gen_password() . gen_password();
self::$cfg_db_staff_user = self::$cfg_db_basename . '_staff';
self::$cfg_db_staff_passwd = gen_password() . gen_password();
self::$cfg_db_external_user = self::$cfg_db_basename . '_ext';
self::$cfg_db_external_passwd = gen_password() . gen_password();
self::$cfg_db_sysadmin_user = self::$cfg_db_basename . '_sys';
self::$cfg_db_sysadmin_passwd = gen_password() . gen_password();
self::$cfg_db_sct_user = self::$cfg_db_basename . '_sct';
self::$cfg_db_sct_passwd = gen_password() . gen_password();
self::$cfg_db_inv_user = self::$cfg_db_basename . '_inv';
self::$cfg_db_inv_passwd = gen_password() . gen_password();
self::$cfg_cron_user = '******';
self::$cfg_cron_passwd = gen_password() . gen_password();
$priv_SQL = array();
//create 'database user authentication user' and grant permissions
self::$db->query("CREATE USER '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "' IDENTIFIED BY '" . self::$cfg_db_password . "'");
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_username . $string['wnotcreated'] . ' ' . self::$db->error));
}
//$priv_SQL[] = "REVOKE ALL PRIVILEGES ON $dbname.* FROM '". self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".admin_access TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT ON " . $dbname . ".courses TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".client_identifiers TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".labs TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".lti_keys TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".lti_user TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".modules_student TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".paper_metadata_security TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, UPDATE, INSERT, DELETE ON " . $dbname . ".password_tokens TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".properties TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".schools TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT ON " . $dbname . ".sid TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".special_needs TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".sys_errors TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT,INSERT ON " . $dbname . ".temp_users TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".users TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".users_metadata TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".denied_log TO '" . self::$cfg_db_username . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "FLUSH PRIVILEGES";
foreach ($priv_SQL as $sql) {
self::$db->query($sql);
@ob_flush();
@flush();
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_username . $string['wnotpermission'] . ' ' . self::$db->error));
self::$db->rollback();
}
}
self::$db->commit();
$priv_SQL = array();
//create 'database user student user' and grant permissions
self::$db->query("CREATE USER '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "' IDENTIFIED BY '" . self::$cfg_db_student_passwd . "'");
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_student_user . $string['wnotcreated'] . ' ' . self::$db->error));
}
//$priv_SQL[] = "REVOKE ALL PRIVILEGES ON $dbname.* FROM '". self::$cfg_db_student_user . "'@'". self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".announcements TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".cache_median_question_marks TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".cache_paper_stats TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".cache_student_paper_marks TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".exam_announcements TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".feedback_release TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".help_log TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".help_searches TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".help_tutorial_log TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".client_identifiers TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".keywords_question TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".labs TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log0 TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log1 TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log2 TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log3 TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log4 TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log4_overall TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log5 TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log6 TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".log_extra_time TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".log_lab_end_time TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log_late TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log_metadata TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".lti_resource TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".lti_context TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".marking_override TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".modules TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT ON " . $dbname . ".modules_student TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".objectives TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".options TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".paper_feedback TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".paper_metadata_security TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".papers TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".properties TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".properties_modules TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".questions TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".question_exclude TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".question_statuses TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".reference_material TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".reference_modules TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".reference_papers TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".relationships TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".schools TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".sid TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".sessions TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".std_set TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".std_set_questions TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".state TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".student_help TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".special_needs TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".sys_errors TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".temp_users TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".users TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".users_metadata TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".access_log TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".denied_log TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".killer_questions TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".save_fail_log TO '" . self::$cfg_db_student_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "FLUSH PRIVILEGES";
foreach ($priv_SQL as $sql) {
self::$db->query($sql);
@ob_flush();
@flush();
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_student_user . $string['wnotpermission'] . ' ' . self::$db->error));
self::$db->rollback();
}
}
self::$db->commit();
$priv_SQL = array();
//create 'database user external user' and grant permissions
self::$db->query("CREATE USER '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "' IDENTIFIED BY '" . self::$cfg_db_external_passwd . "'");
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_external_user . $string['wnotcreated'] . ' ' . self::$db->error));
}
//$priv_SQL[] = "REVOKE ALL PRIVILEGES ON $dbname.* FROM '". self::$cfg_db_external_user . "'@'". self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT ON " . $dbname . ".help_log TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT ON " . $dbname . ".help_searches TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".keywords_question TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log0 TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log1 TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log2 TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log3 TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log4 TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log4_overall TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log5 TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log_late TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log_metadata TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".modules TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".modules_staff TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".options TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".papers TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".properties TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".questions TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".question_statuses TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".reference_material TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".reference_modules TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".reference_papers TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".review_comments TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".review_metadata TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".special_needs TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".std_set TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".std_set_questions TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".staff_help TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".student_help TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".sys_errors TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".users TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".access_log TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".denied_log TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".properties_reviewers TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".client_identifiers TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".labs TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".properties_modules TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".log_extra_time TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".log_lab_end_time TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".schools TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".paper_metadata_security TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".modules_student TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".question_exclude TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".users_metadata TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".marking_override TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".sid TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".student_notes TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".paper_notes TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".exam_announcements TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".relationships TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".feedback_release TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".cache_paper_stats TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".paper_feedback TO '" . self::$cfg_db_external_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "FLUSH PRIVILEGES";
foreach ($priv_SQL as $sql) {
self::$db->query($sql);
@ob_flush();
@flush();
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_external_user . $string['wnotpermission'] . ' ' . self::$db->error));
self::$db->rollback();
}
}
self::$db->commit();
$priv_SQL = array();
//create 'database user staff user' and grant permissions
self::$db->query("CREATE USER '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "' IDENTIFIED BY '" . self::$cfg_db_staff_passwd . "'");
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_staff_user . $string['wnotcreated'] . ' ' . self::$db->error));
}
//$priv_SQL[] = "REVOKE ALL PRIVILEGES ON $dbname.* FROM '". self::$cfg_db_staff_user . "'@'". self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".* TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".cache_median_question_marks TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".cache_paper_stats TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".cache_student_paper_marks TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".ebel TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".exam_announcements TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".feedback_release TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".folders TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".folders_modules_staff TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".help_log TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".help_searches TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".help_tutorial_log TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".hofstee TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".keywords_question TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".keywords_user TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log0 TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log1 TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log2 TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log3 TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".log4 TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".log4_overall TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".log5 TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".log6 TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".log_late TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".log_metadata TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".lti_resource TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".lti_context TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".marking_override TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT ON " . $dbname . ".modules TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".modules_staff TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".modules_student TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".objectives TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".options TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".paper_metadata_security TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".paper_notes TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".paper_feedback TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".papers TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".password_tokens TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".performance_main TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".performance_details TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".properties TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".properties_modules TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".question_exclude TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".questions TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".question_statuses TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".questions_metadata TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".questions_modules TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".recent_papers TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".reference_material TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".reference_modules TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".reference_papers TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".relationships TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".review_comments TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".review_metadata TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, DELETE ON " . $dbname . ".scheduling TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".sessions TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".sid TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".sms_imports TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".special_needs TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".std_set TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".std_set_questions TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".state TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".student_notes TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".temp_users TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".textbox_marking TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".textbox_remark TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".track_changes TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".users TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".users_metadata TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT ON " . $dbname . ".access_log TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT ON " . $dbname . ".denied_log TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".properties_reviewers TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".sys_errors TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".killer_questions TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT ON " . $dbname . ".save_fail_log TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, UPDATE ON " . $dbname . ".toilet_breaks TO '" . self::$cfg_db_staff_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "FLUSH PRIVILEGES";
foreach ($priv_SQL as $sql) {
self::$db->query($sql);
@ob_flush();
@flush();
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_staff_user . $string['wnotpermission'] . ' ' . self::$db->error));
self::$db->rollback();
}
}
self::$db->commit();
$priv_SQL = array();
//create 'database user SCT user' and grant permissions
self::$db->query("CREATE USER '" . self::$cfg_db_sct_user . "'@'" . self::$cfg_web_host . "' IDENTIFIED BY '" . self::$cfg_db_sct_passwd . "'");
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_sct_user . $string['wnotcreated'] . ' ' . self::$db->error));
}
//$priv_SQL[] = "REVOKE ALL PRIVILEGES ON $dbname.* FROM '". self::$cfg_db_sct_user . "'@'". self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".options TO '" . self::$cfg_db_sct_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".paper_metadata_security TO '" . self::$cfg_db_sct_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".paper_notes TO '" . self::$cfg_db_sct_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".papers TO '" . self::$cfg_db_sct_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".properties TO '" . self::$cfg_db_sct_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".questions TO '" . self::$cfg_db_sct_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".question_statuses TO '" . self::$cfg_db_sct_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".questions_metadata TO '" . self::$cfg_db_sct_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".sct_reviews TO '" . self::$cfg_db_sct_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".denied_log TO '" . self::$cfg_db_sct_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "FLUSH PRIVILEGES";
foreach ($priv_SQL as $sql) {
self::$db->query($sql);
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_sct_user . $string['wnotpermission'] . ' ' . self::$db->error));
self::$db->rollback();
}
}
self::$db->commit();
$priv_SQL = array();
//create 'database user Invigilator user' and grant permissions
self::$db->query("CREATE USER '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "' IDENTIFIED BY '" . self::$cfg_db_inv_passwd . "'");
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_inv_user . $string['wnotcreated'] . ' ' . self::$db->error));
}
//$priv_SQL[] = "REVOKE ALL PRIVILEGES ON $dbname.* FROM '". self::$cfg_db_inv_user . "'@'". self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".exam_announcements TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".client_identifiers TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".labs TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".log2 TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".log_metadata TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".log_extra_time TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $dbname . ".log_lab_end_time TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".modules_student TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".paper_notes TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".properties TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".properties_modules TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".modules TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".papers TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".questions TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".question_statuses TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE ON " . $dbname . ".student_notes TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".sid TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".special_needs TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT ON " . $dbname . ".users TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".access_log TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT INSERT ON " . $dbname . ".denied_log TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, DELETE ON " . $dbname . ".toilet_breaks TO '" . self::$cfg_db_inv_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "FLUSH PRIVILEGES";
foreach ($priv_SQL as $sql) {
self::$db->query($sql);
@ob_flush();
@flush();
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_inv_user . $string['wnotpermission'] . ' ' . self::$db->error));
self::$db->rollback();
}
}
self::$db->commit();
$priv_SQL = array();
//create 'database user sysadmin user' and grant permissions
self::$db->query("CREATE USER '" . self::$cfg_db_sysadmin_user . "'@'" . self::$cfg_web_host . "' IDENTIFIED BY '" . self::$cfg_db_sysadmin_passwd . "'");
if (self::$db->errno != 0) {
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_sysadmin_user . $string['wnotcreated'] . ' ' . self::$db->error));
}
//$priv_SQL[] = "REVOKE ALL PRIVILEGES ON $dbname.* FROM '". self::$cfg_db_sysadmin_user . "'@'". self::$cfg_web_host . "'";
$priv_SQL[] = "GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, DROP ON " . $dbname . ".* TO '" . self::$cfg_db_sysadmin_user . "'@'" . self::$cfg_web_host . "'";
$priv_SQL[] = "FLUSH PRIVILEGES";
foreach ($priv_SQL as $sql) {
self::$db->query($sql);
@ob_flush();
@flush();
if (self::$db->errno != 0) {
echo self::$db->error . "<br />";
self::displayError(array('013' => $string['wdatabaseuser'] . self::$cfg_db_sysadmin_user . $string['wnotpermission'] . ' ' . self::$db->error));
self::$db->rollback();
}
}
self::$db->commit();
//create sysadmin user
UserUtils::create_user($_POST['SysAdmin_username'], $_POST['SysAdmin_password'], $_POST['SysAdmin_title'], $_POST['SysAdmin_first'], $_POST['SysAdmin_last'], $_POST['SysAdmin_email'], 'University Lecturer', '', '1', 'Staff,SysAdmin', '', self::$db);
//create cron user
UserUtils::create_user(self::$cfg_cron_user, self::$cfg_cron_passwd, '', '', 'cron', '', '', '', '', 'Staff,SysCron', '', self::$db);
//create 100 guest accounts
for ($i = 1; $i <= 100; $i++) {
UserUtils::create_user('user' . $i, '', 'Dr', 'A', 'User' . $i, '', 'none', '', '1', 'Student', '', self::$db);
}
self::$db->commit();
//add unknown school & faculty
$facultyID = FacultyUtils::add_faculty('UNKNOWN Faculty', self::$db);
$scoolID = SchoolUtils::add_school($facultyID, 'UNKNOWN School', self::$db);
//add traing school
$facultyID = FacultyUtils::add_faculty('Administrative and Support Units', self::$db);
$scoolID = SchoolUtils::add_school($facultyID, 'Training', self::$db);
//create special modules
module_utils::add_modules('TRAIN', 'Training Module', 1, $scoolID, '', '', 0, false, false, false, true, null, null, self::$db, 0, 0, 1, 1, '07/01');
module_utils::add_modules('SYSTEM', 'Online Help', 1, $scoolID, '', '', 0, true, true, true, true, null, null, self::$db, 0, 0, 1, 1, '07/01');
self::$db->commit();
// Create default question statuses
$statuses = array(array('name' => 'Normal', 'exclude_marking' => false, 'retired' => false, 'is_default' => true, 'change_locked' => true, 'validate' => true, 'display_warning' => 0, 'colour' => '#000000', 'display_order' => 0), array('name' => 'Retired', 'exclude_marking' => false, 'retired' => true, 'is_default' => false, 'change_locked' => true, 'validate' => false, 'display_warning' => 1, 'colour' => '#808080', 'display_order' => 1), array('name' => 'Incomplete', 'exclude_marking' => false, 'retired' => false, 'is_default' => false, 'change_locked' => false, 'validate' => false, 'display_warning' => 1, 'colour' => '#000000', 'display_order' => 2), array('name' => 'Experimental', 'exclude_marking' => true, 'retired' => false, 'is_default' => false, 'change_locked' => false, 'validate' => true, 'display_warning' => 0, 'colour' => '#808080', 'display_order' => 3), array('name' => 'Beta', 'exclude_marking' => false, 'retired' => false, 'is_default' => false, 'change_locked' => false, 'validate' => true, 'display_warning' => 1, 'colour' => '#000000', 'display_order' => 4));
foreach ($statuses as $data) {
$qs = new QuestionStatus(self::$db, $string, $data);
$qs->save();
}
//FLUSH PRIVILEGES
self::$db->query("FLUSH PRIVILEGES");
if (self::$db->errno != 0) {
self::logWarning(array('014' => $string['logwarning20']));
}
self::$db->commit();
self::$db->autocommit(false);
}
$tmp_roles = 'Student';
break;
}
$new_password = trim($_POST['new_password']);
$new_surname = UserUtils::my_ucwords(trim($_POST['new_surname']));
$new_username = trim($_POST['new_username']);
$new_email = trim($_POST['new_email']);
$new_first_names = UserUtils::my_ucwords(trim($_POST['new_first_names']));
$new_grade = $_POST['new_grade'];
$new_year = isset($_POST['new_year']) ? $_POST['new_year'] : 1;
}
if (isset($_POST['submit']) and $unique_username == true) {
if ($new_username == '' or strpos($new_username, '_') !== false or $new_surname == '' or $new_email == '' or $new_first_names == '' or $new_grade == '') {
$problem = true;
} else {
$new_userID = UserUtils::create_user($new_username, $new_password, $_POST['new_users_title'], $new_first_names, $new_surname, $new_email, $new_grade, $_POST['new_gender'], $new_year, $tmp_roles, $_POST['new_sid'], $mysqli);
// Send out email welcome.
if (isset($_POST['new_welcome']) and $_POST['new_welcome'] != '') {
$result = $mysqli->prepare("SELECT email FROM users WHERE username = ?");
$result->bind_param('s', $userObject->get_username());
$result->execute();
$result->bind_result($tmp_email);
$result->fetch();
$result->close();
$subject = "{$string['newrogoaccount']}";
$headers = "From: {$tmp_email}\n";
$headers .= "MIME-Version: 1.0\nContent-type: text/html; charset=UTF-8\n";
$headers .= "bcc: {$tmp_email}\n";
$sname = ucwords($_POST['new_surname']);
$message = <<<MESSAGE
<!DOCTYPE html>
