Понедельничная головоломка - замена authid CU

Не хочу откладывать до традиционной пятницы

Задача:
1. Ситуация.
Есть некое ПО, реализованное в схеме XXX.
С ним работают пользователи БД (например, YYY).
Вызывая функционал ПО с помощью методов объектных типов и пакетов (XXX.OT%, XXX.PCK%), пользователи получают выборки, описанные в метаданных ПО (запросы). Вызов происходит с помощью execute immediate, dbms_sql, причем (в связи с наследованием) сам вызов execute immediate происходит глубоко в потрохах ПО (цепочка вызовов XXX.OT_4 -> (parent) XXX.OT_3 ->... XXX.OT_1.DoExecuteImmediate).
Пользователь XXX обладает большими правами, по крайней мере, в его схеме валяется куча таблиц (например, XXX.T1).

2. Проблема.
Допустим, пользователь YYY имеет права создавать новые выборки в системе. Таким образом, они могут создать выборку "select * from T1". Выборки создаются в предположении, что текущая схема - XXX.
Допустим, что пользователь YYY не имеет прав на выборку из таблицы T1 (с помощью грантов). Но, выполняя выборку с помощью объектного метода XXX.OT_4 (права на методы - отдельный уровень метаданных, но выборку пользователь создал сам и имеет не нее права), пользователь выполняет ее с правами XXX, и, естественно, все видит.

3. Вопрос.
Как заставить систему функционировать так, чтобы вызов произвольного SQL происходил с правами вызвавшего XXX.OT_4 пользователя?
Hint: authid current_user на OT_1 не сработает, т.к. его вызывает OT_2, который уже не authid, и права будут от XXX.
Т.е. пользователь YYY должен иметь возможность создать запрос к таблице T1, сервереная часть должна под пользователем YYY проверить корректность запроса (как будто под XXX) а вот посмтореть данные пользователю YYY не судьба. Только имея грант на T1, можно получить данные из запроса.

4. Ограничения
Не подходит:
- Всем объектным типам, наследникам OT_1, включая его, назначить authid current_user.
- Положиться на решение на основе RLS (OLS тоже не предлагать)
- Перенести логику выборок в отдельный пакет в схеме XXX и дать ему authid current_user - для этого придется менять внешний интерфейс ПО.

5. Возможный вариант.
Все пользователи регистрируются в метаданных ПО, иначе не имеют доступа к базовому функционалу. В момент регистрации предполагается в схеме пользователя создавать пакет (права XXX это позволяют), который выполняет execute immediate и возвращает SYS_REFCURSOR (+ еще пару методов для execute immediate без выборки и работы с dbms_sql). Базовые методы ОТ_1 вместо вызова execute immediate переписать так, чтобы они вызывали пакет в схеме текущего пользователя, который и производит выполнение SQL - в таком случае права проверяются для него, а не для XXX.
Недостаток - нужна некоторая доработка по генерации этих пакетов при регистрации и контролю нормального состояния пакетов - не поменялись ли. Предполагается кэшировать LAST_DDL_TIME (или TIMESTAMP) из dba_objects для спецификации и тела сгенерированных пакетов. От SYS'а, кто это может поменять, не закрываемся

Disclaimer
КГ/АМ - не принимаются, ибо не конструктифф.
Переписать все - аналогично.
Ограничения отменить нельзя.
Аффтар к истокам архитектуры отношения не имеет, так что памидорами не кидаться. Изначально система полностью рулилась через RLS, теперь надо и через гранты, причем ведущиеся вне метаданных системы.

в триггере на логон сменить схему?
alter session set current_schema=XXX.

700098

в триггере на логон сменить схему? alter session set current_schema=XXX.

current_schema на привилегии вообще не влияет. Только на разрешение имен.

Goldminer

700098 в триггере на логон сменить схему? alter session set current_schema=XXX. current_schema на привилегии вообще не влияет. Только на разрешение имен.

м.б.
я честно в задание не очень вникал

При выполнении всех ограничений задача сводится к разрыву цепочки вызовов перед последним шагом, динамическим выполнением запроса. Возможно ли записать параметры вызова динамического sql в промежуточную таблицу и затем вызвать пакет XXX с invoker rights?
Ваше решение сохраняет всю цепочку, но смущает создание пакетов при регистрации, похоже, они не будут часто меняться, можно их создать один раз.

raw_

При выполнении всех ограничений задача сводится к разрыву цепочки вызовов перед последним шагом, динамическим выполнением запроса. Возможно ли записать параметры вызова динамического sql в промежуточную таблицу и затем вызвать пакет XXX с invoker rights? Ваше решение сохраняет всю цепочку, но смущает создание пакетов при регистрации, похоже, они не будут часто меняться, можно их создать один раз.

