Access control 2

Продолжим постигать волшебство грантования.
Прежде чем создавать пользователей и выдавать им поочерёдно гранты из предыдущего примера надо иметь пользователя с админскими правами. Сразу после создания базы вход осуществлялся юзером "SYS", под правами которого глобально одним махом в ConquestSS создавался пользователь с постфиксом "_DBA" и полнейшим набором системных привилегий и ролей. Мастер юзера в SQLDetective имеет кнопки в тулбаре весьма опасных экшенов: "Grant all roles to user", "Grant all system privileges to user" и аналогичный функционал для параметра "WITH ADMIN OPTION". Получался эдакий дубликат системного пользователя "SYS". А затем уже этот "WSL_DBA" или "TESTER_DBA" с менее сложным паролем, чем у  "SYS", создавал иных простых пользователей и системные объекты, поскольку все роли и системные привилегии выданы были с опцией "Admin", то есть с возможностью раздавать другим. Таким глупым шагом упрощалась работа тестировщика в последствии: продукты проверялись на двух видах пользователей - с админскими привилегиями и без них. То есть тестились только крайние значения из списка грантов: либо всё, либо ничего. Как следствие, от конечных пользователей продуктов приходили баги об ограниченности доступа к функционалу продукта, и техподдержка выявляла эти узкие необработанные места в каждом конкретном случае. К сожалению, руководитель проекта никак не способствовал автоматизации тестирования, аргументируя дороговизной приложений для тестировщиков, вынужденных ежемесячно терять время на переустановку триала TestComplete вместо написания новых полезных авто-тестов. В продуктах от ConquestSS, работающих с базой Oracle, очень много разнообразных вариантов и мест доступа к базе, поэтому невозможно получить точный список комбинаций из выданных ролей и выполняемых запросов от имени простого юзера и с DBA правами. Да, если бы эти вариации можно было скомпоновать в матрицу, то отпала бы необходимость в единственном TestComplete из линейки приложений для тестировщиков, распознающего Delphi интерфейс. И поскольку матрицу команд могли составить только разработчики выборкой из кода, то данный объём работ также отрицался PM-ом (Project Manager), как  избыточная нагрузка на программистов. Хотя правильнее всего было выбрать из кода все запросы при помощи аналогичных утилит, создаваемых командой ConquestSS, и проверить их выполнимость пользователями базы со всевозможным набором грантов.
Итак, некий админский юзер, в нашем случае это "ADMIN_GRANTS" с некоторыми системными привилегиями и ролями, но опцией "Admin", создаёт пользователей (USER1, USER2, USER3) с минимальным набором системных привилегий (UNLIMITED TABLESPACE, CREATE TABLE, CREATE PROCEDURE, CREATE SYNONYM, CREATE PUBLIC SYNONYM) и обязательными ролями (CONNECT, RESOURCE) для подключения к базе. Следует напомнить, что в ранних версиях Oracle не было особой необходимости выдавать некоторые системные привилегии, так как они входили в стандартные роли для подключения.
Объектные привилегии раздаются самими простыми пользователями после создания этих объектов. Напомню, что синонимы можно создать на не существующие пока ещё объекты, но объектные привилегии (разные для типов объектов: например, "Execute" для хранимых программ и "Select" для набора данных) можно выдать на синоним только после создания самого "родительского" объекта (таблица или пакет в нашем случае).
После того, как мы выдали объектные привилегии, начинаем проверять их достаточность: через продуктовый интерфейс (например, Oracle Forms) или в IDE (Integrated Development Environment) разработчика (например, Oacle SQL Developer) выполняем команды по считыванию и редактированию данных, в том числе и через выполнение хранимых подпрограмм, подконнектившись к базе разными пользователями (USER1, USER2, USER3). Когда для выполнения команды не хватает прав, то возможны ошибки: "ORA-00942: table or view does not exist", "PLS-00904: insufficient privilege to access object", "ORA-00904: : invalid identifier", "PLS-00201: identifier must be declared" и иные. Если по первым двум сообщениям понятно, что к объекту недостаточен доступ и настройка ролей может поправить в корне ситуацию, то по описанию вторых двух совершенно не понятно, что причина таже самая. Поэтому для исследования критичных моментов быстрая помощь может быть оказана диаграммами "Call Tree", "ER" (Entity Relationships), "Ref&Dep" (References, Dependencies), "Grants Access" с возможностью поиска объектов по имени (обычно поиск реализуется в SVG формате, чего к сожалению не имеет ClearSQL).
Как всегда, особого внимания заслуживают синонимы. Объектные привилегии, выданные на синоним, автоматически распространяются на сам объект, то есть после применения грантов можно обращаться как к синониму по упрощённому имени, так и к "родительскому" объекту.
Если гранты выданы так называемому юзеру "Public", то доступ к объекту возможен для всех пользователей базы. Создать Public-синоним (или обычный синоним) не достаточно для доступа к объекту, поэтому продумывайте минимальный объём объектных привилегий и выдавайте их на синоним сторонним юзерам сразу после создания синонима. В нашем примере после создания объектов "SYN_P_US3_TB1" и "SYN_P_US3PKG1" их могут увидеть в списке доступных синонимов пользователи USER1, USER2, но выбрать данные или выполнить пакетную функцию может только владелец "родительских" объектов, то есть USER3. Но, как только объектные привилегии выданы на синонимы юзеру PUBLIC, а значит и на исходные объекты, юзера всей базы начнут их использовать. Хотя есть вариант выдать объектные привилегии на public-синоним только отдельным пользователям, например, один будет только править (UPDATE) таблицу, а другой только выбирать (SELECT) из неё данные. Такое распределение обычно делают для справочников.
Когда синоним создан через "DBLink", то доступ к объекту можно получить и из другой базы, указанной в DBLink, но только тем пользователем (один или все) другой базы, которому обозначен доступ в настройках DBLink. В диаграмме "Grants Access" тоже полезно иметь узлы из числа объектов "DBLink".
Дабы упростить выдачу системных и объектных привилегий, их можно объединить в роль пользовательского плана. Создать, пополнить и грантовать такую роль может только юзер с системными привилегиями "CREATE ROLE" и "GRANT ANY ROLE". В нашем примере это пользователь "ADMIN_GRANTS" и созданные им роли "ROLE_TBL", "ROLE_ROLE_TBL", "ROLE_PRC_TBL". Поскольку в Oracle можно выдать роль на роль, то диаграмма "Grants Access" через уровень вложенности поможет выявить цикличность выданных привилегий и расшифрует избыток прав. У нас объектные привилегии на "USER1.US1_TB1" содержатся в роли "ROLE_TBL", а эта роль в свою очередь является частью роли "ROLE_ROLE_TBL". Поэтому, выдавая роль "ROLE_ROLE_TBL" юзеру "USER3", мы автоматически даём ему доступ к таблице "USER1.US1_TB1". Отслеживайте по ролям излишнюю выдачу грантов на свои же объекты.

