source: security/trunk/python/NDG/BADCUserRoles.py @ 494

"""NDG User Roles interface to BADC Attribute Authority

NERC Data Grid Project

P J Kershaw 01/07/05

Copyright (C) 2005 CCLRC & NERC

This software may be distributed under the terms of the Q Public License,
version 1.0 or later.
"""

cvsID = '$Id$'

# BADC Ingres database
import ingresdbi

# For parsing of properties file
import cElementTree as ElementTree

from AttAuthority import AAUserRoles, AAUserRolesError
from X509 import X500DN



class BADCUserRoles(AAUserRoles):
    """User Roles interface to BADC Attribute Authority"""

    def __init__(self,
                 propertiesFilePath=None,
                 CreateRoles=True,
                 **connectKeys):
        
        """Connect to BADC User database

        Omit connectKeys or explicitly set CreateRoles to False if wish to
        avoid querying the database on object creation"""

        self.__con = None
        self.__rolesLUT = {}

        if propertiesFilePath is not None:
            connectKeys = self.readPropertiesFile(propertiesFilePath)

        # keywords for connection to database either from input arguments or
        # read from properties file
        if connectKeys.items() is not None:
            self.connect(**connectKeys)
            if CreateRoles: self.createRoles()
         
        
    def __del__(self):
        """Call close() - Commit any changes and close database connection"""
        try:
            self.close()
        except:
            pass
        

    def readPropertiesFile(self, propertiesFilePath):

        """Read the configuration properties for the database connection

        propertiesFilePath: file path to properties file
        """
        
        if not isinstance(propertiesFilePath, basestring):
            raise AAUserRolesError("Input Properties file path " + \
                  "must be a valid string.")

        try:
            tree = ElementTree.parse(propertiesFilePath)
            
        except IOError, ioErr:
            raise AAUserRolesError(\
                                "Error parsing properties file \"%s\": %s" % \
                                (ioErr.filename, ioErr.strerror))

        
        prop = tree.getroot()

        # Return properties as dictionary
        return dict([(elem.tag, elem.text) for elem in prop])


    def connect(self, enableTrace=False, **ingresDBIConnectKeys):
        """Make a connection to the database"""
        
        if enableTrace is True:
            trace = (7, None)
        else:
            trace = (0, None)
        
        self.__con = ingresdbi.connect(**ingresDBIConnectKeys)

        
    def close(self):    
        """Commit any changes and close database connection"""
        self.__con.commit()
        self.__con.close()


    def createRoles(self):
        """Query the BADC user database in order to create a list of user
        roles.

        This is called on object creation but can be called again to update
        an existing object following changes to the database content"""

        # Roles based on the institute type a user belongs to 
        instSqlStmt = "select accountid, type from " + \
                      "tbusers, addresses, tbinstitutes " + \
                      "where tbusers.addresskey = addresses.addresskey and " +\
                      "addresses.institutekey = tbinstitutes.institutekey"

        cursor = self.__con.cursor()
        try:
            cursor.execute(instSqlStmt)
            instQueryRes = cursor.fetchall()
            
        except Exception, e:
            raise AAUserRolesError("Querying for institutes: %s" % str(e))

        # Make a dictionary of username vs. roles list
        self.__rolesLUT = dict([(k, [v.lower()]) for k,v in instQueryRes])
    

        # Get groups and NERC funded info to add as roles
        grpSqlStmt = "select accountid, nercfunded, grp from " + \
                     "tbusers, tbdatasetjoin, tbdatasets " + \
                     "where tbusers.userkey = tbdatasetjoin.userkey and " + \
                     "tbdatasetjoin.datasetid = tbdatasets.datasetid and " + \
                     "tbdatasetjoin.removed = 0"

        try:
            cursor.execute(grpSqlStmt)
            grpQueryRes = cursor.fetchall()
            
        except Exception, e:
            raise AAUserRolesError(\
                        "Querying for groups and NERC funded: %s" % str(e))

        for i in grpQueryRes:
            # Check - some users may have been missed out in first query
            if not self.__rolesLUT.has_key(i[0]):
                self.__rolesLUT[i[0]] = []
                
            # Add new groups as roles
            if i[2] and i[2] not in self.__rolesLUT[i[0]]:
                self.__rolesLUT[i[0]].append(i[2])

            # Check for NERC funded
            if i[1] != 0 and 'nercFunded' not in self.__rolesLUT[i[0]]:
                self.__rolesLUT[i[0]].append('nercFunded')


        # Catch remaining users left out from join queries above
        cursor.execute("select accountid from tbusers")
        allQueryRes = cursor.fetchall()
        for i in allQueryRes:
            if i[0] and not self.__rolesLUT.has_key(i[0]):
                self.__rolesLUT[i[0]] = []



        
    def usrIsRegistered(self, dn=None, x500DN=None):
        """Return boolean to indicate whether user with given DN is
        registered

        Interface method to AttAuthority - Overrides AAUserRoles base
        class"""

        # Parse username from DN string
        try:
            if dn:
                userName = X500DN(dn)['CN']
            else:
                userName = x500DN['CN']
                
        except Exception, e:
            raise AAUserRolesError("Parsing userName from DN %s: %s" % (dn,e))


        sqlStmt = "select distinct accountid from tbusers " + \
                  "where accountid = '%s'" % userName

        try:
            self.__con.cursor().execute(sqlStmt)
            match = cursor.fetchall()
            
        except Exception, e:
            raise AAUserRolesError("Searching for user %s: %s" % (userName,e))
        if len(match) == 1:
            return True
        else:
            return False


    def getRoles(self, dn=None, x500DN=None):
        """Return valid roles for the BADC database
        
        Interface method to AttAuthority - Overrides AAUserRoles base
        class

        dn:     distinguished name as a string
        x500DN: distinguished name as an X500DN instance"""

        # Parse username from DN string
        try:
            if dn:
                userName = X500DN(dn)['CN']
            else:
                userName = x500DN['CN']
                
        except Exception, e:
            raise AAUserRolesError("Parsing userName from DN %s: %s" % (dn,e))

        try:
            return self.__rolesLUT[userName]
        except:
            return []


    def cursor(self):
        """Return a database cursor instance"""
        return self.__con.cursor()

    
    def printSurnames(self):
        """Test method - print all user surnames held in database"""
        
        c = self.__con.cursor()

        c.execute("select surname from tbusers order by surname")

        description = c.description
        pprint.pprint(description)

        rows = c.fetchall()

        for row in rows:            
            count = 0
            for column in row:
                  print description[count][0] , ': ', column
                  count += 1
                  
            print "-----------------------------"


def badcUserRolesTest(**keys):

    userRoles = BADCUserRoles(**keys)
    userRoles.printSurnames()
    
    
if __name__ == "__main__":
    badcUserRolesTest()