Establecer permisos en todos los objetos para usuarios de SQL


1

Tengo un procedimiento que recorre todos los objetos de la base de datos y les asigna los permisos adecuados para ese objeto.¿Quiero saber si hay una mejor manera de hacer esto?Utilizo una base de datos modelo para crear nuevas bases de datos, así que tengo que ejecutar esto cada vez que creo una nueva base de datos.Aquí hay una idea de cómo se ve (nota: falta una parte desde el principio que elimina a todos los usuarios y recrea a los usuarios necesarios; esto es necesario desde el cambio del SID):

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

Este script continúa haciendo cosas similares para todas las vistas, funciones, etc. en la base de datos.¿Alguna idea para acelerar esto o hay una mejor manera de hacerlo?

6

Línea única para otorgar permisos en el nivel de esquema a un rol

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

Y una segunda línea para agregar usuarios al rol.

EXEC sp_addrolemember 'SomeRole', 'whatever user'

Y hacer esto en el modelo para que todas las nuevas bases de datos hereden.

Razones, debe establecer permisosuna vezsolamente:

  • Un esquema es un contenedor de objetos.
  • Los nuevos objetos heredan permisos del esquema.
  • Un rol es contenedor para los usuarios.
  • Los nuevos usuarios se agregan a un rol y heredan

Como ha encontrado, la migración o restauración de una base de datos puede perder permisos de objetos cuando se asigna directamente a los usuarios.Entonces, ¿por qué ponerte en esa posición?

Tú también puedesCREATE LOGIN with a SIDpor lo que es lo mismo en todos sus servidores y usted tampoco deja huérfanos a los usuarios.

Si hubieras hecho la pregunta correcta, podríamos haberte guardado algo de codificación ...

Personalmente y por último, diría que esta es una mala práctica sobre "necesidad de tener", no "manta para hacer nada"

  0

Algo así como: 'SI NO EXISTE (SELECCIONE 1 DE sys.database_principals DÓNDE [type] = 'R' AND [name] = 'ReadOnlyUsers')' 'CREATE ROLE [ReadOnlyUsers]' 'EXEC sp_addrolemember ',' ReadOnlyUs ' '' GRANT? (Seleccione? O?) ON SCHEMA :: dbo TO [ReadOnlyUsers] '' GO' 'EXEC sp_addrolemember 'ReadOnlyUsers', 'ReadOnlyUser'' Cómo diferenciar EXEC, ACTUALIZAR, BORRAR, etc. si todos los objetos son bajo dbo aunque?Por ejemplo, ReadOnlyUser aún necesitaría EXEC procesos almacenados. 20 sep. 112011-09-20 15:27:51

  0

@David George: los permisos de esquema se aplican automáticamente independientemente del objeto: por lo tanto, un proceso obtiene EXEC y una tabla obtiene S/I/U/D.Su GRANT sería GRANT EXEX, SELECT, UPDATE, etc. EN ... 20 sep. 112011-09-20 16:33:55

  0

así que ejecutaría 'GRANT EXEC, SELECT, UPDATE, INSERT, DELETE on SCHEMA :: dbo TO [ReadWriteUsers]' ¿Eso permitiría a ese usuario el permiso para potencialmente DROPAR un procedimiento? 20 sep. 112011-09-20 18:54:03

  0

No, necesitan ALTER TABLE o algo así para eso. 20 sep. 112011-09-20 22:29:39