SQL Server-ის login-ები, user-ები და როლები, ახსნილი

SQL Server ვინაობას ორად ყოფს. Login სერვერთან დაკავშირების საშუალებას გაძლევს; user ამ login-ს ერთი ბაზის შიგნით ვინაობას აძლევს. უფლებები ენიჭება user-ებს (და როლებს, რომლებიც user-ების ჯგუფებია) თითო ბაზაში, და login-ებს - სერვერის მასშტაბის უფლებამოსილებებისთვის. sa არის login ყველა არსებული უფლებამოსილებით. აპლიკაციისთვის სწორი მოწყობაა საკუთარი login, user მხოლოდ მის ბაზაში, წევრობა db_datareader-სა და db_datawriter-ში (ან უფრო ვიწრო grant-ები სქემაზე), და EXECUTE, თუ stored procedure-ებს იძახებს - სერვერის დონეზე არაფერი. ეს პოსტი თითოეულ ნაწილს განმარტავს, სკრიპტებს იძლევა და განიხილავს ორ პრობლემას, რომელიც ადრე თუ გვიან ყველას ხვდება: ობოლი user-ები აღდგენის შემდეგ და login-ის ჩავარდნები უსარგებლო შეტყობინებებით.

Login-ები და user-ები: ორი ფენა#

ყოველი კავშირი ორ შემოწმებას გადის.

1. ავთენტიფიკაცია, სერვერზე. კლიენტი წარადგენს login-ის სახელსა და პაროლს (SQL Server ავთენტიფიკაცია) ან Windows ვინაობას. თუ login არსებობს, ჩართულია და პაროლი ემთხვევა, კავშირი ღიაა. Login-ები master-ში ცხოვრობს და ჩამოთვლილია sys.server_principals-სა და sys.sql_logins-ში.
2. წვდომა, თითო ბაზაზე. როცა კავშირი ბაზას იყენებს - თავის ნაგულისხმევ ბაზას, connection string-ში დასახელებულს ან USE ინსტრუქციით არჩეულს - SQL Server ამ ბაზაში ეძებს user-ს, რომელიც login-ზეა მიბმული. User-ები ჩამოთვლილია თითოეული ბაზის sys.database_principals-ში, და მიბმა უსაფრთხოების იდენტიფიკატორით (SID) ხდება და არა სახელით.

Login-ს, რომელსაც ბაზაში user არ აქვს, ამ ბაზის გამოყენება არ შეუძლია, ორი გამონაკლისით: sysadmin სერვერის როლის წევრები, რომლებიც ყველა ბაზაში dbo-დ შედიან, და ბაზები, სადაც guest user ჩართულია, რაც მომხმარებლის ბაზებში ნაგულისხმევად ასე არ არის და ასე უნდა დარჩეს.

ამ დაყოფის გამოა, რომ login-ს სხვადასხვა ბაზაში სხვადასხვა უფლებები შეიძლება ჰქონდეს, და რომ ბაზის სხვა სერვერზე გადატანა სიფრთხილეს მოითხოვს: user-ები ბაზასთან ერთად მოგზაურობს, login-ები - არა.

sa ანგარიში#

sa ჩაშენებული SQL login-ია SID-ით 0x01, sysadmin ფიქსირებული სერვერის როლის მუდმივი წევრი. მას ყველაფერი შეუძლია: ბაზების შექმნა და წაშლა, სერვერის კონფიგურაციის შეცვლა, login-ების შექმნა, ყველა ცხრილის წაკითხვა და SQL Server-ისთვის გახსნილი ოპერაციული სისტემის დონის ოპერაციების გაშვება. მისი sysadmin-იდან ამოღება შეუძლებელია და მისი წაშლაც შეუძლებელია.

რას ნიშნავს ეს შენთვის:

• `sa` ადმინისტრირებისთვის დატოვე. გამოიყენე SSMS-იდან ან sqlcmd-იდან, როცა სერვერს მართავ, და არა აპლიკაციის connection string-ებში. გაჟონილი sa პაროლი გაჟონილი სერვერია.
• მიეცი მას გრძელი შემთხვევითი პაროლი და შეინახე პაროლების მენეჯერში, და არა ტექსტურ ფაილში კოდის გვერდით.
• ჰოსტირებულ სერვერზე ნუ გამორთავ და ნუ გადაარქმევ, თუ არ იცი, რა არის მასზე დამოკიდებული. ორივე შესაძლებელია (ALTER LOGIN sa DISABLE;, ALTER LOGIN sa WITH NAME = ...;) და ორივე გამაგრების სტანდარტული რჩევაა სერვერებზე, რომლებიც მთლიანად შენ გეკუთვნის. ჰოსტირებულ სერვერზე ჯერ შექმენი სხვა sysadmin login, რომელიც გამოცადე, თორემ შეიძლება საკუთარი ინსტანსიდან თავი ჩაიკეტო.

