information schema и applicable_roles

Всё по теме СУБД Oracle: установка, настройка, использование, решение проблем и т.д. и т.п. и др. и пр.
pluzanov
Сообщения: 44
Зарегистрирован: Пт июл 08, 2022 4:43 pm

information schema и applicable_roles

Сообщение pluzanov »

Всем привет!

В стандарте SQL словарь данных описан в виде таблиц информационной схемы (information schema).
Хочу узнать поддерживает ли Oracle такое представление словаря и в частности таблицу applicable_roles.

Мне нужно провести эксперимент: создать несколько ролей(пользователей), включить одни роли в другие и посмотреть что выдает applicable_roles.

Доступа к Oracle у меня нет, может кто-то помочь?
Аватара пользователя
SQL*Plus
Сообщения: 208
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: information schema и applicable_roles

Сообщение SQL*Plus »

Давайте попробуем вместе разобраться.

В Oracle Database все таблицы и представления (view) словаря данных содержатся в схеме SYS.

Есть несколько VIEW, отображающие сведения о ролях и представлениях.
Так что разберемся.

Говорите, что нужно делать для проведения эксперимента и будем это делать! :-)
pluzanov
Сообщения: 44
Зарегистрирован: Пт июл 08, 2022 4:43 pm

Re: information schema и applicable_roles

Сообщение pluzanov »

SQL*Plus писал(а): Пт сен 01, 2023 12:41 pm Говорите, что нужно делать для проведения эксперимента и будем это делать! :-)
Для начала нужно понять, есть ли представление applicable_roles в Oracle? Если нет, то и делать ничего не надо :-)
Пытался искать в документации, но не нашел такого.

В стандарте SQL таблица applicable_roles показывает какие роли доступны для текущей роли (пользователя). Что-то похожее на DBA_ROLE_PRIVS.
Аватара пользователя
SQL*Plus
Сообщения: 208
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: information schema и applicable_roles

Сообщение SQL*Plus »

В Oracle Database 19c Enterprise Edition нет объекта с именем applicable_roles / APPLICABLE_ROLES:

Код: Выделить всё

SQL> SELECT banner_full FROM v$version;

BANNER_FULL                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> SELECT * FROM dba_objects
  2  WHERE UPPER(object_name) = 'APPLICABLE_ROLES';
  
no rows selected
Но я думаю, что можно найти аналогичное по содержанию представление.
Аватара пользователя
SQL*Plus
Сообщения: 208
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: information schema и applicable_roles

Сообщение SQL*Plus »

pluzanov писал(а): Пт сен 01, 2023 1:11 pm В стандарте SQL таблица applicable_roles показывает какие роли доступны для текущей роли (пользователя).
Что-то похожее на DBA_ROLE_PRIVS.
Фрагмент иерархической выборки из DBA_ROLE_PRIVS:

Код: Выделить всё

SQL> SELECT LPAD(' ', level-1) || level || '. ' ||grantee AS tree
  2       , granted_role  
  3  FROM dba_role_privs 
  4  CONNECT BY PRIOR granted_role = grantee
  5  START WITH grantee = 'DBA';

TREE                                     GRANTED_ROLE                                                                                                                    
---------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
1. DBA                                   ACCHK_READ                                                                                                                      
1. DBA                                   CAPTURE_ADMIN                                                                                                                   
1. DBA                                   DATAPUMP_EXP_FULL_DATABASE                                                                                                      
 2. DATAPUMP_EXP_FULL_DATABASE           EXP_FULL_DATABASE                                                                                                               
  3. EXP_FULL_DATABASE                   EXECUTE_CATALOG_ROLE                                                                                                            
   4. EXECUTE_CATALOG_ROLE               HS_ADMIN_EXECUTE_ROLE                                                                                                           
  3. EXP_FULL_DATABASE                   SELECT_CATALOG_ROLE                                                                                                             
   4. SELECT_CATALOG_ROLE                HS_ADMIN_SELECT_ROLE                                                                                                            
