public void ExecuteImport(long id) { using (var ctx = new DbMonitorEntities()) { var me = ctx.MirrorExport.Find(id); var dic = ctx.Dictionary.Where(d => d.DTypeCode == "DmExport" && d.DEnable == 1).ToList(); //导入到本地 var home = dic.Where(d => d.DCode == "dm_home").First().DName; var backup_dir = dic.Where(d => d.DCode == "backup_dir").FirstOrDefault().DName; var local_user = dic.Where(d => d.DCode == "user").FirstOrDefault().DName; var local_pwd = dic.Where(d => d.DCode == "pwd").FirstOrDefault().DName; var imp_log = me.MELogFile.Replace(".log", "_imp.log"); using (var dal = new DmDAL(connStr)) { var sql = string.Format("SELECT COUNT(1) FROM DBA_USERS WHERE USERNAME='******'", me.MESchemas); var cnt = Convert.ToInt32(dal.ExecuteScalar(sql)); if (cnt == 1) { sql = string.Format("DROP USER {0} CASCADE", me.MESchemas); dal.ExecuteNonQuery(sql); } sql = string.Format("create user {0} identified by {0} default tablespace BACKUP", me.MESchemas); dal.ExecuteNonQuery(sql); } StringBuilder sbExp = new StringBuilder(); sbExp.AppendFormat("%DM_HOME%\\bin\\dimp USERID={0}/{1} FILE={2} LOG={3} DIRECTORY={4}", local_user, local_pwd, me.MEFileName, imp_log, backup_dir); me.MEImportStatus = "开始导入"; me.MEImportTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"); ctx.SaveChanges(); bool bImportOK = false; bImportOK = ImportTask(home, backup_dir, imp_log, sbExp.ToString()); if (bImportOK) { me.MEImportStatus = "导入成功"; me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"); ctx.SaveChanges(); } else { me.MEImportStatus = "导入失败"; me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"); ctx.SaveChanges(); } } }
public ActionResult List(long scId, string user, string objname, string endtime, int page = 1, int limit = 20) { JsonResult ret = new JsonResult(); ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet; try { StringBuilder sbCount = new StringBuilder(); StringBuilder sbSql = new StringBuilder(); string tv = string.Format("{0}.{1}", user, objname); if (string.IsNullOrWhiteSpace(endtime)) { endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); } sbCount.AppendFormat("SELECT COUNT(*) FROM {0} WHEN TIMESTAMP '{1}'", tv, endtime); sbSql.AppendFormat("SELECT * FROM {0} WHEN TIMESTAMP '{1}'", tv, endtime); sbSql.AppendFormat(" LIMIT {0} OFFSET {1}", limit, (page - 1) * limit); int count = 0; DataTable dt = null; using (var dal = new DmDAL(GetSessionConnStr(scId))) { count = Convert.ToInt32(dal.ExecuteScalar(sbCount.ToString())); dt = dal.ExecuteQuery(sbSql.ToString()); } ret.Data = JsonConvert.SerializeObject(new { status = 0, message = "", total = count, data = dt }); } catch (Exception ex) { ret.Data = JsonConvert.SerializeObject(new { status = 1, message = "发生异常:" + ex.Message, total = 0, data = "" }); RecordException(ex); } return(ret); }
public ActionResult List(long scId, string user, string schema, string objname, string type, int page = 1, int limit = 30) { JsonResult ret = new JsonResult(); ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet; try { StringBuilder sbSql = new StringBuilder(); StringBuilder sbCount = new StringBuilder(); sbCount.Append("SELECT COUNT(*) FROM "); sbSql.Append("SELECT * FROM "); string subTable = @"(SELECT ad.*, obj1.NAME AS USERNAME, obj2.NAME AS OBJECTNAME, (SELECT NAME FROM sysobjects WHERE TYPE$='SCH' AND SUBTYPE$ IS NULL AND ID = (SELECT SCHID FROM sysobjects WHERE ID = ad.TVPID)) AS SCHEMANAME, (SELECT NAME FROM SYSCOLUMNS WHERE ID = ad.TVPID AND COLID = ad.COLID) AS COLNAME, (CASE LEVEL WHEN 1 THEN '语句级' WHEN 2 THEN '对象级' ELSE '' END) AS SLEVEL, (CASE TYPE WHEN 0 THEN 'ALL' WHEN 12 THEN 'USER' WHEN 13 THEN 'ROLE' WHEN 9 THEN 'TABLESPACE' WHEN 14 THEN 'SCHEMA' WHEN 15 THEN 'TABLE' WHEN 16 THEN 'VIEW' WHEN 17 THEN 'INDEX' WHEN 18 THEN 'PROCEDURE' WHEN 19 THEN 'TRIGGER' WHEN 20 THEN 'SEQUENCE' WHEN 21 THEN 'CONTEXT' WHEN 26 THEN 'SYNONYM' WHEN 22 THEN 'GRANT' WHEN 23 THEN 'REVOKE' WHEN 24 THEN 'AUDIT' WHEN 25 THEN 'NOAUDIT' WHEN 30 THEN 'INSERT TABLE' WHEN 33 THEN 'UPDATE TABLE' WHEN 32 THEN 'DELETE TABLE' WHEN 31 THEN 'SELECT TABLE' WHEN 18 THEN 'PROCEDURE' WHEN 44 THEN 'PACKAGE' WHEN 45 THEN 'PACKAGE BODY' WHEN 34 THEN 'MAC POLICY' WHEN 35 THEN 'MAC LEVEL' WHEN 36 THEN 'MAC COMPARTMENT' WHEN 37 THEN 'MAC GROUP' WHEN 38 THEN 'MAC LABEL' WHEN 40 THEN 'MAC USER' WHEN 41 THEN 'MAC TABLE' WHEN 39 THEN 'MAC SESSION' WHEN 28 THEN 'CHECKPOINT' WHEN 75 THEN 'SAVEPOINT' WHEN 76 THEN 'EXPLAIN' WHEN 77 THEN 'NOT EXIST' WHEN 70 THEN 'DATABASE' WHEN 74 THEN 'CONNECT' WHEN 72 THEN 'COMMIT' WHEN 73 THEN 'ROLLBACK' WHEN 43 THEN 'SET TRANSACTION' WHEN 50 THEN 'INSERT' WHEN 53 THEN 'UPDATE' WHEN 52 THEN 'DELETE' WHEN 51 THEN 'SELECT' WHEN 54 THEN 'EXECUTE' WHEN 56 THEN 'MERGE INTO' WHEN 55 THEN 'EXECUTE TRIGGER' WHEN 57 THEN 'LOCK TABLE' ELSE '' END) AS STYPE, '' AS SWHENEVER FROM SYSAUDITOR.SYSAUDIT ad LEFT OUTER JOIN sysobjects obj1 ON obj1.ID = ad.UID LEFT OUTER JOIN sysobjects obj2 ON obj2.ID = ad.TVPID)"; sbCount.Append(subTable); sbSql.Append(subTable); if (!string.IsNullOrWhiteSpace(user)) { sbCount.AddCondition(string.Format("USERNAME LIKE '%{0}%'", user.ToUpper())); sbSql.AddCondition(string.Format("USERNAME LIKE '%{0}%'", user.ToUpper())); } if (!string.IsNullOrWhiteSpace(schema)) { sbCount.AddCondition(string.Format("SCHEMANAME LIKE '%{0}%'", schema.ToUpper())); sbSql.AddCondition(string.Format("SCHEMANAME LIKE '%{0}%'", schema.ToUpper())); } if (!string.IsNullOrWhiteSpace(objname)) { sbCount.AddCondition(string.Format("OBJECTNAME LIKE '%{0}%'", objname.ToUpper())); sbSql.AddCondition(string.Format("OBJECTNAME LIKE '%{0}%'", objname.ToUpper())); } if (!string.IsNullOrWhiteSpace(type)) { sbCount.AddCondition(string.Format("STYPE LIKE '%{0}%'", type.ToUpper())); sbSql.AddCondition(string.Format("STYPE LIKE '%{0}%'", type.ToUpper())); } sbSql.AppendFormat(" LIMIT {0} OFFSET {1}", limit, (page - 1) * limit); int count = 0; DataTable dt = null; using (var dal = new DmDAL(GetSessionConnStr(scId))) { count = Convert.ToInt32(dal.ExecuteScalar(sbCount.ToString())); dt = dal.ExecuteQuery(sbSql.ToString()); } foreach (DataRow dr in dt.Rows) { var when = Convert.ToInt32(dr["WHENEVER"]); string sWhen = ""; switch (when) { case 1: sWhen = "SUCCESSFUL"; break; case 2: sWhen = "FAIL"; break; case 3: sWhen = "ALL"; break; } dr["SWHENEVER"] = sWhen; } ret.Data = JsonConvert.SerializeObject(new { status = 0, message = "", total = count, data = dt }); } catch (Exception ex) { ret.Data = JsonConvert.SerializeObject(new { status = 1, message = "发生异常:" + ex.Message, total = 0, data = "" }); RecordException(ex); } return(ret); }
public ActionResult List(long scId, string user, string schema, string objname, string type, string begtime, string endtime, int page = 1, int limit = 20) { JsonResult ret = new JsonResult(); ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet; try { using (var dal = new DmDAL(GetSessionConnStr(scId))) { StringBuilder sbSql = new StringBuilder(); StringBuilder sbCount = new StringBuilder(); sbCount.Append("SELECT COUNT(*) FROM SYSAUDITOR.V$AUDITRECORDS"); sbSql.Append("SELECT ar.*,'' AS POLICY_NAME FROM SYSAUDITOR.V$AUDITRECORDS ar"); if (!string.IsNullOrWhiteSpace(user)) { sbSql.AddCondition(string.Format("USERNAME LIKE '%{0}%'", user.ToUpper())); sbCount.AddCondition(string.Format("USERNAME LIKE '%{0}%'", user.ToUpper())); } if (!string.IsNullOrWhiteSpace(schema)) { sbSql.AddCondition(string.Format("SCHNAME LIKE '%{0}%'", schema.ToUpper())); sbCount.AddCondition(string.Format("SCHNAME LIKE '%{0}%'", schema.ToUpper())); } if (!string.IsNullOrWhiteSpace(objname)) { sbSql.AddCondition(string.Format("OBJNAME LIKE '%{0}%'", objname.ToUpper())); sbCount.AddCondition(string.Format("OBJNAME LIKE '%{0}%'", objname.ToUpper())); } if (!string.IsNullOrWhiteSpace(type)) { sbSql.AddCondition(string.Format("OPERATION LIKE '%{0}%'", type.ToUpper())); sbCount.AddCondition(string.Format("OPERATION LIKE '%{0}%'", type.ToUpper())); } if (!string.IsNullOrWhiteSpace(begtime)) { sbSql.AddCondition(string.Format("OPTIME >= '{0}'", begtime)); sbCount.AddCondition(string.Format("OPTIME >= '{0}'", begtime)); } if (!string.IsNullOrWhiteSpace(endtime)) { sbSql.AddCondition(string.Format("OPTIME < '{0}'", endtime)); sbCount.AddCondition(string.Format("OPTIME < '{0}'", endtime)); } sbSql.AppendFormat(" LIMIT {0} OFFSET {1}", limit, (page - 1) * limit); int count = Convert.ToInt32(dal.ExecuteScalar(sbCount.ToString())); var dt = dal.ExecuteQuery(sbSql.ToString()); var policies = db.AuditPolicy.Where(p => p.SCID == scId).ToList(); if (dt.Rows.Count > 0 && policies.Count > 0) { foreach (DataRow row in dt.Rows) { var username = row["USERNAME"].ToString(); var schemaname = row["SCHNAME"].ToString(); var obj = row["OBJNAME"].ToString(); var op = row["OPERATION"].ToString(); var sqlUpperCase = row["SQL_TEXT"].ToString().ToUpper(); if (schemaname == "" || obj == "") { continue; } var policy = policies.Where(p => p.APUser == username && p.APSchema == schemaname && p.APObjectName == obj && p.APStatement == op && sqlUpperCase.Contains(p.APCondition)).FirstOrDefault(); /*var po = policies.FirstOrDefault(); * if (po.APUser == username) * { * Console.WriteLine("abc"); * } * if (po.APSchema == schema) * { * Console.WriteLine("abc"); * } * if (po.APObjectName == obj) * { * Console.WriteLine("abc"); * } * if (po.APStatement == op) * { * Console.WriteLine("abc"); * } * if (sqlUpperCase.Contains(po.APCondition)) * { * Console.WriteLine(""); * }*/ if (policy != null) { row["POLICY_NAME"] = policy.APName; } } } ret.Data = JsonConvert.SerializeObject(new { status = 0, message = "", total = count, data = dt }); } } catch (Exception ex) { ret.Data = JsonConvert.SerializeObject(new { status = 1, message = "发生异常:" + ex.Message, total = 0, data = "" }); RecordException(ex); } return(ret); }