Vous êtes ici :
Lorsque vous créez un database snapshot (instantané de base de données), les utilisateurs, permissions et appartenance au rôles de bases de données sont issus de la base source et ne peuvent plus être modifiés par la suite, le snapshot étant en lecture seule.
Si vous voulez que certains utilisateurs aient accès à ce snapshot, sans pour autant qu’il aient l’accès à la base source, le seul moyen est d’octroyer temporairement des permissions, juste pour la création du snapshot. Cela devient vite une opération lourde à gérer et à maintenir…
Le script qui suit permet d’automatiser ce processus sur un ensemble de bases de données. Avant la création du snapshot, il effectue :
- La création des users (pour des logins existants)
- L’ajout de leur appartenance à des rôles de base de données
Une fois le snapshot créé, il permet d’effectuer l’opération inverse :
- Supprimer les users créés
Pour installer l’ensemble des objets, se positionner dans la base de données msdb (ou une autre base de données d’administration)
3 tables sont créées (qui contiendront la configuration à appliquer), ainsi que les 4 procédures stockées suivantes :
- TemporaryPermissionsAdd : permet d’ajouter un jeu de permissions à un login
- TemporaryPermissionsRemove : permet de retirer un jeu de permissions à un login, mêmes paramètres
- TemporaryPermissionsReport : permet d’afficher le jeu de permissions configurées
Pour ces 3 premières procédures, les paramètres sont : @LoginName sysname, @DatabaseName sysname, @RoleName sysname, @JobName VARCHAR(30)
(le paramètre jobname permet d’établir plusieurs profils de configuration différents)
- TemporaryPermissionsSetAll : permet d’octroyer ou de retirer les permissions, prend les paramètres suivants :
- @action = ‘ADD’ ou ‘REMOVE’
- @jobname = nom du profil de configuration
- @test : si not NULL, mode de test sans effectuer l’opération
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 |
/*------------------------------------------------------------------- [SCRIPT] TemporaryPermissionsInstallScript.sql [DATABASE] msdb or an admin database [DESCRIPTION] Install system (tables, procedures) [MAJ PAR] DATAFLY - Arian Papillon [DATEMAJ] 20180619 [INSTRUCTIONS] -------------------------------------------------------------------*/ ---- Création des tables /****** Object: Table [dbo].[TemporaryPermissionsDatabases] Script Date: 19/06/2014 16:42:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TemporaryPermissionsDatabases]( [ID_Database] [INT] IDENTITY(1,1) NOT NULL, [Name_Database] [sysname] NOT NULL, PRIMARY KEY CLUSTERED ( [ID_Database] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UC_TemporaryPermissionsDatabases] UNIQUE NONCLUSTERED ( [Name_Database] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[TemporaryPermissionsLogins] Script Date: 19/06/2014 16:42:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TemporaryPermissionsLogins]( [ID_Login] [int] IDENTITY(1,1) NOT NULL, [Name_Login] [sysname] NOT NULL, PRIMARY KEY CLUSTERED ( [ID_Login] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UC_TemporaryPermissionsLogins] UNIQUE NONCLUSTERED ( [Name_Login] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[TemporaryPermissionsRoles] Script Date: 20/06/2014 13:24:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TemporaryPermissionsRoles]( [ID_Role] [int] IDENTITY(1,1) NOT NULL, [Name_Role] [sysname] NOT NULL, [ID_Login] [int] NULL, [ID_Database] [int] NULL, [Name_Job] [varchar](30) NULL, PRIMARY KEY CLUSTERED ( [ID_Role] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UC_TemporaryPermissionsRoles] UNIQUE NONCLUSTERED ( [ID_Database] ASC, [ID_Login] ASC, [Name_Role] ASC, [Name_Job] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[TemporaryPermissionsRoles] WITH CHECK ADD CONSTRAINT [FK_TemporaryPermissionsRoles_TemporaryPermissionsDatabases] FOREIGN KEY([ID_Database]) REFERENCES [dbo].[TemporaryPermissionsDatabases] ([ID_Database]) GO ALTER TABLE [dbo].[TemporaryPermissionsRoles] CHECK CONSTRAINT [FK_TemporaryPermissionsRoles_TemporaryPermissionsDatabases] GO ALTER TABLE [dbo].[TemporaryPermissionsRoles] WITH CHECK ADD CONSTRAINT [FK_TemporaryPermissionsRoles_TemporaryPermissionsUsers] FOREIGN KEY([ID_Login]) REFERENCES [dbo].[TemporaryPermissionsLogins] ([ID_Login]) GO ALTER TABLE [dbo].[TemporaryPermissionsRoles] CHECK CONSTRAINT [FK_TemporaryPermissionsRoles_TemporaryPermissionsUsers] GO ------------------- Procedures ------------------------------------------------ /****** Object: StoredProcedure [dbo].[TemporaryPermissionsSetAll] Script Date: 20/06/2014 11:45:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[TemporaryPermissionsSetAll] @action VARCHAR(10) = NULL , @jobname VARCHAR(30) = NULL , @test VARCHAR(10) = NULL AS DECLARE @SQLCmd NVARCHAR(MAX) , @LoginName sysname = NULL , @DatabaseName sysname = NULL , @RoleName sysname = NULL IF @action IS NULL OR @jobname IS NULL BEGIN PRINT 'Cette procédure modifie les permissions des bases de données en fonction des paramètres enregistrés' PRINT 'EXEC TemporaryPermissionsSetAll @action=paramètre , @job=paramètre [,@test=paramètre]' PRINT 'Utiliser le paramètre @action avec ADD ou REMOVE et le paramètre @job pour ajouter ou retirer les permissions pré-configurées' PRINT 'Renseigner le paramètre @test pour tester sans exécuter' RETURN END IF @test IS NOT NULL PRINT 'Mode TEST :' -- Curseur sur les bases DECLARE DBCurseur CURSOR FOR SELECT DISTINCT [Name_Database] FROM [dbo].[TemporaryPermissionsDatabases] D JOIN [dbo].[TemporaryPermissionsRoles] R ON D.ID_Database = R.ID_Database WHERE R.Name_Job = @jobname OPEN DBCurseur FETCH NEXT FROM DBCurseur INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE LoginCurseur CURSOR FOR SELECT DISTINCT Name_Login FROM [dbo].[TemporaryPermissionsLogins] L JOIN [dbo].[TemporaryPermissionsRoles] R ON L.ID_Login = R.ID_Login JOIN [dbo].[TemporaryPermissionsDatabases] D ON R.ID_Database = D.ID_Database WHERE D.Name_Database = @DatabaseName AND R.Name_Job = @jobname OPEN LoginCurseur FETCH NEXT FROM LoginCurseur INTO @LoginName WHILE @@FETCH_STATUS = 0 BEGIN -- Suppression des utilisateurs SET @SQLCmd = 'USE [' + @DatabaseName + '] ' + 'IF EXISTS (SELECT * FROM sys.schemas WHERE name = N''' + @LoginName + ''') ' + ' DROP SCHEMA [' + @LoginName + '] ' + 'IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @LoginName + ''') ' + ' DROP USER [' + @LoginName + ']' PRINT @SQLCmd IF @test IS NULL BEGIN BEGIN TRY EXEC (@SQLCmd) END TRY BEGIN CATCH PRINT 'Erreur : la commande n''a pas pu être exécutée' END CATCH END -- Si ADD, ajout du user et des permissions pour la base et le user IF UPPER(@action) = 'ADD' BEGIN -- Create new user SET @SQLCmd = 'USE [' + @DatabaseName + '] CREATE USER [' + @LoginName + '] FROM LOGIN [' + @LoginName + '] ' PRINT @SQLCmd IF @test IS NULL BEGIN BEGIN TRY EXEC (@SQLCmd) END TRY BEGIN CATCH PRINT 'Erreur : la commande n''a pas pu être exécutée' END CATCH END DECLARE CreateCurseur CURSOR FOR SELECT R.Name_Role FROM [dbo].[TemporaryPermissionsDatabases] D JOIN [dbo].[TemporaryPermissionsRoles] R ON D.ID_Database = R.ID_Database JOIN [dbo].[TemporaryPermissionsLogins] L ON R.ID_Login = L.ID_Login WHERE ( L.Name_Login = @LoginName ) AND ( D.Name_Database = @DatabaseName ) AND (R.Name_Job = @jobname) OPEN CreateCurseur FETCH NEXT FROM CreateCurseur INTO @RoleName WHILE @@FETCH_STATUS = 0 BEGIN -- Set rolemember SET @SQLCmd = 'USE [' + @DatabaseName + '] EXEC sp_addrolemember N''' + @RoleName + ''', N''' + @LoginName + ''' ' PRINT @SQLCmd IF @test IS NULL BEGIN BEGIN TRY EXEC (@SQLCmd) END TRY BEGIN CATCH PRINT 'Erreur : la commande n''a pas pu être exécutée' END CATCH END FETCH NEXT FROM CreateCurseur INTO @RoleName END CLOSE CreateCurseur DEALLOCATE CreateCurseur END FETCH NEXT FROM LoginCurseur INTO @LoginName END CLOSE LoginCurseur DEALLOCATE LoginCurseur FETCH NEXT FROM DBCurseur INTO @DatabaseName END CLOSE DBCurseur DEALLOCATE DBCurseur GO /****** Object: StoredProcedure [dbo].[TemporaryPermissionsReport] Script Date: 20/06/2014 11:43:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[TemporaryPermissionsReport] @LoginName sysname = NULL, @DatabaseName sysname = NULL, @RoleName sysname = NULL, @JobName varchar(30) = NULL AS SELECT R.Name_Job,D.Name_Database,L.Name_Login, R.Name_Role FROM [dbo].[TemporaryPermissionsDatabases] D JOIN [dbo].[TemporaryPermissionsRoles] R ON D.ID_Database = R.ID_Database JOIN [dbo].[TemporaryPermissionsLogins] L ON R.ID_Login = L.ID_Login WHERE (L.Name_Login = @LoginName OR @LoginName IS NULL) AND (D.Name_Database = @DatabaseName OR @DatabaseName IS NULL) AND (R.Name_Role = @RoleName OR @RoleName IS NULL) AND (R.Name_Job = @JobName OR @JobName IS NULL) ORDER BY R.Name_Job, D.Name_Database, L.Name_Login, R.Name_Role GO /****** Object: StoredProcedure [dbo].[TemporaryPermissionsRemove] Script Date: 20/06/2014 11:39:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[TemporaryPermissionsRemove] @LoginName sysname , @DatabaseName sysname , @RoleName sysname , @JobName VARCHAR(30) AS -- Vérification paramètres SET NOCOUNT ON DECLARE @LoginID INT , @DatabaseID INT , @SQLCmd NVARCHAR(MAX) , @RoleID INT IF NOT EXISTS ( SELECT * FROM [dbo].[TemporaryPermissionsLogins] L JOIN [dbo].[TemporaryPermissionsRoles] R ON L.ID_Login = R.ID_Login JOIN [dbo].[TemporaryPermissionsDatabases] D ON R.ID_Database = D.ID_Database WHERE L.Name_Login = @LoginName AND D.Name_Database = @DatabaseName AND R.Name_Role = @RoleName AND R.Name_Job = @JobName) BEGIN PRINT 'Ce jeu de permissions n''existe pas : ' + @LoginName + ' pour la base ' + @DatabaseName + ' avec le rôle ' + @RoleName END ELSE BEGIN SELECT @LoginID = R.ID_Login , @DatabaseID = R.ID_Database , @RoleID = R.ID_Role FROM [dbo].[TemporaryPermissionsLogins] L JOIN [dbo].[TemporaryPermissionsRoles] R ON L.ID_Login = R.ID_Login JOIN [dbo].[TemporaryPermissionsDatabases] D ON R.ID_Database = D.ID_Database WHERE L.Name_Login = @LoginName AND D.Name_Database = @DatabaseName AND R.Name_Role = @RoleName AND R.Name_Job = @JobName -- Supprimer la permission DELETE FROM [dbo].[TemporaryPermissionsRoles] WHERE ID_Role = @RoleID -- Supprimer le login si aucune autre permission IF ( SELECT COUNT(*) FROM [dbo].[TemporaryPermissionsLogins] L JOIN [dbo].[TemporaryPermissionsRoles] R ON L.ID_Login = R.ID_Login WHERE L.ID_Login = @LoginID ) = 0 BEGIN DELETE FROM [dbo].[TemporaryPermissionsLogins] WHERE ID_Login = @LoginID END -- Supprimer la base si aucune autre permission IF ( SELECT COUNT(*) FROM [dbo].[TemporaryPermissionsDatabases] D JOIN [dbo].[TemporaryPermissionsRoles] R ON D.ID_Database = R.ID_Database WHERE R.ID_Database = @DatabaseID ) = 0 BEGIN DELETE FROM [dbo].[TemporaryPermissionsDatabases] WHERE ID_Database = @DatabaseID END PRINT 'Ce jeu de permissions a été supprimé : ' + @LoginName + ' pour la base ' + @DatabaseName + ' avec le rôle, Job '+@JobName END GO /****** Object: StoredProcedure [dbo].[TemporaryPermissionsAdd] Script Date: 20/06/2014 11:28:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[TemporaryPermissionsAdd] @LoginName sysname , @DatabaseName sysname , @RoleName sysname , @JobName VARCHAR(30) AS -- Vérification paramètres SET NOCOUNT ON DECLARE @LoginID INT, @DatabaseID INT, @SQLCmd NVARCHAR(MAX), @RoleExist INT IF NOT EXISTS ( SELECT * FROM sys.syslogins WHERE name = @LoginName ) BEGIN PRINT 'Le login n''existe pas !' RETURN END IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = @DatabaseName ) BEGIN PRINT 'La base de données n''existe pas !' RETURN END SET @SQLCmd = 'SELECT @RoleExist=COUNT(*) FROM '+@DatabaseName+'.sys.database_principals WHERE name = '''+@RoleName+''' AND type = ''R''' EXECUTE sp_executesql @SQLCmd, N'@RoleExist INT OUTPUT',@RoleExist OUTPUT IF @RoleExist = 0 BEGIN PRINT 'Le rôle n''existe pas !' RETURN END -- insertions SELECT @DatabaseID = ID_Database FROM TemporaryPermissionsDatabases WHERE Name_Database = @DatabaseName IF @DatabaseID IS NULL BEGIN INSERT [dbo].[TemporaryPermissionsDatabases] ([Name_Database]) VALUES (@DatabaseName) SET @DatabaseID = @@IDENTITY END SELECT @LoginID = [ID_Login] FROM [dbo].[TemporaryPermissionsLogins] WHERE [Name_Login] = @LoginName IF @LoginID IS NULL BEGIN INSERT [dbo].[TemporaryPermissionsLogins] ([Name_Login]) VALUES (@LoginName) SET @LoginID = @@IDENTITY END IF NOT EXISTS (SELECT * FROM [dbo].[TemporaryPermissionsRoles] WHERE [Name_Role] = @RoleName AND [ID_Login] = @LoginID AND [ID_Database] = @DatabaseID AND Name_Job = @JobName) INSERT [dbo].[TemporaryPermissionsRoles] ([Name_Role],[ID_Login],[ID_Database],Name_Job) VALUES (@RoleName,@LoginID,@DatabaseID,@JobName) PRINT 'Permission configurée pour le job '+@JobName+' : ajout de '+@LoginName+' pour la base '+ @DatabaseName + ' avec le rôle '+@RoleName GO |
Table of Contents