Access control

На днях возникла проблема отслеживания прав на объекты. Привилегии в базе Oracle делятся на системные, объектные, ролевые. Отдельно заслуживают внимания так называемый пользователь Public и синонимы.
Для примера создадим трёх пользователей с таблицами и пакетами. Выдадим объектные привилегии юзерам на таблицу, колонку в таблице, на спецификации пакетов. Какие-то права определим конкретно (только просмотр или только редактирование). В число примеров включим грантование юзеру Public и на синонимы без прав на сами объекты.
-- создаём юзеров с системными привилегиями
CREATE USER USER1
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER1
/
GRANT RESOURCE TO USER1
/
GRANT CREATE PROCEDURE TO USER1
/
GRANT CREATE SYNONYM TO USER1
/
GRANT CREATE TABLE TO USER1
/
GRANT UNLIMITED TABLESPACE TO USER1
/
CREATE USER USER2
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER2
/
GRANT RESOURCE TO USER2
/
GRANT CREATE PROCEDURE TO USER2
/
GRANT CREATE SYNONYM TO USER2
/
GRANT CREATE TABLE TO USER2
/
GRANT UNLIMITED TABLESPACE TO USER2
/
CREATE USER USER3
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER3
/
GRANT RESOURCE TO USER3
/
GRANT CREATE PROCEDURE TO USER3
/
GRANT CREATE PUBLIC SYNONYM TO USER3
/
GRANT CREATE SYNONYM TO USER3
/
GRANT CREATE TABLE TO USER3
/
GRANT UNLIMITED TABLESPACE TO USER3
/
--создаём роли и выдаём юзерам
CREATE ROLE ROLE_PRC_TBL
/
GRANT ROLE_PRC_TBL TO USER1
/
CREATE ROLE ROLE_ROLE_TBL
/
GRANT ROLE_ROLE_TBL TO USER3
/
CREATE ROLE ROLE_TBL
/
GRANT ROLE_TBL TO ROLE_ROLE_TBL
/
GRANT ROLE_TBL TO USER2
/
--создаём объекты в схемах, синонимы на некоторые и выдаём объектные привилегии
connect user1
/
CREATE TABLE USER1.US1_TB1
  (
    US1TB1COL1                        VARCHAR2(10 BYTE)     NULL,
    US1TB1COL2                        NUMBER(5,0)           NULL,
    US1TB1COL3                        DATE                  NULL
  )
