"Fossies" - the Fresh Open Source Software Archive

Member "LinOTP-release-2.10.5.2/linotpd/src/linotp/lib/audit/SQLAudit.py" (13 May 2019, 22973 Bytes) of package /linux/misc/LinOTP-release-2.10.5.2.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) Python source code syntax highlighting (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file. For more information about "SQLAudit.py" see the Fossies "Dox" file reference documentation.

    1 # -*- coding: utf-8 -*-
    2 #
    3 #    LinOTP - the open source solution for two factor authentication
    4 #    Copyright (C) 2010 - 2019 KeyIdentity GmbH
    5 #
    6 #    This file is part of LinOTP server.
    7 #
    8 #    This program is free software: you can redistribute it and/or
    9 #    modify it under the terms of the GNU Affero General Public
   10 #    License, version 3, as published by the Free Software Foundation.
   11 #
   12 #    This program is distributed in the hope that it will be useful,
   13 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
   14 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   15 #    GNU Affero General Public License for more details.
   16 #
   17 #    You should have received a copy of the
   18 #               GNU Affero General Public License
   19 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
   20 #
   21 #
   22 #    E-mail: linotp@keyidentity.com
   23 #    Contact: www.linotp.org
   24 #    Support: www.keyidentity.com
   25 #
   26 """
   27 This is the Audit Class, that writes Audits to SQL DB
   28 
   29 uses a public/private key for signing the log entries
   30 
   31     # create keypair:
   32     # openssl genrsa -out private.pem 2048
   33     # extract the public key:
   34     # openssl rsa -in private.pem -pubout -out public.pem
   35 
   36 """
   37 
   38 import datetime
   39 from sqlalchemy import schema, types, orm, and_, or_, asc, desc
   40 
   41 from M2Crypto import EVP, RSA
   42 from binascii import hexlify
   43 from binascii import unhexlify
   44 from sqlalchemy import create_engine
   45 from linotp.lib.audit.base import AuditBase
   46 from pylons import config
   47 
   48 import logging.config
   49 import traceback
   50 
   51 import linotp
   52 
   53 from linotp.lib.text_utils import utf8_slice
   54 
   55 # Create the logging object from the linotp.ini config file
   56 ini_file = config.get("__file__")
   57 if ini_file is not None:
   58     # When importing the module with Sphinx to generate documentation
   59     # 'ini_file' is None. In other cases this should not be the case.
   60     logging.config.fileConfig(ini_file, disable_existing_loggers=False)
   61 
   62 log = logging.getLogger(__name__)
   63 
   64 metadata = schema.MetaData()
   65 
   66 def now():
   67     u_now = u"%s" % datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f")
   68     return u_now
   69 
   70 ######################## MODEL ################################################
   71 table_prefix = config.get("linotpAudit.sql.table_prefix", "")
   72 
   73 audit_table_name = '%saudit' % table_prefix
   74 
   75 audit_table = schema.Table(audit_table_name, metadata,
   76     schema.Column('id', types.Integer, schema.Sequence('audit_seq_id',
   77                                                        optional=True),
   78                   primary_key=True),
   79     schema.Column('timestamp', types.Unicode(30), default=now, index=True),
   80     schema.Column('signature', types.Unicode(512), default=u''),
   81     schema.Column('action', types.Unicode(30), index=True),
   82     schema.Column('success', types.Unicode(30), default=u"False"),
   83     schema.Column('serial', types.Unicode(30), index=True),
   84     schema.Column('tokentype', types.Unicode(40)),
   85     schema.Column('user', types.Unicode(255), index=True),
   86     schema.Column('realm', types.Unicode(255), index=True),
   87     schema.Column('administrator', types.Unicode(255)),
   88     schema.Column('action_detail', types.Unicode(512), default=u''),
   89     schema.Column('info', types.Unicode(512), default=u''),
   90     schema.Column('linotp_server', types.Unicode(80)),
   91     schema.Column('client', types.Unicode(80)),
   92     schema.Column('log_level', types.Unicode(20), default=u"INFO", index=True),
   93     schema.Column('clearance_level', types.Integer, default=0)
   94 )
   95 
   96 
   97 AUDIT_ENCODE = ["action", "serial", "success", "user", "realm", "tokentype",
   98                 "administrator", "action_detail", "info", "linotp_server",
   99                 "client", "log_level"]
  100 
  101 class AuditTable(object):
  102 
  103     def __init__(self, serial=u"", action=u"", success=u"False",
  104                  tokentype=u"", user=u"",
  105                  realm=u"", administrator=u"",
  106                  action_detail=u"", info=u"",
  107                  linotp_server=u"",
  108                  client=u"",
  109                  log_level=u"INFO",
  110                  clearance_level=0,
  111                  config_param=None):
  112         """
  113         build an audit db entry
  114 
  115         *parmeters require to be compliant to the table defintion, which
  116          implies that type unicode is recomended where appropriate
  117 
  118         :param serial: token serial number
  119         :type serial: unicode
  120         :param action: the scope of the audit entry, eg. admin/show
  121         :type action: unicode
  122         :param success: the result of the action
  123         :type success: unicode
  124         :param tokentype: which token type was involved
  125         :type tokentype: unicode
  126         :param user: user login
  127         :type user: unicode
  128         :param realm: the involved realm
  129         :type realm: unicode
  130         :param administrator: the admin involved
  131         :type administrator: unicode
  132         :param action_detail: the additional action details
  133         :type action_detail: unicode
  134         :param info: additional info for failures
  135         :type info: unicode
  136         :param linotp_server: the server name
  137         :type linotp_server: unicode
  138         :param client: info about the requesting client
  139         :type client: unicode
  140         :param loglevel: the loglevel of the action
  141         :type loglevel: unicode
  142         :param clearance_level: *??*
  143         :type clearance_level: integer
  144 
  145         """
  146 
  147         log.debug("[__init__] creating AuditTable object, action = %s"
  148                   % action)
  149 
  150         if config_param:
  151             self.config = config_param
  152         else:
  153             self.config = config
  154         self.trunc_as_err = self.config.get('linotpAudit.error_on_truncation',
  155                                             'False') == 'True'
  156         self.serial = unicode(serial or '')
  157         self.action = unicode(action or '')
  158         self.success = unicode(success or '0')
  159         self.tokentype = unicode(tokentype or '')
  160         self.user = unicode(user or '')
  161         self.realm = unicode(realm or '')
  162         self.administrator = unicode(administrator or '')
  163 
  164         #
  165         # we have to truncate the 'action_detail' and the 'info' data
  166         # in utf-8 compliant way
  167         #
  168         self.action_detail = utf8_slice(unicode(action_detail or ''), 512).next()
  169         self.info = utf8_slice(unicode(info or ''), 512).next()
  170 
  171         self.linotp_server = unicode(linotp_server or '')
  172         self.client = unicode(client or '')
  173         self.log_level = unicode(log_level or '')
  174         self.clearance_level = clearance_level
  175         self.timestamp = now()
  176         self.siganture = ' '
  177 
  178     def _get_field_len(self, col_name):
  179         leng = -1
  180         try:
  181             ll = audit_table.columns[col_name]
  182             ty = ll.type
  183             leng = ty.length
  184         except Exception as exx:
  185             leng = -1
  186 
  187         return leng
  188 
  189     def __setattr__(self, name, value):
  190         """
  191         to support unicode on all backends, we use the json encoder with
  192         the assci encode default
  193 
  194         :param name: db column name or class memeber
  195         :param value: the corresponding value
  196 
  197         :return: - nothing -
  198         """
  199         if type(value) in [str, unicode]:
  200             field_len = self._get_field_len(name)
  201             encoded_value = linotp.lib.crypto.uencode(value)
  202             if field_len != -1 and len(encoded_value) > field_len:
  203                 log.warning("truncating audit data: [audit.%s] %s",
  204                             name, value)
  205                 if self.trunc_as_err is not False:
  206                     raise Exception("truncating audit data: [audit.%s] %s" %
  207                                     (name, value))
  208 
  209                 ## during the encoding the value might expand -
  210                 ## so we take this additional length into account
  211                 add_len = len(encoded_value) - len(value)
  212                 value = value[:field_len - add_len]
  213 
  214         if name in AUDIT_ENCODE:
  215             ## encode data
  216             if value:
  217                 value = linotp.lib.crypto.uencode(value)
  218         super(AuditTable, self).__setattr__(name, value)
  219 
  220     def __getattribute__(self, name):
  221         """
  222         to support unicode on all backends, we use the json decoder with
  223         the assci decode default
  224 
  225         :param name: db column name or class memeber
  226 
  227         :return: the corresponding value
  228         """
  229         #Default behaviour
  230         value = object.__getattribute__(self, name)
  231         if name in AUDIT_ENCODE:
  232             if value:
  233                 value = linotp.lib.crypto.udecode(value)
  234             else:
  235                 value = ""
  236 
  237         return value
  238 
  239 orm.mapper(AuditTable, audit_table)
  240 
  241 
  242 # replace sqlalchemy-migrate by the ability to ad a column
  243 def add_column(engine, table, column):
  244     """
  245     small helper to add a column by calling a native 'ALTER TABLE' to
  246     replace the need for sqlalchemy-migrate
  247 
  248     from:
  249     http://stackoverflow.com/questions/7300948/add-column-to-sqlalchemy-table
  250 
  251     :param engine: the running sqlalchemy
  252     :param table: in which table should this column be added
  253     :param column: the sqlalchemy definition of a column
  254 
  255     :return: boolean of success or not
  256     """
  257 
  258     result = False
  259 
  260     table_name = table.description
  261     column_name = column.compile(dialect=engine.dialect)
  262     column_type = column.type.compile(engine.dialect)
  263 
  264     try:
  265         engine.execute('ALTER TABLE %s ADD COLUMN %s %s'
  266                                 % (table_name, column_name, column_type))
  267         result = True
  268 
  269     except Exception as exx:
  270         # Obviously we already migrated the database.
  271         result = False
  272 
  273     return result
  274 
  275 
  276 ###############################################################################
  277 class Audit(AuditBase):
  278     """
  279     Audit Implementation to the generic audit interface
  280     """
  281     def __init__(self, config):
  282         self.name = "SQlAudit"
  283         self.config = config
  284         connect_string = config.get("linotpAudit.sql.url")
  285         pool_recycle = config.get("linotpAudit.sql.pool_recyle", 3600)
  286         implicit_returning = config.get("linotpSQL.implicit_returning", True)
  287 
  288         self.engine = None
  289         ########################## SESSION ##################################
  290 
  291         # Create an engine and create all the tables we need
  292         if implicit_returning:
  293             # If implicit_returning is explicitly set to True, we
  294             # get lots of mysql errors
  295             # AttributeError: 'MySQLCompiler_mysqldb' object has no
  296             # attribute 'returning_clause'
  297             # So we do not mention explicit_returning at all
  298             self.engine = create_engine(connect_string,
  299                                         pool_recycle=pool_recycle)
  300         else:
  301             self.engine = create_engine(connect_string,
  302                                         pool_recycle=pool_recycle,
  303                                         implicit_returning=False)
  304 
  305         metadata.bind = self.engine
  306         metadata.create_all()
  307 
  308         # Set up the session
  309         self.sm = orm.sessionmaker(bind=self.engine, autoflush=True,
  310                                    autocommit=True, expire_on_commit=True)
  311         self.session = orm.scoped_session(self.sm)
  312 
  313         # initialize signing keys
  314         self.readKeys()
  315 
  316         self.PublicKey = RSA.load_pub_key(
  317                                           self.config.get("linotpAudit.key.public"))
  318         self.VerifyEVP = EVP.PKey()
  319         self.VerifyEVP.reset_context(md='sha256')
  320         self.VerifyEVP.assign_rsa(self.PublicKey)
  321 
  322         return
  323 
  324     def _attr_to_dict(self, audit_line):
  325 
  326         line = {}
  327         line['number'] = audit_line.id
  328         line['id'] = audit_line.id
  329         line['date'] = str(audit_line.timestamp)
  330         line['timestamp'] = str(audit_line.timestamp)
  331         line['missing_line'] = ""
  332         line['serial'] = audit_line.serial
  333         line['action'] = audit_line.action
  334         line['action_detail'] = audit_line.action_detail
  335         line['success'] = audit_line.success
  336         line['token_type'] = audit_line.tokentype
  337         line['tokentype'] = audit_line.tokentype
  338         line['user'] = audit_line.user
  339         line['realm'] = audit_line.realm
  340         line['administrator'] = audit_line.administrator
  341         line['action_detail'] = audit_line.action_detail
  342         line['info'] = audit_line.info
  343         line['linotp_server'] = audit_line.linotp_server
  344         line["client"] = audit_line.client
  345         line['log_level'] = audit_line.log_level
  346         line['clearance_level'] = audit_line.clearance_level
  347 
  348         return line
  349 
  350     def _sign(self, audit_line):
  351         '''
  352         Create a signature of the audit object
  353         '''
  354         line = self._attr_to_dict(audit_line)
  355         s_audit = getAsString(line)
  356 
  357         key = EVP.load_key_string(self.private)
  358         key.reset_context(md='sha256')
  359         key.sign_init()
  360         key.sign_update(s_audit)
  361         signature = key.sign_final()
  362         return u'' + hexlify(signature)
  363 
  364 
  365     def _verify(self, auditline, signature):
  366         '''
  367         Verify the signature of the audit line
  368         '''
  369         res = False
  370         if not signature:
  371             log.debug("[_verify] missing signature %r" % auditline)
  372             return res
  373 
  374         s_audit = getAsString(auditline)
  375 
  376         self.VerifyEVP.verify_init()
  377         self.VerifyEVP.verify_update(s_audit)
  378         res = self.VerifyEVP.verify_final(unhexlify(signature))
  379 
  380         return res
  381 
  382     def log(self, param):
  383         '''
  384         This method is used to log the data. It splits information of
  385         multiple tokens (e.g from import) in multiple audit log entries
  386         '''
  387 
  388         try:
  389             serial = param.get('serial', '') or ''
  390             if not serial:
  391                 # if no serial, do as before
  392                 self.log_entry(param)
  393             else:
  394                 # look if we have multiple serials inside
  395                 serials = serial.split(',')
  396                 for serial in serials:
  397                     p = {}
  398                     p.update(param)
  399                     p['serial'] = serial
  400                     self.log_entry(p)
  401 
  402         except Exception as  exx:
  403             log.exception("[log] error writing log message: %r" % exx)
  404             self.session.rollback()
  405             raise exx
  406 
  407         return
  408 
  409     def log_entry(self, param):
  410         '''
  411         This method is used to log the data.
  412         It should hash the data and do a hash chain and sign the data
  413         '''
  414 
  415         at = AuditTable(
  416                     serial=param.get('serial'),
  417                     action=param.get('action'),
  418                     success=1 if param.get('success') else 0,
  419                     tokentype=param.get('token_type'),
  420                     user=param.get('user'),
  421                     realm=param.get('realm'),
  422                     administrator=param.get('administrator'),
  423                     action_detail=param.get('action_detail'),
  424                     info=param.get('info'),
  425                     linotp_server=param.get('linotp_server'),
  426                     client=param.get('client'),
  427                     log_level=param.get('log_level'),
  428                     clearance_level=param.get('clearance_level'),
  429                     config_param=self.config,
  430             )
  431 
  432         self.session.add(at)
  433         self.session.flush()
  434         # At this point "at" contains the primary key id
  435         at.signature = self._sign(at)
  436         self.session.merge(at)
  437         self.session.flush()
  438 
  439 
  440     def initialize_log(self, param):
  441         '''
  442         This method initialized the log state.
  443         The fact, that the log state was initialized, also needs to be logged.
  444         Therefor the same params are passed as i the log method.
  445         '''
  446         pass
  447 
  448     def set(self):
  449         '''
  450         This function could be used to set certain things like the signing key.
  451         But maybe it should only be read from linotp.ini?
  452         '''
  453         pass
  454 
  455 
  456     def _buildCondition(self, param, AND):
  457         '''
  458         create the sqlalchemy condition from the params
  459         '''
  460         conditions = []
  461         boolCheck = and_
  462         if not AND:
  463             boolCheck = or_
  464 
  465         for k, v in param.items():
  466             if "" != v:
  467                 if "serial" == k:
  468                     conditions.append(AuditTable.serial.like(v))
  469                 elif "user" == k:
  470                     conditions.append(AuditTable.user.like(v))
  471                 elif "realm" == k:
  472                     conditions.append(AuditTable.realm.like(v))
  473                 elif "action" == k:
  474                     conditions.append(AuditTable.action.like(v))
  475                 elif "action_detail" == k:
  476                     conditions.append(AuditTable.action_detail.like(v))
  477                 elif "date" == k:
  478                     conditions.append(AuditTable.timestamp.like(v))
  479                 elif "number" == k:
  480                     conditions.append(AuditTable.id.like(v))
  481                 elif "success" == k:
  482                     conditions.append(AuditTable.success.like(v))
  483                 elif "tokentype" == k:
  484                     conditions.append(AuditTable.tokentype.like(v))
  485                 elif "administrator" == k:
  486                     conditions.append(AuditTable.administrator.like(v))
  487                 elif "info" == k:
  488                     conditions.append(AuditTable.info.like(v))
  489                 elif "linotp_server" == k:
  490                     conditions.append(AuditTable.linotp_server.like(v))
  491                 elif "client" == k:
  492                     conditions.append(AuditTable.client.like(v))
  493 
  494         all_conditions = None
  495         if conditions:
  496             all_conditions = boolCheck(*conditions)
  497 
  498         return all_conditions
  499 
  500     def row2dict(self, audit_line):
  501         """
  502         convert an SQL audit db to a audit dict
  503 
  504         :param audit_line: audit db row
  505         :return: audit entry dict
  506         """
  507 
  508         line = self._attr_to_dict(audit_line)
  509 
  510         ## if we have an \uencoded data, we extract the unicode back
  511         for key, value in line.items():
  512             if value and type(value) in [str, unicode]:
  513                 value = linotp.lib.crypto.udecode(value)
  514                 line[key] = value
  515             elif value is None:
  516                 line[key] = ''
  517 
  518         # Signature check
  519         # TODO: use instead the verify_init
  520 
  521         res = self._verify(line, audit_line.signature)
  522         if res == 1:
  523             line['sig_check'] = "OK"
  524         else:
  525             line['sig_check'] = "FAIL"
  526 
  527 
  528         return line
  529 
  530     def searchQuery(self, param, AND=True, display_error=True, rp_dict=None):
  531         '''
  532         This function is used to search audit events.
  533 
  534         param:
  535             Search parameters can be passed.
  536 
  537         return:
  538             a result object which has to be converted with iter() to an
  539             iterator
  540         '''
  541 
  542         if rp_dict is None:
  543             rp_dict = {}
  544 
  545         if 'or' in param:
  546             if "true" == param['or'].lower():
  547                 AND = False
  548 
  549         # build the condition / WHERE clause
  550         condition = self._buildCondition(param, AND)
  551 
  552         order = AuditTable.id
  553         if rp_dict.get("sortname"):
  554             sortn = rp_dict.get('sortname').lower()
  555             if "serial" == sortn:
  556                 order = AuditTable.serial
  557             elif "number" == sortn:
  558                 order = AuditTable.id
  559             elif "user" == sortn:
  560                 order = AuditTable.user
  561             elif "action" == sortn:
  562                 order = AuditTable.action
  563             elif "action_detail" == sortn:
  564                 order = AuditTable.action_detail
  565             elif "realm" == sortn:
  566                 order = AuditTable.realm
  567             elif "date" == sortn:
  568                 order = AuditTable.timestamp
  569             elif "administrator" == sortn:
  570                 order = AuditTable.administrator
  571             elif "success" == sortn:
  572                 order = AuditTable.success
  573             elif "tokentype" == sortn:
  574                 order = AuditTable.tokentype
  575             elif "info" == sortn:
  576                 order = AuditTable.info
  577             elif "linotp_server" == sortn:
  578                 order = AuditTable.linotp_server
  579             elif "client" == sortn:
  580                 order = AuditTable.client
  581             elif "log_level" == sortn:
  582                 order = AuditTable.log_level
  583             elif "clearance_level" == sortn:
  584                 order = AuditTable.clearance_level
  585 
  586         # build the ordering
  587         order_dir = asc(order)
  588 
  589         if rp_dict.get("sortorder"):
  590             sorto = rp_dict.get('sortorder').lower()
  591             if "desc" == sorto:
  592                 order_dir = desc(order)
  593 
  594         if type(condition).__name__ == 'NoneType':
  595             audit_q = self.session.query(AuditTable)\
  596                 .order_by(order_dir)
  597         else:
  598             audit_q = self.session.query(AuditTable)\
  599                 .filter(condition)\
  600                 .order_by(order_dir)
  601 
  602         # FIXME? BUT THIS IS SO MUCH SLOWER!
  603         # FIXME: Here desc() ordering also does not work! :/
  604 
  605         if 'rp' in rp_dict or 'page' in rp_dict:
  606             # build the LIMIT and OFFSET
  607             page = 1
  608             offset = 0
  609             limit = 15
  610 
  611             if 'rp' in rp_dict:
  612                 limit = int(rp_dict.get('rp'))
  613 
  614             if 'page' in rp_dict:
  615                 page = int(rp_dict.get('page'))
  616 
  617             offset = limit * (page - 1)
  618 
  619             start = offset
  620             stop = offset + limit
  621             audit_q = audit_q.slice(start, stop)
  622 
  623         ## we drop here the ORM due to memory consumption
  624         ## and return a resultproxy for row iteration
  625         result = self.session.execute(audit_q.statement)
  626         return result
  627 
  628 
  629 
  630     def getTotal(self, param, AND=True, display_error=True):
  631         '''
  632         This method returns the total number of audit entries in
  633         the audit store
  634         '''
  635         condition = self._buildCondition(param, AND)
  636         if type(condition).__name__ == 'NoneType':
  637             c = self.session.query(AuditTable).count()
  638         else:
  639             c = self.session.query(AuditTable).filter(condition).count()
  640 
  641         return c
  642 
  643 def getAsString(data):
  644     '''
  645     We need to distinguish, if this is an entry after the adding the
  646     client entry or before. Otherwise the old signatures will break!
  647     '''
  648 
  649     s = ("number=%s, date=%s, action=%s, %s, serial=%s, %s, user=%s, %s,"
  650          " admin=%s, %s, %s, server=%s, %s, %s") % (
  651                 str(data.get('id')), str(data.get('timestamp')),
  652                 data.get('action'), str(data.get('success')),
  653                 data.get('serial'), data.get('tokentype'),
  654                 data.get('user'), data.get('realm'),
  655                 data.get('administrator'), data.get('action_detail'),
  656                 data.get('info'), data.get('linotp_server'),
  657                 data.get('log_level'), str(data.get('clearance_level')))
  658 
  659     if 'client' in data:
  660         s += ", client=%s" % data.get('client')
  661     return s
  662 
  663 
  664 ###eof#########################################################################