ძირითადი მასალა

კომპიუტერული პროგრამირება

კურსი: კომპიუტერული პროგრამირება > თემა 3

გაკვეთილი 4: მონაცემთა ბაზის მოდიფიცირება SQL-ის საშუალებით

გახადეთ თქვენი SQL უფრო უსაფრთხო

SQL შეიძლება, მშვენიერი რამ იყოს, მაგრამ ის ზოგჯერ სახიფათოცაა. თუ იყენებთ SQL-ს იმ მონაცემებზე წვდომისათვის, რომლებსაც იყენებს ასობით, ათასობით ან მილიონობით მომხმარებელი, ფრთხილად უნდა იყოთ, რადგან შეიძლება, შემთხვევით დააზიანოთ ან მთლიანად წაშალოთ მონაცემები. მიუხედავად ამისა, არსებობს მრავალი გზა, რომლითაც შეგიძლიათ, თქვენი SQL უფრო უსაფრთხო გახადოთ.

ცუდი განახლებებისა და წაშლების თავიდან არიდება

სანამ UPDATE-ს გამოიძახებთ, გაუშვით SELECT იმავე WHERE-ით, რათა დარწმუნდეთ, რომ სწორ სვეტსა და მწკრივს განაახლებთ.

მაგალითად, გაშვებამდე:

UPDATE users SET deleted = true WHERE id = 1;

შეგეძლოთ, გაგეშვათ:

SELECT id, deleted FROM users WHERE id = 1;

როგორც კი გადაწყვეტთ განახლების გაშვებას, შეგიძლიათ, გამოიყენოთ LIMIT ოპერატორი, რათა დარწმუნდეთ, რომ ზედმეტად ბევრ მწკრივს არ განაახლებთ:

UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;

ან, თუ შლიდით:

DELETE users WHERE id = 1 LIMIT 1;

ტრანზაქციების გამოყენება

როდესაც გავცემთ SQL ბრძანებას, რომელიც რამენაირად ცვლის ჩვენს მონაცემთა ბაზას, ის იწყებს „ტრანზაქციას“. ტრანზაქცია არის ოპერაციათა მიმდევრობა, რომელიც აღიქმება, როგორც საქმის ერთი ლოგიკური ნაწილი (როგორიცაა ბანკის ტრანზაქცია). მონაცემთა სამყაროში ტრანზაქცია უნდა დაემორჩილოს „ACID-ის“ პრინციპებს, რათა ის ნამდვილად სანდოდ დამუშავდეს.

როდესაც ისეთ ბრძანებას გავცემთ, როგორიცაა CREATE, UPDATE, INSERT ან DELETE, ჩვენ ავტომატურად ვიწყებთ ტრანზაქციას. მიუხედავად ამისა, თუ სურვილი გვაქვს, შეგვიძლია, რამდენიმე ბრძანება ჩავსვათ დიდ ტრანზაქციაში. შეიძლება, გვინდოდეს, რომ ერთი UPDATE სრულდებოდეს იმ შემთხვევაში, თუ სხვა UPDATE-იც სრულდება, ამიტომ მათ ორივეს მოვათავსებთ ერთ ტრანზაქციაში.

ამ შემთხვევაში, შეგვიძლია, ბრძანებები მოვათავსოთ BEGIN TRANSACTION–სა და COMMIT–ში:

BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;

თუ რაიმე მიზეზის გამო მონაცემთა ბაზა ვერ გასცემს ორივე UPDATE ბრძანებას, მაშინ ის საწყის მდგომარეობაში დააბრუნებს ტრანზაქციას და მონაცემთა ბაზას უცვლელად დატოვებს.

