def set_application_setting(category, setting, value): try: # key is required, category is not if not setting: print "Info: attempt to set application setting - missing setting name [%s/%s]." % (category, setting) return False, "Setting is a required value." sSQL = "select setting_xml from application_settings where id = 1" db = catocommon.new_conn() sxml = db.select_col_noexcep(sSQL) # if there's no settings xml row, insert it if not sxml: sSQL = "delete from application_settings" db.exec_db_noexcep(sSQL) sSQL = "insert into application_settings (id, setting_xml) values (1, '<settings />')" db.exec_db_noexcep(sSQL) sxml = "<settings />" if sxml: xdoc = ET.fromstring(sxml) if xdoc is not None: # category is optional - if omitted, the new one goes in the root if category: xcat = xdoc.find(category) if xcat is None: xcat = ET.Element(category) xdoc.append(xcat) xnew = xcat.find(setting) if xnew is None: xnew = ET.Element(setting) xcat.append(xnew) xnew.text = ("" if value is None else value) else: xnew = xdoc.find(setting) if xnew is None: xnew = ET.Element(setting) xdoc.append(xnew) xnew.text = ("" if value is None else value) sSQL = "update application_settings set setting_xml='%s' where id=1" % ET.tostring(xdoc) db = catocommon.new_conn() if not db.exec_db_noexcep(sSQL): print "Info: attempt to set application setting [%s/%s] failed." % (category, setting) return False, db.error return True, "" except Exception, ex: raise Exception(ex)
def DBUpdate(self): try: db = catocommon.new_conn() # if the password has not changed leave it as is. sPasswordUpdate = "" if self.Password and self.Password != "~!@@!~": sPasswordUpdate = ", password = '******'" # same for privileged_password sPriviledgedPasswordUpdate = "" if self.PrivilegedPassword != "~!@@!~": # updated password # priviledged password can be blank, so if it is, set it to null if self.PrivilegedPassword: sPriviledgedPasswordUpdate = ", privileged_password = null" else: sPriviledgedPasswordUpdate = ", privileged_password = '******'" sSQL = "update asset_credential " \ "set username = '******'," \ "domain = '" + self.Domain + "'," \ "shared_or_local = '" + self.SharedOrLocal + "'," \ "shared_cred_desc = '" + catocommon.tick_slash(self.Description) + "'" \ + sPasswordUpdate + sPriviledgedPasswordUpdate + \ "where credential_id = '" + self.ID + "'" if not db.exec_db_noexcep(sSQL): return False, db.error # # add security log # uiCommon.WriteObjectPropertyChangeLog(uiGlobals.CatoObjectTypes.Asset, sAssetID, sAssetName.strip().replace("'", "''") + "Changed credential", sOriginalUserName, sCredUsername) return True, None except Exception, ex: raise ex
def DBCreateNew(self): db = catocommon.new_conn() if not self.Username and not self.PrivateKey: raise Exception("A Credential requires a User Name OR a Private Key.") sPriviledgedPasswordUpdate = catocommon.cato_encrypt(self.PrivilegedPassword) if self.PrivilegedPassword else None # if it's a local credential, the credential_name is the asset_id. # if it's shared, there will be a name. if self.SharedOrLocal == "1": # whack and add - easiest way to avoid conflicts sSQL = "delete from asset_credential where credential_name = %s and shared_or_local = '1'" db.exec_db(sSQL, (self.Name)) sSQL = """insert into asset_credential (credential_id, credential_name, username, password, domain, shared_or_local, shared_cred_desc, privileged_password, private_key) values (%s, %s, %s, %s, %s, %s, %s, %s, %s)""" params = (self.ID, self.Name, self.Username, catocommon.cato_encrypt(self.Password), self.Domain, self.SharedOrLocal, self.Description, sPriviledgedPasswordUpdate, catocommon.cato_encrypt(self.PrivateKey)) if not db.exec_db_noexcep(sSQL, params): if db.error == "key_violation": raise Exception("A Credential with that name already exists. Please select another name.") else: raise Exception(db.error) return True
def DBUpdate(self): db = catocommon.new_conn() # if the password has not changed leave it as is. sPasswordUpdate = "" if self.Password and self.Password != "~!@@!~": sPasswordUpdate = ", password = '******'" # same for privileged_password sPriviledgedPasswordUpdate = "" if self.PrivilegedPassword != "~!@@!~": # updated password # priviledged password can be blank, so if it is, set it to null if self.PrivilegedPassword: sPriviledgedPasswordUpdate = ", privileged_password = null" else: sPriviledgedPasswordUpdate = ", privileged_password = '******'" # same for private key, but a different rule since it's a textarea sPKUpdate = "" if self.PrivateKey != "********": sPKUpdate = ", private_key = '" + catocommon.cato_encrypt(self.PrivateKey) + "'" sSQL = """update asset_credential set credential_name = %s, username = %s, domain = %s, shared_or_local = %s, shared_cred_desc = %s {0} {1} {2} where credential_id = %s""".format(sPasswordUpdate, sPriviledgedPasswordUpdate, sPKUpdate) db.exec_db(sSQL, (self.Name, self.Username, self.Domain, self.SharedOrLocal, self.Description, self.ID)) db.close() return True
def DBCreateNew(self): try: db = catocommon.new_conn() sPriviledgedPasswordUpdate = "" if self.PrivilegedPassword: sPriviledgedPasswordUpdate = "NULL" else: sPriviledgedPasswordUpdate = "'" + catocommon.cato_encrypt(self.PrivilegedPassword) + "'" # if it's a local credential, the credential_name is the asset_id. # if it's shared, there will be a name. if self.SharedOrLocal == "1": # whack and add - easiest way to avoid conflicts sSQL = "delete from asset_credential where credential_name = '%s' and shared_or_local = '1'" % self.Name if not db.exec_db_noexcep(sSQL): return False, db.error sSQL = "insert into asset_credential " \ "(credential_id, credential_name, username, password, domain, shared_or_local, shared_cred_desc, privileged_password) " \ "values ('" + self.ID + "','" + self.Name + "','" + self.Username + "','" + catocommon.cato_encrypt(self.Password) + "','" \ + self.Domain + "','" + self.SharedOrLocal + "','" + self.Description + "'," + sPriviledgedPasswordUpdate + ")" if not db.exec_db_noexcep(sSQL): if db.error == "key_violation": return False, "A Credential with that name already exists. Please select another name." else: return False, db.error # add security log # need to move this function to catocommon # uiCommon.WriteObjectAddLog(uiGlobals.CatoObjectTypes.Credential, sCredentialID, sCredentialName, "") return True, None except Exception, ex: raise ex
def FromID(self, credential_id): """ Note the absence of password or privileged_password in this method. We don't store passwords, even encrypted, in the object. """ try: if not credential_id: raise Exception("Error building Credential object: ID is required."); sSQL = """select credential_id, credential_name, username, domain, shared_cred_desc, shared_or_local from asset_credential where credential_id = '%s'""" % credential_id db = catocommon.new_conn() dr = db.select_row_dict(sSQL) if dr is not None: self.ID = dr["credential_id"] self.Name = dr["credential_name"] self.Username = dr["username"] self.SharedOrLocal = dr["shared_or_local"] self.Domain = ("" if not dr["domain"] else dr["domain"]) self.Description = ("" if not dr["shared_cred_desc"] else dr["shared_cred_desc"]) else: raise Exception("Unable to build Credential object. Either no Credentials are defined, or no Credential by ID could be found.") except Exception, ex: raise Exception(ex)
def HasHistory(user_id): """Returns True if the user has historical data.""" try: db = catocommon.new_conn() # history in user_session. sql = "select count(*) from user_session where user_id = '" + user_id + "'" iResults = db.select_col_noexcep(sql) if db.error: raise Exception(db.error) if iResults: return True # history in user_security_log sql = "select count(*) from user_security_log where user_id = '" + user_id + "'" iResults = db.select_col_noexcep(sql) if db.error: raise Exception(db.error) if iResults: return True return False except Exception, ex: raise ex
def dbExists(self): try: # task_id is the PK, and task_name+version is a unique index. # so, we check the conflict property, and act accordingly sSQL = "select ecotemplate_id from ecotemplate" \ " where ecotemplate_name = '" + self.Name + "'" \ " or ecotemplate_id = '" + self.ID + "'" print sSQL db = catocommon.new_conn() dr = db.select_row_dict(sSQL) if db.error: print db.error raise Exception("Ecotemplate Object: Unable to check for existing Name or ID. " + db.error) if dr is not None: if dr["ecotemplate_id"]: # PAY ATTENTION! # if the template exists... it might have been by name, so... # we're setting the ids to the same as the database so it's more accurate. self.ID = dr["ecotemplate_id"] print dr["ecotemplate_id"] return True return False except Exception, ex: raise ex
def Delete(ids): """ Delete a list of clouds. """ db = catocommon.new_conn() # we have to check each cloud and see if it's used as the default... # if so, you can't delete it without first fixing the account. # ACCOUNTS REQUIRE A DEFAULT CLOUD existed = False for delete_id in ids: sql = "select count(*) from cloud_account where default_cloud_id = %s" % (delete_id) exists = db.select_col_noexcep(sql) if not exists: sql = "delete from clouds_keypair where cloud_id = %s" % (delete_id) db.tran_exec(sql) sql = "delete from clouds where cloud_id = %s" % (delete_id) db.tran_exec(sql) db.tran_commit() else: existed = True db.close() msg = "" if existed: msg = "Some of the selected Clouds were not deleted because they are referenced by a Cloud Account. Delete the Account first, or assign it a new Default Cloud." return True, msg
def __init__(self, sFilter="", sObjectID=""): try: sWhereString = "" if sFilter: aSearchTerms = sFilter.split() for term in aSearchTerms: if term: sWhereString += " and (t.tag_name like '%%" + term + "%%' " \ "or t.tag_desc like '%%" + term + "%%') " # if an object id arg is passed, we explicitly limit to that object if sObjectID: sWhereString += " and ot.object_id = '%s'" % sObjectID sSQL = """select t.tag_name, t.tag_desc, count(ot.tag_name) as in_use from tags t left outer join object_tags ot on t.tag_name = ot.tag_name where (1=1) %s group by t.tag_name, t.tag_desc order by t.tag_name""" % sWhereString db = catocommon.new_conn() self.rows = db.select_all_dict(sSQL) except Exception, ex: raise Exception(ex)
def FromID(self, sEcosystemID): try: sSQL = "select e.ecosystem_id, e.ecosystem_name, e.ecosystem_desc, e.storm_file, e.storm_status," \ " e.account_id, e.ecotemplate_id, et.ecotemplate_name, e.created_dt, e.last_update_dt," \ " (select count(*) from ecosystem_object where ecosystem_id = e.ecosystem_id) as num_objects" \ " from ecosystem e" \ " join ecotemplate et on e.ecotemplate_id = et.ecotemplate_id" \ " where e.ecosystem_id = '" + sEcosystemID + "'" db = catocommon.new_conn() dr = db.select_row_dict(sSQL) if db.error: raise Exception("Ecosystem Object: Unable to get Ecosystem from database. " + db.error) if dr: self.ID = dr["ecosystem_id"] self.Name = dr["ecosystem_name"] self.AccountID = dr["account_id"] self.EcotemplateID = dr["ecotemplate_id"] self.EcotemplateName = dr["ecotemplate_name"] self.Description = (dr["ecosystem_desc"] if dr["ecosystem_desc"] else "") self.StormFile = (dr["storm_file"] if dr["storm_file"] else "") self.StormStatus = (dr["storm_status"] if dr["storm_status"] else "") self.CreatedDate = (str(dr["created_dt"]) if dr["created_dt"] else "") self.LastUpdate = (str(dr["last_update_dt"]) if dr["storm_status"] else "") self.NumObjects = str(dr["num_objects"]) else: raise Exception("Error building Ecosystem object: " + db.error) except Exception, ex: raise ex
def HasHistory(asset_id): """Returns True if the asset has historical data.""" db = catocommon.new_conn() sql = "select count(*) from task_instance where asset_id = %s" iResults = db.select_col(sql, (asset_id)) db.close() return catocommon.is_true(iResults)
def DBSave(self): try: """ The messenger has some special considerations when updating! ($#d@x!& ^^^ can't even use that, it was messing up the web.py templator - do something else """ sSQL = """update messenger_settings set mode_off_on='{0}', loop_delay_sec={1}, retry_delay_min={2}, retry_max_attempts={3}, smtp_server_addr='{4}', smtp_server_user='******', smtp_server_port={6}, from_email='{7}', from_name='{8}', admin_email='{9}'""" # only update password if it has been changed. sPasswordFiller = "~!@@!~" if self.SMTPUserPassword != sPasswordFiller: sSQL += ",smtp_server_password='******'"; sSQL = sSQL.format(("1" if catocommon.is_true(self.Enabled) else "0"), str(self.PollLoop), str(self.RetryDelay), str(self.RetryMaxAttempts), self.SMTPServerAddress, self.SMTPUserAccount, str(self.SMTPServerPort), self.FromEmail, self.FromName, self.AdminEmail, catocommon.cato_encrypt(self.SMTPUserPassword)) db = catocommon.new_conn() if not db.exec_db_noexcep(sSQL): return False, db.error return True, "" except Exception, ex: print ex.__str__() raise Exception(ex)
def __init__(self, sFilter=""): db = catocommon.new_conn() sWhereString = "" if sFilter: aSearchTerms = sFilter.split() for term in aSearchTerms: if term: sWhereString += """ and (a.asset_name like '%%{0}%%' or a.port like '%%{0}%%' or a.address like '%%{0}%%' or a.db_name like '%%{0}%%' or a.asset_status like '%%{0}%%' or ac.username like '%%{0}%%' or ot.tag_name like '%%{0}%%')""".format(term) sSQL = """select a.asset_id as ID, a.asset_name as Name, a.asset_status as Status, a.address as Address, case when ac.shared_or_local = 1 then 'Local' else 'Shared' end as SharedOrLocal, case when ac.domain <> '' then concat(ac.domain, cast(char(92) as char), ac.username) else ac.username end as Credentials, coalesce(group_concat(ot.tag_name order by ot.tag_name separator ','), '') as Tags from asset a left outer join object_tags ot on a.asset_id = ot.object_id left outer join asset_credential ac on ac.credential_id = a.credential_id where 1=1 %s group by a.asset_id order by a.asset_name""" % sWhereString self.rows = db.select_all_dict(sSQL) db.close()
def __init__(self, sFilter="", sProvider=""): try: sWhereString = "" if sFilter: aSearchTerms = sFilter.split() for term in aSearchTerms: if term: sWhereString += " and (account_name like '%%" + term + "%%' " \ "or account_number like '%%" + term + "%%' " \ "or provider like '%%" + term + "%%' " \ "or login_id like '%%" + term + "%%') " # if a sProvider arg is passed, we explicitly limit to this provider if sProvider: sWhereString += " and provider = '%s'" % sProvider sSQL = "select account_id, account_name, account_number, provider, login_id, auto_manage_security," \ " case is_default when 1 then 'Yes' else 'No' end as is_default," \ " (select count(*) from ecosystem where account_id = cloud_account.account_id) as has_ecosystems" \ " from cloud_account" \ " where 1=1 " + sWhereString + " order by is_default desc, account_name" db = catocommon.new_conn() self.rows = db.select_all_dict(sSQL) except Exception, ex: raise Exception(ex)
def DBUpdate(self): try: db = catocommon.new_conn() #of course we do nothing if this cloud was hardcoded in the xml #just return success, which should never happen since the user can't get to edit a hardcoded Cloud anyway. if not self.IsUserDefined: return True #what's the original name? sSQL = "select cloud_name from clouds where cloud_id = '" + self.ID + "'" sOriginalName = db.select_col_noexcep(sSQL) if not sOriginalName: if db.error: raise Exception("Error getting original cloud name:" + db.error) sSQL = "update clouds set" + " cloud_name = '" + self.Name + "'," \ " provider = '" + self.Provider.Name + "'," \ " api_protocol = '" + self.APIProtocol + "'," \ " api_url = '" + self.APIUrl + "'" \ " where cloud_id = '" + self.ID + "'" if not db.exec_db_noexcep(sSQL): if db.error == "key_violation": return False, "A Cloud with that name already exists. Please select another name." else: return False, db.error return True, None except Exception, ex: raise Exception(ex)
def ValidatePassword(uid, pwd): """ Checks a password against the system settings for length, complexity, etc. """ s_set = settings.settings.security() # is this password long enough? too long? if len(pwd) < s_set.PassMinLength or len(pwd) > s_set.PassMaxLength: return False, "Password must be between %d and %d characters in length." % (s_set.PassMinLength, s_set.PassMaxLength) # is it complex (if required to be)? if s_set.PassComplexity: if not re.search(r"[A-Z~!@#$%^&?+=]", pwd): return False, "Password must contain at least one capital letter and at least one special character. (~!@#$%^&?+=)" # has it been used lately (if that's required)? # in which case we need to save the old one to history, and delete # any rows over this counter. # this test isn't necessary if no user_id was provided if uid and s_set.PasswordHistory: db = catocommon.new_conn() sql = """select password from user_password_history where user_id = '%s' order by change_time desc limit %d""" % (uid, s_set.PasswordHistory) previous_pwds = db.select_csv(sql, False) if previous_pwds: if catocommon.cato_encrypt(pwd) in previous_pwds: return False, "That password cannot yet be reused." db.close() return True, None
def set_application_section(section, value): doc = {} sql = "select settings_json from application_settings where id = 1" db = catocommon.new_conn() sjson = db.select_col(sql) db.close() if sjson: doc = json.loads(sjson) doc[section] = json.loads(value) sql = "update application_settings set settings_json=%s where id = 1" db = catocommon.new_conn() if not db.exec_db_noexcep(sql, catocommon.ObjectOutput.AsJSON(doc)): raise Exception("Info: attempt to set application section [%s] failed." % (section))
def DBUpdate(self): db = catocommon.new_conn() # of course we do nothing if this cloud was hardcoded in the xml # just return success, which should never happen since the user can't get to edit a hardcoded Cloud anyway. if not self.IsUserDefined: return True # what's the original name? sSQL = "select cloud_name from clouds where cloud_id = '%s'" % self.ID sOriginalName = db.select_col_noexcep(sSQL) if not sOriginalName: if db.error: raise Exception("Error getting original cloud name:" + db.error) sDefaultAccountID = "null" if self.DefaultAccount: sDefaultAccountID = "'%s'" % self.DefaultAccount.ID sSQL = """update clouds set cloud_name = '%s', provider = '%s', api_protocol = '%s', api_url = '%s', default_account_id = %s where cloud_id = '%s'""" % (self.Name, self.Provider.Name, self.APIProtocol, self.APIUrl, sDefaultAccountID, self.ID) if not db.exec_db_noexcep(sSQL): if db.error == "key_violation": raise InfoException("A Cloud with that name already exists. Please select another name.") else: raise Exception(db.error) db.close() return True
def __init__(self, sFilter="", sProvider=""): db = catocommon.new_conn() sWhereString = "" if sFilter: aSearchTerms = sFilter.split() for term in aSearchTerms: if term: sWhereString += " and (ca.account_name like '%%" + term + "%%' " \ "or ca.account_number like '%%" + term + "%%' " \ "or ca.provider like '%%" + term + "%%' " \ "or c.cloud_name like '%%" + term + "%%' " \ "or ca.login_id like '%%" + term + "%%') " # if a sProvider arg is passed, we explicitly limit to this provider if sProvider: sWhereString += " and ca.provider = '%s'" % sProvider sSQL = """select ca.account_id as ID, ca.account_name as Name, ca.account_number as AccountNumber, ca.provider as Provider, ca.login_id as LoginID, c.cloud_name as DefaultCloud, case is_default when 1 then 'Yes' else 'No' end as IsDefault from cloud_account ca left outer join clouds c on ca.default_cloud_id = c.cloud_id where 1=1 %s order by ca.is_default desc, ca.account_name""" % sWhereString rows = db.select_all_dict(sSQL) self.rows = rows if rows else {} db.close()
def GetDefaultAccount(self): db = catocommon.new_conn() sSQL = """select default_account_id from clouds where cloud_id = '%s'""" % self.ID row = db.select_row_dict(sSQL) if row and row["default_account_id"]: # note: since there's no RI in the db on this id (it would create a circular dependency)... # we'll "fix" bad data here by updating the default_account if it doesn't exist. try: ca = CloudAccount() ca.FromID(row["default_account_id"]) if ca.ID: # when a default account is added to a cloud # we DO NOT include the default cloud of the account # or we'll have recursion del ca.DefaultCloud self.DefaultAccount = ca db.close() return ca except Exception: sSQL = """update clouds set default_account_id = null where cloud_id = %s""" row = db.exec_db_noexcep(sSQL, (self.ID)) db.close() return None
def __init__(self, sFilter=""): db = catocommon.new_conn() sWhereString = "" if sFilter: aSearchTerms = sFilter.split() for term in aSearchTerms: if term: sWhereString += " and (c.cloud_name like '%%" + term + "%%' " \ "or ca.account_name like '%%" + term + "%%' " \ "or c.provider like '%%" + term + "%%' " \ "or c.api_url like '%%" + term + "%%') " sSQL = """select c.cloud_id as ID, c.cloud_name as Name, c.provider as Provider, c.api_url as APIUrl, c.api_protocol as APIProtocol, ca.account_name as DefaultAccount from clouds c left outer join cloud_account ca on c.default_account_id = ca.account_id where 1=1 %s order by c.provider, c.cloud_name""" % sWhereString rows = db.select_all_dict(sSQL) self.rows = rows if rows else {} db.close()
def IsObjectAllowed(object_id, object_type): # if permissions checking is turned off, everything is allowed if catoconfig.CONFIG["ui_permissions"] == "false": return True # given a task id, we need to find the original task id, # then check if the user can see it based on tags if GetSessionUserRole() == "Administrator": return True if not object_id or not object_type: log("Invalid or missing Object ID or Object Type.") return False sql = """select 1 from object_tags otu join object_tags oto on otu.tag_name = oto.tag_name where (otu.object_type = 1) and otu.object_id = %s and oto.object_type = %s and oto.object_id = %s""" uid = GetSessionUserID() db = catocommon.new_conn() result = db.select_col_noexcep(sql, (uid, object_type, object_id)) db.close() return catocommon.is_true(result)
def SetNodeValueinXMLColumn(sTable, sXMLColumn, sWhereClause, sNodeToSet, sValue): db = catocommon.new_conn() log("Setting node [%s] to [%s] in [%s.%s where %s]." % (sNodeToSet, sValue, sTable, sXMLColumn, sWhereClause), 4) sSQL = "select " + sXMLColumn + " from " + sTable + " where " + sWhereClause sXML = db.select_col_noexcep(sSQL) if not sXML: log("Unable to get xml." + db.error) else: # parse the doc from the table xd = catocommon.ET.fromstring(sXML) if xd is None: log("Error: Unable to parse XML.") # get the specified node from the doc, IF IT'S NOT THE ROOT if xd.tag == sNodeToSet: xNodeToSet = xd else: xNodeToSet = xd.find(sNodeToSet) if xNodeToSet is not None: xNodeToSet.text = sValue # then send the whole doc back to the database sSQL = "update " + sTable + " set " + sXMLColumn + " = %s where " + sWhereClause if not db.exec_db_noexcep(sSQL, (catocommon.ET.tostring(xd))): log("Unable to update XML Column [" + sXMLColumn + "] on [" + sTable + "]." + db.error) else: log("Unable to update XML Column ... [" + sNodeToSet + "] not found.") return
def __init__(self, cloud_name): self.cloud_id = None self.cloud_name = cloud_name self.url = None self.protocol = None self.region = None self.provider = None self.default_account = None self.path = "/" self.conn = None self.api_version = None db = catocommon.new_conn() sql = """select cloud_id, provider, api_url, api_protocol, default_account_id, region from clouds where cloud_name = %s""" row = db.select_row(sql, (cloud_name)) db.close() if not row: msg = "The Cloud endpoint name %s is not configured in the database" % cloud_name raise Exception(msg) self.cloud_id, self.provider, self.url, self.protocol, self.default_account, self.region = row[:] self.cloud_name = cloud_name if self.provider == "Amazon AWS": self.url = None elif self.provider == "Eucalyptus": self.path = "/services/Eucalyptus/" self.api_version = "2010-08-31"
def __init__(self): try: sSQL = "select pass_max_age, pass_max_attempts, pass_max_length, pass_min_length, pass_age_warn_days," \ " auto_lock_reset, login_message, auth_error_message, pass_complexity, pass_require_initial_change, pass_history," \ " page_view_logging, report_view_logging, allow_login, new_user_email_message" \ " from login_security_settings" \ " where id = 1" db = catocommon.new_conn() row = db.select_row_dict(sSQL) if row: self.PassMaxAge = row["pass_max_age"] self.PassMaxAttempts = row["pass_max_attempts"] self.PassMaxLength = row["pass_max_length"] self.PassMinLength = row["pass_min_length"] self.PassComplexity = catocommon.is_true(row["pass_complexity"]) self.PassAgeWarn = row["pass_age_warn_days"] self.PasswordHistory = row["pass_history"] self.PassRequireInitialChange = catocommon.is_true(row["pass_require_initial_change"]) self.AutoLockReset = row["auto_lock_reset"] self.LoginMessage = row["login_message"] self.AuthErrorMessage = row["auth_error_message"] self.NewUserMessage = row["new_user_email_message"] self.PageViewLogging = catocommon.is_true(row["page_view_logging"]) self.ReportViewLogging = catocommon.is_true(row["report_view_logging"]) self.AllowLogin = catocommon.is_true(row["allow_login"]) except Exception, ex: raise Exception(ex)
def __init__(self, sFilter=""): db = catocommon.new_conn() sWhereString = "" if sFilter: aSearchTerms = sFilter.split() for term in aSearchTerms: if term: sWhereString += """ and (u.full_name like '%%{0}%%' or u.user_role like '%%{0}%%' or u.username like '%%{0}%%' or u.status like '%%{0}%%' or u.last_login_dt like '%%{0}%%' or ot.tag_name like '%%{0}%%')""".format(term) sSQL = """select u.user_id, u.username, u.full_name, u.last_login_dt, u.email, case when u.status = '1' then 'Enabled' when u.status = '-1' then 'Locked' when u.status = '0' then 'Disabled' end as status, u.authentication_type, u.user_role as role, coalesce(group_concat(ot.tag_name order by ot.tag_name separator ','), '') as Tags from users u left outer join object_tags ot on u.user_id = ot.object_id where u.status <> 86 %s group by u.user_id order by u.full_name""" % sWhereString self.rows = db.select_all_dict(sSQL) db.close()
def __init__(self, sFilter=""): try: sWhereString = "" if sFilter: aSearchTerms = sFilter.split() for term in aSearchTerms: if term: sWhereString += " and (a.asset_name like '%%" + term + "%%' " \ "or a.port like '%%" + term + "%%' " \ "or a.address like '%%" + term + "%%' " \ "or a.db_name like '%%" + term + "%%' " \ "or a.asset_status like '%%" + term + "%%' " \ "or ac.username like '%%" + term + "%%') " sSQL = "select a.asset_id, a.asset_name, a.asset_status, a.address," \ " case when ac.shared_or_local = 1 then 'Local - ' else 'Shared - ' end as shared_or_local," \ " case when ac.domain <> '' then concat(ac.domain, cast(char(92) as char), ac.username) else ac.username end as credentials" \ " from asset a" \ " left outer join asset_credential ac on ac.credential_id = a.credential_id" \ " where 1=1 " + sWhereString + " order by a.asset_name" db = catocommon.new_conn() self.rows = db.select_all_dict(sSQL) except Exception, ex: raise Exception(ex)
def __init__(self, object_id): try: db = catocommon.new_conn() self.object_id = object_id if object_id: sSQL = "select registry_xml from object_registry where object_id = '%s'" % object_id self.xml_text = db.select_col_noexcep(sSQL) if db.error: raise Exception("Error: Could not look up Registry XML." + db.error) if self.xml_text: self.xml_tree = ET.fromstring(self.xml_text) else: # if the object_id is a guid, it's an object registry:... add one if it's not there. if uiCommon.IsGUID(object_id): sSQL = "insert into object_registry values ('%s', '<registry />')" % object_id if not db.exec_db_noexcep(sSQL): raise Exception("Error: Could not create Registry." + db.error) self.xml_tree = ET.fromstring("<registry />") print self.xml_text except Exception, ex: raise Exception(ex)
def DBSave(self): try: sSQL = "update login_security_settings set" \ " pass_max_age='" + self.PassMaxAge + "'," \ " pass_max_attempts='" + self.PassMaxAttempts + "'," \ " pass_max_length='" + self.PassMaxLength + "'," \ " pass_min_length='" + self.PassMinLength + "'," \ " pass_complexity='" + ("1" if catocommon.is_true(self.PassComplexity) else "0") + "'," \ " pass_age_warn_days='" + self.PassAgeWarn + "'," \ " pass_history = '" + self.PasswordHistory + "'," \ " pass_require_initial_change='" + ("1" if catocommon.is_true(self.PassRequireInitialChange) else "0") + "'," \ " auto_lock_reset='" + ("1" if catocommon.is_true(self.AutoLockReset) else "0") + "'," \ " login_message='" + self.LoginMessage.replace("'", "''") + "'," \ " auth_error_message='" + self.AuthErrorMessage.replace("'", "''").replace(";", "") + "'," \ " new_user_email_message='" + self.NewUserMessage.replace("'", "''").replace(";", "") + "'," \ " page_view_logging='" + ("1" if catocommon.is_true(self.PageViewLogging) else "0") + "'," \ " report_view_logging='" + ("1" if catocommon.is_true(self.ReportViewLogging) else "0") + "'," \ " allow_login='******'" \ " where id = 1" db = catocommon.new_conn() if not db.exec_db_noexcep(sSQL): return False, db.error return True, "" except Exception, ex: raise Exception(ex) print ex
def DBUpdate(self): db = catocommon.new_conn() # only update the passwword if it has changed sNewPassword = "" if self.LoginPassword != "($%#d@x!&": sNewPassword = (", login_password = '******'" if self.LoginPassword else "") sSQL = "update cloud_account set" \ " account_name = '" + self.Name + "'," \ " account_number = '" + self.AccountNumber + "'," \ " provider = '" + self.Provider.Name + "'," \ " default_cloud_id = '" + self.DefaultCloud.ID + "'," \ " is_default = '" + ("1" if self.IsDefault else "0") + "'," \ " auto_manage_security = 0," \ " login_id = '" + self.LoginID + "'" + \ sNewPassword + \ " where account_id = '" + self.ID + "'" if not db.exec_db_noexcep(sSQL): if db.error == "key_violation": raise InfoException("A Cloud Account with that name already exists. Please select another name.") else: raise Exception(db.error) # if "default" was selected, unset all the others if self.IsDefault: sSQL = "update cloud_account set is_default = 0 where account_id <> %s" # not worth failing... we'll just end up with two defaults. db.exec_db_noexcep(sSQL, (self.ID)) db.close() return True
def DBUpdate(self): db = catocommon.new_conn() # do the description no matter what just to be quick sql = "update tags set tag_desc = %s where tag_name = %s" db.exec_db(sql, (self.Description, self.Name)) db.close() return True
def AddPWToHistory(self, pw): try: db = catocommon.new_conn() sql = "insert user_password_history (user_id, change_time, password) values ('%s', now(), '%s')" % (self.ID, pw) if not db.exec_db_noexcep(sql): print db.error except Exception, ex: return False, ex.__str__()
def DBCreateNew(sUsername, sFullName, sAuthType, sPassword, sGeneratePW, sForcePasswordChange, sUserRole, sEmail, sStatus, sGroupArray): try: # TODO: All the password testing, etc. db = catocommon.new_conn() sNewID = catocommon.new_guid() if sAuthType == "local": if sPassword: if sPassword: result, msg = User.ValidatePassword(None, sPassword) if result: sEncPW = "'%s'" % catocommon.cato_encrypt(sPassword) else: return None, msg elif catocommon.is_true(sGeneratePW): sEncPW = "'%s'" % catocommon.cato_encrypt(catocommon.generate_password()) else: return None, "A password must be provided, or check the box to generate one." elif sAuthType == "ldap": sEncPW = " null" sSQL = "insert into users" \ " (user_id, username, full_name, authentication_type, force_change, email, status, user_role, user_password)" \ " values ('" + sNewID + "'," \ "'" + sUsername + "'," \ "'" + sFullName + "'," \ "'" + sAuthType + "'," \ "'" + sForcePasswordChange + "'," \ "'" + (sEmail if sEmail else "") + "'," \ "'" + sStatus + "'," \ "'" + sUserRole + "'," \ "" + sEncPW + "" \ ")" if not db.tran_exec_noexcep(sSQL): if db.error == "key_violation": return None, "A User with that Login ID already exists. Please select another." else: return None, db.error db.tran_commit() if sGroupArray: # if we can't create groups we don't actually fail... for tag in sGroupArray: sql = "insert object_tags (object_type, object_id, tag_name) values (1, '%s','%s')" % (sNewID, tag) if not db.exec_db_noexcep(sql): print "Error creating Groups for new user %s." % sNewID # now it's inserted... lets get it back from the db as a complete object for confirmation. u = User() u.FromID(sNewID) u.AddPWToHistory(sEncPW) return u, None except Exception, ex: raise ex
def DBCreateNew(sAccountName, sAccountNumber, sProvider, sLoginID, sLoginPassword, sIsDefault): try: db = catocommon.new_conn() # if there are no rows yet, make this one the default even if the box isn't checked. if sIsDefault == "0": iExists = -1 sSQL = "select count(*) as cnt from cloud_account" iExists = db.select_col_noexcep(sSQL) if iExists == None: if db.error: db.tran_rollback() return None, "Unable to count Cloud Accounts: " + db.error if iExists == 0: sIsDefault = "1" sNewID = catocommon.new_guid() sPW = (catocommon.cato_encrypt(sLoginPassword) if sLoginPassword else "") sSQL = "insert into cloud_account" \ " (account_id, account_name, account_number, provider, is_default, login_id, login_password, auto_manage_security)" \ " values ('" + sNewID + "'," \ "'" + sAccountName + "'," \ "'" + sAccountNumber + "'," \ "'" + sProvider + "'," \ "'" + sIsDefault + "'," \ "'" + sLoginID + "'," \ "'" + sPW + "'," \ "0)" if not db.tran_exec_noexcep(sSQL): if db.error == "key_violation": sErr = "A Cloud Account with that name already exists. Please select another name." return None, sErr else: return None, db.error # if "default" was selected, unset all the others if sIsDefault == "1": sSQL = "update cloud_account set is_default = 0 where account_id <> '" + sNewID + "'" if not db.tran_exec_noexcep(sSQL): raise Exception(db.error) db.tran_commit() # now it's inserted... lets get it back from the db as a complete object for confirmation. ca = CloudAccount() ca.FromID(sNewID) # yay! return ca, None except Exception, ex: raise ex
def SetNodeAttributeinXMLColumn(sTable, sXMLColumn, sWhereClause, sNodeToSet, sAttribute, sValue): # THIS ONE WILL do adds if the attribute doesn't exist, or update it if it does. try: db = catocommon.new_conn() log( "Setting [%s] attribute [%s] to [%s] in [%s.%s where %s]" % (sNodeToSet, sAttribute, sValue, sTable, sXMLColumn, sWhereClause), 4) sXML = "" sSQL = "select " + sXMLColumn + " from " + sTable + " where " + sWhereClause sXML = db.select_col_noexcep(sSQL) if db.error: log("Unable to get xml." + db.error) return "" if sXML: # parse the doc from the table xd = ET.fromstring(sXML) if xd is None: log("Unable to parse xml." + db.error) return "" # get the specified node from the doc # here's the rub - the request might be or the "root" node, # which "find" will not, er ... find. # so let's first check if the root node is the name we want. xNodeToSet = None if xd.tag == sNodeToSet: xNodeToSet = xd else: xNodeToSet = xd.find(sNodeToSet) if xNodeToSet is None: # do nothing if we didn't find the node return "" else: # set it xNodeToSet.attrib[sAttribute] = sValue # then send the whole doc back to the database sSQL = "update " + sTable + " set " + sXMLColumn + " = '" + catocommon.tick_slash(ET.tostring(xd)) + "'" \ " where " + sWhereClause if not db.exec_db_noexcep(sSQL): log("Unable to update XML Column [" + sXMLColumn + "] on [" + sTable + "]." + db.error) return "" except Exception: log_nouser(traceback.format_exc(), 0) finally: if db.conn.socket: db.close()
def POST(self, method): try: self.db = catocommon.new_conn() methodToCall = getattr(self, method) result = methodToCall() return result except Exception as ex: raise ex finally: if self.db.conn.socket: self.db.close()
def AddSecurityLog(LogType, Action, ObjectType, ObjectID, LogMessage): sTrimmedLog = catocommon.tick_slash(LogMessage).strip() if sTrimmedLog: if len(sTrimmedLog) > 7999: sTrimmedLog = sTrimmedLog[:7998] sSQL = """insert into user_security_log (log_type, action, user_id, log_dt, object_type, object_id, log_msg) values ('%s', '%s', '%s', now(), %d, '%s', '%s')""" % ( LogType, Action, GetSessionUserID(), ObjectType, ObjectID, sTrimmedLog) db = catocommon.new_conn() if not db.exec_db_noexcep(sSQL): log_nouser(db.error, 0) db.close()
def DBDelete(self): try: db = catocommon.new_conn() sSQL = "delete from asset_credential where credential_id = '%s'" % self.ID if not db.exec_db_noexcep(sSQL): return False, db.error # add security log #uiCommon.WriteObjectDeleteLog(uiGlobals.CatoObjectTypes.Asset, sAssetID, sAssetName.strip().replace("'", "''"), "Credential deleted" + sOriginalCredentialID + " " + sOriginalUserName) return True, None except Exception, ex: raise ex
def __init__(self): try: sSQL = "select mode_off_on, loop_delay_sec, max_processes" \ " from poller_settings" \ " where id = 1" db = catocommon.new_conn() row = db.select_row_dict(sSQL) if row: self.Enabled = catocommon.is_true(row["mode_off_on"]) self.LoopDelay = row["loop_delay_sec"] self.MaxProcesses = row["max_processes"] except Exception, ex: raise Exception(ex)
def DBSave(self): try: sSQL = "update poller_settings set" \ " mode_off_on='" + ("1" if catocommon.is_true(self.Enabled) else "0") + "'," \ " loop_delay_sec='" + str(self.LoopDelay) + "'," \ " max_processes='" + str(self.MaxProcesses) + "'" db = catocommon.new_conn() if not db.exec_db_noexcep(sSQL): return False, db.error return True, "" except Exception, ex: raise Exception(ex)
def RemoveNodeFromXMLColumn(sTable, sXMLColumn, sWhereClause, sNodeToRemove): try: db = catocommon.new_conn() log( "Removing node [%s] from [%s.%s where %s]." % (sNodeToRemove, sTable, sXMLColumn, sWhereClause), 4) sSQL = "select " + sXMLColumn + " from " + sTable + " where " + sWhereClause sXML = db.select_col_noexcep(sSQL) if not sXML: log("Unable to get xml." + db.error) else: # parse the doc from the table xd = ET.fromstring(sXML) if xd is None: log("Error: Unable to parse XML.") # get the specified node from the doc xNodeToWhack = xd.find(sNodeToRemove) if xNodeToWhack is None: log( "INFO: attempt to remove [%s] - the element was not found." % sNodeToRemove, 4) # no worries... what you want to delete doesn't exist? perfect! return # OK, here's the deal... # we have found the node we want to delete, but we found it using an xpath, # ElementTree doesn't support deleting by xpath. # so, we'll use a parent map to find the immediate parent of the node we found, # and on the parent we can call ".remove" parent_map = dict((c, p) for p in xd.getiterator() for c in p) xParentOfNodeToWhack = parent_map[xNodeToWhack] # whack it if xParentOfNodeToWhack is not None: xParentOfNodeToWhack.remove(xNodeToWhack) sSQL = "update " + sTable + " set " + sXMLColumn + " = '" + catocommon.tick_slash(ET.tostring(xd)) + "'" \ " where " + sWhereClause if not db.exec_db_noexcep(sSQL): log("Unable to update XML Column [" + sXMLColumn + "] on [" + sTable + "]." + db.error) return except Exception: log_nouser(traceback.format_exc(), 0) finally: if db.conn.socket: db.close()
def get_application_setting(xpath): try: sSQL = "select setting_xml from application_settings where id = 1" db = catocommon.new_conn() sxml = db.select_col_noexcep(sSQL) if sxml: xdoc = ET.fromstring(sxml) if xdoc is not None: return xdoc.findtext(xpath, "") print "Info: attempt to find application settings [%s] failed." % xpath return "" except Exception, ex: raise Exception(ex)
def HasHistory(asset_id): """Returns True if the asset has historical data.""" try: db = catocommon.new_conn() # history in user_session. sql = "select count(*) from tv_task_instance where asset_id = '" + asset_id + "'" iResults = db.select_col_noexcep(sql) if db.error: raise Exception(db.error) if iResults: return True return False except Exception, ex: raise ex
def __init__(self): try: sSQL = "select mode_off_on, loop_delay_sec, schedule_min_depth, schedule_max_days, clean_app_registry" \ " from scheduler_settings" \ " where id = 1" db = catocommon.new_conn() row = db.select_row_dict(sSQL) if row: self.Enabled = catocommon.is_true(row["mode_off_on"]) self.LoopDelay = row["loop_delay_sec"] self.ScheduleMinDepth = row["schedule_min_depth"] self.ScheduleMaxDays = row["schedule_max_days"] self.CleanAppRegistry = row["clean_app_registry"] except Exception, ex: raise Exception(ex)
def DBSave(self): try: sSQL = "update scheduler_settings set" \ " mode_off_on='" + ("1" if catocommon.is_true(self.Enabled) else "0") + "'," \ " loop_delay_sec='" + str(self.LoopDelay) + "'," \ " schedule_min_depth='" + str(self.ScheduleMinDepth) +"'," \ " schedule_max_days='" + str(self.ScheduleMaxDays) +"'," \ " clean_app_registry = '" + str(self.CleanAppRegistry) + "'" db = catocommon.new_conn() if not db.exec_db_noexcep(sSQL): return False, db.error return True, "" except Exception, ex: raise Exception(ex)
def FromID(self, sAccountID): try: if not sAccountID: raise Exception( "Error building Cloud Account object: Cloud Account ID is required." ) sSQL = "select account_name, account_number, provider, login_id, login_password, is_default" \ " from cloud_account" \ " where account_id = '" + sAccountID + "'" db = catocommon.new_conn() dr = db.select_row_dict(sSQL) if dr is not None: self.ID = sAccountID self.Name = dr["account_name"] self.AccountNumber = ("" if not dr["account_number"] else dr["account_number"]) self.LoginID = ("" if not dr["login_id"] else dr["login_id"]) self.LoginPassword = ("" if not dr["login_password"] else catocommon.cato_decrypt( dr["login_password"])) self.IsDefault = (True if dr["is_default"] == 1 else False) # find a provider object cp = providers.CloudProviders() if not cp: raise Exception( "Error building Cloud Account object: Unable to get CloudProviders." ) return #check the CloudProvider class first ... it *should be there unless something is wrong. if cp.has_key(dr["provider"]): self.Provider = cp[dr["provider"]] else: raise Exception( "Provider [" + dr["provider"] + "] does not exist in the cloud_providers session xml.") else: raise Exception( "Unable to build Cloud Account object. Either no Cloud Accounts are defined, or no Account with ID [" + sAccountID + "] could be found.") except Exception, ex: raise Exception(ex)
def PopulateAsset(self, asset_id="", asset_name=""): """ Note the absence of password or privileged_password in this method. We don't store passwords, even encrypted, in the object. """ try: if not asset_id and not asset_name: raise Exception("Error building Asset object: ID or Name is required."); sSQL = """select a.asset_id, a.asset_name, a.asset_status, a.port, a.db_name, a.conn_string, a.address, ac.username, ac.domain, ac.shared_cred_desc, ac.credential_name, a.credential_id, case when ac.shared_or_local = '0' then 'Shared' else 'Local' end as shared_or_local from asset a left outer join asset_credential ac on ac.credential_id = a.credential_id """ if asset_id: sSQL += " where a.asset_id = '%s'""" % asset_id elif asset_name: sSQL += " where a.asset_name = '%s'""" % asset_name db = catocommon.new_conn() dr = db.select_row_dict(sSQL) if dr is not None: self.ID = dr["asset_id"] self.Name = dr["asset_name"] self.Status = dr["asset_status"] self.Port = ("" if not dr["port"] else str(dr["port"])) self.DBName = ("" if not dr["db_name"] else dr["db_name"]) self.Address = ("" if not dr["address"] else dr["address"]) self.UserName = ("" if not dr["username"] else dr["username"]) self.SharedOrLocal = ("" if not dr["shared_or_local"] else dr["shared_or_local"]) self.CredentialID = ("" if not dr["credential_id"] else dr["credential_id"]) self.Domain = ("" if not dr["domain"] else dr["domain"]) self.SharedCredName = ("" if not dr["credential_name"] else dr["credential_name"]) self.SharedCredDesc = ("" if not dr["shared_cred_desc"] else dr["shared_cred_desc"]) self.ConnString = ("" if not dr["conn_string"] else dr["conn_string"]) else: raise Exception("Unable to build Asset object. Either no Assets are defined, or no Asset by ID/Name could be found.") except Exception, ex: raise Exception(ex)