SQL Hardening (i) insurance business

Insurance Business:

Table structure:

SQL statements:
/*1. According to the applicant's telephone query
Insured name ID number all policy number insurance Payment type */
SELECT
T2.cust_name,
T2.idcard,
T4.PRO_ID,
T5.pay_type_name,
T6.protype_name
From
Contacts T1,--Contact form
Customer T2,--Client table
Holder T3,--
product T4,
Pay_type T5,
Protype T6
WHERE
T1.contact_text = ' 15987654565 '
and t1.cust_id = t2.cust_id
and t3.cust_id = t2.cust_id
and t3.pro_id = t4.pro_id
and T4.pay_type = t5.pay_type_id
and T4.pro_type = t6.protype_id

/*
2. Check the policy number according to the policy number of policy insurance
Name of insured person's name, ID card and beneficiary's ID card
*/
SELECT
T1.PRO_ID,
T1.pro_type,
C1.cust_name,
C1.idcard,
C2.cust_name,
C2.idcard,
C3.cust_name,
C3.idcard
From
Product T1,
Holder T2,
insurer T3,
benefit T4,
Customer C1,
Customer C2,
Customer C3
WHERE
t1.pro_id = ' 1100012313441122 '
and t1.pro_id = t2.pro_id
and t2.cust_id = c1.cust_id
and t1.pro_id = t3.pro_id
and t3.cust_id = c2.cust_id
and t1.pro_id = t4.pro_id
and t4.bene_id = c3.cust_id

/*
4. Find the policy for all insured beneficiaries who are the same person
*/
SELECT * FROM product t1, Holder T2, insurer T3, benefit T4
where t1.pro_id = t2.pro_id
and t1.pro_id = t3.pro_id
and t1.pro_id = t4.pro_id
and t2.cust_id = t3.cust_id
and t3.cust_id = t4.bene_id

/*
6 Find the top three insurance policies in each Shang (group back order) */
SELECT * FROM (
Select Row_number () over (partition by Pro_type ORDER BY premium DESC) RN,
Product.* from Product
) Where rn<=3

//============================================

--A comparison of time
SELECT * FROM product where Pro_start_date < To_date (' 2017-04-10 23:12:12 ', ' yyyy-mm-dd hh24:mi:ss ')
/* Conversions on time and string

MM Month note not mm
Mi min

Hh24 24-Hour Hour
HH 12-Hour Hour

String cannot exceed the length of the format
*/
View
A: What is a view

Views, also known as virtual tables, do not occupy physical space.
Views are only logically defined. Every time you use it, just re-execute the SQL.
A view can socialize multiple table queries each table is called the base table
Modifying the Data base table of a view also changes the data

Second: The role of the attempt
1. Simplify your query
2. Avoiding Sensitive columns
3. Simplified rights Management adds security

Three: Create view user needs to have CREATE VIEW permission grant create view to USERNAME;
--Must use administrator privileges when authorizing

Four syntax:
create [or replace] [force] view [schema.] View_name
[(Column1,column2,...)]
as 
Select ...
[with CHECK option] [constraint constraint_name]
[with Read Only];
Tips:
1 or replace: If there is a view with the same name, use the new view instead of the existing View
2 force: Enforce to create the view regardless of whether the base table exists or whether you have permission to use the base table
3 Column1,column2 ,... : The column name of the view must be the same as the number of columns in the select query;
If the select query contains a function or expression, you must define a column name for the  . At this point, you can specify the column name with Column1, Column2, You can also specify the column name in the select query.
4 with CHECK option: Specifies that the DML operation performed on the view must satisfy the condition of the "views subquery", that is, to "check" the delete and deletion changes through the view,
the data that requires additions and deletions, must be the data that the select query can query, Otherwise, the action is not allowed and an error prompt is returned. By default,  
does not check if the rows can be retrieved by the select query before adding or removing them.  
5 with Read only: Views created can only be used for querying data, not for changing data.

Five: Example:
CREATE VIEW Customer_contact_inner
As
Select T1.cust_name CustName,
t1.cust_id CustID,
T1.idcard Idcard,
T1.age Age,
T1.sex Sex,
T1.brith Birth,
T2.contact_type ContactType,
T2.contact_text text
From customer T1, contact T2
where t1.cust_id = t2.cust_id

SELECT * from customer_contact_inner where CustName = ' Zhang '
This reduces the logical statement of the query

SQL Hardening (i) insurance business