Повторно создайте ПРЕДОСТАВЛЕНИЯ на роли через схемы
Подобный этому вопросу, я хотел бы знать, как генерировать все GRANT операторы вышли ко всем ролям в ряде схем и списка ролей, имена которых заканчиваются в "ПРОКСИ". Я хочу воссоздать операторы как:
GRANT SELECT ON TABLE_NAME TO ROLE_NAME;
GRANT EXECUTE ON PACKAGE_NAME TO ROLE_NAME;
Цель состоит в том, чтобы помочь мигрировать от базы данных разработки до базы данных тестирования (Oracle 11 г). Существуют некоторые инструменты, которые пытаются сделать это автоматически, но часто перестать работать.
Какие-либо идеи?
3 ответа
Этот сценарий генерирует список всех табличных привилегий, предоставляемых ролям...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee
||case when grantable = 'YES' then ' with grant option' else null end
||';'
from dba_tab_privs
where owner in ('A', 'B')
and grantee in ( select role from dba_roles )
order by grantee, owner
/
Обратите внимание, что я не ограничиваю роли получателя, потому что ваш вопрос по этому поводу расплывчатый. Вам может понадобиться добавить фильтр в sub_query на dba_roles. Если у вас есть роли, предоставленные другим ролям, вы захотите подобрать и эти роли ...
select 'grant '||granted_role||' to '||grantee
||case when admin_option = 'YES' then ' with admin option' else null end
||';'
from dba_role_privs
where grantee in ( select role from dba_roles )
order by grantee, granted_role
/
Чтобы получить список ролей ...
select 'create role '||role ||';'
from dba_roles
where role like '%PROXY'
/
Обратите внимание, что эти сценарии не генерируют гранты для системных привилегий. Кроме того, жизнь немного сложнее, если вы используете объекты каталогов, потому что это требует дополнительного ключевого слова....
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee
||case when grantable = 'YES' then ' with grant option' else null end
||';'
from dba_tab_privs
where owner in ('A', 'B')
and grantee in ( select role from dba_roles )
and table_name not in ( select directory_name from dba_directories )
union all
select 'grant '||privilege||' on directory '||table_name||' to '||grantee
||case when grantable = 'YES' then ' with grant option' else null end
||';'
from dba_tab_privs
where grantee in ( select role from dba_roles )
and table_name in ( select directory_name from dba_directories )
/
edit
In 9i Oracle представил пакет СУБД_METADATA, который завершает множество подобных запросов в простом PL/SQL API. Например, этот вызов вызов вызовет CLOB со всеми привилегиями объекта, предоставленными A ...
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'A') from dual
/
Очевидно, что это намного проще, чем прокатывать наши собственные.
Это соответствует нашим потребностям:
SELECT
'GRANT ' || p.privilege || ' ON ' || p.table_name || ' TO ' ||
p.grantee || ';' AS generated_grant
FROM
dba_tab_privs p
WHERE
p.grantor IN ( 'SCHEMA_NAME_01', 'SCHEMA_NAME_02' ) AND
p.grantee IN (
SELECT DISTINCT
granted_role
FROM
dba_role_privs
WHERE
grantee LIKE '%PROXY' AND
granted_role NOT IN ('CONNECT','AQ_ADMINISTRATOR_ROLE','RESOURCE')
) AND
p.table_name NOT LIKE 'BIN%' AND
p.table_name NOT LIKE '%$%'
ORDER BY
p.table_name, p.grantee, p.privilege;
Вы можете сделать это с помощью кода PL / SQL:
TYPE obj_name_type is TABLE OF ALL_OBJECTS%OBJECT_NAME INDEX BY BINARY_INTEGER;
object_names obj_name_type;
i INTEGER;
BEGIN
SELECT object_name BULK COLLECT INTO object_names FROM ALL_OBJECTS WHERE OWNER = 'whatever' AND object_type = 'PROCEDURE';
FOR i IN 1 .. object_names.last LOOP
EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' object_names(i) ' TO ' role_name
END LOOP;
END;
Вы можете сделать его более универсальным для отображения типов разрешений к типам объектов или что-то, что - вы, но это основная идея.
Вы должны использовать , выполняйте немедленный , потому что вы не можете запустить статизм DDL в процедурном коде.