есть SQL база, с ней будет работать небольшое число потребителей (апач, 1с и т.д.), но с каждым потребителем работают сотни конечных пользователей.

Как правильно разграничить доступ этих конечных пользователей на SQL сервере?

разумеется сразу отказываемся от доступа к таблицам и переходим на ХП, а вот как в рамках хранимок это сделать?

сразу на ум приходит несколько способов
1. в каждую хранимку добавляем 2 параметра ID_User и Pass_User, и в хранимке проверяем
2. при первом входе выдаем юзеру уникальный ID который действует в рамках сеанса или еще чего-то, и далее этот параметр передаем в хранимку
3. наверно есть более правильные способы?

(0) найти и нанять спеца по конкретным SQL базам не?

зачем на sql должны ходить непосредственно конечные юзеры?

(1) + в смысле по серверам бд

(0) зачем хочешь ограничить конечных пользователей? Если они все равно будут работать через апач, 1С? Какая конечная цель?

представим интернет магазин

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

(5) В этом случае у тебя есть приложение которое отдает данные согласно настроенными/сделанным правам доступа. Конечные пользователи не работают с БД.

(6) ну не совсем так, дело в том, что промежуточного звена может и не быть.

Пример:

есть терминал, на нем работают 10 филиалов, каждый в своем узле РИБ 1с, эти узлы конектятся к SQL по АДО

(7) Делай вебсервис и пусть коннектятся к sql через вебсервис

(7) https://msdn.microsoft.com/ru-ru/library/ms189802.aspx

(8) и что это поменяет? как веб сервис поможет решить проблему доступа?

(10) с авторизацией потому что

(9) не пойдет...

мне нужно реализовать аналог RLS но в хранимке SQL

(12) ну, так и проверяй доступ в своей хранимке

на 2016 есть рлс

(11) я не собираюсь на SQL заводить тысячи акаунтов и рулить политиками SQL....

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

(15) так у тебя получается безопасность через незнание чужого ID.
В целом это не совсем безопасность

(15) не понял. А как же авторизация? как ты определишь, что подключился именно Вася Пупкин с максимальными правами, а не гость какой-нибудь?

(15) в 11 я говорил не про sql а про веб сервис с авторизацией

(17) в этом и есть вопрос

(19) тогда тебе придется и пароли хранить и в каждую ХП передавать и юзера и пароль и прочие параметры

Двухзвенка? Серьезно? В 2017?

в любом случае нужен сервис авторизации.
Он же может быть и проксей к sql.
получаем классический сервер приложений

(20) это я и описал под п.1 в (0) но мне это не очень нравится...

(15) А зачем заводить тысячу аккаунтов? В некой табличке лежат данные пользователя, каждая запись имеет ИД пользователя. Имена и ИД пользователя хранятся в отдельной табличке. Веб сервис по имени пользователя (или еще как) определяет его ИД и возвращает из первой таблички данные с этим ИД. Не?

(16) Точно. Но это можно обойти, например:
1) Устанавливается сеанс связи с БД, для этого Клиент кидает запрос к хранимке со своим ИД - она ему возвращает произвольный длинный ключ.
2) Клиент сливает этот ключ со своим ИД, полученное прогоняет через МД-5 и все запросы кидает с указанием этого МД-5 хеша
3) Сервер при выдаче ключа у себя тоже генерирует аналогичный хеш и сопоставляет его с указанным ИД, далее возвращает записи для этого ИД-шника.

(25) не много не правильно

1. Устанавливается сеанс связи, сеанс имеет ИД, ИД сеанса возвращается клиенту.
2. Клиент берет свой логин+пароль+ИДСеанса и шифрует их ИДсеанса+ХешПароля шлет на сервер вместе с открытым логином
3. Сервер расшифровывает это (на сервере есть логин, пароль, ИД), и сравнивает, Если все ок отправляет уникальный гуид который будет сеансовым ключем для всех дальнейших вызовов

разумеется все это при условии включённого шифрования трафика

