Обеспечение безопасности БД

Содержание
1.Постановка задачи 4
1.1 Общие требования 4
1.2 Требования к БД 4
1.3 Требования к витринам данных 4
1.3.1 Витрина «Кредитные договора» 5
1.3.2 Витрина «Клиенты физические лица» 7
1.3.3 Витрина «Клиенты малый и микро бизнес» 8
1.3.4 Витрина «Роль клиента в договоре» 10
1.3.5 Витрина «События» 10
1.3.6 Витрина «Результаты работы» 11
1.3.7 Витрина «Изменение баланса» 13
1.3.8 Витрина «Платежи» 14
1.3.9 Витрина «Обеспечение» 15
1.3.10 Витрина «Портфель» 16
1.3.11 Витрина «Взыскание» 19
1.3.12 Витрина «История стадий взыскания» 23
1.3.13 Витрина «История сегментов взыскания» 24
1.4 Требования к выгрузке данных 26
1.5 Требования к безопасности 26
2. Архитектура решения 28
2.1 Основные объекты архитектуры 28
2.2 Архитектура проекта 28
2.2.1 Описание данных 28
2.2.2 Описание рабочих процессов по преобразованию потоков 28
2.2.3 Описание транзакций 29
3. Технологический раздел 30
3.1 Описание реализации безопасности 30
3.1.1 Шифрование данных 30
3.1.2 Валидация 30
3.2 Описание реализации витрин данных 31
3.3 Описание порядка действий по внесению изменений в данные 32
3.4 Порядок выгрузки данных 33
Вывод 34
Список использованной литературы 35
Приложения 36

