source: raven-j/heron_wsgi/admin_lib/i2b2pm.py

Last change on this file was 0:4b7a93ab1a38, checked in by Tamara McMahon <tmcmahon@…>, 2 years ago

KUMC DROC Data Dictionary requested by Mosa

File size: 19.2 KB
Line 
1"""i2b2pm -- Just-in-time I2B2 Project accounts and permissions
2---------------------------------------------------------------
3
4We use :class:`I2B2PM` to establish user accounts and permissions in
5the I2B2 project management cell that represent a HERON user's authority.
6
7  >>> pm, storyparts = Mock.make([I2B2PM, None])
8
9An object with a reference to this :class:`I2B2PM` can have us
10generate authorization to access I2B2, once it has verified to its
11satisfaction that the repository access policies are met.  For
12example, to generate a one-time authorization password and the
13corresponding hashed form for a qualified investigator who has signed
14the system access agreement and acknowledged the disclaimer::
15
16  >>> pw, js = pm.authz('john.smith', 'John Smith', 'BlueHeron')
17  >>> pw
18  'dfd03595-ab3e-4448-9c8e-a65a290cc3c5'
19
20The password field in the `User` record is hashed::
21
22  >>> js.password
23  u'da67296336429545fe63f61644e420'
24
25
26The effect is a `pm_user_data` record::
27
28  >>> dbsrc = storyparts.get((orm.session.Session, CONFIG_SECTION))
29  >>> import pprint
30  >>> ans = dbsrc().execute('select user_id, password, status_cd'
31  ...                       ' from pm_user_data')
32  >>> pprint.pprint(ans.fetchall())
33  [(u'john.smith', u'da67296336429545fe63f61644e420', u'A')]
34
35... and appropriate `pm_project_user_roles` records::
36
37  >>> ans = dbsrc().execute('select project_id, user_id, '
38  ...                       ' user_role_cd, status_cd'
39  ...                       ' from pm_project_user_roles')
40  >>> pprint.pprint(ans.fetchall())
41  [(u'BlueHeron', u'john.smith', u'USER', u'A'),
42   (u'BlueHeron', u'john.smith', u'DATA_LDS', u'A'),
43   (u'BlueHeron', u'john.smith', u'DATA_OBFSC', u'A'),
44   (u'BlueHeron', u'john.smith', u'DATA_AGG', u'A')]
45
46If John logs in again, a new one-time authorization is issued::
47
48  >>> auth, js2 = pm.authz('john.smith', 'John Smith', 'BlueHeron')
49  >>> auth
50  '89cd1d9a-ace1-4673-8a12-50ebac2625f9'
51
52This updates the `password` column of the `pm_user_data` record::
53
54  >>> ans = dbsrc().execute('select user_id, password, status_cd'
55  ...                       ' from pm_user_data')
56  >>> pprint.pprint(ans.fetchall())
57  [(u'john.smith', u'e5ab367ceece604b7f7583d024ac4e2b', u'A')]
58
59
60Access to REDCap Data
61=====================
62
63When REDCap data is integrated into HERON, HERON users should have
64access to the REDCap data corresponding to the REDCap projects that
65they have access to. Access to REDCap data is controlled via metadata.
66One I2B2 project can be shared among multiple HERON users that have
67access to the same REDCap projects.
68
69  >>> pm, md, dbsrc = Mock.make([
70  ...     I2B2PM, i2b2metadata.I2B2Metadata,
71  ...     (orm.session.Session, CONFIG_SECTION)])
72
73
74Suppose redcap projects 1, 2, 3, and 4 have been loaded into HERON,
75but their metadata has not been associated with any I2B2 projects:
76
77  >>> _mock_i2b2_projects(dbsrc(),
78  ...                     ((1, None), (2, None), (3, None), (4, None)))
79
80The default HERON project has no REDCap data, so it is a suitable
81project in the case where the list of REDCap projects is empty:
82
83  >>> pm.i2b2_project([])
84  ('BlueHeron', None)
85
86Suppose a HERON user has permission to REDCap projects 1, 11, and 91.
87Note that REDCap project 11 is not loaded into HERON.  The first
88available I2B2 project is selected and its description and metadata
89are updated suitably:
90
91  >>> pm.i2b2_project([1, 11, 91])
92  (u'REDCap_1', 'redcap_1_91')
93
94
95In another case, suppose 4 i2b2 projects are created and eventually 1,
962, and 3 get associated REDCap metadata:
97
98  >>> pm, md, dbsrc, storyparts = Mock.make([
99  ...     I2B2PM, i2b2metadata.I2B2Metadata,
100  ...     (orm.session.Session, CONFIG_SECTION), None])
101  >>> _mock_i2b2_projects(dbsrc(),
102  ...                     ((1, None), (2, None), (3, None), (4, None)))
103  >>> _mock_i2b2_proj_usage(dbsrc(),
104  ...                       (('1', 'redcap_10'),
105  ...                        ('2', 'redcap_20'),
106  ...                        ('3', 'redcap_30')))
107
108Someone with permissions to REDCap projects 1, 11, and 91 is directed
109to as-yet-unused I2B2 project:
110
111  >>> pm.i2b2_project([1, 11, 91])
112  (u'REDCap_4', 'redcap_1_91')
113
114Another users with permissions to REDCap projects 1, 11, and 91
115can use the same I2B2 project:
116
117  >>> pm.i2b2_project([1, 11, 91])
118  (u'REDCap_4', 'redcap_1_91')
119
120At this point, all the I2B2 projects have associated REDCap metadata.
121A user with access to an as-yet-unseen list of REDCap projects
122is referred to the default project:
123
124  >>> pm.i2b2_project([1, 41, 71])
125  ('BlueHeron', None)
126
127
128Suppose John Smith logs in to one HERON project; he'll be
129given roles to that project & the default project - BlueHeron:
130
131  >>> s = dbsrc()
132  >>> auth, js3 = pm.authz('john.smith', 'John Smith', 'REDCap_1')
133  >>> js = s.query(User).filter_by(user_id = 'john.smith').one()
134  >>> set([role.project_id for role in js.roles])
135  set([u'BlueHeron', u'REDCap_1'])
136
137If his REDCap rights are changed, he'll get access to a different
138I2B2 project; his roles in the above project go away:
139
140  >>> s = dbsrc()
141  >>> auth, js3 = pm.authz('john.smith', 'John Smith', 'REDCap_4')
142  >>> js = s.query(User).filter_by(user_id = 'john.smith').one()
143  >>> set([role.project_id for role in js.roles])
144  set([u'REDCap_4', u'BlueHeron'])
145
146If he has an ADMIN role when he logs in, the Admin role should not be deleted.
147The ADMIN role is not project specific:
148  >>> s = dbsrc()
149  >>> admin_role = UserRole(user_id='john.smith', project_id='@',
150  ...     user_role_cd='ADMIN', status_cd='A')
151  >>> s.add(admin_role)
152  >>> auth, js3 = pm.authz('john.smith', 'John Smith', 'REDCap_4')
153  >>> js = s.query(User).filter_by(user_id = 'john.smith').one()
154  >>> set([role.user_role_cd for role in js.roles])
155  set(['ADMIN', u'DATA_OBFSC', u'USER', u'DATA_LDS', u'DATA_AGG'])
156  >>> set([role.project_id for role in js.roles])
157  set(['@', u'REDCap_4', u'BlueHeron'])
158
159"""
160
161import logging
162import hashlib
163
164import injector
165from injector import inject, provides, singleton
166from sqlalchemy import Column, ForeignKey, and_
167from sqlalchemy import func, orm
168from sqlalchemy.types import String, Date, Enum
169from sqlalchemy.ext.declarative import declarative_base
170
171import rtconfig
172import jndi_util
173import ocap_file
174import i2b2metadata
175
176CONFIG_SECTION = 'i2b2pm'
177
178KUUIDGen = injector.Key('UUIDGen')
179KIdentifiedData = injector.Key('IdentifiedData')
180
181DEFAULT_PID = 'BlueHeron'
182
183Base = declarative_base()
184log = logging.getLogger(__name__)
185
186
187class I2B2PM(ocap_file.Token):
188    @inject(datasrc=(orm.session.Session, CONFIG_SECTION),
189            i2b2md=i2b2metadata.I2B2Metadata,
190            identified_data=KIdentifiedData,
191            uuidgen=KUUIDGen)
192    def __init__(self, datasrc, i2b2md, identified_data, uuidgen):
193        '''
194        :param datasrc: a function that returns a sqlalchemy session
195        '''
196        self._datasrc = datasrc
197        self._md = i2b2md
198        self._uuidgen = uuidgen
199        self.identified_data = identified_data
200
201    def account_for(self, agent, project_id):
202        '''Build a facet with authority reduced to one user and one project.
203
204        Note: We only use the agent cn and full_name(), not its
205              unforgeable authority. The caller is responsible for
206              policy enforcement.
207        '''
208        return I2B2Account(self, agent, project_id)
209
210    def i2b2_project(self, rc_pids):
211        '''select project based on redcap projects user has access to.
212
213        :return: (project_id, project_desc)
214        '''
215        default_pid = DEFAULT_PID
216        pms = self._datasrc()
217        log.info('Finding I2B2 project for REDCap pids: %s', rc_pids)
218        rc_pids = self._md.rc_in_i2b2(rc_pids)
219        if not rc_pids:
220            log.info('User REDCap projects are not in HERON')
221            return default_pid, None
222        log.debug('REDCap pids that are in HERON: %s', rc_pids)
223
224        proj_desc = proj_desc_for(rc_pids)
225        log.debug('proj_desc in pick_project: %s', proj_desc)
226
227        def ready_project():
228            '''Is there already an existing project with this redcap data?
229            '''
230            # Note first() returns None if there is no such project.
231            rs = pms.query(Project).filter_by(
232                    project_description=proj_desc).order_by(
233                                        Project.project_id.desc()).first()
234            log.debug('rs in pick_project: %s', rs)
235            return rs
236
237        def empty_project():
238            '''Find a REDCap project whose project_description has
239            not been set.
240            '''
241            return pms.query(Project).\
242                        filter(Project.project_description == None).\
243                        filter(Project.project_id.like('REDCap_%')).first()
244
245        def update_desc(project, proj_desc):
246            log.info('Update description of project %s to %s',
247                      project.project_id, proj_desc)
248            project.project_description = proj_desc
249            pms.commit()
250            return project.project_id, proj_desc
251
252        ready = ready_project()
253        if ready:
254            return update_desc(ready, proj_desc)
255        else:
256            empty = empty_project()
257            if empty:
258                self._md.project_terms(empty.project_id, rc_pids)
259                return update_desc(empty, proj_desc)
260
261        log.warn('Ran out of projects! Using default.')
262        return default_pid, None
263
264    def authz(self, uid, full_name,
265              project_id,
266              roles=('USER', 'DATA_LDS', 'DATA_OBFSC', 'DATA_AGG',
267                     'DATA_DEID')):
268        '''Generate authorization to use an i2b2 project.
269        '''
270        log.debug('generate authorization for: %s', (uid, full_name))
271        ds = self._datasrc()
272
273        t = func.now()
274        auth = str(self._uuidgen.uuid4())
275        pw_hash = hexdigest(auth)
276
277        # TODO: consider factoring out the "update the change_date
278        # whenever you set a field" aspect of Audited.
279        try:
280            me = ds.query(User).filter(User.user_id == uid).one()
281        except orm.exc.NoResultFound:
282            me = User(user_id=uid, full_name=full_name,
283                      entry_date=t, change_date=t, status_cd='A',
284                      password=pw_hash,
285                      # Related UserRole records might exist
286                      # even though there is no User record.
287                      roles=ds.query(UserRole).filter_by(user_id=uid).all())
288            log.info('adding: %s', me)
289            ds.add(me)
290        else:
291            log.info('found: %s', me)
292            me.password, me.status_cd, me.change_date = pw_hash, 'A', t
293        # http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html?highlight=query.update#sqlalchemy.orm.query.Query.update # noqa
294        ds.query(UserRole).filter(and_(UserRole.user_id == uid,
295            UserRole.user_role_cd.in_(list(roles)))).\
296            delete(synchronize_session='fetch')
297
298        #If a user has permissions to REDCap i2b2 project,
299        # also grant permissions to default project #2111
300        for project in set([project_id, DEFAULT_PID]):
301            for r in roles:
302                myrole = UserRole(user_id=uid, project_id=project,
303                                  user_role_cd=r,
304                                  entry_date=t, change_date=t, status_cd='A')
305                log.info('I2B2PM: adding: %s', myrole)
306                me.roles.append(myrole)
307
308        ds.commit()
309        return auth, me
310
311
312def hexdigest(txt):
313    '''mimic i2b2's own hex digest algorithm
314
315    It seems to omit leading 0's.
316
317    >>> hexdigest('test')
318    '98f6bcd4621d373cade4e832627b4f6'
319    '''
320    return ''.join([hex(ord(b))[2:] for b in hashlib.md5(txt).digest()])
321
322
323def revoke_expired_auths(ds):
324    '''Revoke one-time passwords for all users whose sessions are expired.
325    '''
326    ds.execute('''
327    update i2b2pm.pm_user_data ipud
328    set ipud.password = null
329    where
330        ipud.user_id not like '%SERVICE_ACCOUNT'
331        and ipud.password is not null and (
332        select max(ipus.expired_date)
333        from i2b2pm.pm_user_session ipus
334        where ipus.user_id = ipud.user_id) < sysdate
335    ''')
336    ds.commit()
337
338
339def proj_desc_for(rc_pids):
340    """Encode a set of REDCap project IDs in an I2B2 project description.
341
342    >>> proj_desc_for((1, 15, 2))
343    'redcap_1_2_15'
344    """
345    return 'redcap_' + ('_'.join([str(pid) for pid in sorted(rc_pids)]))
346
347
348class I2B2Account(ocap_file.Token):
349    def __init__(self, pm, agent, project_id):
350        self.__pm = pm
351        self.__agent = agent
352        self._project_id = project_id
353
354    def __repr__(self):
355        return 'Access(%s)' % self.__agent
356
357    def creds(self):
358        agent = self.__agent
359        key, u = self.__pm.authz(agent.cn, agent.full_name(), self._project_id)
360        return (agent.cn, key)
361
362
363class Audited(object):
364    change_date = Column(Date)
365    entry_date = Column(Date)
366    changeby_char = Column(String)  # foreign key?
367    status_cd = Column(Enum('A', 'D'))
368
369
370class User(Base, Audited):
371    __tablename__ = 'pm_user_data'
372
373    user_id = Column(String, primary_key=True)
374    full_name = Column(String)
375    password = Column(String)  # hex(md5sum(password))
376    email = Column(String)
377    roles = orm.relationship('UserRole', backref='pm_user_data')
378
379    def __repr__(self):
380        return "<User(%s, %s)>" % (self.user_id, self.full_name)
381
382
383class UserRole(Base, Audited):
384    __tablename__ = 'pm_project_user_roles'
385
386    project_id = Column(String, ForeignKey('pm_project_data.project_id'),
387                        primary_key=True)
388    user_id = Column(String,
389                     ForeignKey('pm_user_data.user_id'),
390                     primary_key=True)
391    user_role_cd = Column(Enum('ADMIN', 'MANAGER', 'USER',
392                               'DATA_OBFSC', 'DATA_AGG', 'DATA_DEID',
393                               'DATA_LDS', 'DATA_PROT'),
394                          primary_key=True)
395
396    def __repr__(self):
397        return "<UserRole(%s, %s, %s)>" % (self.project_id,
398                                           self.user_id,
399                                           self.user_role_cd)
400
401
402class UserSession(Base, Audited):
403    __tablename__ = 'pm_user_session'
404
405    user_id = Column(String,
406                     ForeignKey('pm_user_data.user_id'),
407                     primary_key=True)
408    expired_date = Column(Date)
409
410    def __repr__(self):
411        return "<UserSession(%s, %s)>" % (self.user_id,
412                                          self.expired_date)
413
414
415class Project(Base, Audited):
416    __tablename__ = 'pm_project_data'
417
418    project_id = Column(String, primary_key=True)
419    project_description = Column(String)
420
421    def __repr__(self):
422        return "<Project(%s, %s)>" % (self.project_id,
423                                      self.project_description)
424
425
426class RunTime(rtconfig.IniModule):  # pragma: nocover
427    jndi_name = 'java:/PMBootStrapDS'
428
429    # abusing Session a bit; this really provides a subclass, not an
430    # instance, of Session
431    def sessionmaker(self, jndi, CONFIG):
432        import os
433        from sqlalchemy import create_engine
434
435        rt = rtconfig.RuntimeOptions(['jboss_deploy'])
436        rt.load(self._ini, CONFIG)
437
438        jdir = ocap_file.Readable(rt.jboss_deploy, os.path, os.listdir, open)
439        ctx = jndi_util.JBossContext(jdir, create_engine)
440
441        sm = orm.session.sessionmaker()
442
443        def make_session_and_revoke():
444            engine = ctx.lookup(jndi)
445            ds = sm(bind=engine)
446            revoke_expired_auths(ds)
447            return ds
448
449        return make_session_and_revoke
450
451    @singleton
452    @provides((orm.session.Session, CONFIG_SECTION))
453    def pm_sessionmaker(self):
454        return self.sessionmaker(self.jndi_name, CONFIG_SECTION)
455
456    @singleton
457    @provides(i2b2metadata.I2B2Metadata)
458    @inject(mdsm=(orm.session.Session, i2b2metadata.CONFIG_SECTION_MD))
459    def metadata(self, mdsm):
460        imd = i2b2metadata.I2B2Metadata(mdsm)
461        return imd
462
463    @provides(KUUIDGen)
464    def uuid_maker(self):
465        import uuid
466        return uuid
467
468    @provides(KIdentifiedData)
469    def identified_data(self):
470        rt = rtconfig.RuntimeOptions(['identified_data'])
471        rt.load(self._ini, CONFIG_SECTION)
472        mode = rt.identified_data.lower() in ('1', 'true')
473        return mode
474
475    @classmethod
476    def mods(cls, ini):
477        return [i2b2metadata.RunTime(ini), cls(ini)]
478
479
480class Mock(injector.Module, rtconfig.MockMixin):
481    '''Mock up I2B2PM dependencies: SQLite datasource
482    '''
483    @singleton
484    @provides((orm.session.Session, CONFIG_SECTION))
485    def pm_sessionmaker(self):
486        from sqlalchemy import create_engine
487
488        engine = create_engine('sqlite://')
489        Base.metadata.create_all(engine)
490        return orm.session.sessionmaker(engine)
491
492    @provides(i2b2metadata.I2B2Metadata)
493    def metadata(self):
494            return i2b2metadata.MockMetadata(1)
495
496    @provides(KUUIDGen)
497    def uuid_maker(self):
498        class G(object):
499            def __init__(self):
500                from uuid import UUID
501                self._d = iter([UUID('dfd03595-ab3e-4448-9c8e-a65a290cc3c5'),
502                                UUID('89cd1d9a-ace1-4673-8a12-50ebac2625f9'),
503                                UUID('dc584070-9e36-493e-80ce-ac277c1ce611'),
504                                UUID('0100f48b-c313-4086-92a9-6bfc621cc0df'),
505                                UUID('537d9d95-b017-4d9d-b096-2d1af316eb86'),
506                                UUID('537d9d95-b017-4d9d-b096-2d1af316eb34'),
507                                UUID('537d9d95-b017-4d9d-b096-2d1af316eb92')])
508
509            def uuid4(self):
510                return self._d.next()
511
512        return G()
513
514    @provides(KIdentifiedData)
515    def identified_data(self):
516        return False
517
518
519def _mock_i2b2_projects(ds, id_descs):
520    '''Mock up i2b2 projects
521    '''
522    for pid, desc in id_descs:
523        ds.add(Project(project_id='REDCap_%s' % pid,
524                       project_description=desc))
525    ds.commit()
526
527
528def _mock_i2b2_proj_usage(ds, assignments):
529    '''Mock up assigning REDCap metadata to i2b2 projects.
530    '''
531    for (i2b2_id, rc_pid) in assignments:
532        ds.query(Project).filter_by(project_id='REDCap_' + i2b2_id).\
533            update({"project_description": 'redcap_%s' % rc_pid})
534        ds.commit()
535
536
537def _integration_test():  # pragma: nocover
538    #python i2b2pm.py badagarla 12,11,53 'Bhargav A'
539    import sys
540
541    logging.basicConfig(level=logging.DEBUG)
542    salog = logging.getLogger('sqlalchemy.engine.base.Engine')
543    salog.setLevel(logging.INFO)
544
545    if '--list' in sys.argv:
546        _list_users()
547        return
548
549    user_id, rc_pids, full_name = sys.argv[1:4]
550
551    (pm, ) = RunTime.make(None, [I2B2PM])
552    t, _ = pm.i2b2_project(rc_pids.split(','))
553    print "THE PROJECT THAT WAS PICKED: %s" % (t)
554    print pm.authz(user_id, full_name, t)
555
556
557def _list_users():  # pragma: nocover
558    import csv, sys
559    (sm, ) = RunTime.make(None,
560                          [(orm.session.Session, CONFIG_SECTION)])
561    s = sm()
562    # get column names
563    #ans = s.execute("select * from pm_user_session "
564    #                "  where rownum < 2")
565    #print ans.fetchone().items()
566
567    ans = s.execute("select max(entry_date), count(*), user_id "
568                    "  from pm_user_session "
569                    "  where user_id not in ('OBFSC_SERVICE_ACCOUNT')"
570                    "  group by user_id"
571                    "  order by user_id")
572
573    out = csv.writer(sys.stdout)
574    out.writerow(('last_login', 'login_count', 'user_id'))
575    out.writerows([(when.isoformat(), qty, uid)
576                   for when, qty, uid in ans.fetchall()])
577
578
579if __name__ == '__main__':  # pragma: nocover
580    _integration_test()
Note: See TracBrowser for help on using the repository browser.