/
GRANT DELETE, INSERT, SELECT, UPDATE ON USER1.US1_TB1 TO ROLE_TBL
/
CREATE OR REPLACE PACKAGE USER1.us1pkg1
AS
  PROCEDURE us1pkg1prc1;
  FUNCTION us1pkg1fun1 RETURN VARCHAR2;
END;
/
GRANT DEBUG, EXECUTE ON USER1.US1PKG1 TO USER3
/
CREATE OR REPLACE PACKAGE BODY USER1.us1pkg1
AS
  PROCEDURE us1pkg1prc1 IS
  BEGIN
    dbms_output.put_line('us1pkg1prc1');  
  EXCEPTION
    WHEN OTHERS THEN
      NULL;  
  END;
  FUNCTION us1pkg1fun1 RETURN VARCHAR2 IS
  BEGIN
    return 'yes us1pkg1fun1';  
  END;
END;
/
CREATE OR REPLACE SYNONYM USER1.SYN_US1PKG1 FOR USER1.US1PKG1
/
CREATE TABLE USER2.US2_TB1
  (
    US2TB1COL1                        VARCHAR2(10 BYTE)     NULL,
    US2TB1COL2                        NUMBER(5,0)           NULL,
    US2TB1COL3                        DATE                  NULL
  )
/
GRANT SELECT ON USER2.US2_TB1 TO ROLE_PRC_TBL
/
CREATE TABLE USER2.US2_TB2
  (
    US2TB2COL1                        VARCHAR2(10 BYTE)     NULL,
    US2TB2COL2                        NUMBER(5,0)           NULL,
    US2TB2COL3                        DATE                  NULL
  )
/
CREATE OR REPLACE SYNONYM USER2.SYN_US2_TB2 FOR USER2.US2_TB2
/
CREATE OR REPLACE PACKAGE USER2.us2pkg1
AS
  PROCEDURE us2pkg1prc1 (prm1 in number);
  FUNCTION us2pkg1fun1 (prm2 in number) RETURN VARCHAR2;
END;
/
GRANT EXECUTE ON USER2.US2PKG1 TO ROLE_PRC_TBL
/
CREATE OR REPLACE PACKAGE BODY USER2.us2pkg1
AS
  PROCEDURE us2pkg1prc1 (prm1 in number) IS
  BEGIN
    dbms_output.put_line('us2pkg1prc1 '||to_char(prm1));  
  EXCEPTION
    WHEN OTHERS THEN
      NULL;  
  END;
  FUNCTION us2pkg1fun1 (prm2 in number) RETURN VARCHAR2 IS
  BEGIN
    return 'us2pkg1fun1'||to_char(prm2);     
  END;
END;
/
CREATE OR REPLACE PACKAGE USER2.us2pkg2
AS
  PROCEDURE us2pkg2prc1 (prm3 in number, prm4 in date);
  FUNCTION us2pkg2fun1 (prm5 in number, prm6 in number) RETURN VARCHAR2;
