Хранимые процедуры SQL

Базы данных Хранимые процедуры SQL

Помечено: ,

  • В этой теме 0 ответов, 1 участник, последнее обновление 5 месяцев назад сделано Васильев Владимир Сергеевич.
Просмотр 0 веток ответов
  • Автор
    Сообщения
    • #6375
      @admin
      StudLance.ru

      При разработке баз данных (БД) для коммерческих приложений всегда встает вопрос выбора стратегии разделения прав доступа к данным между пользователями. Обычно можно выделить несколько категорий пользователей, которые имеют принципиально разные права доступа к данным в БД:

      1. Администраторы системы/приложения;
      2. Сотрудники компании, участвующие в бизнес-процессе на разных этапах;
      3. Клиенты и сторонние пользователи, получающие ограниченный доступ к системе.

      Администраторы имеют полный доступ к объектам БД, могут изменять данные и структуру таблиц, создавать и удалять триггеры и хранимые процедуры и т.п.

      Рядовые сотрудники компании имеют гораздо меньшие права: могут просматривать и изменять данные некоторых таблиц, другие таблицы могут только просматривать, а к части таблиц вообще не имеют доступа.

      Клиенты и сторонние пользователи как правила не имеют прямого доступа к таблицам вообще, а могут просматривать данные с помощью представлений (view) или добавлять информацию при помощи вызова хранимых процедур с определенными параметрами.

      Для реализации подобной схемы обычно пользуются стандартными средствами, предоставляемыми СУБД – заданием прав доступа на таблицы (иногда — на отдельные поля), представления и хранимые процедуры. Для СУБД, имеющих развитую систему прав доступа (permissions) этого обычно бывает достаточно. Но бывают случаи, когда требуется организовать дополнительный уровень безопасности с запретом прямого изменения данных в таблицах пользователями. Например, чтобы в БД MS SQL нельзя было, подключившись с помощью Query Analyzer, произвольно изменить данные с помощью SQL-запросов типа Update, Delete или Insert. Это может использоваться для параметрического контроля изменений и дополнительной проверки прав пользователя на доступ к данным.

      Таким образом, для организации доступа к данным по схеме «чтение/изменение/запись» потребуются два типа хранимых процедур:

      1. Процедура типа apply, которая производит обновление существующих данных и добавление новых;
      2. Процедура типа delete, которая производит удаление данных.

      Оба типа процедур в своем коде содержат проверку одного из параметров («авторизационный» параметр), по значению которого определяются права пользователя на запуск данной процедуры. Иными словами, если этот параметр не известен пользователю и введен неправильно – то хранимая процедура запустится, но не произведет никаких действий.

      Рассмотрим применение вышеизложенной концепции на примере MS SQL Server. Пусть дана таблица Vendor, которую можно создать при помощи скрипта. В примерах ниже используется MS SQL Server:

      CREATE TABLE [vendor] (
        [vendor_id] [int] IDENTITY (1, 1) NOT NULL ,
        [vendor_name] [varchar] (40) COLLATE Latin1_General_CI_AI NULL ,
        [vendor_tax_id] [varchar] (20) COLLATE Latin1_General_CI_AI NULL ,
        [vendor_phone] [varchar] (20) COLLATE Latin1_General_CI_AI NULL ,
        [vendor_address_line1] [varchar] (20) COLLATE Latin1_General_CI_AI NULL ,
        [vendor_fax] [varchar] (20) COLLATE Latin1_General_CI_AI NULL ,
        [vendor_address_line2] [varchar] (20) COLLATE Latin1_General_CI_AI NULL ,
        [vendor_city] [varchar] (20) COLLATE Latin1_General_CI_AI NULL ,
        [vendor_zip] [varchar] (20) COLLATE Latin1_General_CI_AI NULL ,
        [vendor_state_id] [varchar] (20) COLLATE Latin1_General_CI_AI NULL ,
        [vendor_active] [int] NULL ,
        [vendor_comments] [varchar] (200) COLLATE Latin1_General_CI_AI NULL
      ) ON [PRIMARY]

      Код хранимых процедур типа apply и delete для изменения данных в этой таблице будет выглядеть следующим образом:

      CREATE PROCEDURE sp_apply_vendor
        -- in parameters
        @employee_id int,
        @vendor_id int,
        @vendor_name varchar(40),
        @vendor_tax_id varchar(20),
        @vendor_phone varchar(20),
        @vendor_address_line1 varchar(20),
        @vendor_address_line2 varchar(20),
        @vendor_fax varchar(20),
        @vendor_city varchar(20),
        @vendor_zip varchar(20),
        @vendor_state_id varchar(20),
        @vendor_active int,
        @vendor_comments varchar(200),
        -- out parameters
        @par_out int output
      AS
      
      exec @par_out = sp_check_permission @employee_id
      if @par_out <> 0 return
      
      if exists(select * from vendor where vendor_id=@vendor_id)
      begin
        --update record
        update vendor set
        vendor_name = @vendor_name,
        vendor_tax_id = @vendor_tax_id,
        vendor_phone = @vendor_phone,
        vendor_address_line1 = @vendor_address_line1,
        vendor_address_line2 =@vendor_address_line2,
        vendor_fax = @vendor_fax,
        vendor_city = @vendor_city,
        vendor_zip = @vendor_zip,
        vendor_state_id = @vendor_state_id,
        vendor_active = @vendor_active,
        vendor_comments = @vendor_comments
        where vendor_id=@vendor_id
      
        set @par_out=@vendor_id
      end
      else
      begin
        --insert record
        insert into vendor(
        vendor_name,
        vendor_tax_id,
        vendor_phone,
        vendor_address_line1,
        vendor_address_line2,
        vendor_fax,
        vendor_city,
        vendor_zip,
        vendor_state_id,
        vendor_active,
        vendor_comments
        ) values(
        @vendor_name,
        @vendor_tax_id,
        @vendor_phone,
        @vendor_address_line1,
        @vendor_address_line2,
        @vendor_fax,
        @vendor_city,
        @vendor_zip,
        @vendor_state_id,
        @vendor_active,
        @vendor_comments)
      
        set @par_out=@@identity
      end
      
      CREATE PROCEDURE sp_delete_vendor
        -- in parameters
        @employee_id int,
        @vendor_id int,
        -- out parameters
        @par_out int output
      AS
      
      exec @par_out = sp_check_permission @employee_id
      if @par_out <> 0 return
      
      delete from vendor where vendor_id=@vendor_id

      В этих хранимых процедурах производится проверка «авторизационного» параметра @employee_id посредством вызова хранимой процедуры sp_check_permission. Если эта хранимая процедура возвращает значение, отличное от 0 — то считается, что значение параметра передано неверное и вызывающая процедура завершается без выполнения каких-либо действий.

      Процедура sp_check_permission может выполнять проверку переданного ей параметра в соответствии с бизнес логикой приложения. Например, в простейшем случае это может выглядеть так:

      CREATE PROCEDURE sp_check_permission
        @employee_id int
      AS
      if @employee_id =123456 return 0 else return -1

      Таким образом, мы получили «паролированный» доступ к запуску хранимых процедур – для запуска процедуры нужно знать правильный «пароль», который нужно передать в качестве значения авторизационного параметра. Этот пароль может быть известен только приложению – так можно сделать запуск хранимых процедур доступным коду приложения, но не доступным напрямую пользователю, под логином которого выполняется приложение.

      Данный подход позволяет расширить функции СУБД по разграничению прав доступа пользователей к данным, что особо актуально для СУБД, у которых встроенный набор таких средств может оказаться недостаточным: например, MySQL.

      Автор: 5Star Solution LLC© 2007.

      StudLance.ru

Просмотр 0 веток ответов
  • Для ответа в этой теме необходимо авторизоваться.
×