RE:NODE-ზე SQL Server-ის გეგმას მოჰყვება ამ სერვერისთვის გენერირებული sa პაროლი და შენთვის შექმნილი ბაზა, რომელიც sa-ს ნაგულისხმევად არის დაყენებული, ამიტომ SQL Server Management Studio პირდაპირ მასში იხსნება. ეს საწყისი წერტილია; პოსტის დანარჩენი ნაწილი იმაზეა, როგორ არ გამოიყენო sa ყველაფრისთვის. SQL Server-თან დაკავშირება SSMS-ით პირველ შესვლას განიხილავს.

ფიქსირებული სერვერის როლები#

სერვერის როლები სერვერის მასშტაბის უფლებებს ატარებს. Login-ებს მათში ALTER SERVER ROLE ... ADD MEMBER-ით ამატებ.

SQL Server 2022-მა დაამატა უფრო ვიწრო ფიქსირებული სერვერის როლების ნაკრები, რომელთა სახელები ##MS_-ით იწყება, მაგალითად ##MS_ServerStateReader## (სერვერის მდგომარეობის ნახვა მონიტორინგისთვის), ##MS_DefinitionReader## (ობიექტების განმარტებების ნახვა) და ##MS_DatabaseConnector## (ყველა ბაზასთან დაკავშირება). ისინი სასარგებლოა მონიტორინგის ინსტრუმენტებისთვის, რომლებსაც სხვა შემთხვევაში sysadmin მიეცემოდათ მხოლოდ dynamic management view-ების წასაკითხად.

აპლიკაციის login არცერთ მათგანში არ უნდა იყოს.

ფიქსირებული ბაზის როლები#

ბაზის როლები უფლებებს ერთი ბაზის შიგნით ატარებს. User-ებს მათში ALTER ROLE ... ADD MEMBER-ით ამატებ.

db_datareader და db_datawriter ბაზის ყველა არსებულ და მომავალ ცხრილს ფარავს, რაც მოსახერხებელია და ოდნავ ფართო. უფრო ზუსტი კონტროლისთვის სამაგიეროდ სქემაზე მიანიჭე: სქემაზე მინიჭებული უფლებები მასში არსებულ ყველა ობიექტზე ვრცელდება, მოგვიანებით შექმნილის ჩათვლით.

შენიშნე, რა აკლია db_datareader-სა და db_datawriter-ს: EXECUTE. აპლიკაციას, რომელიც stored procedure-ებს იძახებს, ეს ცალკე უნდა მიენიჭოს.

მინიმალური უფლებების login აპლიკაციისთვის#

ქვემოთ მოცემული სკრიპტი აპლიკაციისთვის ქმნის login-ს, აძლევს მას user-ს მის ბაზაში და ანიჭებს იმას, რაც ტიპურ ვებ-აპლიკაციას მუშაობისას სჭირდება. გაუშვი sa-თი ან სხვა sysadmin-ით.

USE [master];CREATE LOGIN [orders_app]    WITH PASSWORD = N'use-a-long-random-generated-password',         DEFAULT_DATABASE = [app],         CHECK_POLICY = ON;USE [app];CREATE USER [orders_app] FOR LOGIN [orders_app] WITH DEFAULT_SCHEMA = [dbo];ALTER ROLE [db_datareader] ADD MEMBER [orders_app];ALTER ROLE [db_datawriter] ADD MEMBER [orders_app];GRANT EXECUTE ON SCHEMA::[dbo] TO [orders_app];

Login-სა და user-ს სხვადასხვა სახელი შეიძლება ჰქონდეს; ერთი და იგივე სახელის მიცემა მიბმას ცხადს ხდის, როცა მას ერთი წლის შემდეგ წაიკითხავ. DEFAULT_DATABASE ნიშნავს, რომ login სწორ ბაზაში მოხვდება, თუნდაც connection string-მა ბაზის დასახელება დაივიწყოს.

