def from_ip(cls, ip): result = sql_query("SELECT c.nutzer_id FROM computer as c " "LEFT JOIN nutzer as n " "ON c.nutzer_id = n.nutzer_id " "WHERE c_ip = %s " "AND (n.status < 8 OR n.status > 10) " "ORDER BY c.nutzer_id DESC", (ip,)).fetchone() if result is None: return AnonymousUserMixin() username = sql_query("SELECT unix_account FROM nutzer " "WHERE nutzer_id = %s", (result['nutzer_id'],)).fetchone()['unix_account'] user = cls.get(username) if not user: logger.warning("User %s could not be fetched from LDAP", username, extra={'data': { 'username': username, 'user_id': result['nutzer_id'], }}) return AnonymousUserMixin() return user
def create(self, password): sql_query( "CREATE DATABASE " "IF NOT EXISTS `%s`" % self.user.uid, database=db_helios ) self.change_password(password)
def drop(self): sql_query( "DROP DATABASE " "IF EXISTS `%s`" % self.user.uid, database=db_helios ) sql_query( "DROP USER %s@'10.1.7.%%'", (self.user.uid,), database=db_helios )
def from_ip(ip): result = sql_query("SELECT c.nutzer_id FROM computer as c " "LEFT JOIN nutzer as n " "ON c.nutzer_id = n.nutzer_id " "WHERE c_ip = %s " "AND (n.status < 8 OR n.status > 10) " "ORDER BY c.nutzer_id DESC", (ip,)).fetchone() if result is None: return AnonymousUserMixin() username = sql_query("SELECT unix_account FROM nutzer " "WHERE nutzer_id = %s", (result['nutzer_id'],)).fetchone()['unix_account'] return User.get(username, ip=ip)
def change_password(self, password): user = sql_query( "SELECT user " "FROM mysql.user " "WHERE user = %s", (self.user.uid,), database=db_helios ).fetchall() if not user: sql_query( "CREATE USER %s@'10.1.7.%%' " "IDENTIFIED BY %s", (self.user.uid, password), database=db_helios ) else: sql_query( "SET PASSWORD " "FOR %s@'10.1.7.%%' = PASSWORD(%s)", (self.user.uid, password), database=db_helios ) sql_query( "GRANT SELECT, INSERT, UPDATE, DELETE, " "ALTER, CREATE, DROP, INDEX, LOCK TABLES " "ON `%s`.* " "TO %%s@'10.1.7.%%%%'" % self.user.uid, (self.user.uid,), database=db_helios )
def has_db(self): try: userdb = sql_query( "SELECT SCHEMA_NAME " "FROM INFORMATION_SCHEMA.SCHEMATA " "WHERE SCHEMA_NAME = %s", (self.user.uid,), database=db_helios ).fetchone() return userdb is not None except OperationalError: logger.critical("User db of user %s unreachable", self.user.uid) raise
def cache_information(self): user = sql_query( "SELECT nutzer_id, wheim_id, etage, zimmernr, status " "FROM nutzer " "WHERE unix_account = %s", (self.uid,) ).fetchone() if not user: logger.critical("User %s does not have a database entry", self.uid, extra={'stack': True}) raise DBQueryEmpty("No User found for unix_account '{}'" .format(self.uid)) self._id = user['nutzer_id'] self._address = "{0} / {1} {2}".format( # MySQL Dormitory IDs in are from 1-11, so we map to 0-10 with x-1 DORMITORIES[user['wheim_id'] - 1], user['etage'], user['zimmernr'] ) self._status_id = user['status'] devices = sql_query( "SELECT c_etheraddr, c_ip, c_hname, c_alias " "FROM computer " "WHERE nutzer_id = %s", (user['nutzer_id']) ).fetchall() if devices: self._devices = [{ 'ip': device['c_ip'], 'mac': device['c_etheraddr'].upper(), 'hostname': device['c_hname'], 'hostalias': device['c_alias'], } for device in devices] else: logger.warning("User {} (id {}) does not have any devices" .format(self.uid, self._id)) self._devices = [] # cache credit current_timetag = timetag_today() try: # aggregated credit from 1(MEZ)/2(MESZ) AM credit = sql_query( "SELECT amount FROM credit " "WHERE user_id = %(id)s " "AND timetag >= %(today)s - 1 " "ORDER BY timetag DESC LIMIT 1", {'today': current_timetag, 'id': self._id} ).fetchone()['amount'] # subtract the current traffic not yet aggregated in `credit` traffic = sql_query( "SELECT input + output as throughput " "FROM traffic.tuext AS t " "LEFT JOIN computer AS c on c.c_ip = t.ip " "WHERE c.nutzer_id = %(id)s AND t.timetag = %(today)s", {'today': current_timetag, 'id': self._id} ).fetchone() credit -= traffic['throughput'] except OperationalError as e: logger.critical("Unable to connect to MySQL server", extra={'data': {'exception_args': e.args}}) self._credit = None raise else: self._credit = round(credit / 1024, 2) # cache traffic history self._traffic_history = [] for delta in range(-6, 1): current_timetag = timetag_today() + delta day = datetime.today() + timedelta(days=delta) traffic_of_the_day = sql_query( "SELECT sum(t.input) as input, sum(t.output) as output, " "sum(t.input+t.output) as throughput " "FROM traffic.tuext as t " "LEFT JOIN computer AS c ON c.c_ip = t.ip " "WHERE t.timetag = %(timetag)s AND c.nutzer_id = %(id)s", {'timetag': current_timetag, 'id': self._id}, ).fetchone() credit_of_the_day = sql_query( "SELECT amount FROM credit " "WHERE user_id = %(id)s " "AND timetag >= %(timetag)s - 1 " "ORDER BY timetag DESC LIMIT 1", {'timetag': current_timetag, 'id': self._id}, ).fetchone()['amount'] self._traffic_history.append({ 'day': day.weekday(), 'input': traffic_of_the_day['input'] / 1024, 'output': traffic_of_the_day['output'] / 1024, 'throughput': traffic_of_the_day['throughput'] / 1024, 'credit': credit_of_the_day / 1024, })
def get_information(self): """Executes select query for the username and returns a prepared dict. * Dormitory IDs in Mysql are from 1-11, so we map to 0-10 with "x-1". Returns "-1" if a query result was empty (None), else returns the prepared dict. """ userinfo = {} user = sql_query( "SELECT nutzer_id, wheim_id, etage, zimmernr, status " "FROM nutzer " "WHERE unix_account = %s", (self.uid,) ).fetchone() if not user: raise DBQueryEmpty mysql_id = user['nutzer_id'] userinfo.update( id=info_property( "{}-{}".format(mysql_id, calculate_userid_checksum(mysql_id))), address=info_property("{0} / {1} {2}".format( DORMITORIES[user['wheim_id'] - 1], user['etage'], user['zimmernr'] )), # todo use more colors (yellow for finances etc.) status=info_property(status_string_from_id(user['status']), status_color=(STATUS_COLORS.GOOD if user['status'] is 1 else None)), ) computer = sql_query( "SELECT c_etheraddr, c_ip, c_hname, c_alias " "FROM computer " "WHERE nutzer_id = %s", (user['nutzer_id']) ).fetchone() if not computer: raise DBQueryEmpty userinfo.update( ip=info_property(computer['c_ip']), mail=info_property(self.mail, actions={ACTIONS.EDIT, ACTIONS.DELETE}), mac=info_property(computer['c_etheraddr'].upper(), actions={ACTIONS.EDIT}), # todo figure out where that's being used hostname=info_property(computer['c_hname']), hostalias=info_property(computer['c_alias']) ) try: if user_has_mysql_db(self.uid): user_db_prop = info_property( gettext("Aktiviert"), status_color=STATUS_COLORS.GOOD, actions={ACTIONS.EDIT} ) else: user_db_prop = info_property( gettext("Nicht aktiviert"), status_color=STATUS_COLORS.INFO, actions={ACTIONS.EDIT} ) except OperationalError: logger.critical("User db unreachable") user_db_prop = info_property(gettext( "Datenbank nicht erreichbar")) finally: userinfo.update(userdb=user_db_prop) return userinfo