def search_for_source(attribute: str, value: str) -> list:
'''
Function to query a specific search on the sources table.
Two arguments are being passed: attribute and value.
The attribute indicates the column to search for (e.g. by ID or by Name).
The value indicates the value to search for within the column.
Example: search_for_source('name', 'Google') -> would search for 'Google' in the 'name' column
The return value contains all results from the query (i.e. the 'fetchall()' result)
'''
cursor = dbc.establish_connection('data').cursor()
if attribute == "threat_level":
value = int(value)
stmt = sql.SQL(
"SELECT id, name FROM sources WHERE {attribute} = {value} order by id"
).format(
attribute=sql.Identifier(attribute.lower()),
value=sql.Literal(value),
)
else:
value = value.lower()
value = '%' + value + '%'
stmt = sql.SQL(
"SELECT id, name FROM sources WHERE lower({attribute}) like {value} order by id"
).format(
attribute=sql.Identifier(attribute.lower()),
value=sql.Literal(value),
)
cursor.execute(stmt)
result = cursor.fetchall()
output = []
for item in result:
output.append((item[0], item[1]))
return output
def admin_log( #pylint: disable=too-many-arguments
log_type:str, admin_id:int, user_id:int, modified=None, old_val=None, new_val=None
) -> bool:
'''
Function to create an admin event log entry in the database.
Input: admin's id that actioned the event, the type of operation, the effected user id,
the modified attribute and the before and after value of the attribute.
The operation type can be: Create User, Unlock User, Deactivate User and Edit User.
Returns True if log was created successfully and false if not.
'''
conn = dbc.establish_connection('eventlog')
cursor = conn.cursor() # Connect Cursor to Eventlog DB
dt_now = datetime.now()
datestamp = dt_now.strftime("%d/%m/%Y %H:%M:%S") # captures datetime when function is called
sql = """
INSERT INTO adminlogs
(datetime, operation, admin_id, user_id, modified_attribute, old_value, new_value)
VALUES(%(datetime)s,%(operation)s,%(adid)s,%(uid)s,%(attr)s,%(old)s,%(new)s)
"""
val = {
'datetime':datestamp,
'operation':log_type,
'adid':admin_id,
'uid':user_id,
'attr':modified,
'old':old_val,
'new':new_val
}
try:
cursor.execute(sql,val)
conn.commit()
except psycopg2.OperationalError:
return False
return True
def fetch_last_login(uid:int) -> str:
'''
Function to fetch a user's last login date.
Returns date string if existent or None if empty.
'''
cursor = dbc.establish_connection('authentication').cursor()
sql = 'SELECT last_login FROM users WHERE id=%(uid)s'
val = {'uid':uid}
try:
cursor.execute(sql,val)
result = cursor.fetchall()[0][0]
except IndexError:
return None
return result
def username_exists(username: str) -> bool:
'''
Checks the users database if a given username exists in the DB already.
If not, returns bool 'False', if it exists returns bool 'True'.
'''
cursor = dbc.establish_connection(
'authentication').cursor() # Connect Cursor to Auth DB
psql = "SELECT count(*) FROM users WHERE username=%(val)s"
val = {'val': username}
cursor.execute(psql, val)
result = cursor.fetchone()[0]
if result == 0:
return False
return True
def fetch_all_authorities() -> list:
'''
Query to fetch all users in the system with the 'Authority' role.
Returns a list of tuples containing pairs of emails and first_names
to be used by the source notification email.
'''
cursor = dbc.establish_connection(
'authentication').cursor() # Connect Cursor to Auth DB
psql = "SELECT * FROM users WHERE user_role=3"
cursor.execute(psql)
result = cursor.fetchall()
output = []
for item in result:
output.append((item[9], item[1]))
return output
def modify_user(uid: int, attribute: str, new_value: str,
admin_id: int) -> bool:
'''
Function to modify an existing user. Takes the user id as input to execute upon.
The attribute sets the field to be modified, the new_value denotes the value after modification.
'''
conn = dbc.establish_connection('authentication')
cursor = conn.cursor() # Connect Cursor to Authentication DB
stmt = sql.SQL("SELECT {attribute} FROM users WHERE id = {uid}").format(
attribute=sql.Identifier(attribute),
uid=sql.Literal(uid),
)
cursor.execute(stmt)
curr_val = cursor.fetchall()[0][0]
if attribute == 'user_role':
new_value = int(
new_value
) # change new value to int type if the user role is being changed
stmt = sql.SQL(
"UPDATE users SET {attribute} = {value} WHERE id = {uid}").format(
attribute=sql.Identifier(attribute),
uid=sql.Literal(uid),
value=sql.Literal(new_value),
)
try:
cursor.execute(stmt)
conn.commit()
except psycopg2.OperationalError as error:
print(RED + 'Issue with modifying user on database. Error:', error)
print(YELLOW + 'Error TYPE:', type(error))
return False
except psycopg2.errors.DatetimeFieldOverflow: #pylint: disable=no-member
print(
RED +
'Issue with the given Date of Birth. Please check your input and try again.'
)
return False
log.admin_log('Edit User',
admin_id,
uid,
modified=attribute,
old_val=str(curr_val),
new_val=str(new_value)) # log Edit User event
return True
def update_last_login(uid:int) -> bool:
'''
Function to update a user's last login value with the current datetime stamp.
Returns True if successful and False if not.
'''
conn = dbc.establish_connection('authentication')
cursor = conn.cursor()
now = datetime.now()
sql = 'UPDATE users SET last_login = %(now)s WHERE id = %(uid)s'
val = {'now':now, 'uid':uid}
try:
cursor.execute(sql,val)
conn.commit()
except psycopg2.OperationalError:
return False
return True
def lock_user(uid: int) -> bool:
'''
Function to lock a user if there are more than three failed login attempts. Input: User's ID.
If lock was successful, will return bool 'True'. If there unsuccessful, returns bool 'False'.
'''
conn = dbc.establish_connection('authentication')
cursor = conn.cursor() # Connect Cursor to Authentication DB
psql = "UPDATE users SET status=3 WHERE id=%(uid)s;"
val = {'uid': uid}
try:
cursor.execute(psql, val)
conn.commit()
except psycopg2.OperationalError as error:
print(RED + 'Issue with locking user on database. Error:', error)
print(YELLOW + 'Error TYPE:', type(error))
return False
log.auth_log("Account Locked", uid) # log locked account event
return True
def modify_source(source_id: int, attribute: str, new_value: str,
uid: int) -> bool:
'''
Function to modify the information of an existing source.
Takes as input the id of the source that is being modified, the attribute to be modified
and the new value that should be saved.
Example: modify_source(1, 'name', 'Google') -> Changes the 'name' of the source id=1 to 'Google'
Returns bool True/False depending on whether the modification was successful.
'''
conn = dbc.establish_connection('data')
cursor = conn.cursor()
stmt = sql.SQL("SELECT {attribute} FROM sources WHERE id = {sid}").format(
attribute=sql.Identifier(attribute),
sid=sql.Literal(source_id),
)
cursor.execute(stmt)
curr_val = cursor.fetchall()[0][0]
if attribute == 'threat_level':
new_value = int(
new_value
) # change new value to int type if Threat Level is being changed
stmt = sql.SQL(
"UPDATE sources SET {attribute}={value}, modified_date={dtnow} WHERE id = {sid}"
).format(
attribute=sql.Identifier(attribute),
sid=sql.Literal(source_id),
value=sql.Literal(new_value),
dtnow=sql.Literal(datetime.now()),
)
try:
cursor.execute(stmt)
conn.commit()
except psycopg2.OperationalError:
return False
log.operation_log("Edit Source",
uid,
source_id,
modified=attribute,
old_val=str(curr_val),
new_val=str(new_value)) # log edit source event
return True
def get_source_by_id(source_id: int) -> list:
'''
Function to return source information by its id.
Used in the search operation of the interface module.
Takes as argument the source id and returns a tuple:
(id, name, url, threat level, description, creation date, modified date)
'''
cursor = dbc.establish_connection('data').cursor()
psql = """
SELECT id, name, url, threat_level, description, creation_date, modified_date
FROM sources WHERE id = %(value)s
"""
val = {'value': source_id}
try:
cursor.execute(psql, val)
result = cursor.fetchall()[0]
except IndexError:
return None
return (result[0], result[1], result[2], result[3], result[4], result[5],
result[6])
def auth_log(log_type:str, uid:int) -> bool:
'''
Function to create an authentication event log entry in the database.
Takes as input the user_id that actioned the event, as well as the operation type of the log.
Type can be: Successful Login, Password Change, Account Locked, Locked Account Login Attempt.
Returns True if log was created successfully and false if not.
'''
conn = dbc.establish_connection('eventlog')
cursor = conn.cursor() # Connect Cursor to Eventlog DB
dt_now = datetime.now()
datestamp = dt_now.strftime("%d/%m/%Y %H:%M:%S") # captures datetime when the function is called
sql = """
INSERT INTO authlogs(datetime, operation, user_id)
VALUES (%(datetime)s,%(operation)s,%(uid)s)
"""
val = {'datetime':datestamp,'operation':log_type, 'uid':uid}
try:
cursor.execute(sql,val)
conn.commit()
except psycopg2.OperationalError:
return False
return True
def existing_user(user:str, password:str) -> tuple: #pylint: disable=inconsistent-return-statements
'''
Function to authenticate existing user against the database.
Takes username and clear password as input.
If successful, returns authenticated user as tuple: (user id, first name, user role).
If username not found or user is locked or deactivated, returns None.
If password was incorrect, returns False.
'''
cursor = dbc.establish_connection('authentication').cursor()
sql = 'SELECT id, first_name, user_role, password, status FROM users WHERE username = %(val)s'
val = {'val':user}
try:
cursor.execute(sql,val)
result = cursor.fetchall()[0]
except psycopg2.OperationalError as error:
print(RED+"Encountered an issue with the database. Error:", error)
print(WHITE, end='')
return None
except IndexError:
return None
user_status = result[4]
if user_status==2:
log.auth_log("Failed Login: Deactivated User", result[0])
print('This user is deactivated.', end='')
print('Please contact the system administrator team for further information.')
print(WHITE)
sys.exit()
elif user_status==3:
log.auth_log("Failed Login: Locked User", result[0])
print(RED+'This user is currently locked. ', end='')
print('Please contact the system administrator team for further information.')
print(WHITE)
sys.exit()
try:
match = ph.verify(result[3], password)
except argon2.exceptions.VerifyMismatchError:
return False
if match:
return (result[0], result[1], result[2])
def fetch_user_info(uid=None, email=None, username=None) -> tuple: #pylint: disable=too-many-return-statements
'''
Queries user information based on the given email, username or uid.
Returns a tuple of user id, first name, last name, email, dob and status if user was found.
Returns None if user was not found.
'''
cursor = dbc.establish_connection(
'authentication').cursor() # Connect Cursor to Auth DB
if uid is not None:
psql = "SELECT * FROM users WHERE id=%(val)s"
val = {'val': uid}
try:
cursor.execute(psql, val)
result = cursor.fetchall()[0]
except IndexError:
return None
return (result[0], result[1], result[2], result[9], result[3],
result[8])
if email is not None:
psql = "SELECT * FROM users WHERE email=%(val)s"
val = {'val': email}
try:
cursor.execute(psql, val)
result = cursor.fetchall()[0]
except IndexError:
return None
return (result[0], result[1], result[2], result[9], result[3],
result[8])
if username is not None:
psql = "SELECT * FROM users WHERE username=%(val)s"
val = {'val': username}
try:
cursor.execute(psql, val)
result = cursor.fetchall()[0]
except IndexError:
return None
return (result[0], result[1], result[2], result[9], result[3],
result[8])
return None
def create_new_source(name: str, url: str, threat_level: int, description: str,
uid: int) -> bool:
'''
Function to create a new entry in the sources database table.
Takes as input the name of the source, the url, the threat level and the description.
Returns bool True/False depending on whether the creation was successful.
If successful, triggers email notification to all users with role=3 (External Authority).
'''
conn = dbc.establish_connection('data')
cursor = conn.cursor()
psql = """
INSERT INTO sources (name, url, threat_level, description, creation_date, modified_date)
VALUES (%(name)s,%(url)s,%(threat_level)s,%(description)s,%(creation_date)s,%(modified_date)s)
RETURNING id;
"""
val = {
'name': name,
'url': url,
'threat_level': threat_level,
'description': description,
'creation_date': datetime.now(),
'modified_date': datetime.now()
}
try:
cursor.execute(psql, val)
conn.commit()
except psycopg2.OperationalError:
return False
else:
recipients = adops.fetch_all_authorities(
) # fetching list of authority users
source_id = cursor.fetchone()[
0] # retrieving the id of the newly created source
log.operation_log("Create Source", uid,
source_id) # log source creation event
notification.new_source_email(recipients, source_id, name, url,
threat_level) # notification
return True
def unlock_user(uid: int, admin_id: int) -> bool:
'''
Function to unlock an already locked user. Input: admin's id and the id of the user to unlock.
If unlock was successful, will return bool 'True'. If there was an error, returns bool 'False'.
'''
conn = dbc.establish_connection('authentication')
cursor = conn.cursor() # Connect Cursor to Authentication DB
psql = "UPDATE users SET status=1 WHERE id=%(uid)s"
val = {'uid': uid}
try:
cursor.execute(psql, val)
conn.commit()
except psycopg2.OperationalError as error:
print(RED + 'Issue with unlocking user on database. Error:', error)
print(YELLOW + 'Error TYPE:', type(error))
return False
log.admin_log('Unlock User',
admin_id,
uid,
modified='status',
old_val='3',
new_val='1')
return True
def change_password(user_id: int, new_password: str) -> bool:
'''
Function to change the password of a specific user. Input: user id and new password hash.
Returns a bool value depending on whether the modification was successful.
If successful, triggers email notification to user that password has changed.
'''
conn = dbc.establish_connection('authentication')
cursor = conn.cursor()
psql = "UPDATE users SET password = %(val)s WHERE id = %(id)s;"
val = {'val': new_password, 'id': user_id}
try:
cursor.execute(psql, val)
conn.commit()
except psycopg2.OperationalError:
return False
log.auth_log("Password Change", user_id)
fetch_user = adops.fetch_user_info(
uid=user_id) # retrieves user's information
u_email = fetch_user[3]
u_first_name = fetch_user[1]
notification.changed_password_email(u_first_name,
u_email) # triggers email notification
return True
def deactivate_user(uid: int, admin_id: int, curr_status: int) -> bool:
'''
Function to deactivate a user if the access is no longer needed. Input: User's ID.
If deactivation was successful, will return bool 'True'. If not, returns bool 'False'.
'''
conn = dbc.establish_connection('authentication')
cursor = conn.cursor() # Connect Cursor to Authentication DB
psql = "UPDATE users SET status=2 WHERE id=%(uid)s;"
val = {'uid': uid}
try:
cursor.execute(psql, val)
conn.commit()
except psycopg2.OperationalError as error:
print(RED + 'Issue with deactivating user on database. Error:', error)
print(YELLOW + 'Error TYPE:', type(error))
return False
log.admin_log('Deactivate User',
admin_id,
uid,
modified='status',
old_val=str(curr_status),
new_val='2') # log deactivation event
return True
def register_new_user(
first_name: str, last_name: str, dob: str, email: str, role: int,
admin_id: int
) -> bool: #pylint: disable=too-many-arguments, disable=too-many-locals
'''
Function to sign up a new user. Takes user information as arg to sign up accordingly.
Takes Admin's id as argument to create log in the eventlog database.
If sign-up is successful, triggers notification email.
'''
conn = dbc.establish_connection('authentication')
cursor = conn.cursor() # Connect Cursor to Authentication DB
username = generate_username(first_name, last_name)
password = generate_password(12)
clear_pswd = password[0]
hash_pswd = password[1]
psql = """
INSERT INTO users
(first_name, last_name, dob, user_role, username, password, status, email)
VALUES (%(first_name)s,%(last_name)s,%(dob)s,%(role)s,%(uname)s,%(pw)s,%(stat)s,%(email)s)
RETURNING id
"""
val = {
'first_name': first_name,
'last_name': last_name,
'dob': dob,
'role': role,
'uname': username,
'pw': hash_pswd,
'stat': 1,
'email': email
}
try:
cursor.execute(psql, val)
conn.commit()
except psycopg2.OperationalError as error:
print(RED + 'Issue with registering new user on database. Error:',
error)
print(YELLOW + 'Error TYPE:', type(error))
return False
except psycopg2.errors.DatetimeFieldOverflow: #pylint: disable=no-member
print(
RED +
'Issue with the given Date of Birth. Please check your input and try again.'
)
return False
else:
created_user_id = cursor.fetchone()[0]
log.admin_log('Create User', admin_id,
created_user_id) # logging event in logs
print(
GREEN +
f'User successfully created. Sending Registration email to {email}...'
)
sent_email = notification.registration_email(first_name, email, username,
clear_pswd)
if sent_email:
print(GREEN + 'Email sent successfully!')
else:
print(
RED +
'Email could not be sent. Please ensure that the SMTP is reachable.'
)
return True