(26) +

а теперь вопрос - можно ли доверять ID сеансу связи?

А сервер  - это какой сервер?

Если двузвенка то в любом случае разруливать доступ придется логикой приложения.
Или делать каждого юзера в sql  с правами на таблицы.

Если ты не доверяешь управлять правами приложению, ты ты не можешь и доверять переденным данным от приложения

(28) SQL
(29) я не имею права доверять клиенту

(30) сам себе жизнь усложняешь

на ноде ту же прослойку написать пару часо работы

прослойка какая-нибудь нужна по любому...

нормальный одмин за скуль выставленный наружу - оторвет руки

его (скуль) сразу ломать начнут

(32) еще раз спрашиваю - что дает прослойка? что она должна делать? по какому алгоритму она определяет и самое главное передает в SQL разрешенный уровень доступа для конкретной ХП

(35) прослойка занимается авторизацией.
То бишь не нужно будет эиту автоиризацию изобретать, а просто взять готовые библиотеки

между прослойкой и sql полное доверие. прослойка видит все

(36) авторизация какая?

доменная? точно не наш случай !!!

прослойка нужна для защиты от ДДОС и фильтрации IP по регионам, но слово "авторизация" мне не понятно...


для примера возьмем веб сервис 1с - где происходит авторизация? она происходит на сервере 1с а на апаче она опциональна и в дубль... [внезапно правда?]

> где происходит авторизация? она происходит на сервере 1с а на апаче она опциональна и в дубль

и что?

это же не значит что на sql есть 150 логинов

(38) ты мыслишь методами 90х годов

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

(38) Если берем 1С Веб (Файл БД - модуль 1с для апач - апач), то авторизация в модуле 1с для апач, где модуль смотрит таблицу БД с логинами/паролями. Это как раз пример прослойки.

(0) Для Оракла я бы тебе подсказал, а как этот механизм называется в скуле, я не знаю. Нужно рыть носом BOL, что лень, честно говоря.

select SYSTEM_USER, не?

(0) Кажется Application Roles это оно.

Проверяешь логин/пароль по табличке, если OK, дергаешь sp_setapprole и все, до конца сеанса юзер имеет права роли и только их.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/application-roles

http://sqlmag.com/business-intelligence/mastering-application-roles

(44) вот это похоже то, что нужно !!!

(44) Интересно. Но я так понял логин/пароль открытым текстом пойдут.

(46) нет, там третий параметр есть

(45) похоже это не совсем то, что нужно... хотя определенные мысли как это можно использовать - есть

короче сделал через временную таблицу, подскажите, такое использование параметров безопасно с точки зрения SQL инъекции ?


ALTER PROCEDURE [dbo].[Authorization]  
    @KeySource char(200), -- Имя узла обмена
    @PassSource char(200) -- Пароль узла обмена
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @EmpIDVariable int;
    SELECT @EmpIDVariable = ID FROM Sources WHERE Sources.KeySource = @KeySource AND Sources.Pass = @PassSource
    CREATE TABLE #TempSource (id_Source INT);
    INSERT INTO #TempSource ("id_Source") VALUES (@EmpIDVariable);
    SELECT id_Source FROM #TempSource;
END

(48) С параметрами OK. А зачем этот онанизм с INSERT-SELECT?

(49) ступил, селект не нужен...

а как запретить текущему пользователю менять/удалять эту временную таблицу?

(50) insert тоже, create as select

А зачем запрещать?

(51) вкратце суть такая

при авторизации я пишу в эту таблицу ИД
далее все остальные ХП делают джойн к этой ВТ

получается что-то вроде RLS ... соответственно нужен запрет на подмену...

сейчас я сделал просто -  завел юзера и дал к текущей базе всего 2 правила - соединение и выполнение, вроде поведение какое мне нужно, включая ВТ

(52) Джойни с табличной функцией.

(48) блин, вчера работало, сегодня нет....

сегодня временная таблица удаляется при завершении ХП, а вчера не удалялась...