მიგრაციები უხერხული ნაწილია. Entity Framework Core, Flyway და მსგავსი ინსტრუმენტები ცხრილებს ქმნის და ცვლის, რაც ზემოთ მოცემულ runtime login-ს არ შეუძლია - განზრახ. სუფთა შაბლონი მეორე login-ია, რომელსაც მხოლოდ deploy-ის ნაბიჯი იყენებს:

USE [master];CREATE LOGIN [orders_migrate] WITH PASSWORD = N'another-long-password', DEFAULT_DATABASE = [app];USE [app];CREATE USER [orders_migrate] FOR LOGIN [orders_migrate];ALTER ROLE [db_ddladmin] ADD MEMBER [orders_migrate];ALTER ROLE [db_datareader] ADD MEMBER [orders_migrate];ALTER ROLE [db_datawriter] ADD MEMBER [orders_migrate];

თუ ეს პროექტისთვის ზედმეტი ცერემონიაა, ერთი login საკუთარი ბაზის db_owner-ში მაინც ბევრად უკეთესია, ვიდრე sa: მას ერთი ბაზის გაფუჭება შეუძლია და არა სერვერის. EF Core მიგრაციები production-ში განიხილავს, როგორ გაუშვა მიგრაციები ცალკე ნაბიჯად, რომ ორ-login-იანი შაბლონი პრაქტიკული იყოს.

ერთ სერვერზე რამდენიმე აპლიკაციიდან თითოეულს საკუთარი ბაზა, login და user უნდა ჰქონდეს. მაშინ გაჟონილი connection string, SQL injection-ის შეცდომა ან ცუდი მიგრაცია ერთ აპლიკაციაში ერთ ბაზას შეეხება.

სქემები, GRANT, DENY და იმის შემოწმება, რა შეუძლია user-ს#

უფლებები შეიძლება სამ დონეზე მიენიჭოს: ბაზაზე, სქემაზე ან ცალკეულ ობიექტზე. სქემის დონის grant-ები ჩვეულებრივ სწორი სიზუსტეა:

CREATE SCHEMA [reporting] AUTHORIZATION [dbo];CREATE ROLE [reporting_reader];GRANT SELECT ON SCHEMA::[reporting] TO [reporting_reader];CREATE USER [bi_tool] FOR LOGIN [bi_tool];ALTER ROLE [reporting_reader] ADD MEMBER [bi_tool];

საკუთარი როლის შექმნა და მისთვის მინიჭება, user-ებისთვის პირდაპირ მინიჭების ნაცვლად, ნიშნავს, რომ მეორე რეპორტინგის user ერთი ALTER ROLE-ის მანძილზეა, და უფლებები ერთ ადგილას არის ჩაწერილი.

DENY სხვა ნებისმიერი როლიდან მიღებულ GRANT-ს აჭარბებს, ერთი გამონაკლისით: მას არანაირი ეფექტი არ აქვს sysadmin-ის წევრებსა და ბაზის მფლობელზე, რომლებიც უფლებების შემოწმებას გვერდს უვლიან. ზომიერად გამოიყენე - მაგალითად, რომ რეპორტინგის როლმა ვერ წაიკითხოს dbo.Payments ცხრილი, რომელსაც მისი სქემის grant სხვა შემთხვევაში მოიცავდა.

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

EXECUTE AS USER = 'orders_app';SELECT * FROM fn_my_permissions(NULL, 'DATABASE');SELECT HAS_PERMS_BY_NAME('dbo.Orders', 'OBJECT', 'DELETE') AS can_delete;REVERT;

საპირისპირო მიმართულების აუდიტისთვის - ვის რაზე აქვს წვდომა - ჩამოთვალე როლებში წევრობა ორივე დონეზე. გაუშვი ეს დროდადრო, და განსაკუთრებით მას შემდეგ, რაც ვინმეს პრობლემის მოსაგვარებლად "დროებით" მეტი უფლებები მისცეს:

-- Server roles and their membersSELECT r.name AS server_role, m.name AS login_nameFROM sys.server_role_members AS rmJOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_idJOIN sys.server_principals AS m ON m.principal_id = rm.member_principal_idORDER BY r.name, m.name;-- Database roles and their members, in the current databaseSELECT r.name AS database_role, m.name AS user_nameFROM sys.database_role_members AS rmJOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_idJOIN sys.database_principals AS m ON m.principal_id = rm.member_principal_idORDER BY r.name, m.name;

