PageRenderTime 63ms CodeModel.GetById 32ms RepoModel.GetById 0ms app.codeStats 0ms

/S4M_pyramid/model/stemformatics/stemformatics_auth.py

https://bitbucket.org/TheSaturnTeam/s4m_pyramid
Python | 1606 lines | 1594 code | 8 blank | 4 comment | 21 complexity | d2c660c1b84ecdfc0ceadaddcba798a8 MD5 | raw file

Large files files are truncated, but you can click here to view the full file

  1. #-------Last synchronised with Pylons repo (master) on---------------#
  2. #-------------------------8 Feb 2018---------------------------------#
  3. #-------------------------by WU Yan----------------------------------#
  4. #TODO-1
  5. import logging
  6. log = logging.getLogger(__name__)
  7. import sqlalchemy as SA
  8. from sqlalchemy import or_, and_, desc
  9. import psycopg2
  10. import psycopg2.extras
  11. import re
  12. import string
  13. import json
  14. from S4M_pyramid.lib.deprecated_pylons_globals import magic_globals, url, config
  15. from S4M_pyramid.lib.deprecated_pylons_abort_and_redirect import abort, redirect
  16. from decorator import decorator
  17. import smtplib
  18. __all__ = ['Stemformatics_Auth']
  19. import formencode.validators as fe
  20. SUBSCRIBER_NAME = fe.Regex("[\w ]*", not_empty=False, if_empty="Anonymous User")
  21. SUBSCRIBER_STATE = fe.Regex("[\w ]*", not_empty=False, if_empty="PENDING")
  22. DESCRIPTIVE_TEXT = fe.Regex("[\w ]*", not_empty=False, if_empty="")
  23. POS_INT = fe.Int(min=1, not_empty=True)
  24. NUMBER = fe.Number(not_empty=True)
  25. IDENTIFIER = fe.PlainText(not_empty=True)
  26. URL = fe.URL(not_empty=True)
  27. VQ = re.compile(r"[^\'\"\`\$\\]*")
  28. import hashlib
  29. from random import random
  30. import datetime
  31. import S4M_pyramid.lib.helpers as h
  32. from S4M_pyramid.model import redis_interface_normal as r_server
  33. class Stemformatics_Auth(object):
  34. """\
  35. Stemformatics_Auth Model Object
  36. ========================================
  37. A simple model of static functions to make the controllers thinner for login controller
  38. Please note for most of these functions you will have to pass in the db object
  39. All functions have a try that will return None if errors are found
  40. """
  41. @staticmethod
  42. def user_status_dict():
  43. status_dict = {0:'Inactive',1:'Active',2:'Pending'}
  44. return status_dict
  45. @staticmethod
  46. def get_status_dict_by_name():
  47. status_dict = Stemformatics_Auth.user_status_dict()
  48. status_dict_by_name = {}
  49. for status_id in status_dict:
  50. name = status_dict[status_id]
  51. status_dict_by_name[name] = status_id
  52. return status_dict_by_name
  53. @staticmethod
  54. def set_smart_redirect(redirect_url):
  55. magic_globals.fetch()
  56. session = magic_globals.session
  57. session['redirect_url'] = redirect_url
  58. session.save()
  59. return True
  60. """
  61. This is for checking if this uid is a real user, not using guest account either
  62. """
  63. @staticmethod
  64. def check_real_user(uid):
  65. status_dict_by_name = Stemformatics_Auth.get_status_dict_by_name()
  66. status_id = status_dict_by_name['Active']
  67. guest_username = config['guest_username']
  68. conn_string = config['psycopg2_conn_string']
  69. conn = psycopg2.connect(conn_string)
  70. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  71. sql = "select uid from stemformatics.users where uid = %s and status= %s and username != %s;"
  72. cursor.execute(sql,(uid,status_id,guest_username,))
  73. # retrieve the records from the database
  74. result = cursor.fetchall()
  75. cursor.close()
  76. conn.close()
  77. try:
  78. if result[0][0] == uid:
  79. real_user = True
  80. else:
  81. real_user = False
  82. except:
  83. real_user = False
  84. return real_user
  85. @staticmethod
  86. def get_smart_redirect(default_redirect_url):
  87. magic_globals.fetch()
  88. session = magic_globals.session
  89. if 'redirect_url' in session:
  90. redirect_url = session['redirect_url']
  91. else:
  92. redirect_url = ""
  93. session['redirect_url'] = ""
  94. session.save()
  95. if redirect_url != "":
  96. return redirect_url
  97. else:
  98. return default_redirect_url
  99. def __init__ (self):
  100. pass
  101. @staticmethod
  102. def authorise(db=None):
  103. """\
  104. Usage:
  105. @Stemformatics_Auth.authorise()
  106. def summary(self):
  107. """
  108. def check_authorised(func, *args, **kwargs):
  109. log.debug('start of authorise')
  110. magic_globals.fetch()
  111. c = magic_globals.c
  112. session = magic_globals.session
  113. request = magic_globals.request
  114. if 'path_before_login' in session:
  115. del session['path_before_login']
  116. session.save()
  117. if 'user' in session:
  118. c.user = session.get('user').lower()
  119. c.uid = session.get('uid')
  120. c.full_name = session.get('full_name')
  121. c.role = session.get('role')
  122. c.notifications = 1
  123. return func(*args, **kwargs)
  124. else:
  125. c.user = None
  126. session['path_before_login'] = request.path_info + '?' + request.query_string
  127. session.save()
  128. return redirect(h.url('/auth/login'))
  129. return decorator(check_authorised)
  130. @staticmethod
  131. def check_user_password(db,username,password): #CRITICAL-2
  132. username = username.lower()
  133. try:
  134. db.schema = 'stemformatics'
  135. user = db.users
  136. m = hashlib.sha1()
  137. m.update(password.encode('utf-8'))
  138. sha1_password = m.hexdigest()
  139. where = and_(user.username==username,user.password==sha1_password,user.status == 1)
  140. result = db.users.filter(where).all()
  141. if len(result) != 1:
  142. return None
  143. db_user = result[0]
  144. return db_user
  145. except:
  146. return None
  147. @staticmethod
  148. def confirm_new_user(db,confirm_code,uid): #CRITICAL-2
  149. try:
  150. db.schema = 'stemformatics'
  151. user = db.users
  152. now_time = datetime.datetime.now()
  153. two_days = datetime.timedelta(days=2)
  154. check_time = now_time - two_days
  155. # where = and_(user.confirm_code==confirm_code,user.status==0,user.created > check_time)
  156. result = user.filter(user.uid==uid).all()
  157. if len(result) != 1:
  158. return "This user does not exist. Please register."
  159. if result[0].status == 1:
  160. return "This user is already registered"
  161. if result[0].confirm_code.strip() != confirm_code:
  162. return "This confirm code is invalid or has expired for this user"
  163. if result[0].created < check_time:
  164. return "This confirm code has expired for this user"
  165. db_user = result[0]
  166. result = user.filter(user.uid == db_user.uid).update({'status': 1, 'confirm_code': ''})
  167. db.commit()
  168. Stemformatics_Auth.create_base_export_key(db_user.uid)
  169. db_user.status = 1
  170. db_user.confirm_code = ""
  171. Stemformatics_Auth.triggers_for_change_in_user(db,uid = None)
  172. return db_user
  173. except:
  174. return "Unknown error with confirming"
  175. @staticmethod
  176. def clear_expired_unconfirmed_users(db): #CRITICAL-2
  177. try:
  178. db.schema = 'stemformatics'
  179. user = db.users
  180. now_time = datetime.datetime.now()
  181. two_days = datetime.timedelta(days=2)
  182. check_time = now_time - two_days
  183. # change to status 2 instead of deleting just in case we have override options for this uid
  184. where = and_(user.status==0,user.created < check_time)
  185. result = db.users.filter(where).update({'status':2})
  186. db.commit()
  187. return True
  188. except:
  189. return False
  190. @staticmethod
  191. def remove_user(db,username): #CRITICAL-2
  192. username = username.lower()
  193. try:
  194. db.schema = 'stemformatics'
  195. user = db.users
  196. gsi = db.gene_set_items
  197. gs = db.gene_sets
  198. where = and_(user.username==username)
  199. result = user.filter(where).all()
  200. for userFound in result:
  201. geneSets = gs.filter(gs.uid == userFound.uid).all()
  202. for geneSet in geneSets:
  203. gsi.filter(gsi.gene_set_id == geneSet.id).delete()
  204. gs.filter(gs.id == geneSet.id).delete()
  205. where = and_(user.username==username)
  206. user.filter(where).delete()
  207. db.commit()
  208. return True
  209. except:
  210. return False
  211. @staticmethod
  212. def register_new_user(db,registration_data): #CRITICAL-2
  213. username = registration_data['username'].lower()
  214. # Story #179 have to check for users who are set as 0 or 1. If status of 2, then have to update and not create new user
  215. db.schema = 'stemformatics'
  216. where = and_(db.users.username==username)
  217. result = db.users.filter(where).all()
  218. update_user = False
  219. # check if username already available
  220. if len(result) == 1:
  221. if result[0].status == 2:
  222. update_user = True
  223. else:
  224. return "This username is already taken"
  225. if len(result) > 1:
  226. return "This username is already taken"
  227. # check strong password
  228. validation_regex_text = config['validation_regex']
  229. validation_regex = re.compile(validation_regex_text)
  230. m = validation_regex.match(registration_data['password'])
  231. if m is None:
  232. return config['validation_warning']
  233. m = re.search('^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$',username)
  234. if m is None:
  235. return "This username is not valid, it must be a valid email address"
  236. m = hashlib.sha1()
  237. m.update(registration_data['password'].encode('utf-8'))
  238. sha1_password = m.hexdigest()
  239. base_confirm_code = m.update(str(random()).encode('utf-8'))
  240. confirm_code = m.hexdigest()
  241. # create new user - but check that it's not an update for an existing user with status of 2
  242. if update_user:
  243. now_time = datetime.datetime.now()
  244. dict_change = {'password':sha1_password,'created':now_time,'status': 0, 'confirm_code': confirm_code,'full_name': registration_data['full_name'], 'organisation': registration_data['organisation'], 'send_email_marketing': registration_data['send_email_marketing'], 'send_email_job_notifications': registration_data['send_email_job_notifications']}
  245. result = db.users.filter(db.users.username == username).update(dict_change)
  246. else:
  247. result = db.users.insert(username=username, password=sha1_password,organisation=registration_data['organisation'],full_name = registration_data['full_name'],confirm_code=confirm_code, send_email_marketing= registration_data['send_email_marketing'], send_email_job_notifications = registration_data['send_email_job_notifications'])
  248. db.commit()
  249. db.flush()
  250. where = and_(db.users.username==username)
  251. result = db.users.filter(where).all()
  252. if len(result) != 1:
  253. return "The registration could not be saved"
  254. return result[0]
  255. #except:
  256. # return "Error in this application in register_new_user"
  257. @staticmethod
  258. def set_confirm_forgot_password(db,username): #CRITICAL-2
  259. username = username.lower()
  260. try:
  261. db.schema = 'stemformatics'
  262. user = db.users
  263. # only select users with status of active
  264. where = and_(user.username==username, user.status == 1)
  265. result = user.filter(where).all()
  266. # check if username already available
  267. if len(result) != 1:
  268. return None
  269. m = hashlib.sha1()
  270. base_confirm_code = m.update(str(random()).encode('utf-8'))
  271. confirm_code = m.hexdigest()
  272. now_time = datetime.datetime.now()
  273. two_hours = datetime.timedelta(hours=2)
  274. expiry_password_time = now_time + two_hours
  275. # update confirm code for user
  276. result = user.filter(user.username == username).update({'confirm_code':confirm_code , 'password_expiry': expiry_password_time})
  277. db.commit()
  278. db.flush()
  279. where = and_(user.username==username, user.confirm_code == confirm_code, user.status ==1)
  280. result = user.filter(where).all()
  281. # check if found
  282. if len(result) != 1:
  283. return None
  284. db_user = result[0]
  285. return db_user
  286. except:
  287. return None
  288. @staticmethod
  289. def get_user_from_confirm_code(db,confirm_code): #CRITICAL-2
  290. try:
  291. db.schema = 'stemformatics'
  292. user = db.users
  293. where = and_(user.confirm_code == confirm_code)
  294. result = user.filter(where).all()
  295. if len(result) != 1:
  296. return None
  297. return result[0]
  298. except:
  299. return None
  300. @staticmethod
  301. def get_users_from_usernames(list_of_usernames):
  302. if not isinstance(list_of_usernames,list):
  303. return []
  304. conn_string = config['psycopg2_conn_string']
  305. conn = psycopg2.connect(conn_string)
  306. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  307. sql = "select * from stemformatics.users where username = ANY(%(list_of_usernames)s) ;"
  308. cursor.execute(sql,{'list_of_usernames': list_of_usernames})
  309. # retrieve the records from the database
  310. result = cursor.fetchall()
  311. cursor.close()
  312. conn.close()
  313. return result
  314. @staticmethod
  315. def set_users_to_be_inactive_from_usernames(string_of_usernames):
  316. status_dict_by_name = Stemformatics_Auth.get_status_dict_by_name()
  317. status_id = status_dict_by_name['Inactive']
  318. if not isinstance(string_of_usernames,str) and not isinstance(string_of_usernames,bytes):
  319. return []
  320. list_of_usernames = re.findall("[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+", string_of_usernames)
  321. user_result = Stemformatics_Auth.get_users_from_usernames(list_of_usernames)
  322. list_of_uids = []
  323. return_dict_of_usernames_and_uids = {}
  324. for row in user_result:
  325. uid = row['uid']
  326. list_of_uids.append(uid)
  327. return_dict_of_usernames_and_uids[uid] = row['username']
  328. conn_string = config['psycopg2_conn_string']
  329. conn = psycopg2.connect(conn_string)
  330. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  331. sql = "update stemformatics.users set status = %(status_id)s where uid = ANY(%(list_of_uids)s) ;"
  332. cursor.execute(sql,{'list_of_uids': list_of_uids,'status_id':status_id})
  333. conn.commit()
  334. cursor.close()
  335. conn.close()
  336. return return_dict_of_usernames_and_uids
  337. @staticmethod
  338. def unsubscribe_users_from_outage_critical_notifications(string_of_usernames):
  339. if not isinstance(string_of_usernames,str) and not isinstance(string_of_usernames,bytes):
  340. return []
  341. list_of_usernames = re.findall("[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+", string_of_usernames)
  342. user_result = Stemformatics_Auth.get_users_from_usernames(list_of_usernames)
  343. list_of_uids = []
  344. return_dict_of_usernames_and_uids = {}
  345. for row in user_result:
  346. uid = row['uid']
  347. list_of_uids.append(uid)
  348. return_dict_of_usernames_and_uids[uid] = row['username']
  349. conn_string = config['psycopg2_conn_string']
  350. conn = psycopg2.connect(conn_string)
  351. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  352. sql = "update stemformatics.users set send_email_outages_critical = False where uid = ANY(%(list_of_uids)s) ;"
  353. cursor.execute(sql,{'list_of_uids': list_of_uids})
  354. conn.commit()
  355. cursor.close()
  356. conn.close()
  357. return return_dict_of_usernames_and_uids
  358. @staticmethod
  359. def get_user_from_username(db,username): #CRITICAL-2
  360. username = username.lower()
  361. try:
  362. db.schema = 'stemformatics'
  363. user = db.users
  364. # only select users with blank confirm codes and status of active
  365. where = and_(user.username == username)
  366. result = user.filter(where).all()
  367. if len(result) != 1:
  368. return None
  369. return result[0]
  370. except:
  371. return None
  372. @staticmethod
  373. def get_user_from_uid(db,uid): #CRITICAL-2
  374. try:
  375. db.schema = 'stemformatics'
  376. user = db.users
  377. # only select users with blank confirm codes and status of active
  378. where = and_(user.uid == uid)
  379. result = user.filter(where).all()
  380. if len(result) != 1:
  381. return None
  382. return result[0]
  383. except:
  384. return None
  385. @staticmethod
  386. def reset_password(db,confirmed_user,confirm_code,password): #CRITICAL-2
  387. try:
  388. db.schema = 'public'
  389. user = db.users
  390. # check expiry of password
  391. now_time = datetime.datetime.now()
  392. if confirmed_user.password_expiry is None or confirmed_user.password_expiry < now_time:
  393. return "This has expired. Please try again"
  394. # reset password
  395. username = confirmed_user.username.lower()
  396. updated_user = Stemformatics_Auth.change_password(db,username,password)
  397. if isinstance(updated_user, str):
  398. return updated_user
  399. # clear out expiry
  400. result = user.filter(user.uid == confirmed_user.uid).update({'confirm_code':'','password_expiry': None })
  401. db.commit()
  402. db.flush()
  403. return result
  404. except:
  405. return "Error in application reset_password"
  406. @staticmethod
  407. def clear_expired_password_resets(db): #CRITICAL-2
  408. try:
  409. db.schema = 'stemformatics'
  410. user = db.users
  411. now_time = datetime.datetime.now()
  412. where = and_(user.status==1,user.password_expiry < now_time)
  413. result = db.users.filter(where).update({'confirm_code':'','password_expiry': None })
  414. db.commit()
  415. return True
  416. except:
  417. return False
  418. @staticmethod
  419. def change_password(db,username,password): #CRITICAL-2
  420. username = username.lower()
  421. try:
  422. validation_regex_text = config['validation_regex']
  423. validation_regex = re.compile(validation_regex_text)
  424. m = validation_regex.match(password)
  425. if m is None:
  426. return config['validation_warning']
  427. db.schema = 'stemformatics'
  428. user = db.users
  429. where = and_(user.username==username)
  430. result = user.filter(where).all()
  431. # check if username already available
  432. if len(result) != 1:
  433. return "There was an error finding this user"
  434. m = hashlib.sha1()
  435. m.update(password.encode('utf-8'))
  436. sha1_password = m.hexdigest()
  437. # update user
  438. result = user.filter(user.username == username).update({'password':sha1_password })
  439. db.commit()
  440. db.flush()
  441. where = and_(user.username==username)
  442. check = user.filter(where).all()
  443. if len(check) != 1:
  444. return "The user could not be updated"
  445. if check[0].password != sha1_password:
  446. return "The password could not be saved"
  447. return check[0]
  448. except:
  449. return "Error in application change_password"
  450. @staticmethod
  451. def update_user(db,username,update_user): #CRITICAL-2
  452. try:
  453. username = username.lower()
  454. db.schema = 'stemformatics'
  455. user = db.users
  456. where = and_(user.username==username)
  457. result = user.filter(where).all()
  458. # check if username already available
  459. if len(result) != 1:
  460. return "There was an error finding this user"
  461. if 'password' in update_user:
  462. # check strong password
  463. validation_regex_text = config['validation_regex']
  464. validation_regex = re.compile(validation_regex_text)
  465. m = validation_regex.match(update_user['password'])
  466. if m is None:
  467. return config['validation_warning']
  468. m = hashlib.sha1()
  469. m.update(update_user['password'].encode('utf-8'))
  470. sha1_password = m.hexdigest()
  471. # update password too
  472. result = user.filter(user.username == username).update({'password':sha1_password,'full_name': update_user['full_name'], 'organisation': update_user['organisation'], 'send_email_marketing': update_user['send_email_marketing'], 'send_email_job_notifications': update_user['send_email_job_notifications']})
  473. else:
  474. result = user.filter(user.username == username).update({'full_name': update_user['full_name'], 'organisation': update_user['organisation'], 'send_email_marketing': update_user['send_email_marketing'], 'send_email_job_notifications': update_user['send_email_job_notifications']})
  475. db.commit()
  476. db.flush()
  477. if result != 1:
  478. return "The update could not be saved"
  479. return True
  480. except:
  481. return "Error in this application in saving details"
  482. @staticmethod
  483. def return_all_active_users(db):
  484. status_list = Stemformatics_Auth.get_status_dict_by_name()
  485. active_status = status_list['Active']
  486. conn_string = config['psycopg2_conn_string']
  487. conn = psycopg2.connect(conn_string)
  488. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  489. sql = "select * from stemformatics.users where status= %(status_id)s ;"
  490. cursor.execute(sql,{'status_id': active_status})
  491. # retrieve the records from the database
  492. result = cursor.fetchall()
  493. cursor.close()
  494. conn.close()
  495. return result
  496. @staticmethod
  497. def return_user_notifications(db): #CRITICAL-2
  498. db.schema = 'stemformatics'
  499. user = db.users
  500. result = db.users.filter(user.send_email_marketing == True).all()
  501. return result
  502. @staticmethod
  503. def return_new_users(db,from_date): #CRITICAL-2
  504. db.schema = 'stemformatics'
  505. user = db.users
  506. result = db.users.filter(user.created >= from_date).all()
  507. return result
  508. @staticmethod
  509. def check_stay_signed_in_md5(db,username,user_and_pwd_md5): #CRITICAL-2
  510. db.schema = 'stemformatics'
  511. user = db.users
  512. if username is not None:
  513. username = username.lower()
  514. where = and_(user.username==username,user.status == 1)
  515. result = db.users.filter(where).all()
  516. if len(result) != 1:
  517. return None
  518. db_user = result[0]
  519. sha1_password = db_user.password
  520. h = hashlib.sha1()
  521. h.update(username.encode('utf-8') + sha1_password.encode('utf-8'))
  522. check_user_and_pwd_md5 = h.hexdigest()
  523. if check_user_and_pwd_md5 == user_and_pwd_md5 :
  524. return db_user
  525. else:
  526. return None
  527. @staticmethod
  528. def create_stay_signed_in_md5(username,password): #CRITICAL-2
  529. username = username.lower()
  530. m = hashlib.sha1()
  531. m.update(password.encode('utf-8'))
  532. sha1_password = m.hexdigest()
  533. h = hashlib.sha1()
  534. h.update(username.encode('utf-8') + sha1_password.encode('utf-8'))
  535. user_and_pwd_md5 = h.hexdigest()
  536. return user_and_pwd_md5
  537. @staticmethod
  538. def create_new_pending_user(db,username): #CRITICAL-2
  539. username = username.lower()
  540. # Story #179 have to check for users who are set as 0 or 1. If status of 2, then have to update and not create new user
  541. db.schema = 'stemformatics'
  542. where = and_(db.users.username==username)
  543. result = db.users.filter(where).all()
  544. # check if username already available
  545. if len(result) == 1:
  546. return result[0]
  547. # create new user
  548. result = db.users.insert(username=username,status=2)
  549. db.commit()
  550. db.flush()
  551. where = and_(db.users.username==username)
  552. result = db.users.filter(where).all()
  553. if len(result) != 1:
  554. return "The pending user could not be saved"
  555. return result[0]
  556. #except:
  557. # return "Error in this application in register_new_user"
  558. """
  559. This will check to see if the email already is assigned ot a user. If not it will create one and return the new user id.
  560. """
  561. @staticmethod
  562. def return_uid_from_email_for_sharing(db,email): #CRITICAL-2
  563. email = email.lower()
  564. return_user = Stemformatics_Auth.get_user_from_username(db,email)
  565. pending_user = False
  566. # if no uid then create one with status of 2 which is pending registration
  567. if return_user is None:
  568. # return the new user record
  569. new_user = Stemformatics_Auth.create_new_pending_user(db,email)
  570. # if this is a string then something errored
  571. if isinstance(new_user,str):
  572. error_message = email + ": " + new_user + " "
  573. return error_message
  574. else:
  575. return_user_id = new_user.uid
  576. pending_user = True
  577. else:
  578. return_user_id = return_user.uid
  579. if return_user.status == 2:
  580. pending_user = True
  581. return [return_user_id,pending_user]
  582. @staticmethod
  583. def update_user_status(db,uid,status): #CRITICAL-2
  584. try:
  585. db.schema = 'stemformatics'
  586. user = db.users
  587. where = and_(user.uid==uid)
  588. result = user.filter(where).all()
  589. # check if username already available
  590. if len(result) != 1:
  591. return "There was an error finding this user"
  592. result = user.filter(user.uid == uid).update({'status': status})
  593. db.commit()
  594. db.flush()
  595. if result != 1:
  596. return "The update could not be saved"
  597. return True
  598. except:
  599. return "Error in this application in saving details"
  600. ''' datasets is a list of integers'''
  601. @staticmethod
  602. def save_multi_datasets(db,uid,db_id,datasets): #CRITICAL-2
  603. md_name = 'multi_datasets_view_'+str(db_id)
  604. # turn list of integers into string comma separated list
  605. md_value = str(datasets).strip('[]').replace(' ','')
  606. db.schema = 'stemformatics'
  607. u = db.users
  608. umd = db.users_metadata
  609. where = and_(u.uid==uid)
  610. result = u.filter(where).all()
  611. # check if user available
  612. if len(result) != 1:
  613. return "No user found"
  614. where = and_(umd.uid==uid,umd.md_name==md_name)
  615. result = umd.filter(where).all()
  616. # check if user available
  617. if len(result) == 0:
  618. result_insert = umd.insert(uid=uid,md_name=md_name,md_value=md_value)
  619. else:
  620. umd.filter(where).update({'md_value':md_value})
  621. db.commit()
  622. db.flush()
  623. return True
  624. @staticmethod
  625. def get_publish_gene_set_email_address():
  626. return config['publish_gene_set_email_address']
  627. @staticmethod
  628. def get_multi_datasets(db,uid,db_id): #CRITICAL-2
  629. md_name = 'multi_datasets_view_'+str(db_id)
  630. db.schema = 'stemformatics'
  631. umd = db.users_metadata
  632. where = and_(umd.uid==uid,umd.md_name==md_name)
  633. try:
  634. result = umd.filter(where).one()
  635. except:
  636. return []
  637. temp_md_value = result.md_value.split(',')
  638. datasets = [ int(i) for i in temp_md_value ]
  639. return datasets
  640. # Method for making the dataset list to a dictionary of the keys
  641. @staticmethod
  642. def get_dict_users_private_datasets_metadata(ds_list):
  643. ds_id_list = []
  644. ds_attribute_list = ['Title', 'Organism']
  645. ds_dict = {}
  646. # Makes a list of ds_id's to search metadata and filters out the duplicate entries by
  647. # overriding group permissions with user permissions.
  648. for ds_row in ds_list:
  649. ds_id = ds_row[0]
  650. status = ds_row[1]
  651. permissions = ds_row[2]
  652. try:
  653. tmp = ds_dict[ds_id]
  654. ds_id_list.append(ds_id)
  655. except:
  656. ds_dict[ds_id] = {}
  657. ds_id_list.append(ds_id)
  658. ds_dict[ds_id]['status'] = status
  659. ds_dict[ds_id]['permissions'] = permissions
  660. # Get the metadata based on the ds_id's collected
  661. conn_string = config['psycopg2_conn_string']
  662. conn = psycopg2.connect(conn_string)
  663. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  664. # Joins the metadata table and the datasets table to get the tags above whether or not
  665. # it is a private dataset
  666. sql = "select dm.ds_id, dm.ds_name, ds.handle, dm.ds_value, ds.private from dataset_metadata as dm left join datasets as ds on (dm.ds_id = ds.id) where dm.ds_name = any(%s) and dm.ds_id = any(%s);"
  667. cursor.execute(sql,(ds_attribute_list,ds_id_list,))
  668. metadata = cursor.fetchall()
  669. cursor.close()
  670. conn.close()
  671. #Add the information from the metadata dict to the dict containing the privalges and status
  672. for ds_row in metadata:
  673. ds_id = ds_row[0]
  674. ds_attribute = ds_row[1]
  675. attribute_val = ds_row[3]
  676. ds_dict[ds_id][ds_attribute] = attribute_val
  677. ds_dict[ds_id]["handle"] = ds_row[2]
  678. ds_dict[ds_id]["private"] = ds_row[4]
  679. return ds_dict
  680. # Method for getting private datasets, access and privleges for
  681. @staticmethod
  682. def get_list_users_private_datasets(uid):
  683. # Checks that the user id isn't going to cause an error
  684. if not isinstance(uid, int) or uid < 0:
  685. return None
  686. conn_string = config['psycopg2_conn_string']
  687. conn = psycopg2.connect(conn_string)
  688. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  689. # Makes a join on the private overrides table and group privileges table on a user and gives
  690. # the private datasets a user has access to either under group or user privileges
  691. sql = "select ds_id,o.role,o.object_type from stemformatics.override_private_datasets as o left join stemformatics.group_users as gu on o.object_id = CAST(gu.gid as TEXT) where (o.object_type = 'Group' and gu.uid = %s) or (o.object_type = 'User' and o.object_id = %s) order by object_type;"
  692. cursor.execute(sql,(uid,str(uid),))
  693. result = cursor.fetchall()
  694. cursor.close()
  695. conn.close()
  696. return result
  697. @staticmethod
  698. def get_groups_for_uid(db,uid,role): #CRITICAL-2
  699. public_list = [0]
  700. if not isinstance( uid, int ):
  701. return public_list # at least return public
  702. if not isinstance( role, str) and not isinstance(role, bytes):
  703. role = '' # set role to none
  704. if uid == 0:
  705. return public_list
  706. if role in ['admin','annotator']:
  707. conn_string = config['psycopg2_conn_string']
  708. conn = psycopg2.connect(conn_string)
  709. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  710. sql = "select * from stemformatics.groups;"
  711. cursor.execute(sql)
  712. result = cursor.fetchall()
  713. cursor.close()
  714. conn.close()
  715. groups = [ row['gid'] for row in result ]
  716. else:
  717. conn_string = config['psycopg2_conn_string']
  718. conn = psycopg2.connect(conn_string)
  719. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  720. sql = "select * from stemformatics.group_users where uid = %s;"
  721. cursor.execute(sql,(uid,))
  722. result = cursor.fetchall()
  723. cursor.close()
  724. conn.close()
  725. groups = [ row['gid'] for row in result ]
  726. if 0 not in groups:
  727. groups.append(0) # public group
  728. return sorted(groups)
  729. @staticmethod
  730. def add_group(db,group_name): #CRITICAL-2
  731. db.schema = 'stemformatics'
  732. g = db.groups
  733. result = g.insert(group_name=group_name)
  734. db.commit()
  735. db.flush()
  736. return True
  737. @staticmethod
  738. def get_all_group_names(db): #CRITICAL-2
  739. conn_string = config['psycopg2_conn_string']
  740. conn = psycopg2.connect(conn_string)
  741. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  742. sql = "select * from stemformatics.groups;"
  743. cursor.execute(sql)
  744. result = cursor.fetchall()
  745. cursor.close()
  746. conn.close()
  747. group_names_dict = {}
  748. for group in result:
  749. gid = group['gid']
  750. group_name = group['group_name']
  751. group_names_dict[gid] = group_name
  752. return group_names_dict
  753. @staticmethod
  754. def get_ucsc_links_for_uid(db,uid,db_id): #CRITICAL-2
  755. ucsc_links = {}
  756. if uid is None or not isinstance(uid, int):
  757. return ucsc_links
  758. conn_string = config['psycopg2_conn_string']
  759. conn = psycopg2.connect(conn_string)
  760. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  761. sql = "select gid from stemformatics.group_users where uid = %s;"
  762. cursor.execute(sql,(uid,))
  763. result = cursor.fetchall()
  764. cursor.close()
  765. conn.close()
  766. gids = [0] # Public group has 0 gid
  767. for row in result:
  768. gids.append(row['gid'])
  769. conn = psycopg2.connect(conn_string)
  770. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  771. sql ="select gc.config_name as link_name,gc.config_value as url,gc.db_id,g.gid, g.group_name from stemformatics.group_configs as gc left join stemformatics.groups as g on g.gid = gc.gid where gc.gid = ANY (%s) and gc.db_id = %s and gc.config_type = 'UCSC Links';"
  772. cursor.execute(sql,(gids,db_id,))
  773. # retrieve the records from the database
  774. result = cursor.fetchall()
  775. cursor.close()
  776. conn.close()
  777. ucsc_links = {}
  778. for row in result:
  779. gid = row['gid']
  780. key_name = row['link_name'] + str(gid)
  781. ucsc_links[key_name] = {}
  782. ucsc_links[key_name]['link_name'] = row['link_name']
  783. ucsc_links[key_name]['url'] = row['url']
  784. ucsc_links[key_name]['db_id'] = db_id
  785. ucsc_links[key_name]['gid'] = gid
  786. ucsc_links[key_name]['group_name'] = row['group_name']
  787. return ucsc_links
  788. @staticmethod
  789. def add_user_to_group(db,uid,gid): #CRITICAL-2
  790. db.schema = 'stemformatics'
  791. gu = db.group_users
  792. # check if already there
  793. result_check = gu.filter(and_(gu.gid==gid,gu.uid==uid)).all()
  794. if len(result_check) == 0:
  795. # then create if already there
  796. result = gu.insert(gid=gid,uid=uid,role='User')
  797. Stemformatics_Auth.triggers_for_change_in_user(db,uid = None)
  798. db.commit()
  799. db.flush()
  800. return True
  801. else:
  802. return False
  803. @staticmethod
  804. def get_all_group_users(db): #CRITICAL-2
  805. db.schema = 'stemformatics'
  806. u = db.users
  807. gu = db.with_labels(db.group_users)
  808. join = db.join(gu,u,(gu.stemformatics_group_users_uid == u.uid))
  809. result = [ {'uid':row.uid,'gid':row.stemformatics_group_users_gid,'role':row.role, 'username':row.username,'status':row.status} for row in join.all() ]
  810. return result
  811. @staticmethod
  812. def hierarchy_roles(current_role,role): #CRITICAL-2
  813. new_role = current_role
  814. if role != current_role:
  815. if role == "admin":
  816. new_role = role
  817. else:
  818. if role == "annotator" and current_role != "admin":
  819. new_role = role
  820. else:
  821. if role == "view":
  822. new_role = role
  823. return new_role
  824. @staticmethod
  825. def setup_dict_uid_permissions(db): #CRITICAL-2
  826. db.schema = 'stemformatics'
  827. o = db.override_private_datasets
  828. g = db.group_users
  829. dict_uid_permissions = {}
  830. group_dict = {}
  831. all_group_users = g.all()
  832. for row in all_group_users:
  833. uid = row.uid
  834. gid = row.gid
  835. if gid not in group_dict:
  836. group_dict[gid] = []
  837. group_dict[gid].append(uid)
  838. # the order by means that Group comes before User,
  839. # so that even though a user is part of a group
  840. # we can give that user annotator rights
  841. all_overrides = o.order_by(o.object_type).all()
  842. for override_row in all_overrides:
  843. object_type = override_row.object_type
  844. object_id = override_row.object_id
  845. role = override_row.role
  846. ds_id = override_row.ds_id
  847. if object_type == "User":
  848. uid = int(object_id)
  849. if uid not in dict_uid_permissions:
  850. dict_uid_permissions[uid] = {}
  851. if ds_id not in dict_uid_permissions[uid]:
  852. dict_uid_permissions[uid][ds_id] = ""
  853. current_role =dict_uid_permissions[uid][ds_id]
  854. dict_uid_permissions[uid][ds_id] = Stemformatics_Auth.hierarchy_roles(current_role,role)
  855. if object_type == "Group":
  856. gid = int(object_id)
  857. users_for_group = group_dict[gid]
  858. for uid in users_for_group:
  859. if uid not in dict_uid_permissions:
  860. dict_uid_permissions[uid] = {}
  861. if ds_id not in dict_uid_permissions[uid]:
  862. dict_uid_permissions[uid][ds_id] = ""
  863. current_role = dict_uid_permissions[uid][ds_id]
  864. dict_uid_permissions[uid][ds_id] = Stemformatics_Auth.hierarchy_roles(current_role,role)
  865. return dict_uid_permissions
  866. @staticmethod
  867. def get_dict_of_user_dataset_availability(db): #CRITICAL-2
  868. db.schema = 'public'
  869. ds = db.datasets
  870. db.schema = 'stemformatics'
  871. u = db.users
  872. all_ds = ds.all()
  873. all_users = u.all()
  874. # this is the permissions that are per dataset/uid or dataset/group
  875. uid_permissions = Stemformatics_Auth.setup_dict_uid_permissions(db)
  876. list_of_user_dataset_availability = {}
  877. for user_row in all_users:
  878. uid = user_row.uid
  879. role = user_row.role
  880. list_of_user_dataset_availability[uid] = {}
  881. for ds_row in all_ds:
  882. ds_id = ds_row.id
  883. if role =="admin":
  884. status_for_this_user = "Admin"
  885. else:
  886. if role =="annotator":
  887. status_for_this_user = "Annotate"
  888. else:
  889. status_for_this_user = Stemformatics_Auth.calculate_dataset_status(uid,ds_row,uid_permissions)
  890. list_of_user_dataset_availability[uid][ds_id] = status_for_this_user
  891. return list_of_user_dataset_availability
  892. """ assumption is that uid is not an admin as this was checked beforehand """
  893. @staticmethod
  894. def calculate_dataset_status(uid,ds_row,uid_permissions):
  895. status_for_this_user = "Unavailable"
  896. ds_id = ds_row.id
  897. try:
  898. permission = uid_permissions[uid][ds_id]
  899. except:
  900. permission = None
  901. if not ds_row.published:
  902. if permission == "admin":
  903. status_for_this_user = "Admin"
  904. if permission == "annotator":
  905. status_for_this_user = "Annotate"
  906. else:
  907. if ds_row.private:
  908. if ds_row.show_limited:
  909. if permission == "admin" :
  910. status_for_this_user = "Admin"
  911. else:
  912. if permission == "annotator":
  913. status_for_this_user = "Annotate"
  914. else:
  915. status_for_this_user = "Limited"
  916. else:
  917. if permission == "admin":
  918. status_for_this_user = "Admin"
  919. if permission == "view":
  920. status_for_this_user = "Available"
  921. if permission == "annotator":
  922. status_for_this_user = "Annotate"
  923. else:
  924. status_for_this_user = "Available"
  925. return status_for_this_user
  926. @staticmethod
  927. def setup_redis_get_dict_of_user_dataset_availability(db):
  928. delimiter = config['redis_delimiter']
  929. data_result = Stemformatics_Auth.get_dict_of_user_dataset_availability(db)
  930. guest_username = config['guest_username']
  931. db_user = Stemformatics_Auth.get_user_from_username(db,guest_username)
  932. guest_uid = db_user.uid
  933. for uid in data_result:
  934. # for non-logged in users
  935. if uid == guest_uid:
  936. label_name = "user_dataset_availability"+delimiter+str(0)
  937. label_value = json.dumps(data_result[uid])
  938. r_server.set(label_name,label_value)
  939. label_name = "user_dataset_availability"+delimiter+str(uid)
  940. label_value = json.dumps(data_result[uid])
  941. r_server.set(label_name,label_value)
  942. @staticmethod
  943. def triggers_for_change_in_user(db,uid = None):
  944. Stemformatics_Auth.setup_redis_get_dict_of_user_dataset_availability(db)
  945. @staticmethod
  946. def send_email_for_pending_user(db,uid): #CRITICAL-2 #CRITICAL-6
  947. magic_globals.fetch()
  948. c = magic_globals.c
  949. db.schema = 'stemformatics'
  950. users = db.users
  951. user = users.filter(users.uid==uid).one()
  952. from_email = config['from_email']
  953. to_email = user.username
  954. subject = c.site_name+" Pending User"
  955. feedback_email = config['feedback_email']
  956. external_base_url = url('/',qualified=True)
  957. full_name = user.full_name
  958. username = user.username
  959. registration_url = external_base_url+url('auth/register?username=')+username
  960. from S4M_pyramid.model.stemformatics.stemformatics_dataset import Stemformatics_Dataset
  961. dataset_names = ""
  962. datasets = Stemformatics_Dataset.getChooseDatasetDetails(db,uid)
  963. for ds_id in datasets:
  964. if datasets[ds_id]['private']:
  965. handle = datasets[ds_id]['handle']
  966. authors = datasets[ds_id]['name']
  967. title = datasets[ds_id]['title']
  968. dataset_names += "- "+title + " (" + authors + "). In "+c.site_name+" this is called " + handle + ".\n"
  969. feedback_email = config['feedback_email']
  970. body = "Hello %s, you have a pending user in %s that was created for you to get access to private datasets:\n\n%s\n\nBut first, you will need to register. It is important that you use the email specified as your username. Click here to register: %s \n\nIf you have any questions or comments, please email us at %s " % (full_name,c.site_name,dataset_names,registration_url,feedback_email)
  971. # raise Error
  972. # Send the message via our own SMTP server, but don't include the
  973. # envelope header.
  974. from S4M_pyramid.model.stemformatics.stemformatics_notification import Stemformatics_Notification # wouldn't work otherwise??
  975. success = Stemformatics_Notification.send_email(from_email,to_email,subject,body)
  976. return success
  977. @staticmethod
  978. def change_user_role_for_ds(db,ds_id,object_type,object_id,role):
  979. ds_id = int(ds_id)
  980. object_id = str(object_id)
  981. conn_string = config['psycopg2_conn_string']
  982. conn = psycopg2.connect(conn_string)
  983. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  984. cursor.execute("select * from stemformatics.override_private_datasets where ds_id = %s and object_type = %s and object_id = %s;",(ds_id,object_type,object_id))
  985. result = cursor.fetchall()
  986. cursor.close()
  987. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  988. if len(result) == 0:
  989. cursor.execute("insert into stemformatics.override_private_datasets (ds_id,uid,role,object_type,object_id) values (%s,%s,%s,%s,%s);",(ds_id,0,role,object_type,object_id))
  990. else:
  991. cursor.execute("update stemformatics.override_private_datasets set role = %s where ds_id = %s and object_type = %s and object_id = %s;",(role,ds_id,object_type,object_id))
  992. # retrieve the records from the database
  993. conn.commit()
  994. cursor.close()
  995. conn.close()
  996. return "Success"
  997. @staticmethod
  998. def get_user_role(db,uid): #CRITICAL-2
  999. if isinstance(uid,int):
  1000. try:
  1001. conn_string = config['psycopg2_conn_string']
  1002. conn = psycopg2.connect(conn_string)
  1003. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  1004. cursor.execute("select role from stemformatics.users where uid = %s;",(uid,))
  1005. # retrieve the records from the database
  1006. result = cursor.fetchall()
  1007. cursor.close()
  1008. conn.close()
  1009. return result[0][0]
  1010. except:
  1011. return None
  1012. else:
  1013. return None
  1014. @staticmethod
  1015. def get_number_of_active_users():
  1016. conn_string = config['psycopg2_conn_string']
  1017. conn = psycopg2.connect(conn_string)
  1018. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  1019. cursor.execute("select count(*) from stemformatics.users where status = 1;")
  1020. # retrieve the records from the database
  1021. result = cursor.fetchall()
  1022. cursor.close()
  1023. conn.close()
  1024. number_of_active_users = result[0][0]
  1025. return number_of_active_users
  1026. @staticmethod
  1027. def create_base_export_key(uid):
  1028. conn_string = config['psycopg2_conn_string']
  1029. conn = psycopg2.connect(conn_string)
  1030. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  1031. cursor.execute("select * from stemformatics.users where uid = %s;",(uid,))
  1032. # retrieve the records from the database
  1033. result = cursor.fetchall()
  1034. cursor.close()
  1035. conn.close()
  1036. if len(result) ==1:
  1037. username = result[0]['username']
  1038. date_stamp = result[0]['created']
  1039. h = hashlib.sha1()
  1040. h.update((date_stamp.strftime('%Y-%m-%d') + username + str(uid)).encode('utf-8'))
  1041. base_export_key = h.hexdigest()
  1042. conn = psycopg2.connect(conn_string)
  1043. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  1044. cursor.execute("update stemformatics.users set base_export_key = %s where uid = %s;",(base_export_key,uid,))
  1045. conn.commit()
  1046. cursor.close()
  1047. conn.close()
  1048. return True
  1049. else:
  1050. return False
  1051. @staticmethod
  1052. def create_export_key(uid):
  1053. """
  1054. get uid and username
  1055. create md5 value to return
  1056. """
  1057. conn_string = config['psycopg2_conn_string']
  1058. conn = psycopg2.connect(conn_string)
  1059. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  1060. cursor.execute("select username,base_export_key from stemformatics.users where uid = %s;",(uid,))
  1061. # retrieve the records from the database
  1062. result = cursor.fetchall()
  1063. cursor.close()
  1064. conn.close()
  1065. if len(result) ==1:
  1066. username = result[0]['username']
  1067. base_export_key = result[0]['base_export_key']
  1068. server_name=url('/',qualified=True)
  1069. h = hashlib.sha1()
  1070. h.update((datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f') + username + str(uid)).encode('utf-8'))
  1071. export_key = h.hexdigest()
  1072. conn = psycopg2.connect(conn_string)
  1073. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  1074. cursor.execute("insert into stemformatics.export_key values (%s, %s) ;",(uid,export_key))
  1075. conn.commit()
  1076. cursor.close()
  1077. conn.close()
  1078. return export_key
  1079. else:
  1080. return None
  1081. @staticmethod
  1082. def validate_export_key(export_key,uid):
  1083. conn_string = config['psycopg2_conn_string']
  1084. conn = psycopg2.connect(conn_string)
  1085. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  1086. cursor.execute("select date_created from stemformatics.export_key where uid = %s and key = %s;",(uid,export_key,))
  1087. # retrieve the records from the database
  1088. result = cursor.fetchall()
  1089. cursor.close()
  1090. conn.close()
  1091. if len(result) ==1:
  1092. date_created = result[0]['date_created']
  1093. export_key_validity = int(config['export_key_validity'])
  1094. expiry_time = date_created + datetime.timedelta(days=export_key_validity)
  1095. current_date = datetime.datetime.now()
  1096. if current_date <= expiry_time:
  1097. return True
  1098. return False
  1099. else:
  1100. return False
  1101. @staticmethod
  1102. def get_gid_by_name(group_name):
  1103. if not isinstance( group_name, str ) and not isinstance(group_name,bytes):
  1104. return False
  1105. conn_string = config['psycopg2_conn_string']
  1106. conn = psycopg2.connect(conn_string)
  1107. cursor = conn.cursor(cursor_factory=psycopg2.ext

Large files files are truncated, but you can click here to view the full file