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
