information schema и applicable_roles

pluzanov · Пт сен 01, 2023 11:49 am

Всем привет!

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

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

Доступа к Oracle у меня нет, может кто-то помочь?

SQL*Plus · Пт сен 01, 2023 12:41 pm

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

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

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

Говорите, что нужно делать для проведения эксперимента и будем это делать!

pluzanov · Пт сен 01, 2023 1:11 pm

SQL*Plus писал(а): ↑Пт сен 01, 2023 12:41 pm Говорите, что нужно делать для проведения эксперимента и будем это делать!

Для начала нужно понять, есть ли представление applicable_roles в Oracle? Если нет, то и делать ничего не надо

Пытался искать в документации, но не нашел такого.

В стандарте SQL таблица applicable_roles показывает какие роли доступны для текущей роли (пользователя). Что-то похожее на DBA_ROLE_PRIVS.

SQL*Plus · Пт сен 01, 2023 1:31 pm

В 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 · Пт сен 01, 2023 1:36 pm

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).

pluzanov · Пт сен 01, 2023 1:46 pm

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 · Пт сен 01, 2023 1:53 pm

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

Только alice и bob - это будут роли (не пользователи).

SQL*Plus · Пт сен 01, 2023 2:39 pm

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 и листинг его выполнения в приложенных файлах.

pluzanov · Пт сен 01, 2023 2:46 pm

А если сейчас admin1 сделает REVOKE alice FROM bob; то строка исчезнет?

SQL*Plus · Пт сен 01, 2023 2:58 pm

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

Скрипт и лог его выполнения прилагаются.

ORA600

information schema и applicable_roles

information schema и applicable_roles

Re: information schema и applicable_roles

Re: information schema и applicable_roles

Re: information schema и applicable_roles

Re: information schema и applicable_roles

Re: information schema и applicable_roles

Re: information schema и applicable_roles

Re: information schema и applicable_roles

Re: information schema и applicable_roles

Re: information schema и applicable_roles