SQL उपयोगकर्ताओं के लिए सभी ऑब्जेक्ट पर अनुमतियाँ सेट करें


1

मेरे पास एक ऐसी प्रक्रिया है जो डेटाबेस में सभी वस्तुओं से गुजरती है और उन्हें उस वस्तु के लिए उचित अनुमति प्रदान करती है।मैं जानना चाहता हूं कि क्या ऐसा करने का कोई बेहतर तरीका है?मैं नए डेटाबेस बनाने के लिए एक मॉडल डेटाबेस का उपयोग करता हूं इसलिए मुझे हर बार जब मैं एक नया डेटाबेस बनाता हूं तो मुझे इसे चलाना होगा।यहां एक विचार है कि यह कैसा दिखता है (नोट: शुरुआत से गायब एक हिस्सा है जो सभी उपयोगकर्ताओं को ड्रॉप करता है और आवश्यक उपयोगकर्ताओं को फिर से बनाता है; एसआईडी के परिवर्तन के बाद यह आवश्यक है):

CREATE PROCEDURE usp_SetPermissions 
AS 
    BEGIN

DECLARE @CurrentId INT
DECLARE @ObjectName NVARCHAR(128)
DECLARE @Message NVARCHAR(160)
DECLARE @Error INT
DECLARE @Sql NVARCHAR(256)

CREATE TABLE #tmpDbObjects 
(
     ID INT IDENTITY(1,1),
     ObjectName NVARCHAR(128),
     Completed BIT
)

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'U' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
    BEGIN
     -- Pick first uncompleted object
     SELECT TOP 1 @CurrentId = ID,
            @ObjectName = ObjectName
     FROM #tmpDbObjects

     -- Grant permissions to DB user
     SET @Sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
     EXEC sp_sqlexec @Sql

     -- Update object completion 
     DELETE #tmpDbObjects
     WHERE [Id] = @CurrentId

     -- Clear variables
     SET @Sql = NULL
     SET @CurrentId = NULL
    END

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'P' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
      BEGIN
     -- Pick first uncompleted object
     SELECT TOP 1 @CurrentId = ID,
            @ObjectName = ObjectName
     FROM #tmpDbObjects

     -- Grant permissions to DB user
     SET @Sql = 'GRANT EXEC ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
EXEC sp_sqlexec @Sql

     -- Update object completion 
     DELETE #tmpDbObjects
     WHERE [Id] = @CurrentId

     -- Clear variables
     SET @Sql = NULL
     SET @CurrentId = NULL
    END

यह स्क्रिप्ट डेटाबेस में सभी विचारों, कार्यों आदि के लिए समान कार्य करने के लिए जारी रहती है।इस बात को तेज करने के लिए कोई विचार या ऐसा करने का एक बेहतर तरीका है?

6

भूमिका के लिए स्कीमा स्तर पर अनुमति देने के लिए एकल पंक्ति

GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole

और उपयोगकर्ताओं को भूमिका में जोड़ने के लिए एक दूसरी पंक्ति

EXEC sp_addrolemember 'SomeRole', 'whatever user'

और मॉडल में ऐसा करें ताकि सभी नए डेटाबेस इनहेरिट हों।

कारण, आपको केवल एक बार अनुमति निर्धारित करनी चाहिए:

  • एक स्कीमा वस्तुओं के लिए एक कंटेनर है।
  • नई वस्तुएं स्कीमा से अनुमति प्राप्त करती हैं
  • एक भूमिका उपयोगकर्ताओं के लिए कंटेनर है
  • नए उपयोगकर्ताओं को एक भूमिका में जोड़ा जाता है और विरासत में मिला है

जैसा कि आपने पाया है, किसी डेटाबेस को माइग्रेट करना या पुनर्स्थापित करना उपयोगकर्ताओं को सीधे असाइन किए जाने पर ऑब्जेक्ट अनुमतियों को खो सकता है।तो क्यों अपने आप को उस स्थिति में रखा जाए?

आप CREATE LOGIN with a SID भी कर सकते हैं तो यह आपके सभी सर्वरों पर समान है और आपको अनाथ उपयोगकर्ता भी नहीं मिलते हैं।

यदि आपने सही प्रश्न पूछा है तो हम आपको कुछ कोडिंग बचा सकते हैं ...

व्यक्तिगत रूप से और अंत में, मैं कहूंगा कि यह "जरूरत है" पर बुरा व्यवहार है, "कंबल कुछ भी नहीं"

  0

तो कुछ इस तरह से: 'IF NOT EXISTS (Select 1 Fys from sys.database_principals WHERE [टाइप] = 'R' और [name] = 'ReadOnlyUsers')' 'CREATE ROLE [ReadlylyUsers]' 'EXEC sp_addrolemember 'db_datareader', 'ReadlyUU)। '' अनुदान? (चयन करें? या?) पर SCHEMA :: dbo से [ReadOnlyUsers] '' GO'' EXEC sp_addrolemember 'ReadOnlyUsers', 'ReadOnlyUser'' कैसे EXEC, UPDATE, DELETE, आदि को अलग करने के लिए यदि सभी ऑब्जेक्ट हैं। हालांकि dbo के तहत?उदाहरण के लिए ReadOnlyUser को अभी भी संग्रहीत PROEC EXEC करने की आवश्यकता होगी। 20 sep. 112011-09-20 15:27:51

  0

@ डेविड जॉर्ज: स्कीमा अनुमतियाँ स्वचालित रूप से वस्तु की परवाह किए बिना लागू होती हैं: इसलिए एक खरीद को EXEC और एक टेबल को S/I/U/D मिलता है।आपका ग्रांट ग्रांट एक्सेक्स, सेलेक्ट, अपडेट आदि होगा। 20 sep. 112011-09-20 16:33:55

  0

तो मैं 'ग्रांट एक्सेल, सेलेक्ट, अपडेट, इनसेट, स्लैम पर रन :: डिबो टू [ReadWriteUsers] चलाऊंगा 'क्या वह उस उपयोगकर्ता को संभावित रूप से DROP की प्रक्रिया की अनुमति तब देगा? 20 sep. 112011-09-20 18:54:03

  0

नहीं, उन्हें इसके लिए ALTER TABLE या ऐसे चाहिए 20 sep. 112011-09-20 22:29:39