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>