ჩვენ აგრეთვე ვიყენებთ ტრანზაქციებს, როდესაც გვინდა, დავრწმუნდეთ, რომ ჩვენი ყველა ბრძანება მოქმედებს მონაცემთა ერთსა და იმავე ხედზე - ამავდროულად, არ უნდა დავუშვათ იგივე მონაცემებზე სხვა ტრანზაქციების მოქმედება მაშინ, როცა ბრძანებათა ეს მიმდევრობა არის გაშვებული. როდესაც უყურებთ ბრძანებათა მიმდევრობას, რომლის გაშვებაც გსურთ, ჰკითხეთ საკუთარ თავს, რა მოხდებოდა, თუ სხვა მომხმარებელი გასცემდა ბრძანებებს იმავე დროს. შეიძლება, რომ თქვენი მონაცემები უცნაურ მდგომარეობაში აღმოჩნდნენ? ამ შემთხვევაში, უნდა გაუშვათ ტრანზაქცია.

მაგალითად, შემდეგი ბრძანებები ქმნიან მწკრივს, რომელიც აღნიშნავს, რომ მომხმარებელმა მოიპოვა ბეჯი, და შემდეგ განაახლებს მომხმარებლის ბოლო დროის აქტივობას, რათა აღწეროს ის:

INSERT INTO user_badges VALUES (1, "SQL Master", "4pm");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;

ამის პარალელურად, შეიძლება, სხვა მომხმარებელი ან პროცესი აჯილდოებდეს მომხმარებელს მეორე ბეჯით:

INSERT INTO user_badges VALUES (1, "Great Listener", "4:05pm");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;

ეს ბრძანებები ახლა უკვე ამ მიმდევრობით შეიძლება გაიცეს:

INSERT INTO user_badges VALUES (1, "SQL Master");
INSERT INTO user_badges VALUES (1, "Great Listener");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;

მათი ბოლო აქტივობა ახლა იქნებოდა "Earned SQL Master badge" (მოიპოვა SQL-ის ოსტატის ბეჯი") მიუხედავად იმისა, რომ ბოლოს ჩასმული ბეჯი იყო "Great listener" („დიდებული მსმენელი"). ეს არ არის კატასტროფა, მაგრამ არც ისაა, რასაც ველოდით.

ამის ნაცვლად შეგვეძლო, ის გაგვეშვა ტრანზაქციაში, რათა შუაში სხვა ტრანზაქცია არ მომხდარიყო:

BEGIN TRANSACTION;
INSERT INTO user_badges VALUES (1, "SQL Master");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;
COMMIT;

მარქაფების გაკეთება

თქვენ აუცილებლად უნდა გაჰყვეთ ამ რჩევებს, მაგრამ ზოგჯერ შეცდომები მაინც ხდება. ამის გამო კომპანიები ქმნიან მარქაფებს თავიანთი მონაცემთა ბაზებისათვის - ყოველ საათში, ყოველდღე ან ყოველკვირა, გააჩნია მონაცემთა ბაზისა და მისთვის მეხსიერებაში გამოყოფილი ადგილის ზომას. როცა რამე ცუდი ხდება, შეუძლიათ, მონაცემები შემოიტანონ ძველი მონაცემთა ბაზიდან იმ ცხრილისთვის, რომელიც დაზიანდა ან დაიკარგა. შეიძლება, მონაცემები ძველი იყოს, მაგრამ ხშირად ძველი მონაცემების ქონა სჯობს მონაცემების გარეშე დარჩენას.

რეპლიკაცია

კიდევ ერთი მიდგომაა რეპლიკაცია - მონაცემთა ბაზების რამდენიმე ასლის ყოველთვის შენახვა სხვადასხვა ადგილზე. თუ რაიმე მიზეზის გამო მონაცემთა ბაზის რომელიმე კონკრეტული ასლი მიუწვდომელია (მაგალითად, მეხი დაეცა შენობას, რომელშიც ის ინახებოდა, რაც მე დამმართნია!), მაშინ შესაძლებელია სხვა ასლისათვის მოთხოვნის გაგზავნა, რომელიც, იმედია, ჯერ კიდევ ხელმისაწვდომია. თუ მონაცემები მნიშვნელოვანია, მაშინ უნდა გამოვიყენოთ რეპლიკაცია, რათა მათ ხელმისაწვდომობაში დარწმუნებული ვიყოთ. მაგალითად, თუ ექიმი ცდილობს, მოიპოვოს პაციენტის ალერგიების ჩამონათვალი, რათა გაარკვიოს, როგორ უმკურნალოს მას კრიტიკულ სიტუაციაში, მაშინ მას არ აქვს საკმარისი დრო იმისთვის, რომ დაელოდოს, როდის მოიპოვებენ მონაცემებს ინჟინრები მარქაფიდან, მან მაშინვე უნდა მიიღოს ეს მონაცემები.