END;
/
GRANT DEBUG ON USER2.US2PKG2 TO USER1
/
GRANT DEBUG ON USER2.US2PKG2 TO USER3
/
CREATE OR REPLACE PACKAGE BODY USER2.us2pkg2
AS
  PROCEDURE us2pkg2prc1 (prm3 in number, prm4 in date) IS
  BEGIN
    dbms_output.put_line('us2pkg2prc1 '||to_char(prm3));     
  END;
  FUNCTION us2pkg2fun1 (prm5 in number, prm6 in number) RETURN VARCHAR2 IS
  BEGIN
   return 'us2pkg2fun1'||to_char(prm5); 
  END;
END;
/
CREATE TABLE USER3.US3_TB1
  (
    US3TB1COL1                        VARCHAR2(10 BYTE)     NULL,
    US3TB1COL2                        NUMBER(5,0)           NULL,
    US3TB1COL3                        DATE                  NULL
  )
/
CREATE TABLE USER3.US3_TB2
  (
    US3TB2COL1                        VARCHAR2(10 BYTE)     NULL,
    US3TB2COL2                        NUMBER(5,0)           NULL,
    US3TB2COL3                        DATE                  NULL
  )
/
GRANT SELECT ON USER3.US3_TB2 TO USER1
/
CREATE TABLE USER3.US3_TB3
  (
    US3TB3COL1                        VARCHAR2(10 BYTE)     NULL,
    US3TB3COL2                        NUMBER(5,0)           NULL,
    US3TB3COL3                        DATE                  NULL
  )
/
CREATE OR REPLACE PACKAGE USER3.us3pkg1
AS
  PROCEDURE us3pkg1prc1;
  FUNCTION us3pkg1fun1 RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY USER3.us3pkg1
AS
  PROCEDURE us3pkg1prc1 IS
  BEGIN
    dbms_output.put_line('us3pkg1prc1');     
  END;
  FUNCTION us3pkg1fun1 RETURN VARCHAR2 IS
  BEGIN
    return 'us3pkg1fun1';     
  END;
END;
/
CREATE OR REPLACE PACKAGE USER3.us3pkg2
AS
  PROCEDURE us3pkg2prc1 (prm7 in number);
  FUNCTION us3pkg2fun1 (prm8 in date) RETURN VARCHAR2;
END;
/
GRANT EXECUTE ON USER3.US3PKG2 TO USER1
/
CREATE OR REPLACE PACKAGE BODY USER3.us3pkg2
AS
  PROCEDURE us3pkg2prc1 (prm7 in number) IS
  BEGIN
    dbms_output.put_line('us3pkg2prc1 '||to_char(prm7));  
  END;
  FUNCTION us3pkg2fun1 (prm8 in date) RETURN VARCHAR2 IS
  BEGIN
    return 'yes us3pkg2fun1';  
  END;
END;
/
CREATE OR REPLACE PACKAGE USER3.us3pkg3
AS
  PROCEDURE us3pkg3prc1;
  FUNCTION us3pkg3fun1 RETURN VARCHAR2;
  PROCEDURE us3pkg3prc2;
  FUNCTION us3pkg3fun2 RETURN VARCHAR2;
END;
/
GRANT DEBUG, EXECUTE ON USER3.US3PKG3 TO USER1
/
GRANT DEBUG, EXECUTE ON USER3.US3PKG3 TO USER2
/
CREATE OR REPLACE PACKAGE BODY USER3.us3pkg3
AS
  PROCEDURE us3pkg3prc1 IS
  BEGIN
    dbms_output.put_line('us3pkg3prc1');     
  END;
  FUNCTION us3pkg3fun1 RETURN VARCHAR2 IS
  BEGIN
    return 'us3pkg3fun1';  
  END;
    PROCEDURE us3pkg3prc2 IS
  BEGIN
    dbms_output.put_line('us3pkg3prc2');     
  END;
  FUNCTION us3pkg3fun2 RETURN VARCHAR2 IS
  BEGIN
    dbms_output.put_line('yes us3pkg3fun2');  
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('no us3pkg3fun2');  
  END;
