Example #1
0
        public ActionResult List(long scId, int page = 1, int limit = 20, string user = "", string option = "")
        {
            JsonResult ret = new JsonResult();

            ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet;

            try
            {
                StringBuilder sbCount = new StringBuilder();
                StringBuilder sbSql   = new StringBuilder();
                string        tv      = "DBA_PRIV_AUDIT_OPTS";
                sbCount.AppendFormat("SELECT COUNT(1) FROM {0} ", tv);
                sbSql.AppendFormat("SELECT * FROM (SELECT ROWNUM AS ROWNO, t.* FROM {0} t WHERE ", tv);
                //筛选条件
                if (!string.IsNullOrWhiteSpace(user))
                {
                    sbSql.AppendFormat("USER_NAME LIKE '%{0}%' AND ", user);
                    sbCount.AddCondition(string.Format("USER_NAME LIKE '%{0}%'", user));
                }
                if (!string.IsNullOrWhiteSpace(option))
                {
                    sbSql.AppendFormat("PRIVILEGE LIKE '%{0}%' AND ", option.ToUpper());
                    sbCount.AddCondition(string.Format("PRIVILEGE LIKE '%{0}%'", option.ToUpper()));
                }
                sbSql.AppendFormat("ROWNUM <= {0}) table_alias WHERE table_alias.ROWNO > {1}",
                                   page * limit, (page - 1) * limit);

                int       count = 0;
                DataTable dt    = null;
                //string connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));Persist Security Info=True;User ID=sys;Password=sys;DBA Privilege=SYSDBA;";
                string connStr = GetSessionConnStr(scId);
                using (OracleDAL dal = new OracleDAL(connStr))
                {
                    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);
        }
Example #2
0
        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 schema, string user, string objname, string type, string begtime, 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      = "DBA_COMMON_AUDIT_TRAIL";
                sbCount.AppendFormat("SELECT COUNT(1) FROM {0}", tv);
                sbSql.AppendFormat("SELECT * FROM (SELECT ROWNUM AS ROWNO, t.* FROM {0} t WHERE ", tv);
                //筛选条件
                if (!string.IsNullOrWhiteSpace(schema))
                {
                    sbSql.AppendFormat("OBJECT_SCHEMA LIKE '%{0}%' AND ", schema.ToUpper());
                    sbCount.AddCondition(string.Format("OBJECT_SCHEMA LIKE '%{0}%'", schema.ToUpper()));
                }
                if (!string.IsNullOrWhiteSpace(user))
                {
                    sbSql.AppendFormat("DB_USER LIKE '%{0}%' AND ", user.ToUpper());
                    sbCount.AddCondition(string.Format("DB_USER LIKE '%{0}%'", user.ToUpper()));
                }
                if (!string.IsNullOrWhiteSpace(objname))
                {
                    sbSql.AppendFormat("OBJECT_NAME LIKE '%{0}%' AND ", objname.ToUpper());
                    sbCount.AddCondition(string.Format("OBJECT_NAME LIKE '%{0}%'", objname.ToUpper()));
                }
                if (!string.IsNullOrWhiteSpace(type))
                {
                    sbSql.AppendFormat("STATEMENT_TYPE LIKE '%{0}%' AND ", type.ToUpper());
                    sbCount.AddCondition(string.Format("STATEMENT_TYPE LIKE '%{0}%'", type.ToUpper()));
                }
                if (!string.IsNullOrWhiteSpace(begtime))
                {
                    sbSql.AppendFormat("extended_timestamp >= to_date('{0}','yyyy-MM-dd HH24:mi:ss') AND ", begtime);
                    sbCount.AddCondition(string.Format("extended_timestamp >= to_date('{0}','yyyy-MM-dd HH24:mi:ss')", begtime));
                }
                if (!string.IsNullOrWhiteSpace(endtime))
                {
                    sbSql.AppendFormat("extended_timestamp <= to_date('{0}','yyyy-MM-dd HH24:mi:ss') AND ", endtime);
                    sbCount.AddCondition(string.Format("extended_timestamp <= to_date('{0}','yyyy-MM-dd HH24:mi:ss')", endtime));
                }
                sbSql.AppendFormat("ROWNUM <= {0}) table_alias WHERE table_alias.ROWNO > {1}",
                                   page * limit, (page - 1) * limit);

                int       count = 0;
                DataTable dt    = null;
                //string connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));Persist Security Info=True;User ID=sys;Password=sys;DBA Privilege=SYSDBA;";
                string connStr = GetSessionConnStr(scId);
                using (OracleDAL dal = new OracleDAL(connStr))
                {
                    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);
        }
Example #4
0
        public ActionResult List(long scId, int page = 1, int limit = 20, string user = "", string objtype = "", string objname = "")
        {
            JsonResult ret = new JsonResult();

            ret.JsonRequestBehavior = JsonRequestBehavior.AllowGet;

            try
            {
                StringBuilder sbCount = new StringBuilder();
                StringBuilder sbSql   = new StringBuilder();
                string        tv      = "DBA_OBJ_AUDIT_OPTS";
                sbCount.AppendFormat("SELECT COUNT(1) FROM {0} ", tv);
                sbSql.AppendFormat("SELECT * FROM (SELECT ROWNUM AS ROWNO, t.* FROM {0} t WHERE ", tv);
                //筛选条件
                if (!string.IsNullOrWhiteSpace(user))
                {
                    sbSql.AppendFormat("OWNER LIKE '%{0}%' AND ", user);
                    sbCount.AddCondition(string.Format("OWNER LIKE '%{0}%'", user));
                }
                if (!string.IsNullOrWhiteSpace(objtype))
                {
                    sbSql.AppendFormat("OBJECT_TYPE LIKE '%{0}%' AND ", objtype);
                    sbCount.AddCondition(string.Format("OBJECT_TYPE LIKE '%{0}%'", objtype));
                }
                if (!string.IsNullOrWhiteSpace(objname))
                {
                    sbSql.AppendFormat("OBJECT_NAME LIKE '%{0}%' AND ", objname);
                    sbCount.AddCondition(string.Format("OBJECT_NAME LIKE '%{0}%'", objname));
                }
                sbSql.AppendFormat("ROWNUM <= {0}) table_alias WHERE table_alias.ROWNO > {1}",
                                   page * limit, (page - 1) * limit);

                int       count   = 0;
                DataTable dt      = null;
                string    connStr = GetSessionConnStr(scId);
                using (OracleDAL dal = new OracleDAL(connStr))
                {
                    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, 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);
        }