1. DBA                                   DATAPUMP_IMP_FULL_DATABASE                                                                                                      
 2. DATAPUMP_IMP_FULL_DATABASE           EXP_FULL_DATABASE                                                                                                               
  3. EXP_FULL_DATABASE                   EXECUTE_CATALOG_ROLE                                                                                                            
   4. EXECUTE_CATALOG_ROLE               HS_ADMIN_EXECUTE_ROLE                                                                                                           
  3. EXP_FULL_DATABASE                   SELECT_CATALOG_ROLE                                                                                                             
   4. SELECT_CATALOG_ROLE                HS_ADMIN_SELECT_ROLE                                                                                                            
 2. DATAPUMP_IMP_FULL_DATABASE           IMP_FULL_DATABASE                                                                                                               
  3. IMP_FULL_DATABASE                   EXECUTE_CATALOG_ROLE                                                                                                            
   4. EXECUTE_CATALOG_ROLE               HS_ADMIN_EXECUTE_ROLE                                                                                                           
  3. IMP_FULL_DATABASE                   SELECT_CATALOG_ROLE                                                                                                             
   4. SELECT_CATALOG_ROLE                HS_ADMIN_SELECT_ROLE                                                                                                            
1. DBA                                   DELETE_CATALOG_ROLE                                                                                                             
1. DBA                                   EM_EXPRESS_ALL                                                                                                                  
. . .
Видим дерево ролей:
какие роли предоставлены другой роли - прямо или опосредованно (по иерархии).

GRANTEE - это имя пользователя или роли, которому (которой) предоставлена другая роль (GRANTED_ROLE).
Последний раз редактировалось SQL*Plus Пт сен 01, 2023 1:47 pm, всего редактировалось 1 раз.
pluzanov
Сообщения: 44
Зарегистрирован: Пт июл 08, 2022 4:43 pm

Re: information schema и applicable_roles

Сообщение pluzanov »

SQL*Plus писал(а): Пт сен 01, 2023 1:31 pm В Oracle Database 19c Enterprise Edition нет объекта с именем applicable_roles / APPLICABLE_ROLES:
Спасибо! Это уже ответ.
Для справки, в mysql есть такое представление.

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

Например я хочу сначала просто включить роль alice в bob, а второй раз (от имени другого пользователя) с ADMIN OPTION.
Примерный сценарий такой.

Создаем роли alice и bob, а также роли двух админов, у которых есть возможность управлять ролью alice:

Код: Выделить всё

CREATE USER alice;
CREATE USER bob;
CREATE USER admin1;
CREATE USER admin2;
GRANT alice TO admin1 WITH ADMIN OPTION;
GRANT alice TO admin2 WITH ADMIN OPTION;
Теперь под ролью admin1:

Код: Выделить всё

GRANT alice TO bob;
Теперь под ролью admin2:

Код: Выделить всё

GRANT alice TO bob WITH ADMIN OPTION;
А теперь что показывает DBA_ROLE_PRIVS:

Код: Выделить всё

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'BOB';
Аватара пользователя
SQL*Plus
Сообщения: 208
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: information schema и applicable_roles

Сообщение SQL*Plus »

Сейчас проведу эксперимент.

Только alice и bob - это будут роли (не пользователи).
Аватара пользователя
SQL*Plus
Сообщения: 208
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: information schema и applicable_roles

Сообщение SQL*Plus »

pluzanov писал(а): Пт сен 01, 2023 1:46 pm В принципе эксперимент можно и с DBA_ROLE_PRIVS провести. Смысл вот в чем.
Попробовать включить одну роль в другую дважды и посмотреть как это показано в словаре данных: одной строкой или двумя.
Отображается ОДНОЙ строкой:

Код: Выделить всё

SQL> SELECT USER, banner_full FROM v$version;

USER     BANNER_FULL
-------- ----------------------------------------------------------------------
SYSTEM   Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
         Version 21.4.0.0.0