Возможно, но от этого очень хочется уйти, т.к. меняется интерфейс системы, и вместо одного вызова надо делать два (вернее, надо заменить вызов OT_4 на некий пакет PCK authid current_user, который в свою очередь вызовет OT_4 для проверки прав по метаданным), но вот вызов этого-же метода не получится делать из пакетов/процедур/объектых типов, которые не являются authid.
Системы с честной трехзвенкой это обрабатывают нормально - менять надо было бы только middletier, а вот наша 2,5-звенка в этом отношении сложна. Хочется метод поизящней, чем генерация, но и не такой потенциально усложняющий логику, как, фактически, вызов middletier из datatier.

Прикол - заметил, что в предложенном варианте не работают права через роль, чуть не отказался, но вот если сделать пакету в схеме пользователя сделать authid current_user, то права через роли учитываются.
Вот теперь пытаюсь в оракловкой доке описание этого дела найти, т.к. поведение противоречит цитате:

PL/SQL User's Guide and Reference 10g Release 2 (10.2)

Within an invoker's rights subprogram, roles are enabled (unless the subprogram was called directly or indirectly by a definer's rights subprogram)

А у меня вызов XXX.P_NOAUTHID->YYY.P_AUTHID прошел, и выборка из таблицы, на которую даны права через роль, сработали.

RA\/EN

Прикол - заметил, что в предложенном варианте не работают права через роль, чуть не отказался, но вот если сделать пакету в схеме пользователя сделать authid current_user, то права через роли учитываются. Вот теперь пытаюсь в оракловкой доке описание этого дела найти, т.к. поведение противоречит цитате: PL/SQL User's Guide and Reference 10g Release 2 (10.2) Within an invoker's rights subprogram, roles are enabled (unless the subprogram was called directly or indirectly by a definer's rights subprogram) А у меня вызов XXX.P_NOAUTHID->YYY.P_AUTHID прошел, и выборка из таблицы, на которую даны права через роль, сработали.

Непонятно, что именно противоречит? Я правильно понимаю, что вы из пакета (definer) вызываете пакет в схеме yyy (с правами definer)? Соответственно роли у вас работать не будут(в полном соответствии с докой), но права будут от yyy. А в чем противоречие, вы же не вызываете с правами invoker-а?

RA\/EN

А у меня вызов XXX.P_NOAUTHID->YYY.P_AUTHID прошел, и выборка из таблицы, на которую даны права через роль, сработали.

В данном случае для YYY.P_AUTHID "текущим пользователем" будет XXX, которому не нужны никакие роли, чтобы видеть свои объекты. Чтобы убедиться в этом, просто забери у роли права - а выборки всё равно будут работать.
RTFM Who Is the Current User During Subprogram Execution? ^(FAQ)

Elic

RA\/EN А у меня вызов XXX.P_NOAUTHID->YYY.P_AUTHID прошел, и выборка из таблицы, на которую даны права через роль, сработали. В данном случае для YYY.P_AUTHID "текущим пользователем" будет XXX, которому не нужны никакие роли, чтобы видеть свои объекты. Чтобы убедиться в этом, просто забери у роли права - а выборки всё равно будут работать. RTFM Who Is the Current User During Subprogram Execution? (FAQ)

Не-а

Таблица XXX.T1

Роль YYYROLE, роли дано право SELECT ON XXX.T1.

Пользователю YYY дана роль YYYROLE.

Есть процедура YYY.Y_PROC. Она делает "

execute immediate ' begin open :p_rc for select * from XXX.T1; end; ' using in out p_RC; -- Параметр SYS_REFCURSOR.

Есть процедура XXX.X_PROC, которая перевызывает YYY.Y_PROC.

Есть грант XXX на выполнение YYY.Y_PROC.

Есть грант YYY на выполнение XXX.X_PROC.

(Обе процедуры не authid current_user).

Вызываем из-под YYY (в test window):
begin xxx.x_proc(:p_rs); end;

Получаем дулю (объекта нихт)

Даем права grant select on xxx.t1 to yyy;

Выполняем xxx.x_proc - все ОК. Отбираем грант. Проверяем. Дуля.

Делаем YYY.Y_PROC authid current_user.
Выполняем XXX.X_PROC. Получаем данные!

Отнимаем у роли: revoke select on xxx.t1 from yyyrole;
Выполняем xxx.x_proc. Дуля.

Вроде, все правильно?

Проверьте еще, добавьте в процедуру
dbms_output.put_line(sys_context('USERENV','CURRENT_USER'));