END;
/
--создаём public синонимы и позже выдаём права
CREATE OR REPLACE PUBLIC SYNONYM SYN_P_US3_TB1 FOR USER3.US3_TB1
/
CREATE OR REPLACE PUBLIC SYNONYM SYN_P_US3PKG1 FOR USER3.US3PKG1
/
GRANT DELETE, INSERT, SELECT, UPDATE ON SYN_P_US3_TB1 TO PUBLIC
/
GRANT DEBUG, EXECUTE ON SYN_P_US3PKG1 TO PUBLIC
/

К сожалению, моя попытка отследить все зависимости с помощью ClearDB (для доки были взяты все объекты трёх рабочих схем, пользовательские роли и public синонимы, всем доступным диаграммам выставлены уровни DB и третий порядок вложенности, включены все свойства объектов) ограничилась лишь R&D диаграммой, построенной по вьюверам "SYS.ALL(DBA)_REFS" "SYS.ALL(DBA)_DEPENDENCIES".

ER диаграмма и CRUD2 матрица оказались пусты, Call Tree диаграммы - бесполезны. Пришлось рисовать вручную.

 По диаграмме, построенной на данных системных вьюверов ("SYS.ALL(DBA)_TAB_PRIVS", "SYS.TABLE_PRIVILEGES", "SYS.COLUMN_PRIVILEGES", "SYS.DBA_PUBLISHED_COLUMNS", "SYS.DBA_ROLE_PRIVS", "SYS.PUBLICSYN", "SYS.ROLE_ROLE_PRIVS" и других) легче понять, какой юзер сможет воспользоваться данными и в каком объёме. По ней очевидными стали результаты нижеследующих скриптов. 
