Можно ли ограничить максимальное использование диска для пользователя

Можно ли установить ограничение на объем физического дискового пространства, которое пользователь (или группа пользователей) может использовать в одной базе данных? Например, существует ли какой-либо метод связывания схемы с файловой группой, а затем задан максимальный размер для файловой группы? Фактическая база данных будет иметь другие таблицы на ней и поэтому захочет иметь другие файловые группы, которые были бы неограниченными.

7 голосов | спросил SQLMIKE 28 PM00000040000003731 2012, 16:55:37

1 ответ


10

Если у ваших пользователей есть разрешения на создание в их собственной схеме, вы можете настроить триггер DDL для создания таблицы, которая возникает в другой файловой группе, чем та, которую вы связали с этим пользователем. Вы не можете предотвратить создание таблицы, но вы можете отбросить ее и вернуть сообщение об ошибке.

Сначала добавим файловую группу и файл с максимальным размером в существующую базу данных:

USE [master];
GO

ALTER DATABASE floob ADD FILEGROUP SomeUser;
GO

ALTER DATABASE floob ADD FILE 
(
  NAME = SomeUser, 
  FILENAME = 'C:\...wherever...\floob_SomeUser.mdf',
  MAXSIZE = 20MB -- your quota, this part is important!
) TO FILEGROUP SomeUser; -- this part is also important!
GO

Теперь давайте создадим схему и логин, которые могут управлять этой схемой:

CREATE SCHEMA SomeUser;
GO

CREATE LOGIN SomeUser WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO

USE floob;
GO

CREATE USER SomeUser FROM LOGIN [SomeUser] WITH DEFAULT_SCHEMA = SomeUser;
GO

GRANT CONTROL ON SCHEMA::SomeUser TO dbo;
GRANT CREATE TABLE TO SomeUser;
GO

Прежде всего, с этими настройками этот пользователь не может создавать таблицы в dbo или любой другой схеме, отличной от их собственной, что означает, что по умолчанию они смогут создавать таблицы только в ограниченном файле [group]. Если вы подключаетесь к блоку базы данных как этот пользователь и пытаетесь создать таблицу в dbo:

CREATE TABLE dbo.foo(id INT);

Вы получите эту ошибку:

  

Msg 2760, Level 16, State 1, Line 1
Указанное имя схемы «dbo» либо не существует, либо у вас нет разрешения на его использование.

И из-за этого default_schema этого пользователя, не указывая схему SomeUser, по-прежнему будет создана таблица, созданная в SomeUser (хотя вы действительно должны настаивать на лучших практиках здесь, особенно в этом случае, и всегда указывая имя схемы при создании или ссылающиеся объекты ).

Теперь мы можем создать такой триггер, который будет откатывать всю партию в случае, когда файловая группа не указана:

CREATE TRIGGER EnsureFilegroup
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
  DECLARE @e XML = EVENTDATA();

  IF @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)') = N'SomeUser'
     AND NOT EXISTS (SELECT 1 FROM sys.indexes AS i INNER JOIN sys.data_spaces AS d
       ON i.data_space_id = d.data_space_id
       WHERE d.name = N'SomeUser'
       AND i.[object_id] = OBJECT_ID(
        QUOTENAME(@e.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(128)'))
        + '.' + QUOTENAME(@e.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(128)')))
       AND i.index_id IN (0,1))

  BEGIN
    RAISERROR('You must place your objects on the SomeUser filegroup.', 11, 1);
    ROLLBACK TRANSACTION;
  END
END
GO

Особенно удобно сохранять имена пользователей, схем и файловых групп одинаковыми, что упростит их использование в качестве программных. Вы можете сначала выполнить проверку того, что используемая схема не является одной из ваших «неограниченных» схем, а затем приступить к проверке, которая сравнивает имя схемы с именем файловой группы. Если он не соответствует, этот пользователь имеет права на неправильную схему и пытается создать объекты в неположенном месте, и возникает ошибка:

ALTER TRIGGER EnsureFilegroup
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
  DECLARE @e XML = EVENTDATA();

  IF @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)') <> N'dbo'
    -- or NOT IN (N'dbo', N'other unlimited schemas')
  BEGIN

    IF NOT EXISTS (SELECT 1 FROM sys.indexes AS i INNER JOIN sys.data_spaces AS d
       ON i.data_space_id = d.data_space_id
       WHERE d.name = @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)')
       AND i.[object_id] = OBJECT_ID(
        QUOTENAME(@e.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(128)'))
        + '.' + QUOTENAME(@e.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(128)')))
       AND i.index_id IN (0,1))
    BEGIN
      RAISERROR('You must place your objects on your own filegroup.', 11, 1);
      ROLLBACK TRANSACTION;
    END
  END
END
GO
ответил Aaron Bertrand 28 PM00000060000000231 2012, 18:36:02

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132