public ActionResult Create(Domain.AuditPolicy policy, FormCollection collection)
{
JsonResult ret = new JsonResult();
try
{
using (var dal = new DmDAL(GetSessionConnStr(policy.SCID.Value)))
{
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("SP_AUDIT_OBJECT('{0}', '{1}', '{2}', '{3}','{4}','{5}')",
policy.APStatement, policy.APUser, policy.APSchema, policy.APObjectName,
policy.APColumnName, policy.APWhen);
dal.ExecuteNonQuery(sbSql.ToString());
}
db.AuditPolicy.Add(policy);
db.SaveChanges();
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
message = ""
});
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = ex.Message
});
RecordException(ex);
}
return(ret);
}
public ActionResult GetObjectName(long scId, string user, string objtype = "TABLE")
{
JsonResult ret = new JsonResult();
try
{
List <string> objs = new List <string>();
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
objs.AddRange(dal.GetObjectName(user, "UTAB"));
objs.AddRange(dal.GetObjectName(user, "VIEW"));
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
data = objs
});
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = ex.Message
});
RecordException(ex);
}
return(ret);
}
public ActionResult CreateStatement(long scId, string type, string username, string whenever)
{
JsonResult ret = new JsonResult();
try
{
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
StringBuilder sbSql = new StringBuilder();
//sbSql.Append("SP_AUDIT_STMT");
//dal.ExecuteProcedureNonQuery(sbSql.ToString(),
// new DmParameter("TYPE", type),
// new DmParameter("USERNAME", username),
// new DmParameter("WHENEVER", whenever));
sbSql.AppendFormat("SP_AUDIT_STMT('{0}', '{1}', '{2}')",
type, username, whenever);
dal.ExecuteNonQuery(sbSql.ToString());
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
message = ""
});
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = "发生异常：" + ex.Message
});
RecordException(ex);
}
return(ret);
}
public ActionResult GetColumnName(long scId, string user, string objname)
{
JsonResult ret = new JsonResult();
try
{
List <string> objs = new List <string>();
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
objs = dal.GetAllColumns(user, objname);
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
data = objs
});
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = ex.Message
});
RecordException(ex);
}
return(ret);
}
public ActionResult DeleteStatement(long scId, string type, string username, string whenever)
{
JsonResult ret = new JsonResult();
try
{
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("SP_NOAUDIT_STMT('{0}', '{1}', '{2}')",
type, string.IsNullOrWhiteSpace(username) ? "NULL" : username, whenever);
dal.ExecuteNonQuery(sbSql.ToString());
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
message = ""
});
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = "发生异常：" + ex.Message
});
RecordException(ex);
}
return(ret);
}
// GET: OrChanglogManage
public ActionResult Index(long id)
{
ViewBag.SCID = id;
SetModuleAuthority();
var sc = db.SessionConnection.Find(id);
List <DbMonitor.Domain.Dictionary> dic = null;
if (sc != null)
{
if (sc.SCDBType == "ORACLE")
{
using (OracleDAL dal = new OracleDAL(GetSessionConnStr(id)))
{
ViewBag.Users = dal.GetAllUsers();
}
dic = (from d in db.Dictionary
where d.DTypeCode == "OracleObjectType" && d.DEnable == 1
select d).OrderBy(s => s.DCode).ToList();
}
else if (sc.SCDBType == "DM")
{
using (var dal = new DmDAL(GetSessionConnStr(id)))
{
ViewBag.Users = dal.GetAllUsers();
}
dic = (from d in db.Dictionary
where d.DTypeCode == "DmObjectType" && d.DEnable == 1
select d).OrderBy(s => s.DCode).ToList();
}
ViewBag.DBType = sc.SCDBType;
}
return(View(dic));
}
// GET: OrDataCompare
public ActionResult Index(long id)
{
ViewBag.SCID = id;
SetModuleAuthority();
List <Domain.Dictionary> dic = null;
var sc = db.SessionConnection.Find(id);
if (sc.SCDBType == "ORACLE")
{
using (var dal = new OracleDAL(GetSessionConnStr(id)))
{
ViewBag.Users = dal.GetAllUsers().OrderBy(s => s).ToList();
}
dic = db.Dictionary.Where(d => d.DTypeCode == "OracleObjectType" && d.DEnable == 1).ToList();
}
else
{
using (var dal = new DmDAL(GetSessionConnStr(id)))
{
ViewBag.Users = dal.GetAllUsers().OrderBy(s => s).ToList();
}
dic = db.Dictionary.Where(d => d.DTypeCode == "DmObjectType" && d.DEnable == 1).ToList();
}
return(View(dic));
}
static void GrabDmData(MonitorManagement mm)
{
DateTime dtBeg;
DateTime dtEnd;
//从上次最大时间开始
dtBeg = DateTime.Parse(mm.MMLastTime);
dtEnd = DateTime.Now;
//采集SQL
StringBuilder sbSql = new StringBuilder();
sbSql.Append("SELECT a.USERNAME, ");
sbSql.Append("a.SCHNAME, ");
sbSql.Append("a.OBJNAME, ");
sbSql.Append("a.OPERATION, ");
sbSql.Append("a.SQL_TEXT, ");
sbSql.Append(" TO_CHAR(a.OPTIME,'yyyy-mm-dd HH24:MI:SS') TIMESTAMP, ");
sbSql.Append("s.SUBTYPE$ OBJTYPE ");
sbSql.Append("FROM SYSAUDITOR.V$AUDITRECORDS a ");
sbSql.Append("LEFT JOIN sysobjects s ");
sbSql.Append("ON a.SCHID = s.SCHID ");
sbSql.Append("WHERE SUCC_FLAG = 'Y' ");
sbSql.AppendFormat("AND a.OPTIME >= to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtBeg.ToString("yyyy-MM-dd HH:mm:ss"));
sbSql.AppendFormat("AND a.OPTIME < to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtEnd.ToString("yyyy-MM-dd HH:mm:ss"));
DataTable dt = null;
string connStr = GetSessionConnStr(mm.SCID.Value);
using (DmDAL dal = new DmDAL(connStr))
{
dt = dal.ExecuteQuery(sbSql.ToString());
}
var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
using (var ctx = new DbMonitorEntities())
{
foreach (DataRow dr in dt.Rows)
{
ChangeLog log = new ChangeLog();
log.SCID = mm.SCID;
log.CLChangeEvent = dr["OPERATION"].ToString();
log.CLContent = "";
log.CLObjectName = dr["OBJNAME"].ToString();
log.CLSchema = dr["SCHNAME"].ToString();
log.CLObjectType = dr["OBJTYPE"].ToString();
log.CLSQL_Text = dr["SQL_TEXT"].ToString();
log.CLOperator = dr["USERNAME"].ToString();
log.CLChangeTime = dr["TIMESTAMP"].ToString();
log.CLGrabTime = grabTime;
ctx.ChangeLog.Add(log);
}
var editMM = ctx.MonitorManagement.Find(mm.ID);
editMM.MMLastTime = dtEnd.ToString("yyyy-MM-dd HH:mm:ss");
ctx.SaveChanges();
Console.WriteLine("id:{0} grab {1} items", mm.ID, dt.Rows.Count);
}
}
/// <summary>
/// 获取所有模式
/// </summary>
/// <param name="dal"></param>
/// <returns></returns>
public static List <string> GetAllSchemas(this DmDAL dal)
{
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("SELECT * FROM sysobjects WHERE TYPE$='SCH' AND SUBTYPE$ IS NULL");
return(dal.GetOneColumnValue(sbSql.ToString()));
}
/// <summary>
/// 获取所有用户
/// </summary>
/// <param name="dal"></param>
/// <returns></returns>
public static List <string> GetAllUsers(this DmDAL dal)
{
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("SELECT NAME FROM sysobjects WHERE TYPE$='UR' AND SUBTYPE$='USER'");
return(dal.GetOneColumnValue(sbSql.ToString()));
}
/// <summary>
/// 获取用户的所有的表
/// </summary>
/// <param name="dal"></param>
/// <param name="user"></param>
/// <returns></returns>
public static List <string> GetAllTables(this DmDAL dal, string user)
{
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat(@"SELECT NAME FROM sysobjects WHERE
SCHID = (SELECT ID FROM sysobjects WHERE TYPE$ = 'SCH' AND NAME ='{0}')
AND TYPE$='SCHOBJ' AND SUBTYPE$='UTAB'", user);
return(dal.GetOneColumnValue(sbSql.ToString()));
}
// GET: DmAuditPolicy
public ActionResult Index(long id)
{
ViewBag.SCID = id;
SetModuleAuthority();
using (var dal = new DmDAL(GetSessionConnStr(id)))
{
ViewBag.Users = dal.GetAllUsers();
}
return(View());
}
/// <summary>
/// 查询一列值并以List<string>返回
/// </summary>
/// <param name="dal"></param>
/// <param name="sqlText"></param>
/// <returns></returns>
public static List <string> GetOneColumnValue(this DmDAL dal, string sqlText)
{
List <string> ret = new List <string>();
var dt = dal.ExecuteQuery(sqlText);
foreach (DataRow row in dt.Rows)
{
ret.Add(row[0].ToString());
}
return(ret);
}
/// <summary>
/// 获取用户某个对象的所有列
/// </summary>
/// <param name="dal"></param>
/// <param name="user"></param>
/// <returns></returns>
public static List <string> GetAllColumns(this DmDAL dal, string user, string obj)
{
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat(@"SELECT NAME FROM SYSCOLUMNS
WHERE ID = (SELECT ID FROM sysobjects
WHERE SCHID = (SELECT ID FROM sysobjects WHERE TYPE$ = 'SCH' AND NAME ='{0}')
AND NAME='{1}')",
user, obj);
return(dal.GetOneColumnValue(sbSql.ToString()));
}
public ActionResult Create(long scId)
{
CreateAcion();
var policy = db.AuditPolicy.Create();
policy.SCID = scId;
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
ViewBag.User = dal.GetAllUsers();
}
return(View(policy));
}
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 CreateObject(long id)
{
string[] arrStatement = { "ALL", "INSERT", "UPDATE", "DELETE", "SELECT", "EXECUTE", "MERGE INTO", "EXECUTE TRIGGER", "LOCK TABLE" };
ViewBag.STMT = (from d in db.Dictionary
where d.DTypeCode == "DmAuditObject" && d.DEnable == 1
orderby d.DTypeCode
select d.DCode).OrderBy(c => c).ToList();
using (var dal = new DmDAL(GetSessionConnStr(id)))
{
ViewBag.User = dal.GetAllUsers();
}
ViewBag.ObjectTypes = db.Dictionary.Where(d => d.DTypeCode == "DmObjectType" && d.DEnable == 1)
.OrderBy(d => d.DCode).ToList();
return(View(id));
}
static void GrabDmData(MonitorManagement mm)
{
//采集SQL
StringBuilder sbSql = new StringBuilder();
sbSql.Append("select HOST_NAME,INSTANCE_NAME,SVR_VERSION AS VERSION,START_TIME AS STARTUP_TIME,STATUS$ AS STATUS,MODE$ AS DATABASE_STATUS from V$INSTANCE");
DataTable dt = null;
string connStr = DataGraber.GetSessionConnStr(mm.SCID.Value);
using (DmDAL dal = new DmDAL(connStr))
{
dt = dal.ExecuteQuery(sbSql.ToString());
}
var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
using (var ctx = new DbMonitorEntities())
{
//把上一次数据设成历史数据
var ds = ctx.DatabaseStatus.Where(s => s.REALTIME == 1);
foreach (var s in ds)
{
s.REALTIME = 0;
s.EditingTime = grabTime;
}
ctx.SaveChanges();
foreach (DataRow dr in dt.Rows)
{
DatabaseStatus status = ctx.DatabaseStatus.Create();
status.SCID = mm.SCID;
status.INSTANCE_NAME = dr["INSTANCE_NAME"].ToString();
status.HOST_NAME = dr["HOST_NAME"].ToString();
status.VERSION = dr["VERSION"].ToString();
status.STARTUP_TIME = dr["STARTUP_TIME"].ToString();
status.STATUS = dr["STATUS"].ToString();
status.DATABASE_STATUS = dr["DATABASE_STATUS"].ToString();
status.REALTIME = 1;
status.CreationTime = grabTime;
ctx.DatabaseStatus.Add(status);
ctx.SaveChanges();
Console.WriteLine("id:{0} dm-status-grab {1} items", mm.ID, dt.Rows.Count);
}
}
}
public ActionResult GetColumnName(long scId, string user, string objname)
{
JsonResult ret = new JsonResult();
ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
try
{
List <string> colNames = new List <string>();
if (!string.IsNullOrWhiteSpace(user) && !string.IsNullOrWhiteSpace(objname))
{
string sql = string.Format("SELECT * FROM {0}.{1} LIMIT 1 OFFSET 1", user, objname);
DataTable dt = null;
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
dt = dal.ExecuteQuery(sql);
}
if (dt != null)
{
foreach (DataColumn col in dt.Columns)
{
colNames.Add(col.ColumnName);
}
}
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
message = "",
data = colNames
});;
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = "发生异常：" + ex.Message,
data = ""
});
RecordException(ex);
}
return(ret);
}
// GET: DmAuditManage
public ActionResult Index(long id)
{
ViewBag.SCID = id;
SetModuleAuthority();
using (var dal = new DmDAL(GetSessionConnStr(id)))
{
ViewBag.Users = dal.GetAllUsers();
}
var dic = db.Dictionary.Where(d => d.DTypeCode == "DmObjectType" && d.DEnable == 1)
.OrderBy(d => d.DCode).ToList();
var stmt = (from d in db.Dictionary
where (d.DTypeCode == "DmAuditSTMT" || d.DTypeCode == "DmAuditObject") && d.DEnable == 1
orderby d.DTypeCode
select d.DCode).OrderBy(c => c).Distinct().ToList();
stmt.Remove("ALL");
ViewBag.STMT = stmt.OrderBy(s => s).ToList();
return(View(dic));
}
/// <summary>
/// get的参数名必须是id,路由规则定了
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult CreateStatement(long id)
{
string[] arrStatement = { "ALL", "USER", "ROLE", "TABLESPACE", "SCHEMA", "TABLE", "VIEW", "INDEX",
"PROCEDURE", "TRIGGER", "SEQUENCE", "CONTEXT", "SYNONYM", "GRANT",
"REVOKE", "AUDIT", "NOAUDIT", "INSERT TABLE", "UPDATE TABLE",
"DELETE TABLE", "SELECT TABLE", "EXECUTE", "PROCEDURE", "PACKAGE",
"PACKAGE BODY", "MAC POLICY", "MAC LEVEL", "MAC COMPARTMENT",
"MAC GROUP", "MAC LABEL", "MAC USER", "MAC TABLE", "MAC SESSION",
"CHECKPOINT", "SAVEPOINT", "EXPLAIN", "NOT EXIST", "DATABASE",
"CONNECT", "COMMIT", "ROLLBACK", "SET TRANSACTION" };
ViewBag.STMT = (from d in db.Dictionary
where d.DTypeCode == "DmAuditSTMT" && d.DEnable == 1
orderby d.DTypeCode
select d.DCode).OrderBy(c => c).ToList();
using (var dal = new DmDAL(GetSessionConnStr(id)))
{
ViewBag.User = dal.GetAllUsers();
}
return(View(id));
}
public ActionResult Delete(List <int> idList)
{
JsonResult ret = new JsonResult();
try
{
var pt = db.AuditPolicy.Find(idList[0]);
using (var dal = new DmDAL(GetSessionConnStr(pt.SCID.Value)))
{
foreach (var id in idList)
{
var policy = db.AuditPolicy.Find(id);
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("SP_NOAUDIT_OBJECT('{0}', '{1}', '{2}', '{3}','{4}','{5}')",
policy.APStatement, policy.APUser, policy.APSchema, policy.APObjectName,
policy.APColumnName, policy.APWhen);
dal.ExecuteNonQuery(sbSql.ToString());
db.AuditPolicy.Remove(policy);
}
}
db.SaveChanges();
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
message = ""
});
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = ex.Message
});
RecordException(ex);
}
return(ret);
}
public ActionResult CreateObject(long scId, string type,
string username,
string schemaname,
string tvname,
string colname,
string whenever)
{
JsonResult ret = new JsonResult();
try
{
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("SP_AUDIT_OBJECT('{0}', '{1}', '{2}', '{3}'",
type, username, schemaname, tvname);
if (!string.IsNullOrWhiteSpace(colname))
{
sbSql.AppendFormat(",'{0}'", colname);
}
sbSql.AppendFormat(",'{0}')", whenever);
dal.ExecuteNonQuery(sbSql.ToString());
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
message = ""
});
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = "发生异常：" + ex.Message
});
RecordException(ex);
}
return(ret);
}
public ActionResult Create(long scId)
{
CreateAcion();
var sc = db.SessionConnection.Find(scId);
var dic = db.Dictionary.Where(d => d.DTypeCode == "DmExport" && d.DCode == "backup_dir").FirstOrDefault();
Domain.MirrorExport me = new Domain.MirrorExport()
{
SCID = scId,
MEUser = sc.SCUser,
MEPassword = sc.SCPassword,
MEDirectory = dic.DName,
MESchemas = sc.SCUser,
MEExportTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")
};
using (var dal = new DmDAL(GetSessionConnStr(scId)))
{
//ViewBag.Schemas = dal.GetAllSchemas();
ViewBag.Schemas = dal.GetAllUsers();
}
return(View(me));
}
public ActionResult GetObjectName(long scId, string user, string objtype)
{
JsonResult ret = new JsonResult();
try
{
List <string> objs = new List <string>();
var sc = db.SessionConnection.Find(scId);
if (sc.SCDBType == "ORACLE")
{
using (OracleDAL dal = new OracleDAL(GetSessionConnStr(scId)))
{
objs = dal.GetObjectName(user, objtype).OrderBy(s => s).ToList();
}
}
else
{
using (DmDAL dal = new DmDAL(GetSessionConnStr(scId)))
{
objs = dal.GetObjectName(user, objtype).OrderBy(s => s).ToList();
}
}
ret.Data = JsonConvert.SerializeObject(new
{
status = 0,
data = objs
});
}
catch (Exception ex)
{
ret.Data = JsonConvert.SerializeObject(new
{
status = 1,
message = ex.Message
});
RecordException(ex);
}
return(ret);
}
static void GrabDmData(MonitorManagement mm)
{
DateTime dtBeg;
DateTime dtEnd;
//从上次最大时间开始
dtBeg = DateTime.Parse(mm.MMLastTime);
dtEnd = DateTime.Now;
//采集SQL
StringBuilder sbSql = new StringBuilder();
sbSql.Append("SELECT a.USERNAME, ");
sbSql.Append("a.SCHNAME, ");
sbSql.Append("a.OBJNAME, ");
sbSql.Append("a.OPERATION, ");
sbSql.Append("a.SQL_TEXT, ");
sbSql.Append(" TO_CHAR(a.OPTIME,'yyyy-mm-dd HH24:MI:SS') TIMESTAMP, ");
sbSql.Append("'' AS OBJTYPE ");
sbSql.Append("FROM SYSAUDITOR.V$AUDITRECORDS a ");
//sbSql.Append("LEFT JOIN sysobjects s ");
//sbSql.Append("ON a.SCHID = s.SCHID ");
//sbSql.Append("WHERE SUCC_FLAG = 'Y' ");
sbSql.Append("WHERE OPERATION NOT IN ('SELECT') ");
sbSql.AppendFormat("AND a.OPTIME >= to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtBeg.ToString("yyyy-MM-dd HH:mm:ss"));
sbSql.AppendFormat("AND a.OPTIME < to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtEnd.ToString("yyyy-MM-dd HH:mm:ss"));
DataTable dt = null;
string connStr = GetSessionConnStr(mm.SCID.Value);
using (DmDAL dal = new DmDAL(connStr))
{
dt = dal.ExecuteQuery(sbSql.ToString());
}
var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
bool bAdd;
if (dt.Rows.Count > 0)
{
using (var ctx = new DbMonitorEntities())
{
foreach (DataRow dr in dt.Rows)
{
bAdd = true;
ChangeLog log = new ChangeLog();
log.SCID = mm.SCID;
log.CLChangeEvent = dr["OPERATION"].ToString();
log.CLContent = "";
log.CLObjectName = dr["OBJNAME"].ToString();
log.CLSchema = dr["SCHNAME"].ToString();
log.CLObjectType = dr["OBJTYPE"].ToString();
log.CLSQL_Text = dr["SQL_TEXT"].ToString();
log.CLOperator = dr["USERNAME"].ToString();
log.CLChangeTime = dr["TIMESTAMP"].ToString();
log.CLGrabTime = grabTime;
var sql_Upper = log.CLSQL_Text.ToUpper();
if (_dicDmDDL.ContainsKey(log.CLChangeEvent))
{
var objName = GetObjectNameFormDDL(sql_Upper, log.CLChangeEvent);
if (log.CLChangeEvent.Contains("CREATE"))
{
log.CLOldData = "";
log.CLNewData = objName;
}
else if (log.CLChangeEvent.Contains("ALTER"))
{
var pos = sql_Upper.IndexOf(objName);
log.CLNewData = sql_Upper.Substring(pos + objName.Length);
}
else if (log.CLChangeEvent.Contains("DROP"))
{
log.CLOldData = objName;
log.CLNewData = "";
}
log.CLChangeType = _dicDmDDL[log.CLChangeEvent];
switch (log.CLChangeEvent)
{
case "CREATE TABLE":
case "ALTER TABLE":
case "DROP TABLE":
case "TRUNCATE TABLE":
log.CLObjectType = "UTAB";
break;
case "CREATE VIEW":
case "DROP VIEW":
log.CLObjectType = "VIEW";
break;
case "CREATE PROCEDURE":
case "DROP PROCEDURE":
log.CLObjectType = "PROC";
break;
case "CREATE TRIGGER":
case "DROP TRIGGER":
log.CLObjectType = "TRIG";
break;
case "CREATE INDEX":
case "DROP INDEX":
log.CLObjectType = "INDEX";
break;
}
}
//数据操纵
else if (log.CLChangeEvent == "INSERT")
{
var pos = sql_Upper.IndexOf("VALUES") + 6;
var lastPos = sql_Upper.LastIndexOf(")");
log.CLOldData = "";
log.CLNewData = sql_Upper.Substring(pos, lastPos - pos);
log.CLChangeType = "插入数据";
log.CLObjectType = "UTAB";
}
else if (log.CLChangeEvent == "UPDATE")
{
var pos = sql_Upper.IndexOf("SET");
log.CLOldData = "";
log.CLNewData = sql_Upper.Substring(pos);
log.CLChangeType = "更新数据";
log.CLObjectType = "UTAB";
}
else if (log.CLChangeEvent == "DELETE")
{
var pos = sql_Upper.IndexOf(log.CLObjectName);
log.CLOldData = "";
log.CLNewData = sql_Upper.Substring(pos + log.CLObjectName.Length);
log.CLChangeType = "删除数据";
log.CLObjectType = "UTAB";
}
else
{
bAdd = false;
}
if (bAdd)
{
ctx.ChangeLog.Add(log);
}
//有数据才更新数据库
var editMM = ctx.MonitorManagement.Find(mm.ID);
editMM.MMLastTime = dtEnd.ToString("yyyy-MM-dd HH:mm:ss");
ctx.SaveChanges();
}
}
Console.WriteLine("id:{0} grab {1} items", mm.ID, dt.Rows.Count);
}
}
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);
}