-----------сессия первого юзера
connect user1
/
BEGIN   USER3.US3PKG3.US3PKG3PRC2; END;
select USER3.US3PKG3.US3PKG3FUN1() from dual;
BEGIN   USER3.US3PKG2.US3PKG2PRC1(:PRM7 /* IN - NUMBER */ ); END;
select USER3.US3PKG2.US3PKG2FUN1(sysdate) from dual;
--ORA-06550: line 2, column 3:
--PLS-00201: identifier 'SYN_P_US3PKG1' must be declared
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3PKG1 TO public
BEGIN   syn_p_us3pkg1.US3PKG1PRC1; END;
--ORA-00904: : invalid identifier
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3PKG1 TO public
select syn_p_us3pkg1.US3PKG1FUN1 from dual;
--редактировать можно только одну колонку, но в CodeAssistant (SQL Editor или Stored Program Editor в SQLDetective) можно просмотреть структуру всей таблицы
insert into USER3.US3_TB3(US3TB3COL1) values (:US3TB3COL1 /* IN - NUMBER */ );
UPDATE USER3.US3_TB3 SET    US3TB3COL1 = :US3TB3COL1  /* IN - NUMBER */ ;
SELECT *  FROM USER3.US3_TB2 ;
--ORA-00942: table or view does not exist
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3_TB1 TO public
SELECT * FROM USER3.US3_TB1 ;
--ORA-00942: table or view does not exist
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3_TB1 TO public
SELECT * FROM SYN_P_US3_TB1;
--PLS-00904: insufficient privilege to access object USER2.US2PKG2
--available DDL viewing
BEGIN   USER2.US2PKG2.US2PKG2PRC1(:PRM3 /* IN - NUMBER */ , :PRM4 /* IN - DATE */ );  END;
--ORA-01031: insufficient privileges
--available DDL viewing
SELECT USER2.US2PKG2.US2PKG2FUN1(:prm5 /* in number */, :prm6 /* in number */ ) FROM dual;
BEGIN   USER2.US2PKG1.US2PKG1PRC1(:PRM1 /* IN - NUMBER */ );  END;
select USER2.US2PKG1.US2PKG1FUN1(:prm2 /* in number */) from dual;
SELECT * FROM USER2.US2_TB1 ;
-------------------сессия второго юзера
connect user2
/
BEGIN   USER3.US3PKG3.US3PKG3PRC2; END;
select USER3.US3PKG3.US3PKG3FUN1() from dual;
--PLS-00201: identifier 'USER3.US3PKG2' must be declared
BEGIN   USER3.US3PKG2.US3PKG2PRC1(:PRM7 /* IN - NUMBER */ );  END;
--ORA-00904: : invalid identifier
select USER3.US3PKG2.US3PKG2FUN1(sysdate) from dual;
--ORA-06550: line 2, column 3:
--PLS-00201: identifier 'SYN_P_US3PKG1' must be declared
----BUT OK after GRANT ALL PRIVILEGES ON SYN_p_US3pkg1 TO public
BEGIN   syn_p_us3pkg1.US3PKG1PRC1; END;
--ORA-00904: : invalid identifier
----BUT OK after GRANT ALL PRIVILEGES ON SYN_p_US3pkg1 TO public
select syn_p_us3pkg1.US3PKG1FUN1 from dual;
--ORA-00942: table or view does not exist
insert into USER3.US3_TB3(US3TB3COL1) values (:US3TB3COL1 /* IN - NUMBER */ );
--ORA-00942: table or view does not exist
UPDATE USER3.US3_TB3 SET    US3TB3COL1 = :US3TB3COL1  /* IN - NUMBER */ ;
--ORA-00942: table or view does not exist
SELECT *  FROM USER3.US3_TB2 ;
--ORA-00942: table or view does not exist
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3_TB1 TO public
SELECT * FROM USER3.US3_TB1 ;
--ORA-00942: table or view does not exist
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3_TB1 TO public
SELECT * FROM SYN_P_US3_TB1;
--PLS-00201: identifier 'USER1.US1PKG1' must be declared
BEGIN   USER1.US1PKG1.US1PKG1PRC1;  END;
--ORA-00904: : invalid identifier
select USER1.US1PKG1.US1PKG1FUN1  from dual;
SELECT A.*    FROM USER1.US1_TB1 A;
INSERT INTO USER1.US1_TB1(US1TB1COL1, US1TB1COL2, US1TB1COL3)   VALUES (:US1TB1COL1, :US1TB1COL2, :US1TB1COL3);
UPDATE USER1.US1_TB1 SET    US1TB1COL1 = :US1TB1COL1,   US1TB1COL2 = :US1TB1COL2,   US1TB1COL3 = :US1TB1COL3;
DELETE FROM USER1.US1_TB1;
--------------------------сессия третьего юзера
connect user 3
/
--PLS-00904: insufficient privilege to access object USER2.US2PKG2
--available DDL viewing
BEGIN   USER2.US2PKG2.US2PKG2PRC1(:PRM3 /* IN - NUMBER */ , :PRM4 /* IN - DATE */ );  END;
--ORA-01031: insufficient privileges
--available DDL viewing
SELECT USER2.US2PKG2.US2PKG2FUN1(:prm5 /* in number */, :prm6 /* in number */ ) FROM dual;
--PLS-00201: identifier 'USER2.US2PKG1' must be declared
--no DDL viewing
BEGIN   USER2.US2PKG1.US2PKG1PRC1(:PRM1 /* IN - NUMBER */ );  END;
--ORA-00904: : invalid identifier
--no DDL viewing
select USER2.US2PKG1.US2PKG1FUN1(:prm2 /* in number */) from dual;
--ORA-00942: table or view does not exist
SELECT * FROM USER2.US2_TB1 ;
BEGIN   USER1.US1PKG1.US1PKG1PRC1;  END;
select USER1.US1PKG1.US1PKG1FUN1  from dual;
INSERT INTO USER1.US1_TB1(US1TB1COL1, US1TB1COL2, US1TB1COL3)   VALUES (:US1TB1COL1, :US1TB1COL2, :US1TB1COL3);
UPDATE USER1.US1_TB1 SET    US1TB1COL1 = :US1TB1COL1,   US1TB1COL2 = :US1TB1COL2,   US1TB1COL3 = :US1TB1COL3;
DELETE FROM USER1.US1_TB1;
----зелёным шрифтом помечены удачные попытки выполнения, а красным - проблемные доступы к объектам

А у вас есть визуализатор привилегий?