SQL> -- Create roles
SQL> CREATE ROLE alice;
Role created.

SQL> CREATE ROLE bob;
Role created.

SQL> -- Create users
SQL> CREATE USER admin1 IDENTIFIED BY kuku1;
User created.

SQL> CREATE USER admin2 IDENTIFIED BY kuku2;
User created.

SQL> -- Grant necessary privilege for connecting to database
SQL> GRANT CREATE SESSION TO admin1;
Grant succeeded.
SQL> GRANT CREATE SESSION TO admin2;
Grant succeeded.

SQL> GRANT alice TO admin1 WITH ADMIN OPTION;
Grant succeeded.

SQL> GRANT alice TO admin2 WITH ADMIN OPTION;
Grant succeeded.

SQL> -- Connect as user ADMIN1
SQL> CONNECT admin1/kuku1@&_CONNECT_IDENTIFIER
Connected.

SQL> SHOW USER;
USER is "ADMIN1"
SQL> GRANT alice TO bob;

Grant succeeded.

SQL> 
SQL> -- Check BOB's roles
SQL> @@connect_as_SYSTEM.sql
SQL> SET ECHO OFF;
Connected.
SQL> 
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'BOB';

GRANTEE      GRANTED_ROLE ADMIN_OPTION DEL DEF COM INH
------------ ------------ ------------ --- --- --- ---
BOB          ALICE        NO           NO  YES NO  NO

SQL> -- Connect as user ADMIN2
SQL> CONNECT admin2/kuku2@&_CONNECT_IDENTIFIER
Connected.

SQL> SHOW USER;
USER is "ADMIN2"

SQL> GRANT alice TO bob WITH ADMIN OPTION;
Grant succeeded.

SQL> -- Check BOB's roles
SQL> @@connect_as_SYSTEM.sql
Connected.
SQL> 
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'BOB';

GRANTEE      GRANTED_ROLE ADMIN_OPTION DEL DEF COM INH
------------ ------------ ------------ --- --- --- ---
BOB          ALICE        YES          NO  YES NO  NO
Скрипт для SQL*Plus или SQL Developer и листинг его выполнения в приложенных файлах.
Вложения
ROLE_PRIVS_01.zip
(1.41 КБ) 48 скачиваний
Последний раз редактировалось SQL*Plus Пт сен 01, 2023 2:48 pm, всего редактировалось 1 раз.
pluzanov
Сообщения: 44
Зарегистрирован: Пт июл 08, 2022 4:43 pm

Re: information schema и applicable_roles

Сообщение pluzanov »

А если сейчас admin1 сделает REVOKE alice FROM bob; то строка исчезнет?
Аватара пользователя
SQL*Plus
Сообщения: 208
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: information schema и applicable_roles

Сообщение SQL*Plus »

pluzanov писал(а): Пт сен 01, 2023 2:46 pm А если сейчас admin1 сделает REVOKE alice FROM bob; то строка исчезнет?
Исчезнет:

Код: Выделить всё

SQL> -- Check BOB's roles
SQL> @@connect_as_SYSTEM.sql
SQL> SET ECHO OFF;
Connected.

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'BOB';

GRANTEE      GRANTED_ROLE ADMIN_OPTION DEL DEF COM INH
------------ ------------ ------------ --- --- --- ---
BOB          ALICE        YES          NO  YES NO  NO

SQL> -- Connect as user ADMIN1 and revoke role BOB
SQL> CONNECT admin1/kuku1@&_CONNECT_IDENTIFIER
Connected.

SQL> SHOW USER;
USER is "ADMIN1"

SQL> REVOKE alice FROM bob;
Revoke succeeded.

SQL> -- Check BOB's roles
SQL> @@connect_as_SYSTEM.sql
SQL> SET ECHO OFF;
Connected.
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'BOB';
no rows selected
Скрипт и лог его выполнения прилагаются.
Вложения
ROLE_PRIVS_02.zip
(1.49 КБ) 46 скачиваний
Ответить