მიუხედავად ამისა, მონაცემთა ბაზების რეპლიკაცია უფრო შრომატევადია და ხშირად ეს ნიშნავს კომპიუტერის უფრო ნელ მუშაობას, რადგან ყოველ მათგანში უნდა მოხდეს ჩაწერის ოპერაციები, ამიტომ კომპანიებმა უნდა გადაწყვიტონ, უღირთ თუ არა რეპლიკაციისგან მიღებული უპირატესობები გაწეულ ხარჯად. უნდა გაარკვიონ, რომელია საუკეთესო გზა მათ კონკრეტულ შემთხვევაში.

უფლებების მინიჭება

მონაცემთა ბაზების ბევრ სისტემას აქვს ჩაშენებული მომხმარებლები და უფლებები, რადგან ისინი შენახულია სერვერზე და ხელმისაწვდომია არაერთი მომხმარებლისათვის. მომხმარებლის/უფლების კონცეფცია ხანის აკადემიაზე SQL-ის სკრიპტებში არ არის, რადგან SQLite, ზოგადად, გამოიყენება თითო მომხმარებლის მიერ, ამიტომ მასში წერა შეგიძლიათ მხოლოდ მაშინ, როცა წვდომა გაქვთ დისკწამყვანზე (drive-ზე), რომელზეც ის ინახება.

მაგრამ თუ საერთო (ზიარ) სერვერზე გამოიყენებთ მონაცემთა ბაზების სისტემას, მაშინ ყურადღებით დააყენეთ მომხმარებლები და უფლებები თავიდანვე. ზოგადად, ძალიან ცოტა მომხმარებელს უნდა ჰქონდეს სრული წვდომა მონაცემთა ბაზაზე (მაგალითად, ბექენდის ინჟინრებს), რადგან ეს ძალიან სახიფათოა.

მაგალითად, აი, როგორ შეგვიძლია, მივცეთ კონკრეტულ მომხმარებელს სრული წვდომა:

GRANT FULL ON TABLE users TO super_admin;

და, აი, როგორ უნდა მივცეთ სხვა მომხმარებელს წვდომა მხოლოდ SELECT-ზე:

GRANT SELECT ON TABLE users TO analyzing_user;

დიდ კომპანიაში, ძირითადად, მომხმარებელთა უმრავლესობას SELECT-ის წვდომასაც არ აძლევთ, რადგან ცხრილში შეიძლება, პირადი მონაცემები იყოს, როგორიცაა მომხმარებლის ელფოსტის მისამართი ან სახელი. ბევრ კომპანიას აქვს მონაცემთა ბაზების ანონიმური ვერსიები, რომელთა მოთხოვნაც შეუძლიათ ისე, რომ არ ინერვიულონ პირად მონაცემებზე წვდომაზე.

ბონუსი: წაიკითხეთ ეს ცნობილი XKCD კომიქსი უფრო უსაფრთხო SQL-ის შესახებ (პლუს ეს ახსნა).

გსურთ, შეუერთდეთ დისკუსიას?

შესვლა

დავალაგოთ:

პოსტები ჯერ არ არის.

გესმით ინგლისური? დააწკაპუნეთ აქ და გაეცანით განხილვას ხანის აკადემიის ინგლისურენოვან გვერდზე.