def add_token_to_database(encoded_token, identity_claim):
"""
Adds a new token to the database. It is not revoked when it is added.
:param identity_claim:
"""
decoded_token = decode_token(encoded_token)
jti = decoded_token['jti']
token_type = decoded_token['type']
user_identity = decoded_token[identity_claim]
expires = _epoch_utc_to_datetime(decoded_token['exp'])
revoked = 0
currenttime = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
str_query = """INSERT INTO token_blacklist (token_id, token_type, user_identity, revoked, createAt, updateAt, expiresAt)
VALUES ('{jti}', '{token_type}', '{user_identity}', '{revoked}','{createAt}','{updateAt}','{expiresAt}');""".format(
jti=jti,
token_type=token_type,
user_identity=user_identity,
revoked=revoked,
createAt=currenttime,
updateAt=currenttime,
expiresAt=expires)
try:
cursor.execute(str_query)
cnxn.commit()
return True
except pyodbc.Error as ex:
logging.error(ex.args[1])
return False
def getUserMenu(userId):
str_query = """select [g].[module_group_id], [g].[module_group_name] as [module_group],
[f].[module_id], [m].[module_name], [m].[module_icon], [m].[module_display_order], [f].[menu_id] as [form_id], [f].[menu_name] as [form_name],
[f].[menu_icon] as [form_icon], [f].[menu_display_order], [f].[url],
CASE WHEN b.menu_id IS NULL THEN 0 ELSE 1 END AS bookmark,
CASE WHEN B.menu_id IS NULL THEN 'fa fa-star-o text-green' ELSE 'fa fa-star text-grey' END AS bookmark_icon,
[g].[display_order], [f].[is_old_menu], [f].[old_permission], [f].[disabled], [f].[checkPermissions], [f].[form_active]
from [MainMenu] as [f]
left join [module] as [m] on [m].[module_id] = [f].[module_id]
left join [module_group] as [g] on [g].[module_group_id] = [m].[module_group_id]
left join [bookmarkmenu] as [b] on [b].[menu_id] = [f].[menu_id] and [b].[user_id] = '{user_id}'
where [f].[disabled] = '0' and [f].[is_menu] = '1'
order by g.display_order, m.module_display_order, f.menu_display_order""".format(
user_id=userId)
try:
cursor.execute(str_query)
result = cursor.fetchall()
data = [
dict(zip([key[0] for key in cursor.description], row))
for row in result
]
return data
except pyodbc.Error as ex:
logging.error(ex.args[1])
return None
def GetSesionID():
str_query = """ select top 1 [s].*, [e].[emp_sex], [u].[user_login_name] from [sessions] as [s]
left join [users] as [u] on [u].[user_id] = [s].[UserID]
left join [employees] as [e] on [e].[emp_id] = [s].[UserID]
order by [s].[SessionID] desc"""
try:
cursor.execute(str_query)
resuft = cursor.fetchone()
return resuft
except pyodbc.Error as ex:
logging.error(ex.args[1])
return None
def prune_database():
"""
Delete tokens that have expired from the database.
How (and if) you call this is entirely up you. You could expose it to an
endpoint that only administrators could call, you could run it as a cron,
set it up with flask cli, etc.
"""
now = datetime.now()
sql_query = "DELETE FROM products WHERE expiresAt < '{}'".format(now)
cursor.execute(sql_query)
cnxn.commit()
def getUserInfo(userName):
str_query = """SELECT * from users
INNER JOIN employees ON user_id = emp_id
LEFT JOIN mailgroup ON director_emp_id = emp_id
WHERE user_login_name LIKE '{userName}' and status = 0 """.format(
userName=userName)
try:
cursor.execute(str_query)
resuft = cursor.fetchone()
return resuft
except pyodbc.Error as ex:
logging.error(ex.args[1])
return None
def getCustomizeMenu(userId):
str_query = """select * from [customize_bookmark_menu] where [user_id] = '{}'""".format(
userId)
try:
cursor.execute(str_query)
result = cursor.fetchall()
data = [
dict(zip([key[0] for key in cursor.description], row))
for row in result
]
return data
except pyodbc.Error as ex:
logging.error(ex.args[1])
return None
def get_user_tokens(token_id):
"""
Returns all of the tokens, revoked and unrevoked, that are stored for the
given user
"""
sql_query = "SELECT * FROM token_blacklist WHERE token_blacklist.token_id = '{}' ".format(
token_id)
try:
cursor.execute(sql_query)
result = cursor.fetchone()
return result
except pyodbc.Error as ex:
logging.error(ex.args[1])
return None
def CreateSessions(sessions):
str_query = """insert into [sessions] ([SessionID], [UserID], [DateTimeLogin], [IPAddress], [ServerID])
values ('{SessionID}', '{UserID}', '{DateTimeLogin}', '{IPAddress}', '{ServerID}')""".format(
SessionID=sessions['SessionID'],
UserID=sessions['UserID'],
DateTimeLogin=sessions['DateTimeLogin'],
IPAddress=sessions['IPAddress'],
ServerID=sessions['ServerID'])
try:
cursor.execute(str_query)
cnxn.commit()
return True
except pyodbc.Error as ex:
logging.error(ex.args[1])
return False
def revoke_token(token_id, user):
"""
Revokes the given token. Raises a TokenNotFound error if the token does
not exist in the database
"""
try:
sql_query = """UPDATE token_blacklist
SET revoked = 1
WHERE token_id = '{token_id}' AND user_identity = '{user}' ;""".format(
token_id=token_id, user=user)
cursor.execute(sql_query)
cnxn.commit()
return True
except pyodbc.Error as ex:
logging.error(ex.args[1])
return False
def is_token_revoked(decoded_token):
"""
Checks if the given token is revoked or not. Because we are adding all the
tokens that we create into this database, if the token is not present
in the database we are going to consider it revoked, as we don't know where
it was created.
"""
jti = decoded_token['jti']
try:
str_query = """SELECT revoked
FROM token_blacklist
WHERE token_id ='{}'""".format(jti)
cursor.execute(str_query)
result = cursor.fetchone()
if result.revoked == 1:
return True
return False
except:
if not pyodbc.Error:
logging.error(pyodbc.Error.args[1])
return False
def GetBookmarkMenu(userId):
str_query = """select [b].[menu_id],
CASE WHEN b.bookmark_type = 'custom' THEN c.menu_title ELSE m.menu_name END as menu_name,
CASE WHEN b.bookmark_type = 'custom' THEN 'fa fa-th' ELSE m.menu_icon END as menu_icon,
CASE WHEN b.bookmark_type = 'custom' THEN c.menu_url ELSE m.form_active END as form_active,
CASE WHEN b.bookmark_type = 'custom' THEN c.target ELSE '' END as target,
CASE WHEN m.is_old_menu = 1 THEN m.url ELSE (CASE WHEN c.menu_type = 1 THEN c.menu_url ELSE '' END) END as url,
CASE WHEN b.bookmark_type IS NULL THEN 0 ELSE 1 END as order_type, [b].[display_order]
from [BookmarkMenu] as [b] left join [MainMenu] as [m] on [m].[menu_id] = [b].[menu_id]
left join [customize_bookmark_menu] as [c] on [b].[menu_id] = CONVERT(varchar, c.custom_id)
where [b].[user_id] = '{}' order by [b].[display_order] asc, [order_type] desc""".format(
userId)
try:
cursor.execute(str_query)
result = cursor.fetchall()
data = [
dict(zip([key[0] for key in cursor.description], row))
for row in result
]
return data
except pyodbc.Error as ex:
logging.error(ex.args[1])
return None