ყველაფერი sysadmin-ში, გარდა sa-სა და ადმინისტრირებისთვის განზრახ შექმნილი login-ებისა, და ყველაფერი db_owner-ში, რაც მიგრაციის login არ არის, კითხვას იმსახურებს.

როცა წვდომა row-ზე უნდა იყოს დამოკიდებული და არა ცხრილზე - multi-tenant აპლიკაცია, სადაც თითოეულმა კლიენტმა მხოლოდ საკუთარი შეკვეთები უნდა დაინახოს - როლები არასწორი ინსტრუმენტია. Row-level security, ხელმისაწვდომი ყველა edition-ში, Express-ის ჩათვლით, ცხრილს ფილტრის პრედიკატის ფუნქციას ამაგრებს, ასე რომ თავად ბაზა ამატებს tenant-ის პირობას ყოველ query-ს. ეს აპლიკაციის საკუთარი შემოწმებების მიღმა დაცვის სასარგებლო მეორე ხაზია და არა მათი შემცვლელი.

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

პაროლები, პოლიტიკები და credential-ების შეცვლა#

CHECK_POLICY = ON login-ზე პაროლის პოლიტიკას ავრცელებს. Windows-ზე ეს Windows-ის პაროლის პოლიტიკაა; Linux-ზე მემკვიდრეობით მისაღები Windows პოლიტიკა არ არსებობს, ამიტომ ნუ ჩათვლი, რომ სუსტი პაროლი უარყოფილი იქნება - დააგენერირე გრძელი შემთხვევითი პაროლი და კითხვა საერთოდ არ გაჩნდება. CHECK_EXPIRATION ვადის გასვლას ამატებს, რაც ადამიანების login-ებს უფრო შეეფერება, ვიდრე აპლიკაციისას - აპლიკაცია, რომლის პაროლის ვადაც ღამის 3 საათზე გადის, გათიშვაა და არა უსაფრთხოება.

აპლიკაციის პაროლის downtime-ის გარეშე შესაცვლელად ჩვეული მიდგომაა ორი login ან მოვლის მოკლე ფანჯარა:

ALTER LOGIN [orders_app] WITH PASSWORD = N'the-new-long-password';

პაროლის შეცვლის შემდეგ არსებული კავშირები ღია რჩება; ახალი პაროლი მხოლოდ ახალ კავშირებს სჭირდება. განაახლე გარემოს ცვლადი, რომელიც connection string-ს ინახავს, გადატვირთე აპლიკაცია, რომ მისი pool თავიდან დაუკავშირდეს, და გარკვეული დროის შემდეგ sys.dm_exec_sessions-ში დაადასტურე, რომ ძველით აღარაფერია დაკავშირებული. Login-ის დაუყოვნებლივ დასაბლოკად - მაგალითად, გაჟონვის შემდეგ - გამორთე ის და მისი სესიები მოკალი:

ALTER LOGIN [orders_app] DISABLE;SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'orders_app';-- KILL <session_id>; for each one

ობოლი user-ები აღდგენის შემდეგ#

User-ები login-ებს SID-ით უკავშირდება. სხვა სერვერზე შექმნილ SQL login-ს სხვა, შემთხვევით გენერირებული SID აქვს, თუნდაც სახელი იგივე იყოს. ამიტომ, როცა ბაზას სხვა სერვერიდან აღადგენ, მისი user-ები მოდის SID-ებზე მითითებით, რომლებიც აქ არ არსებობს. ესენი ობოლი user-ებია: user ბაზაშია, login სერვერზეა, და დაკავშირება ვარდება, რადგან ისინი ერთმანეთს არ ემთხვევა.

იპოვე ისინი:

SELECT dp.name AS orphaned_userFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sidWHERE dp.type = 'S'  AND dp.authentication_type_desc = 'INSTANCE'  AND sp.sid IS NULL;

გამოასწორე თითოეული, user-ის ამ სერვერის login-ზე ხელახლა მიბმით:

ALTER USER [orders_app] WITH LOGIN = [orders_app];