Text = "Add new payment"; butAddpayment.Width = 150; butAddpayment.Location = new Point(f2.Width / 2 - butAddpayment.Width / 2 - 190, 291); butAddpayment.Click += new EventHandler(butAddpayclick); butFilterData.Click += new EventHandler(butFilterdataClick); imgBut.Click += new EventHandler(butExport); butRegister.Text = "Register new user"; butRegister.Width = 150; butRegister.Location = new Point(f2.Width / 2 - butAddpayment.Width / 2 - 190, 441); butRegister.Click += new EventHandler(butRegisterClick); //butFilterData.Click += new EventHandler(butFilterdataClick); imgBut.Click += new EventHandler(butExport);if (role != 1) { butSign.Enabled = false; } butSign.Text = "Sign payment"; butSign.Width = 150; butSign.Location = new Point(f2.Width / 2 - butAddpayment.Width / 2, 441); butSign.Click += new EventHandler(butRegisterClick); butVitr1.Text = "Показатьвитрину 1"; butVitr1.Width = 120; butVitr1.Location = new Point(f2.Width / 2 - butAddpayment.Width / 2 + 180, 441); butVitr1.Click += new EventHandler(butVitr1Click); butVitr2.Text = "Показатьвитрину 2"; butVitr2.Width = 120; butVitr2.Location = new Point(f2.Width / 2 - butAddpayment.Width / 2 + 320, 441); butVitr2.Click += new EventHandler(butVitr2Click); butVitr3.Text = "Показатьвитрину 3"; butVitr3.Width = 120; butVitr3.Location = new Point(f2.Width / 2 - butAddpayment.Width / 2 + 180, 481); butVitr3.Click += new EventHandler(butVitr3Click); butVitr4.Text = "Показатьвитрину 4"; butVitr4.Width = 120; butVitr4.Location = new Point(f2.Width / 2 - butAddpayment.Width / 2 + 40, 521); butVitr4.Click += new EventHandler(butVitr4Click); butVitr5.Text = "Показатьвитрину 5"; butVitr5.Width = 120; butVitr5.Location = new Point(f2.Width / 2 - butAddpayment.Width / 2 + 180, 521); butVitr5.Click += new EventHandler(butVitr5Click);butVitrAll.Text = "Показать все витрины";butVitrAll.Width = 120; butVitrAll.Location = new Point(f2.Width / 2 - butAddpayment.Width / 2 + 40, 481); butVitrAll.Click += new EventHandler(butVitrAllClick); //butFilterData.Click += new EventHandler(butFilterdataClick); imgBut.Click += new EventHandler(butExport);f2.Text = "Текущие платежи пользователя";f2.FormClosed += new FormClosedEventHandler(form2_FormClosed); /*DataGridView*/dgv = new DataGridView(); //f2.Controls.Add(dgv); /*DataSet*/myds = new DataSet(); /*MySqlDataAdapter*/ MysDataAdapter = new System.Data.SqlClient.SqlDataAdapter(); //MySqlDataAdapter. //MysDataAdapter.Connection = mycon; MysDataAdapter.SelectCommand = myCommand; //mycon.Open();MysDataAdapter.Fill(myds);if (myds.Tables.Count > 0) dgv.DataSource = myds.Tables[0]; dgv.Width = 800; dgv.Height = 300; dgv.Name = "MainGrid"; //dgv.RowTemplate.DefaultCellStyle.BackColor = Color.Transparent; /*dgv.Visible = true; dgv.ColumnCount = 3;dgv.Columns[0].Name = "Product ID";dgv.Columns[1].Name = "Product Name";dgv.Columns[2].Name = "Product Price";string[] row = new string[] { "1", "Product 1", "1000" };dgv.Rows.Add(row);row = new string[] { "2", "Product 2", "2000" };dgv.Rows.Add(row);row = new string[] { "3", "Product 3", "3000" };dgv.Rows.Add(row);row = new string[] { "4", "Product 4", "4000" };dgv.Rows.Add(row); dgv.VirtualMode = false;*/f2.Controls.Add(butAddpayment);f2.Controls.Add(butDelpayment);f2.Controls.Add(dgv);f2.Controls.Add(textbox1);f2.Controls.Add(label1);f2.Controls.Add(textbox2);f2.Controls.Add(label2);f2.Controls.Add(textbox3);f2.Controls.Add(label3);f2.Controls.Add(butCat);f2.Controls.Add(imgBut);f2.Controls.Add(butFilterData);f2.Controls.Add(butRegister);f2.Controls.Add(butSign);f2.Controls.Add(butVitr1);f2.Controls.Add(butVitr2);f2.Controls.Add(butVitr3);f2.Controls.Add(butVitr4);f2.Controls.Add(butVitr5);f2.Controls.Add(butVitrAll); this.Visible = false;f2.Show(); //this.Hide(); //List<string> d=myCommand.Parameters;//myConnection.Open(); //Устанавливаем соединение с базой данных. //Что то делаем... //mycon. mycon.Close(); //Обязательно закрываем соединение! // } /*catch (InvalidCastException e) { MessageBox.Show("Нед подключения к серверу" + e.Message);}*/return; }void butRegisterClick(object sender, EventArgs e) {f5.Show();return; }void butVitrAllClick(object sender, EventArgs e) { //if(frm5!=null)try {frm8.Show(); }catch (Exception e1) { frm8 = new Form8();frm8.Show(); } }void butVitr1Click(object sender, EventArgs e) { //if(frm5!=null)try {frm5.Show(); }catch (Exception e1) { frm5 = new Form5();frm5.Show(); } }void butVitr2Click(object sender, EventArgs e) { //if(frm5!=null)try {frm6.Show(); }catch (Exception e1) { frm6 = new Form6();frm6.Show(); } }void butVitr3Click(object sender, EventArgs e) { //if(frm5!=null)try {frm7.Show(); }catch (Exception e1) { frm7 = new Form7();frm7.Show(); } }void butVitr4Click(object sender, EventArgs e) { //if(frm5!=null)try {frm9.Show(); }catch (Exception e1) { frm9 = new Form9();frm9.Show(); } }void butVitr5Click(object sender, EventArgs e) { //if(frm5!=null)try {frm10.Show(); }catch (Exception e1) { frm10 = new Form10();frm10.Show(); } }void butCatclick(object sender, EventArgs e) { TextBox tb = (TextBox)GetControlByName2("TextBox10"); //TextBox tb1 = (TextBox)GetControlByName2("TextBox12"); //CommandText = "SELECT * FROM payment WHERE paymment_id=" + tb.Text+ ";"; CommandText = "SELECT payment.paymment_id,payment.date_of_create,category.cat_name,payment.name,payment.quantity,payment.cost,payment.valu,payment.sign FROM payment,category WHERE payment.category_id=category.cat_id AND category.cat_name='" + tb.Text + "';"; //MessageBox.Show(CommandText); SqlCommand myC = new SqlCommand(CommandText, mycon); myC.CommandText = CommandText; MysDataAdapter.SelectCommand = myC;myds.Clear();MysDataAdapter.Fill(myds);if (myds.Tables.Count > 0) { //dgv.Rows.Clear(); dgv.DataSource = myds.Tables[0];dgv.Refresh(); } }void butFilterdataClick(object sender, EventArgs e) { TextBox tb = (TextBox)GetControlByName2("TextBox11"); TextBox tb1 = (TextBox)GetControlByName2("TextBox12");string s1 = tb.Text;string s2 = tb1.Text; DateTime dt1 = Convert.ToDateTime(s1); IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true); DateTime dt2 = DateTime.Parse(s1, culture, System.Globalization.DateTimeStyles.AssumeLocal);string s3 = dt2.Year.ToString() + "-" + dt2.Month.ToString() + "-" + dt2.Day.ToString(); dt2 = Convert.ToDateTime(s2);string s4 = dt2.Year.ToString() + "-" + dt2.Month.ToString() + "-" + dt2.Day.ToString(); //MessageBox.Show(s3+" "+s4); CommandText = "SELECT payment.paymment_id,payment.date_of_create,category.cat_name,payment.name,payment.quantity,payment.cost,payment.valu,payment.sign FROM payment,category WHERE date_of_create>='" + s3 + "' AND date_of_create<='" + s4 + "' AND category.cat_id=payment.category_id"; //MessageBox.Show(CommandText); SqlCommand myC = new SqlCommand(CommandText, mycon); myC.CommandText = CommandText; MysDataAdapter.SelectCommand = myC;myds.Clear();MysDataAdapter.Fill(myds);if (myds.Tables.Count > 0) { //dgv.Rows.Clear(); dgv.DataSource = myds.Tables[0];dgv.Refresh(); } }void butExport(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet;object misValue = System.Reflection.Missing.Value; Int16 i, j;xlApp = new Excel.ApplicationClass();xlWorkBook = xlApp.Workbooks.Add(misValue);xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);xlWorkSheet.Cells[1, 1] = "Номер";xlWorkSheet.Cells[1, 2] = "Дата";xlWorkSheet.Cells[1, 3] = "Category_id";xlWorkSheet.Cells[1, 4] = "Наименование";xlWorkSheet.Cells[1, 5] = "Количество";xlWorkSheet.Cells[1, 6] = "Цена";xlWorkSheet.Cells[1, 7] = "Стоимость";for (i = 0; i <= dgv.RowCount - 2; i++) {for (j = 0; j <= dgv.ColumnCount - 1; j++) {xlWorkSheet.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString(); } }xlWorkBook.SaveAs(@"C:\report.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);xlWorkBook.Close(true, misValue, misValue);xlApp.Quit();releaseObject(xlWorkSheet);releaseObject(xlWorkBook);releaseObject(xlApp);return; }private void releaseObject(object obj) {try {System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);obj = null; }catch (Exception ex) {obj = null;MessageBox.Show("Exception Occured while releasing object " + ex.ToString()); }finally {GC.Collect(); } }void butDelpayclick(object sender, EventArgs e) { Button buttonDel = new Button(); f4 = new Form2(); f4.Width = 280; f4.Height = 200; f4.Text = "Удалениеплатежа";strArr = new String[1];strArr[0] = "Category"; //strArr[1] = "Name"; //strArr[2] = "Cost"; //strArr[3] = "Quantity"; //Get the number of input text boxes to generateint inputNumber = 1; //Initialize list of input text boxesinputTextBoxes = new List<TextBox>(); //Generate labels and text boxesfor (int i = 1; i <= inputNumber; i++) { //Create a new label and text box Label labelInput = new Label(); TextBox textBoxNewInput = new TextBox(); //Initialize label's property labelInput.Text = strArr[i - 1]; labelInput.Location = new Point(30, (i * 30)); labelInput.AutoSize = true; //Initialize textBoxes Property textBoxNewInput.Location = new Point(labelInput.Width, labelInput.Top - 3); textBoxNewInput.Name = "TxtBox" + i.ToString(); /*if (i == 2) { textBoxNewInput.PasswordChar = c; }*/ //Add the newly created text box to the list of input text boxesinputTextBoxes.Add(textBoxNewInput); //Add the labels and text box to the formf4.Controls.Add(labelInput);f4.Controls.Add(textBoxNewInput); } //Create an Add button Button buttonDelpay = new Button(); //Initialize Properties buttonDelpay.Text = "Delete payment"; //Make the button show up in the middle of the form and right below the last input box buttonDelpay.Location = new Point(this.Width / 2 - buttonDelpay.Width / 2, 100); //Add an event handler to the button's click event buttonDelpay.Click += new EventHandler(buttonDel_Click);f4.Controls.Add(buttonDelpay);f4.ShowDialog();return; }void buttonDel_Click(object sender, EventArgs e) {int categoryId; //MessageBox.Show("Button is pressed!"); TextBox tb = (TextBox)GetControlByName1("TxtBox1");categoryId = Int32.Parse(tb.Text); //if (i == 1) Category = tb.Text; //string str3 = fl2.ToString(CultureInfo.InvariantCulture); //Valu = str3; //MessageBox.Show(Category+ " "+Name1 + " "+ Quantity+ " " + Cost); CommandText = "DELETE FROM payment WHERE category_id=" + categoryId.ToString();mycon.Open(); SqlCommand myC = new SqlCommand(CommandText, mycon); //MessageBox.Show(CommandText);myC.ExecuteNonQuery();mycon.Close(); //DataGridView grd2=(DataGridView)GetControlByName("MainGrid"); CommandText = "SELECT payment.paymment_id,payment.date_of_create,category.cat_name,payment.name,payment.quantity,payment.cost,payment.valu,payment.sign FROM payment,category WHERE payment.category_id=category.cat_id"; myC.CommandText = CommandText; MysDataAdapter.SelectCommand = myC;myds.Clear();MysDataAdapter.Fill(myds);if (myds.Tables.Count > 0) { //dgv.Rows.Clear(); dgv.DataSource = myds.Tables[0];dgv.Refresh(); }MessageBox.Show("Payment is deleted"); }void butAddpayclick(object sender, EventArgs e) { Button buttonAdd = new Button(); f3 = new Form2();f3.Width = 280; f3.Height = 400; f3.Text = "Добавление нового платежа";strArr = new String[4];strArr[0] = "Category";strArr[1] = "Name";strArr[2] = "Cost";strArr[3] = "Quantity"; //Get the number of input text boxes to generateint inputNumber = 4; //Initialize list of input text boxesinputTextBoxes = new List<TextBox>(); //Generate labels and text boxesfor (int i = 1; i <= inputNumber; i++) { //Create a new label and text box Label labelInput = new Label(); TextBox textBoxNewInput = new TextBox(); //Initialize label's property labelInput.Text = strArr[i - 1]; labelInput.Location = new Point(30, (i * 30)); labelInput.AutoSize = true; //Initialize textBoxes Property textBoxNewInput.Location = new Point(labelInput.Width, labelInput.Top - 3); textBoxNewInput.Name = "textBox" + i.ToString(); /*if (i == 2) { textBoxNewInput.PasswordChar = c; }*/ //Add the newly created text box to the list of input text boxesinputTextBoxes.Add(textBoxNewInput); //Add the labels and text box to the formf3.Controls.Add(labelInput);f3.Controls.Add(textBoxNewInput); } //Create an Add button Button buttonAddpay = new Button(); //Initialize Properties buttonAdd.Text = "Add payment"; //Make the button show up in the middle of the form and right below the last input box buttonAdd.Location = new Point(this.Width / 2 - buttonAdd.Width / 2,inputTextBoxes[inputTextBoxes.Count - 1].Bottom + 20); //Add an event handler to the button's click event buttonAdd.Click += new EventHandler(buttonAddpay_Click);f3.Controls.Add(buttonAdd);f3.ShowDialog(); }void form2_FormClosed(object sender, FormClosedEventArgs e) { //this.Visible = true;this.Close(); }public Form1() { f5 = new Form3(); frm5 = new Form5();passwords = new string[255];tokens = new string[255]; String MyURI = "https://www.contoso.com/"; WebRequest WReq = WebRequest.Create(MyURI); String serverUri = "ftp://ftp.contoso.com/file.txt"; FtpWebRequest request = (FtpWebRequest)WebRequest.Create(serverUri); request.EnableSsl = true; request.Method = WebRequestMethods.Ftp.DeleteFile; //FtpWebResponse response = (FtpWebResponse)request.GetResponse(); CommandText = "SELECT name FROM login";constr = "Server=127.0.0.1;" + "Database=payments;" + "user id=sa;" + "Pwd=root;" + "connection timeout=30"; /*MySqlConnection */mycon = new SqlConnection(constr); /*MySqlCommand*/ SqlCommand myCommand = new SqlCommand();// = new MySqlCommand(CommandText, mycon); ListBox lstview = new ListBox(); lstview.Name = "LST1"; myCommand.CommandText = "SELECT * FROM login";mycon.Open(); myCommand.Connection = mycon; myCommand.CommandType = CommandType.Text;//.TableDirect; SqlDataReader reader = myCommand.ExecuteReader();int j = 0;while (reader.Read()) { //Console.WriteLn(reader[0], reader[1]...); //MessageBox.Show(reader.GetValue(2).ToString());//.ToString+reader[1].ToString+reader[2].ToString+reader[3].ToString);lstview.Items.Add(reader.GetValue(2).ToString());passwords[j] = reader.GetValue(3).ToString();passwords[j] = passwords[j].TrimEnd(' '); //passwords[j].Remove(0); //passwords[j].Remove(0); //MessageBox.Show(passwords[j]);role = Int32.Parse(reader.GetValue(5).ToString());tokens[j] = reader.GetValue(4).ToString();tokens[j] = tokens[j].TrimEnd(' ');j++; }len = j; //string []hash2; hash2 = new string[len];for (int n = 0; n < len; n++) hash2[n] = "";int lenn;char[] chrarr;string reslt;string sum;sum = "";int temp; //chrarr=new char[255];for (int l = 0; l < len; l++) {chrarr = passwords[l].ToCharArray();lenn = passwords[l].Length;for (int m = 0; m < lenn; m++) {temp = (int)chrarr[m];temp += 2;chrarr[m] = (char)temp; //chrarr[m] = chrarr[m] + 2; }string s5 = new string(chrarr);reslt = chrarr.ToString();hash2[l] = s5;sum += s5; } //MessageBox.Show(hash2[0]);mycon.Close();char c = '*';strArr = new String[3];strArr[0] = "Login";strArr[1] = "Password";strArr[2] = "Login by token"; //ListBox lstview=new ListBox(); //Get the number of input text boxes to generateint inputNumber = 3; //Initialize list of input text boxesinputTextBoxes = new List<TextBox>(); //Generate labels and text boxesfor (int i = 1; i <= inputNumber; i++) { //Create a new label and text box Label labelInput = new Label(); TextBox textBoxNewInput = new TextBox(); //Initialize label's property labelInput.Text = strArr[i - 1]; labelInput.Location = new Point(30, (i * 30)); labelInput.AutoSize = true; //Initialize textBoxes Propertyif (i != 3) textBoxNewInput.Location = new Point(labelInput.Width, labelInput.Top - 3);else { textBoxNewInput.Location = new Point(labelInput.Width + 20, labelInput.Top - 3); textBoxNewInput.Width = 100; }if (i == 2) { textBoxNewInput.PasswordChar = c; textBoxNewInput.Name = "TB1"; }else if (i == 3) { textBoxNewInput.Name = "TB4"; } //Add the newly created text box to the list of input text boxesinputTextBoxes.Add(textBoxNewInput); //Add the labels and text box to the formthis.Controls.Add(labelInput);if (i != 1) this.Controls.Add(textBoxNewInput);else if (i == 1) { //lstview.Items.Add("user1"); //lstview.Items.Add("user2"); lstview.Height = 25; lstview.Location = new Point(labelInput.Width + 65, labelInput.Top - 3);this.Controls.Add(lstview); } } //Create an Add button Button buttonAdd = new Button(); //Initialize Properties buttonAdd.Text = "Login"; //Make the button show up in the middle of the form and right below the last input box buttonAdd.Location = new Point(this.Width / 2 - buttonAdd.Width / 2,inputTextBoxes[inputTextBoxes.Count - 1].Bottom + 20); //Add an event handler to the button's click event buttonAdd.Click += new EventHandler(buttonAdd_Click); //Add the button to the formthis.Controls.Add(buttonAdd); //Center the form to the screenthis.CenterToScreen();InitializeComponent(); }private void Form1_Load(object sender, EventArgs e){ } }}Список использованной литературы