20190520线上部署sql内容以及20190508线上部署sql内容develop_20190520_branch

3 files changed, 907 insertions, 0 deletions

diff --git a/src/main/resources/sql/deploymentsql/20190508help_document.sql b/src/main/resources/sql/deploymentsql/20190508help_document.sql
new file mode 100644
index 000000000..121eec747
--- /dev/null
+++ b/src/main/resources/sql/deploymentsql/20190508help_document.sql
@@ -0,0 +1,612 @@
+-- ----------------------------

+-- Table structure for help_document

+-- ----------------------------

+DROP TABLE IF EXISTS `help_document`;

+CREATE TABLE `help_document` (

+  `id` int(11) NOT NULL AUTO_INCREMENT,

+  `file_name` varchar(100) DEFAULT '',

+  `file_comment` text DEFAULT NULL,

+  `back_file_comment` text DEFAULT NULL,

+  PRIMARY KEY (`id`),

+  KEY `INDEX_FILE_NAME` (`file_name`)

+) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4;

+

+-- ----------------------------

+-- Records of help_document

+-- ----------------------------

+INSERT INTO `help_document` VALUES ('1', 'cache_policy.md', 'Cache Policy

+

+On National Proxy System, Individual Cache policy rules determine

+whether to cache or not based on traffic attributes, such as URL and

+Cookies. For cache action, the optimization parameters are:

+

+*A Cache key* -  is a unique string that lets the National Proxy System

+look for web content when requests hit them. It��s made up of a hostname,

+path, and cookie parts. By default, the Proxy use the entire URL as the

+cache key. Selecting the correct cache key will ensure maximum cache

+footprint and increase cache hits.

+

+*Ignore Query String in URL* -  in case the query strings doesn��t

+actually indicate that the object need to be different then you could

+EXCLUDE them from the cache key. For example, after ignoring ��sqp�� and

+��rs�� of URL: ��https://example.com/pic.jpg?~~sqp=UAAI&rs=AOn4~~��.

+

+*Include Cookie Values* -  in case the server send different content for

+the same URL based on the cookie value, you can include that cookie

+value as a part of cache key. For example, the server may set a cookie

+at the client called "prefLang=ru" to record user preferred language,

+you could add "prefLang" to distinguish different web content.

+

+*Disable Revalidate* -  is an ON-OFF switch. The pragma-no-cache header

+in a client��s request causes the proxy to re-fetch the entire object

+from the original server, even if the cached copy of the object is

+fresh. By default this option is switch OFF, which means a client��s

+non-conditional request results in a conditional GET request sent to the

+original server if the object is already in cache. The conditional

+request allows the original server to return the 304 Not Modified

+response, if the content in cache is still fresh. Thereby, the

+server-side bandwidth and latency consumed are lesser as the full

+content is not retrieved again from the original server.

+

+*Cache Dynamic Content* -  is an ON-OFF switch. A URL is considered

+dynamic if it ends in ��.asp(x)�� or contains a question mark (?), a

+semicolon (;), or ��cgi��. *Ignore Query String* overrides this option

+(switch on).

+

+*Cache Cookied Content* -  is an ON-OFF switch. By default, the Proxy

+does NOT cache cookied content of any type. If this option is switch on,

+the system cache all cookied content except HTML.

+

+*Ignore Request no-cache Headers* -  is an ON-OFF switch. By default, the

+proxy strictly observes client Cache-Control: no-cache directives. As

+known as:

+

+i.  Authorization

+

+ii. WWW-Authenticate

+

+iii. Cache-Control: no-store

+

+iv. Cache-Control: no-cache

+

+If a requested object contains a no-cache header, then proxy forwards

+the request to the origin server even if it has a fresh copy in cache.

+You can configure proxy to ignore client no-cache directives such that

+it ignores no-cache headers from client requests and serves the object

+from its cache.

+

+*Ignore Response no-cache Headers* -  is an ON-OFF switch. By default, a

+response from an origin server with a no-cache header is not stored in

+the cache. As known as:

+

+i.  Cache-Control: no-store

+

+ii. Cache-Control: private

+

+iii. Set-Cookie

+

+iv. Cache-Control: no-cache

+

+v.  WWW-Authenticate

+

+vi. Expires header with a value of 0 (zero) or a past date.

+

+If you configure proxy to ignore no-cache headers, then proxy also

+ignores no-store headers. The default behavior of observing no-cache

+directives is appropriate in most cases.

+

+*Forcing Object Caching* -  is an ON-OFF switch. You can force Proxy to

+cache specific URLs (including dynamic URLs) for a specified duration,

+regardless of Cache-Control response headers.

+

+*Minimum Use* -  sets the number of times an item must be requested by

+clients before Proxy caches it. This is useful if the cache is

+constantly filling up, as it ensures that only the most frequently

+accessed items are added to the cache. By default, Proxy cache object at

+its first appearance. The counter resets in every 30 minutes. Note that

+the requests is counted independently on each processing unit.

+

+*Max Cache Object Size* -  sets the upper limit of an object size, larger

+object will not be cached. By default, Proxy does not cache object

+larger than 1 GB.

+

+*Cache Pinning Time* -  configures Proxy to keep certain objects in the

+cache for a specified time. You can use this option to ensure that the

+most popular objects are in cache when needed and to prevent cache

+manager from deleting important objects. Proxy observes Cache-Control

+headers and pins an object in the cache only if it is indeed cacheable.

+

+*Max Cache Size* -  sets the upper limit of the size of storage for a

+policy. By default, Proxy uses all available disk space. When the cache

+size reaches the limit, the cache manager removes the files that were

+least recently used to bring the cache size back under the limit.

+

+*Inactive Time* -  specifies how long an item can remain in the cache

+without being accessed. A file that has not been requested for this time

+is automatically deleted from the cache by the cache manager, regardless

+of whether or not it has expired.

+

+', null);

+INSERT INTO `help_document` VALUES ('2', 'control_policy.md', 'Control Policy

+

+On National Proxy System, Individual Control policy rules determine

+whether to allow, block, redirect or replace a session based on traffic

+attributes, such as URL, request header fields, request body keywords,

+response header fields, response body keywords, IP address, Subscribe ID

+and their combination. You could specify these attributes in the submenu

+of *Control Policy*.

+

+The attributes are detailed in following context:

+

+*URL* -  From proxy��s perspective, a HTTP URL consists of a hierarchical

+sequence of three components: URL = hostname/path[?query] . The URL path

+name can also be specified by the user in the local writing system. If

+not already encoded, it is converted to UTF-8, and any characters not

+part of the basic URL character set are escaped as hexadecimal using

+percent-encoding; for example, search keywords �������ܧڧۡ� in Google

+produces URL��

+

+https://www.google.com/search?q=%D1%80%D1%83%D1%81%D1%81%D0%BA%D0%B8%D0%B9

+

+To perform policy action on above URL, you could input the whole URL in

+the input box. Or, you could input original keywords and let the Proxy

+do the decoding, e.g. ��google.com/search�� & �������ܧڧۡ�. Note that the

+scheme string MUST be excluded from the URL, it��s ��https://�� in this

+case.

+

+NOTE Maximum HTTP/HTTPS URL length is 1023 characters

+

+*Request Header* -  is used to set conditions on request header fields.

+Header fields are colon-separated key-value pairs in clear-text string

+format, terminated by a carriage return (CR) and line feed (LF)

+character sequence. For example, ��user-agent: Mozilla/5.0 (Windows NT

+10.0; Win64; x64)�� is a header filed in request header. The *Matching

+District* is used to configure the field��s key, if the key was

+presented, the Proxy will search in the value for *Keywords*.

+

+*Response Header* -  is used to set conditions on response header fields.

+Its configuration is similar to *Request Header*.

+

+*Request Body* -  is used to set conditions on request��s body message.

+The Proxy searches the pre-configured *Keywords* in it. You can

+configure non-ASCII or non-utf8 keywords by turn on HEX.

+

+*Response Body* -  is used to set conditions on response��s body message.

+Its configuration is similar to *Request Body*.

+

+You could select one of the five actions for above attributes, as known

+as:

+

+*Monitor* -  the Proxy produce a log to record matched HTTP session

+information.

+

+*Block* -  the Proxy terminate matched HTTP session with an error page

+and produce a log. You MUST specify a *Response Code* and a *Response

+Content* to generate an error page.

+

+*Redirect*��the Proxy redirect matched HTTP session to a predefined URL.

+Since redirection need to be performed before delivering response to

+client, condition of response body is not applicable in this action. You

+MUST configure the redirect response via *Response Code* and *Response

+URL*. The Response URL MUST start with a scheme (http:// or https://).

+You SHOULD NOT select **301** as *Response Code* unless you exactly know

+what you are doing. This action produces a log.

+

+*Replace*��the Proxy *Searches in* a given HTTP part to *Find* a given

+string, and *Replace* any matches *with* another given string. If no

+match was found, the session remained untouched. For performance

+concerns, condition of request body and response body is not available

+in this action. For example, you can configure the Proxy to search in

+the response body of URL ��www.example.com/index.html��, find every

+��string1�� and replace with ��string2��. This action produces a log.

+

+*Whitelist*��the Proxy pass-through the matched sessions and produce no

+log.

+

+National Proxy will enforce policy check on traffic attributes, policies

+have been created that there will be some that overlap or are subsets of

+the parameters that the policies use to determine which policy should be

+matched against the traffic. The execute order of policy is ��first

+match, first served��. In case of an incoming traffic attribute matches

+one more policy, the priority order is *Whitelist \> Block \>

+Redirect \> Replace \> Monitor*, action with higher priority overrides

+others. If multiple policies of same action are matched, policy with

+bigger ID number is precedence.

+', null);

+INSERT INTO `help_document` VALUES ('3', 'intercept_policy.md', 'Intercept Policy

+

+An Intercept policy rule allows you to define traffic that you want the

+National Proxy to decrypt and to define traffic that you choose to

+exclude from decryption because the traffic is personal or because of

+local regulations. A connection is intercepted/optimized based on

+traffic attributes, such as IP address, domain name (via SNI matching)

+and Subscribe ID. You could specify these attributes in *IP Intercept*

+and *Domain Intercept*.

+

+Both *IP intercept* and *Domain Intercept* are subject two actions:

+

+*Intercept*��the National Proxy System intercepts network traffic for

+further control policy and cache policy checking. Interception requires

+certificates to establish the National Proxy as a trusted third party.

+National Proxy deployed in transparent mode, which means the users don`t

+have any proxy settings in their browser. When a connection is set to

+intercept, the proxy terminates the connection and initiates a new

+connection between client and server. If the connection is SSL

+encrypted, the original certificate is replaced with a substitute one.

+

+*Bypass*��the Proxy passes through the network connection without apply

+an optimization or policy checking. You can also use bypass action when

+excluding servers from SSL decryption for technical reasons (the site

+breaks decryption for reasons such as certificate pinning, unsupported

+ciphers, or mutual authentication). Apple Store, WhatsApp, Telegram,

+Microsoft Windows Update are common SSL pinning application. In case of

+traffic matches one more policy, bypass overrides intercept.

+

+When *Intercept Related Domains* is enabled, domains that share one

+certificates with the specified domain are considered as the same. For

+example, if the intercept facebook.com with I*ntercept Related Domain*

+option, then \*.xx.fbcdn.net, fb.com, .messenger.com and etc. are also

+intercepted. There may be side effects that intercept undesired websites

+that share one certificate. For example, two websites hosted in a same

+CDN provider (Content Delivery Network) or different products of one

+company.

+

+*Key ring* determines which certificate will be used to generate

+substitute certificate. You could configure key ring through *Proxy

+Policy Object* page. If no key ring is specified, proxy will use the

+default one.

+

+Intercept policy produces no log. You can find out if the interception

+is successful by checking if the certificate is issued by your

+pre-configured Root CA. You need a PC which traffic has already directed

+to the Proxy, and a web browser to test the policy. For Chrome and

+Microsoft Internet Explorer, you could click the lock icon on the

+address bar to view certificate. For Firefox, after you clicking the

+lock icon, click ��\>�� button to show connection details, click ��more

+information��, and then click ��view certificate��. If the browser warning

+that the connection is not secure, one possible reason is you haven��t

+install/trust the root certificate yet.

+

+**Note:** You should exercise caution because web applications may not

+cooperate with SSL interception. Reasons that sites break decryption

+technically include pinned certificates, mutual authentication,

+incomplete certificate chains, unsupported ciphers, and non-standard SSL

+implementation. If a site uses an incomplete certificate chain, the

+National Proxy doesn��t automatically fix the chain as a browser would.

+You need to manually download the missing sub-CA certificates and load

+and deploy them onto the proxy.', null);

+INSERT INTO `help_document` VALUES ('4', 'proxy_policy_object.md', 'Proxy Policy Object

+

+A policy object is a single object or a collective unit that groups

+discrete identities such as IP addresses, URLs, applications, or users.

+With policy objects that are a collective unit, you can reference the

+object in policy instead of manually selecting multiple objects one at a

+time. Typically, when creating a policy object, you group objects that

+require similar permissions in policy.

+

+1.  Key Ring

+

+On National Proxy System, Key Ring is a pair of private key and public

+certificate. You can also import a certificate chain containing multiple

+certificates. Key Ring is a policy object, you can reference it in

+*Intercept Policy*.

+

+There are three *Certificate Type:*

+

+*End-entity Certificate*�� is used for web servers to identify

+themselves. The *Public Key File* MUST be .p12 format that contains

+entire certificate chain. The Private Key File could be .pem, .key or

+.p12 format. This certificate type is not applicable to *Domain

+Intercept* for it cannot be used to sign other certificates. *Expire

+After* parameter is also not applicable to end-entity certificate for

+the same reason.

+

+*Intermedia Certificate* -  is used to sign other certificates. An

+intermediate certificate must be signed by another intermediate

+certificate, or a root certificate. The *Public Key File* MUST be .p12

+format that contains entire certificate chain. The *Expire After*

+parameter indicates the expiration of the substitute certificate that

+was issued by this intermedia certificate.

+

+*Root Certificate* -  is used to sign other certificates. The *Public Key

+File* could be .der, .cer, .crt or .pem format. The *Expire After*

+parameter has the same meaning as Intermedia Certificate.

+

+*CRL* -  or Certificate Revocation List, is a list of digital

+certificates that have been revoked by the issuing certificate authority

+(CA) before their scheduled expiration date and should no longer be

+trusted. On Key Ring settings, CRL is an HTTP URL that point to a valid

+.crl file. Invalid URL or .crl file may produce certificate warnings on

+some browser, i.e. Internet Explorer 11.

+

+Specification of certificate formats:

+

+*.pem* �C (Privacy-enhanced Electronic Mail) Base64 encoded DER

+certificate, enclosed between "-----BEGIN CERTIFICATE-----" and

+"-----END CERTIFICATE-----"

+

+*.cer, .crt, .der* �C usually in binary DER form, but Base64-encoded

+certificates are common too (see .pem above)

+

+*.p12* �C PKCS\#12, may contain certificate(s) (public) and private keys

+(without password protected)

+

+1.  Trusted Certificate

+

+National Proxy System has a build-in trusted certificate authorities

+list. When the original certificate is issued by a certificate authority

+that not in the list, the proxy will issued the substitute certificate

+with an untrusted root certificate, and so consequently, the browser

+could identify unsecure connections.

+

+You can add a custom certificate authority to the trusted certificate

+authorities of the system.

+

+The certificate MUST be PEM format.

+

+Following are the National Proxy System��s default trusted certificate

+authorities:

+

+ACCVRAIZ1

+

+Actalis Authentication Root CA

+

+AddTrust External CA Root

+

+AffirmTrust Commercial

+

+AffirmTrust Networking

+

+AffirmTrust Premium

+

+AffirmTrust Premium ECC

+

+Amazon Root CA 1

+

+Amazon Root CA 2

+

+Amazon Root CA 3

+

+Amazon Root CA 4

+

+Atos TrustedRoot 2011

+

+Autoridad de Certificacion Firmaprofesional CIF A62634068

+

+Baltimore CyberTrust Root

+

+Buypass Class 2 Root CA

+

+Buypass Class 3 Root CA

+

+CA Disig Root R2

+

+CFCA EV ROOT

+

+COMODO Certification Authority

+

+COMODO ECC Certification Authority

+

+COMODO RSA Certification Authority

+

+Certigna

+

+Certinomis - Root CA

+

+Class 2 Primary CA

+

+Certplus Root CA G1

+

+Certplus Root CA G2

+

+Certum Trusted Network CA

+

+Certum Trusted Network CA 2

+

+Chambers of Commerce Root - 2008

+

+AAA Certificate Services

+

+Cybertrust Global Root

+

+D-TRUST Root Class 3 CA 2 2009

+

+D-TRUST Root Class 3 CA 2 EV 2009

+

+DST Root CA X3

+

+Deutsche Telekom Root CA 2

+

+DigiCert Assured ID Root CA

+

+DigiCert Assured ID Root G2

+

+DigiCert Assured ID Root G3

+

+DigiCert Global Root CA

+

+DigiCert Global Root G2

+

+DigiCert Global Root G3

+

+DigiCert High Assurance EV Root CA

+

+DigiCert Trusted Root G4

+

+E-Tugra Certification Authority

+

+EC-ACC

+

+EE Certification Centre Root CA

+

+Entrust.net Certification Authority (2048)

+

+Entrust Root Certification Authority

+

+Entrust Root Certification Authority - EC1

+

+Entrust Root Certification Authority - G2

+

+GDCA TrustAUTH R5 ROOT

+

+GeoTrust Global CA

+

+GeoTrust Primary Certification Authority

+

+GeoTrust Primary Certification Authority - G2

+

+GeoTrust Primary Certification Authority - G3

+

+GeoTrust Universal CA

+

+GeoTrust Universal CA 2

+

+GlobalSign

+

+GlobalSign

+

+GlobalSign Root CA

+

+GlobalSign

+

+GlobalSign

+

+Global Chambersign Root - 2008

+

+Go Daddy Root Certificate Authority - G2

+

+Hellenic Academic and Research Institutions ECC RootCA 2015

+

+Hellenic Academic and Research Institutions RootCA 2011

+

+Hellenic Academic and Research Institutions RootCA 2015

+

+Hongkong Post Root CA 1

+

+ISRG Root X1

+

+IdenTrust Commercial Root CA 1

+

+IdenTrust Public Sector Root CA 1

+

+Izenpe.com

+

+LuxTrust Global Root 2

+

+Microsec e-Szigno Root CA 2009

+

+NetLock Arany (Class Gold) F?tan��s��tv��ny

+

+Network Solutions Certificate Authority

+

+OISTE WISeKey Global Root GA CA

+

+OISTE WISeKey Global Root GB CA

+

+OpenTrust Root CA G1

+

+OpenTrust Root CA G2

+

+OpenTrust Root CA G3

+

+QuoVadis Root Certification Authority

+

+QuoVadis Root CA 1 G3

+

+QuoVadis Root CA 2

+

+QuoVadis Root CA 2 G3

+

+QuoVadis Root CA 3

+

+QuoVadis Root CA 3 G3

+

+SSL.com EV Root Certification Authority ECC

+

+SSL.com EV Root Certification Authority RSA R2

+

+SSL.com Root Certification Authority ECC

+

+SSL.com Root Certification Authority RSA

+

+SZAFIR ROOT CA2

+

+SecureSign RootCA11

+

+SecureTrust CA

+

+Secure Global CA

+

+Sonera Class2 CA

+

+Staat der Nederlanden EV Root CA

+

+Staat der Nederlanden Root CA - G2

+

+Staat der Nederlanden Root CA - G3

+

+Starfield Root Certificate Authority - G2

+

+Starfield Services Root Certificate Authority - G2

+

+SwissSign Gold CA - G2

+

+SwissSign Silver CA - G2

+

+T-TeleSec GlobalRoot Class 2

+

+T-TeleSec GlobalRoot Class 3

+

+TUBITAK Kamu SM SSL Kok Sertifikasi - Surum 1

+

+TWCA Global Root CA

+

+TWCA Root Certification Authority

+

+TeliaSonera Root CA v1

+

+TrustCor ECA-1

+

+TrustCor RootCert CA-1

+

+TrustCor RootCert CA-2

+

+T��RKTRUST Elektronik Sertifika Hizmet Sa?lay?c?s? H5

+

+USERTrust ECC Certification Authority

+

+USERTrust RSA Certification Authority

+

+VeriSign Class 3 Public Primary Certification Authority - G4

+

+VeriSign Class 3 Public Primary Certification Authority - G5

+

+VeriSign Universal Root Certification Authority

+

+VeriSign Class 3 Public Primary Certification Authority - G3

+

+Visa eCommerce Root

+

+XRamp Global Certification Authority

+

+thawte Primary Root CA

+

+thawte Primary Root CA - G2

+

+thawte Primary Root CA - G3

+

+Microsoft Root Authority

+

+Microsoft Root Certificate Authority

+

+Microsoft Root Certificate Authority 2010

+

+Microsoft Root Certificate Authority 2011

+

+Baltimore CyberTrust Root

+

+', null);

+

+commit;

+

+

diff --git a/src/main/resources/sql/deploymentsql/20190508other_sql.sql b/src/main/resources/sql/deploymentsql/20190508other_sql.sql
new file mode 100644
index 000000000..ab782c4a5
--- /dev/null
+++ b/src/main/resources/sql/deploymentsql/20190508other_sql.sql
@@ -0,0 +1,191 @@
+CREATE TABLE `inner_protection_list` (

+  `id` int(11) NOT NULL AUTO_INCREMENT,

+  `keyword` varchar(500) NOT NULL COMMENT '�ؼ���',

+  `target_type` varchar(128) DEFAULT '' COMMENT '����',

+  `description` varchar(200) DEFAULT '',

+  `is_valid` int(2) NOT NULL DEFAULT 0 COMMENT '-1ɾ�� 1��Ч',

+  `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

+  `editor_id` int(11) DEFAULT NULL,

+  `creator_id` int(11) NOT NULL,

+  `edit_time` datetime DEFAULT NULL,

+  PRIMARY KEY (`id`)

+) ENGINE=InnoDB AUTO_INCREMENT=108 DEFAULT CHARSET=utf8mb4;

+INSERT INTO `inner_protection_list` VALUES ('104', '.com', 'domainCheck', 'protection domain', '1', '2019-03-26 13:54:28', '1', '1', '2019-04-02 11:16:36');

+INSERT INTO `inner_protection_list` VALUES ('106', '.com', 'urlCheck', 'protection url', '1', '2019-03-27 11:08:24', '1', '1', '2019-04-02 11:21:52');

+commit;

+

+

+DROP PROCEDURE IF EXISTS `proc_statistics_config`;

+delimiter ;;

+CREATE DEFINER=`root`@`%` PROCEDURE `proc_statistics_config`()

+BEGIN

+

+	DECLARE ntime VARCHAR(40);/*��ǰʱ��*/

+

+	DECLARE otime VARCHAR(40);/*�ϴ�ͳ��ʱ��*/

+

+	DECLARE nRow VARCHAR(40);/*����ͳ������*/

+

+	DECLARE tabName VARCHAR(500);

+

+	DECLARE description VARCHAR(500);

+

+	DECLARE deleteSql VARCHAR(500);

+

+	DECLARE done INT;/*�α��ʶ*/

+

+	DECLARE flag INT;/*ѭ����ʶ*/

+

+	DECLARE t_error INT;/*�����ʶ*/

+

+	DECLARE proc_log_table VARCHAR(100);/*�洢������־��*/

+

+	DECLARE proc_name VARCHAR(100);/*�洢��������*/

+

+	DECLARE icursor CURSOR FOR SELECT  tab_name FROM statistics_tables where is_valid=1;

+

+	DECLARE CONTINUE HANDLER FOR NOT found SET done=1;

+

+	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION set t_error=1;

+

+	select max(statistic_time) into otime from cfg_num_statistics;

+

+	SET done=0;

+

+	SET t_error=0;

+

+	SET proc_log_table='proc_exec_log';

+

+	SET proc_name='proc_statistics_config';

+

+	SET ntime=DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:%S');

+

+	OPEN icursor;

+

+		loop_iloop:LOOP

+

+			FETCH icursor INTO tabName;

+

+			SET description=tabName;

+

+			set @descriptionStart=concat(description,'��ͳ��start');

+

+			/*ͳ�Ƶ�ǰ���ñ����ݵ�ͳ�Ʊ���start*/

+

+			set @v_log_sql1 := concat('insert into ',proc_log_table,'(proc_name,table_name,log_time,description) values(?,?,?,?)');

+

+			PREPARE  execs FROM @v_log_sql1;

+

+			EXECUTE   execs using proc_name,proc_log_table,ntime,@descriptionStart;

+

+			DEALLOCATE PREPARE execs;

+

+			COMMIT;

+

+

+			#20190328 ���Ӷ�ʱ�����is_audit=1 && (is_valid=1 || is_valid=0) ��Ϊapproved ������cfg_state=4[is_valid=0 && is_audit=1],������ʱ������˹��ģ�����cfg_state=1�����������ͨ������Ч�ģ�����չʾ��approvedֵʵ�ʰ���cfg_state=4��cfg_state=1

+			set @insert_statistics_sql := concat('insert into cfg_num_statistics(statistic_time,audit_time,function_id,service_id,action,compile_id,cfg_state) select  ','''',ntime,'''',',','audit_time,function_id,service_id,action,compile_id,if(is_valid=-1,-1,if(is_valid=1,1,if(is_audit=0,0,if(is_audit=1,4,if(is_audit=2,2,if(is_audit=3,3,0)))))) cfg_state from ',tabName);

+

+

+			PREPARE  execs FROM @insert_statistics_sql;

+

+			EXECUTE   execs;

+

+			DEALLOCATE PREPARE execs;

+

+			COMMIT;

+

+

+

+			set @descriptionEnd=concat(description,'��ͳ��end');

+

+			set @v_log_sql2 := concat('insert into ',proc_log_table,'(proc_name,table_name,log_time,description) values(?,?,?,?)');

+

+			PREPARE  execs FROM @v_log_sql2;

+

+			EXECUTE   execs using proc_name,proc_log_table,ntime,@descriptionEnd;

+

+			DEALLOCATE PREPARE execs;

+

+			COMMIT;

+

+			/*�쳣�˳�loop*/

+			IF t_error=1 THEN

+				LEAVE loop_iloop;

+			END IF;

+

+			/*ѭ�������˳�loop*/

+			IF done=1 THEN

+

+				LEAVE loop_iloop;

+

+			ELSE

+

+				SET flag=0;

+

+			END IF;

+

+			IF flag=0 THEN

+

+				SET done=0;

+

+			END IF;

+

+		END LOOP loop_iloop;

+

+	CLOSE icursor;

+	 /*ȡ������ͳ������*/

+	SELECT count(statistic_time) INTO nRow from cfg_num_statistics where statistic_time=ntime;

+	

+

+	IF t_error=1 THEN    /*����쳣�����������*/

+		delete from cfg_num_statistics where statistic_time=ntime;

+		COMMIT;

+	ELSEIF nRow > 0 THEN /*�жϱ���ͳ���Ƿ�������¼�룬�������ɾ���ϴ�ͳ�����ݣ����û��������ϴ�ͳ������*/

+

+		delete from cfg_num_statistics where statistic_time=otime;

+		COMMIT;

+	END IF;

+	COMMIT;

+END

+;;

+delimiter ;

+

+#ִ�д洢����

+call exec_procs();

+

+-- ------------

+-- ��ʱ��������һ��type���ԣ��������޸ģ��޸�ȫ��ͬ��״̬Ϊ3(�����)

+-- ------------

+ALTER TABLE schedule_cfg ADD  type int(1) DEFAULT 1 COMMENT '1:��ʱ����2��ȫ��ͬ��ʱδִ�е�����';

+update schedule_cfg set type=1;

+update sys_data_dictionary_item t set t.item_value=3  where t.dictionary_id=142;

+

+-- ------------

+-- ϵͳ����->����Ա�û�Ԥ��

+-- ------------

+INSERT INTO `sys_menu` (`parent_id`, `parent_ids`, `code`, `name`, `sort`, `href`, `target`, `icon`, `is_show`, `permission`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`, `del_flag`, `menu_bg`, `quick_action`, `is_top`, `function_id`) VALUES ('2', '0,1,2,', 'admin_user_warn', 'admin user warn', '5060', '/sys/warn/userWarnList', '', '', '1', 'sys:warnList:view', '1', '2019-03-28 10:06:26', '1', '2019-03-28 11:36:42', '', '1', NULL, '0', '0', NULL);

+

+CREATE TABLE `sys_user_warn` (

+  `service_id` int(11) NOT NULL COMMENT 'ҵ��ID',

+  `service_desc` varchar(255) DEFAULT '' COMMENT 'ҵ������',

+  `interface_cfg_total` int(11) DEFAULT NULL COMMENT '������������',

+  `system_cfg_total` int(11) DEFAULT NULL COMMENT '������������',

+  `time` datetime DEFAULT NULL COMMENT '���ݲ���ʱ��',

+  `remark` varchar(255) DEFAULT NULL COMMENT '��ע',

+  PRIMARY KEY (`service_id`)

+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='����Ա�û�Ԥ����';

+-- ������������

+INSERT INTO `sys_menu` (`parent_id`, `parent_ids`, `code`, `name`, `sort`, `href`, `target`, `icon`, `is_show`, `permission`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`, `del_flag`, `menu_bg`, `quick_action`, `is_top`, `function_id`) VALUES ('109', '0,1,109,', 'protection_list_manage', 'protection list manage', '5060', '/basics/innerProtectionList/list', '', 'fa fa-sticky-note-o', '1', 'basic:inner_protection_list:list', '1', '2019-03-25 15:28:34', '1', '2019-03-26 15:04:47', '', '1', NULL, '0', '0', NULL);

+	

+-- ------------

+-- ��˲��·����ܣ�ֻ�н��˲˵���������沢�·��û��Ľ�ɫ���˽�ɫ�µ��û����������ÿ�ֱ���·����˰汾ע���ɫ���䣩

+-- ������˲��·��˵�

+-- ------------

+INSERT INTO `sys_menu`(`parent_id`, `parent_ids`, `code`, `name`, `sort`, `href`, `target`, `icon`, `is_show`, `permission`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`, `del_flag`, `menu_bg`, `quick_action`, `is_top`, `function_id`) VALUES (86, '0,1,86,', 'save_and_audit', 'save and audit', 5060, '', '', '', 0, 'save:audit:permission', '1', '2019-04-08 11:30:31', '1', '2019-04-08 11:47:16', 'save_and_audit', 1, NULL, 0, 0, NULL);

+-- ������˲��·���ɫ

+INSERT INTO `sys_role`(`NAME`, `DATA_SCOPE`, `REMARK`, `STATUS`, `CREATE_TIME`, `ROLE_TYPE`) VALUES ('saveAndAudit', 1, '', 1, '2019-04-09 11:33:40', 'user');

+-- ����ΪsaveAndAudit��ɫ����save_and_audit�˵���

+insert into  sys_privilege (PRIVILEGE_MASTER,PRIVILEGE_MASTER_VALUE,PRIVILEGE_ACCESS,PRIVILEGE_ACCESS_VALUE,PRIVILEGE_OPERATION )  select 'ROLE',r.id,1000,m.id,1 from sys_role r,sys_menu m where r.name='saveAndAudit' and m.code='save_and_audit'

+

+

diff --git a/src/main/resources/sql/deploymentsql/extends_ip_port_pattern.sql b/src/main/resources/sql/deploymentsql/extends_ip_port_pattern.sql
new file mode 100644
index 000000000..d95a0a3be
--- /dev/null
+++ b/src/main/resources/sql/deploymentsql/extends_ip_port_pattern.sql
@@ -0,0 +1,104 @@
+#function_region_dict 对应ip_pattern，port_pattern的字段长度拓展一倍，使用分号分隔源/目的

+ALTER TABLE function_region_dict MODIFY config_ip_pattern VARCHAR(20) COMMENT "ip的格式 1:ip掩码;2:IP范围;3:IP;使用逗号分隔,源ip与目的IP使用;分隔";

+ALTER TABLE function_region_dict MODIFY config_port_pattern VARCHAR(20) COMMENT "端口的格式，1:port;2:port_mask;使用逗号分隔,源端口与目的端口使用;分隔";

+#各表修改ip_pattern，port_pattern

+#app_ip_cfg

+ALTER TABLE app_ip_cfg CHANGE ip_pattern src_ip_pattern INT COMMENT '源ip格式';

+ALTER TABLE app_ip_cfg ADD dest_ip_pattern INT COMMENT '目的ip格式';

+ALTER TABLE app_ip_cfg CHANGE port_pattern src_port_pattern INT COMMENT '源端口格式';

+ALTER TABLE app_ip_cfg ADD dest_port_pattern INT COMMENT '目的端口格式';

+#app_ip_range_cfg

+ALTER TABLE app_ip_range_cfg CHANGE ip_pattern src_ip_pattern INT COMMENT '源ip格式';

+ALTER TABLE app_ip_range_cfg ADD dest_ip_pattern INT COMMENT '目的ip格式';

+ALTER TABLE app_ip_range_cfg CHANGE port_pattern src_port_pattern INT COMMENT '源端口格式';

+ALTER TABLE app_ip_range_cfg ADD dest_port_pattern INT COMMENT '目的端口格式';

+#area_ip_cfg

+ALTER TABLE area_ip_cfg CHANGE ip_pattern src_ip_pattern INT COMMENT '源ip格式';

+ALTER TABLE area_ip_cfg ADD dest_ip_pattern INT COMMENT '目的ip格式';

+ALTER TABLE area_ip_cfg CHANGE port_pattern src_port_pattern INT COMMENT '源端口格式';

+ALTER TABLE area_ip_cfg ADD dest_port_pattern INT COMMENT '目的端口格式';

+#asn_ip_cfg

+ALTER TABLE asn_ip_cfg CHANGE ip_pattern src_ip_pattern INT COMMENT '源ip格式';

+ALTER TABLE asn_ip_cfg ADD dest_ip_pattern INT COMMENT '目的ip格式';

+ALTER TABLE asn_ip_cfg CHANGE port_pattern src_port_pattern INT COMMENT '源端口格式';

+ALTER TABLE asn_ip_cfg ADD dest_port_pattern INT COMMENT '目的端口格式';

+#av_cont_ip_cfg

+ALTER TABLE av_cont_ip_cfg change ip_pattern src_ip_pattern int COMMENT '源ip格式';

+ALTER TABLE av_cont_ip_cfg add dest_ip_pattern INT COMMENT '目的ip格式';

+ALTER TABLE av_cont_ip_cfg CHANGE port_pattern src_port_pattern INT COMMENT '源端口格式';

+ALTER TABLE av_cont_ip_cfg ADD dest_port_pattern INT COMMENT '目的端口格式';

+#av_pic_ip_cfg

+ALTER TABLE av_pic_ip_cfg CHANGE ip_pattern src_ip_pattern INT COMMENT '源ip格式';

+ALTER TABLE av_pic_ip_cfg ADD dest_ip_pattern INT COMMENT '目的ip格式';

+ALTER TABLE av_pic_ip_cfg CHANGE port_pattern src_port_pattern INT COMMENT '源端口格式';

+ALTER TABLE av_pic_ip_cfg ADD dest_port_pattern INT COMMENT '目的端口格式';

+#av_voip_ip_cfg

+ALTER TABLE av_voip_ip_cfg CHANGE ip_pattern src_ip_pattern INT COMMENT '源ip格式';

+ALTER TABLE av_voip_ip_cfg ADD dest_ip_pattern INT COMMENT '目的ip格式';

+ALTER TABLE av_voip_ip_cfg CHANGE port_pattern src_port_pattern INT COMMENT '源端口格式';

+ALTER TABLE av_voip_ip_cfg ADD dest_port_pattern INT COMMENT '目的端口格式';

+#ddos_ip_cfg

+ALTER TABLE ddos_ip_cfg CHANGE ip_pattern src_ip_pattern INT COMMENT '源ip格式';

+ALTER TABLE ddos_ip_cfg ADD dest_ip_pattern INT COMMENT '目的ip格式';

+ALTER TABLE ddos_ip_cfg CHANGE port_pattern src_port_pattern INT COMMENT '源端口格式';

+ALTER TABLE ddos_ip_cfg ADD dest_port_pattern INT COMMENT '目的端口格式';

+#dns_ip_cfg

+ALTER TABLE dns_ip_cfg CHANGE ip_pattern src_ip_pattern INT COMMENT '源ip格式';

+ALTER TABLE dns_ip_cfg ADD dest_ip_pattern INT COMMENT '目的ip格式';

+ALTER TABLE dns_ip_cfg CHANGE port_pattern src_port_pattern INT COMMENT '源端口格式';

+ALTER TABLE dns_ip_cfg ADD dest_port_pattern INT COMMENT '目的端口格式';

+#ip_port_cfg

+ALTER TABLE ip_port_cfg CHANGE ip_pattern src_ip_pattern INT COMMENT '源ip格式';

+ALTER TABLE ip_port_cfg ADD dest_ip_pattern INT COMMENT '目的ip格式';

+ALTER TABLE ip_port_cfg CHANGE port_pattern src_port_pattern INT COMMENT '源端口格式';

+ALTER TABLE ip_port_cfg ADD dest_port_pattern INT COMMENT '目的端口格式';

+#ip_reuse_ip_cfg

+ALTER TABLE ip_reuse_ip_cfg CHANGE ip_pattern src_ip_pattern INT COMMENT '源ip格式';

+ALTER TABLE ip_reuse_ip_cfg ADD dest_ip_pattern INT COMMENT '目的ip格式';

+ALTER TABLE ip_reuse_ip_cfg CHANGE port_pattern src_port_pattern INT COMMENT '源端口格式';

+ALTER TABLE ip_reuse_ip_cfg ADD dest_port_pattern INT COMMENT '目的端口格式';

+#ip_reuse_policy_cfg

+ALTER TABLE ip_reuse_policy_cfg CHANGE ip_pattern src_ip_pattern INT COMMENT '源ip格式';

+ALTER TABLE ip_reuse_policy_cfg ADD dest_ip_pattern INT COMMENT '目的ip格式';

+#修改字典的值

+UPDATE function_region_dict SET config_ip_pattern ="1,2,3;1,2,3" WHERE config_ip_pattern="1,2,3";

+UPDATE function_region_dict SET config_ip_pattern ="1;1" WHERE config_ip_pattern="1";

+UPDATE function_region_dict SET config_ip_pattern ="3;3" WHERE config_ip_pattern="3";

+UPDATE function_region_dict SET config_ip_pattern ="1,3;1,3" WHERE config_ip_pattern="1,3";

+

+UPDATE function_region_dict SET config_port_pattern ="1;1" WHERE config_port_pattern="1";

+UPDATE function_region_dict SET config_port_pattern ="1,2;1,2" WHERE config_port_pattern="1,2";

+#Spoofing IP修改只显示目的IP

+UPDATE function_region_dict SET config_ip_port_show=3 WHERE function_id=401;

+#ASN IP修改只显示目的IP

+UPDATE function_region_dict SET config_ip_port_show=3 WHERE function_id=600;

+#sql 更新字段语句

+UPDATE app_ip_cfg c SET c.dest_ip_pattern =(SELECT b.src_ip_pattern FROM ( SELECT a.src_ip_pattern,a.`cfg_id` FROM app_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+UPDATE app_ip_cfg c SET c.dest_port_pattern =(SELECT b.src_port_pattern FROM ( SELECT a.src_port_pattern,a.`cfg_id` FROM app_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+

+UPDATE app_ip_range_cfg c SET c.dest_ip_pattern =(SELECT b.src_ip_pattern FROM ( SELECT a.src_ip_pattern,a.`cfg_id` FROM app_ip_range_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+UPDATE app_ip_range_cfg c SET c.dest_port_pattern =(SELECT b.src_port_pattern FROM ( SELECT a.src_port_pattern,a.`cfg_id` FROM app_ip_range_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+

+UPDATE area_ip_cfg c SET c.dest_ip_pattern =(SELECT b.src_ip_pattern FROM ( SELECT a.src_ip_pattern,a.`cfg_id` FROM area_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+UPDATE area_ip_cfg c SET c.dest_port_pattern =(SELECT b.src_port_pattern FROM ( SELECT a.src_port_pattern,a.`cfg_id` FROM area_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+

+UPDATE asn_ip_cfg c SET c.dest_ip_pattern =(SELECT b.src_ip_pattern FROM ( SELECT a.src_ip_pattern,a.`cfg_id` FROM asn_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+UPDATE asn_ip_cfg c SET c.dest_port_pattern =(SELECT b.src_port_pattern FROM ( SELECT a.src_port_pattern,a.`cfg_id` FROM asn_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+

+UPDATE av_cont_ip_cfg c SET c.dest_ip_pattern =(SELECT b.src_ip_pattern FROM ( SELECT a.src_ip_pattern,a.`cfg_id` FROM av_cont_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+UPDATE av_cont_ip_cfg c SET c.dest_port_pattern =(SELECT b.src_port_pattern FROM ( SELECT a.src_port_pattern,a.`cfg_id` FROM av_cont_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+

+UPDATE av_pic_ip_cfg c SET c.dest_ip_pattern =(SELECT b.src_ip_pattern FROM ( SELECT a.src_ip_pattern,a.`cfg_id` FROM av_pic_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+UPDATE av_pic_ip_cfg c SET c.dest_port_pattern =(SELECT b.src_port_pattern FROM ( SELECT a.src_port_pattern,a.`cfg_id` FROM av_pic_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+

+UPDATE av_voip_ip_cfg c SET c.dest_ip_pattern =(SELECT b.src_ip_pattern FROM ( SELECT a.src_ip_pattern,a.`cfg_id` FROM av_voip_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+UPDATE av_voip_ip_cfg c SET c.dest_port_pattern =(SELECT b.src_port_pattern FROM ( SELECT a.src_port_pattern,a.`cfg_id` FROM av_voip_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+

+UPDATE ddos_ip_cfg c SET c.dest_ip_pattern =(SELECT b.src_ip_pattern FROM ( SELECT a.src_ip_pattern,a.`cfg_id` FROM ddos_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+UPDATE ddos_ip_cfg c SET c.dest_port_pattern =(SELECT b.src_port_pattern FROM ( SELECT a.src_port_pattern,a.`cfg_id` FROM ddos_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+

+UPDATE dns_ip_cfg c SET c.dest_ip_pattern =(SELECT b.src_ip_pattern FROM ( SELECT a.src_ip_pattern,a.`cfg_id` FROM dns_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+UPDATE dns_ip_cfg c SET c.dest_port_pattern =(SELECT b.src_port_pattern FROM ( SELECT a.src_port_pattern,a.`cfg_id` FROM dns_ip_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+

+UPDATE ip_port_cfg c SET c.dest_ip_pattern =(SELECT b.src_ip_pattern FROM ( SELECT a.src_ip_pattern,a.`cfg_id` FROM ip_port_cfg a) b WHERE b.cfg_id=c.`cfg_id`);

+UPDATE ip_port_cfg c SET c.dest_port_pattern =(SELECT b.src_port_pattern FROM ( SELECT a.src_port_pattern,a.`cfg_id` FROM ip_port_cfg a) b WHERE b.cfg_id=c.`cfg_id`);
\ No newline at end of file