ძველი sp_change_users_login პროცედურა იმავე საქმეს აკეთებს და მოძველებულია; ALTER USER ... WITH LOGIN მიმდინარე გზაა. პრობლემის თავიდან ასაცილებლად შექმენი login ახალ სერვერზე თავდაპირველი SID-ით - CREATE LOGIN [orders_app] WITH PASSWORD = N'...', SID = 0x...;, ძველი სერვერის sys.sql_logins-იდან აღებული SID-ით. ბაზის მიგრაცია SQL Server ჰოსტინგზე ამას სრული გადატანის ნაწილად განიხილავს, ხოლო SQL Server-ის backup და აღდგენა თავად აღდგენას.

Contained ბაზები პრობლემის შემოვლის მეორე გზაა. CONTAINMENT = PARTIAL-ით და ჩართული სერვერის ოფციით contained database authentication შეგიძლია შექმნა user-ები პაროლებით, რომლებიც მთლიანად ბაზის შიგნით ცხოვრობს, login-ის გარეშე, ასე რომ ბაზა სერვერებს შორის ავთენტიფიკაციით ხელუხლებლად გადადის. ისინი გონივრული არჩევანია, როცა ბაზები ხშირად გადადის; ერთსერვერიანი მოწყობების უმეტესობას ისინი არ სჭირდება.

Login-ის ჩავარდნების წაკითხვა#

შეცდომა 18456, "Login failed for user", კლიენტისთვის განზრახ ბუნდოვანია, რომ თავდამსხმელს არ დაეხმაროს. სერვერის შეცდომების ლოგი state ნომერს რეალური მიზეზით იწერს. ლოგის წაკითხვა შეგიძლია EXEC sp_readerrorlog;-ით sysadmin-ის სახით, ან SSMS-ში Management, SQL Server Logs-ში.

State-ები 38 და 40 ისეთებია, რომლებიც პაროლის პრობლემას ჰგავს და არ არის. 38 ჩვეულებრივ ნიშნავს, რომ connection string ასახელებს ბაზას, სადაც login-ს user არ აქვს. 40 ნიშნავს, რომ ნაგულისხმევი ბაზა წაიშალა ან გადაერქვა - რაც სერვერზე, სადაც sa-ს ნაგულისხმევი შენთვის შექმნილი ბაზაა, შეიძლება sa-ს დაკავშირებას შეუშალოს, სანამ არ დაუკავშირდები master-ით საწყის ბაზად და არ აღადგენ მას ALTER LOGIN [sa] WITH DEFAULT_DATABASE = [master];-ით.

FAQ#

რა განსხვავებაა login-სა და user-ს შორის SQL Server-ში?

Login სერვერის დონის ვინაობაა, რომელიც დაკავშირების საშუალებას გაძლევს. User ბაზის დონის ვინაობაა, login-ზე მიბმული, რომელიც ერთ ბაზაზე წვდომას გაძლევს. ერთ login-ს შეიძლება ბევრ ბაზაში ჰქონდეს user, თითოეულში სხვადასხვა უფლებებით.

ჩემი აპლიკაცია sa-თი უნდა დაუკავშირდეს?

არა. შექმენი აპლიკაციისთვის login, user-ით მის საკუთარ ბაზაში და მხოლოდ საჭირო როლებით. sa-ს შეუძლია ყველა ბაზის წაშლა და სერვერის კონფიგურაციის შეცვლა, ხოლო აპლიკაციის connection string ის credential-ია, რომლის გაჟონვის ალბათობაც ყველაზე მაღალია.

db_owner უსაფრთხოა აპლიკაციისთვის?

sa-ზე უსაფრთხოა, რადგან ერთი ბაზით შემოიფარგლება, მაგრამ უფრო ფართოა, ვიდრე აპლიკაციას მუშაობისას სჭირდება. მას ცხრილების წაშლა და უფლებების შეცვლა შეუძლია. მომუშავე აპლიკაციისთვის გამოიყენე db_datareader, db_datawriter და EXECUTE, ხოლო db_owner ან db_ddladmin - მხოლოდ მიგრაციებისთვის.

რატომ ვერ უკავშირდება ჩემი login ბაზის აღდგენის შემდეგ?

ბაზის user ობოლია: ის ძველი სერვერის login-ის SID-ზე მიუთითებს. ხელახლა მიაბი ALTER USER [name] WITH LOGIN = [name];-ით, ან თავიდან შექმენი login თავდაპირველი SID-ით.

შემიძლია რეპორტინგისთვის მხოლოდ წაკითხვის user შევქმნა?

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