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;
----зелёным шрифтом помечены удачные попытки выполнения, а красным - проблемные доступы к объектам

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

Commit or not commit

Multy-sessioning or single connect

On working in developer UI the helpful ability to keep several connections may cause a critical problem.
For example, there are global hot-keys in SQLDetective: "F10" executes the COMMIT command in active session," F11" executes the ROLLBACK command in active session. But this globalization is very limited. Icon Dictionary describes: "Performs COMMIT/ROLLBACK  for the active session. All changes in the database that have been made by the program will be committed.". The help-topic "Connection to Oracle databases – Committing and rolling back changes" suggests hot-keys as the first step and it does not explain the "main active session" term mixing up the local session in SQL Editor.
Application allows several  DB connections per instance if the "View / Preferences / General / Session / Only one connection per SQLDetective instance" option is disabled (default value). Data changing is available in four windows: Object Navigator ("Data" tab), Table Wizard ("Data" page), Smart Dataset, SQL Editor (executed SELECT statement with ROWID column into "Data Output" tab).
Table Wizard operates with data only from one active session per window, but you may open the same table in its wizard in each connection. "Data" page of Table Wizard is a part of Smart Dataset tool. By the way, all connections to the same user and DB are numbered. As the "Preferences / Dataset Editors / Smart Dataset / Open separate window for each object" option is disabled by default then you may modify data from different connections in one window (several tabs open). Unfortunately, there is no actions in toolbar or popup menu for commiting or rolling back in the Smart Dataset window. For saving data you should select the main session in main menu (Session / selection) or main toolbar and commit by pressing "F10" or executing "Session /  Commit" from main menu or main toolbar.
On working with data in Object Navigator the main active session is selected automatically on selecting in ObjectSelector. That's why the global actions works automatically in "Data" tab of ContentSelector.
"Data Output" tab in SQL Editor is a part of Smart Dataset tool. But as SQL Editor selects the working session then COMMIT/ROLLBACK command can be executed in the same window by manually typed and running script.

Example of using all four windows:
- run SQLDetective;
- connect to your DB with the same schema name four times;
- locate a table of the fourth connection in Object Navigator;
- execute "Generate SQL / SELECT *, ROWID" for this table;
- execute selection into Data Output;
Result: SQL Editor opens with "select *, rowid from mytable" grid for "[4] MySession".
- locate the same table of the third connection in Object Navigator;
- execute "Browse" for this table;
Result: Smart Dataset "[3] MySession - MyTable" opens.
- locate the same table of the second connection in Object Navigator;
- execute "Open" for this table;
Result: Table Wizard "[2] MySession - MyTable" opens.
- locate the same table of the first connection in Object Navigator;
- open "Data" tab in ContentSelector.
Add a row in Object Navigator. Click in Table Wizard. Press F10. Add a row in Data page. Click in Smart Dataset. Press F10. Add a row in Smart Dataset. Click in SQL Editor. Add a row in Data Output tab. Click in Object Navigator. Refresh data.

Unfortunately, the global actions work only in globally selected session. It's visible by default in the combobox in main toolbar. Each window shows the working session in caption or status bar and it may be not equal to main active session. So, a row was saved in DB only from Object Navigator. And when you try to delete or modify the same row in all windows with pressing "F10" the error "Record is locked by another user." or "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired" occur. To exclude the described behaviour you should work in one session or check the main session before commiting.

As a workaround the Conquest Software Solutions developers should add an ability to select the session for which the commit/rollback action is executed by hot-key. Or run the global action for session in the current active window automatically.

Tip1: please, don't forget about DDL commands that execute commiting automatically.
Tip2: SQLDdetective extract DDL or create some objects with COMMIT command execution.
Tip3: Stored Program Execution, Fast Copier, Version Control System, Export/Import Data  tools have an option to commit/rollback.
Tip4: there are several options for auto commiting data (Preferences / General / Session / Auto commit after each statement, Preferences / General / Session / On Disconnect = Commit/Rollback/Prompt if changes detected, Preferences / General / Session / Confirm commit and rollback, Preferences / Dataset Editors / Commit on post). By default they are disabled to not be executed in auto mode.

Indexes as quick sorting (III)

Part III - How to manage?

The previous parts described the necessity and ways to create an index.
On using and supporting indexes in production DB you should monitor, analyze and manage objects. Developer applications have many useful abilities for your work.

Oracle SQL Developer	SQLDetective
Lists of actions for object (INDEX) managing. Note: screen shots in this article are from Oracle SQL Developer and SQLDetective applications for developers.

You see that different developer UIs allow some useful commands for index modifying. Looks like the action lists are dependent on application price and user necessities.

Oracle SQL Developer	SQLDetective

Oracle SQL Developer allows to drop not only the whole index but it's possible to drop a partition. Don't worry about data on deleting index or its part – you'll not lose the source data (not-indexed).

Oracle SQL Developer	SQLDetective

SQLDetective rebuilds the index by Index Wizard with more abilities than in an additional UI of Oracle SQL Developer.

Oracle SQL Developer	SQLDetective

You should rename an index manually in SQL Editor of SQLDeveloper typing "ALTER INDEX … RENAME TO …". Note: it's possible to rename an index partition (in the latest Oracle DB versions) but the renaming action is not available for some domain indexes.

Oracle SQL Developer	SQLDetective

Fast action for index enabling/disabling is available in both applications.

Oracle SQL Developer	SQLDetective

Use the COALESCE action to merge the contents of index blocks where possible to free blocks for reuse. Note: this command is equal to ALTER INDEX ... SHRINK SPACE.

Oracle SQL Developer	SQLDetective

Results of index analyzing allow to modify objects for best using.

Oracle SQL Developer	SQLDetective

Switch Activity action is available for function-based indexes. The UI for enabling/disabling exists in SQLDetective only.

Oracle SQL Developer	SQLDetective

The same clause INVISIBLE from ALTER INDEX command is available for any type of index but there is additional item in object menu named "Switch Visibility" in SQLDetective.

Oracle SQL Developer	SQLDetective

SQLDetective allows to move the index from one tablespace to another with modifying segment attributes. It's possible to relocate/move an index by popup menu in Storage Manager or Object Navigator. Also several indexes may be moved to other tablespace by Group Action of Object Navigator. Unfortunately, the window GUI has some problems in caption and elements align.
As an index is a copy of table/view/cluster then some properties of parent objects are applied automatically to the index. For example, it's unnecessary to set additional grants to columns used in index formula. But as an index is a standalone object then the storage parameters can be set separately from the parent object - for example, the source table is stored in one tablespace and indexes are stored in another tablespace.

For proper and helpful using of indexes, please, learn the following articles of Oracle Documentation: "Indexes and Index-Organized Tables", "Create Index", "Alter Index", "Drop Index", "Create Table (index organized)", "Create Cluster", "Create Indextype", "Create Operator", "Create Type".
Index your data and the application life becomes faster!

Indexes as quick sorting (II)

Part II – What index?

The previous part of index describing showed some examples of DML fast execution.
Now not only char columns are used in BigData. And it seems that Oracle can't work with all of them:
"
You cannot create an index on columns or attributes whose type is user-defined, LONG, LONG RAW, LOB, or REF, except that Oracle Database supports an index on REF type columns or attributes that have been defined with a SCOPE clause.
"
Don't worry. Oracle DB has a workaround. There are many types of indexes.
Note: The following screen shots are from Oracle SQL Developer and SQLDetective applications.

Oracle SQL Developer	SQLDetective
Don't forget to set Storage parameters on index creating. Not all of them are allowed for altering:
Create indexes only for frequent using statements because the index is stored in tablespace as a table copy.

Here are some useful words from Oracle Documentation about index organizations:
* B-tree (short for balanced trees) indexes
     * Index-organized tables
          Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order. A typical use of this type of table is for information retrieval, spatial data, and OLAP applications.
          A secondary index is an index on an index-organized table.

Oracle SQL Developer	SQLDetective
Table Wizards for Index-Organized table creating

     * Reverse key indexes
          The bytes of the index key are reversed.
     * Descending indexes
          Descending order of data is used.
     * B-tree cluster indexes
          Instead of pointing to a row, the key points to the block that contains rows related to the cluster key.

Oracle SQL Developer	SQLDetective
There is no Cluster Wizard in Oracle SQL Developer
An index on cluster may be created by object wizard in SQLDetective only

* Bitmap and bitmap join indexes
     In a bitmap index, an index entry uses a bitmap to point to multiple rows. In contrast, a B-tree index entry points to a single row

Oracle SQL Developer	SQLDetective
Only SQLDetective 4.4 allows to set joins

* Function-based indexes
     Function-based indexes are efficient for evaluating statements that contain functions in their WHERE clauses.

Oracle SQL Developer	SQLDetective
Type manually your formula into "Expression" editor.
SQLDetective has a tool for generating an expression, but it's used in Query Builder only.

* Application domain indexes
     You can encapsulate application-specific index management routines as an indextype schema object, and then define a domain index on table columns or attributes of an object type. Extensible indexing can efficiently process application-specific operators.

Oracle SQL Developer	SQLDetective
An indextype in Oracle SQL Developer you should create manually, it's not supported by object wizard.
SQLDetective supports the Indextype Wizard.

     Extensive indexing can:
          Accommodate indexes on customized, complex data types such as documents, spatial data, images, and video clips (see "Unstructured Data")
          Make use of specialized indexing techniques

* System indexes are created automatically for table constraints except foreign key.

On index creating and modifying, please, don't fully rely on wizard UI as they support not all logical issues and some rare or not-described in Oracle Documentation clauses are not supported by the developer application.
For using index abilities you should create objects of some other types ("Create Table (index organized)", "Create Cluster", "Create Indextype", "Create Operator", "Create Type"):

Oracle SQL Developer	SQLDetective
Oracle SQL Developer doesn't support Clusters and Indextypes wizards

Read about some other index options in the next topic.

Indexes as quick sorting (I)

Part I – Why indexes?

Oracle Database now grows and grows. It becomes BigData. As modern age wants to do everything fast then there is the best way to select necessary data in time.
Let's see an example.

1. Create a simple table with data:

CREATE TABLE FOR_SORT ( COL1 VARCHAR2(10 BYTE) NULL, COL2 NUMBER(5,0) NULL )
/
-- Insert script for table FOR_SORT
INSERT INTO FOR_SORT(COL1, COL2) VALUES('ddd', 1)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('ggg', 2)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('ccc', 3)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('fff', 4)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('hhh', 5)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('bbb', 6)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('iii', 7)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('aaa', 8)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('eee', 9)
/

2. Explain plan for simple select statement (SELECT A.* FROM FOR_SORT A;) is the following:

The red colored line in Explain Plan results means that the statement is not optimized.

3. Create an index on one column:

CREATE INDEX IND_COL1 ON FOR_SORT ( COL1 )

4. Explain plan for a select statement with indexed column in WHERE clause (SELECT A.* FROM FOR_SORT A WHERE A.COL1 > 'c';) is more cheaper. Pay attention to "Cost" and "CPU Cost" values:

Note: data is ordered automatically by indexed column.

5. Let's compare explain plan for the same statement but with non-indexed column in WHERE clause(SELECT A.* FROM FOR_SORT A WHERE A.COL2 > 2;):

Result of not-ordered select statement is the same but Explain Plan differs - see "Cost" and "CPU Cost" values: 1/2 (indexed column in WHERE clause) -> 3 (not-indexed) and 14613/7321 (indexed) -> 37587 (not-indexed).

6. Compare explain plan for statements with ordered data, with and without mentioning an indexed column:
SELECT A.* FROM FOR_SORT A ORDER BY A.COL1 DESC;

SELECT A.* FROM FOR_SORT A WHERE A.COL1 IS NOT NULL ORDER BY A.COL1 DESC;

"Cost" and "CPU Cost" values show the best selection: ordering takes much resources but the clause with indexed column decreases it (4 -> 2, 3 -> 1, 36022939 -> 17753, 37137 -> 8921). Results of both select statements are equal, a short clause (any mention of indexed column COL1 in WHERE clause but not in ORDER BY clause) was added for execution speed up.

You see, index helps to execute the DML statements faster. About more index using, please, read the next topic.

Data storing

There are several object types in Oracle DB for storing data. Some of them work with physical data, others operate with virtual data. Let's investigate TABLES, VIEWS, MATERIALIZED VIEWS, DIMENSIONS.

SQLDEVELOPER from Oracle and SQLDetective (SD) from Conquest Software Solutions allow to work partly with listed objects and their data.
SQLDEVELOPER works with Tables, Views, Materialized Views by object wizards:

and with some other data by Reports:

SD works with Tables, Views, Materialized Views, Dimensions by object wizards: 

TABLES
Tables are the main objects for work with data: adding, editing, deleting, storing. Table's data is stored physically in tablespaces (see SD wizard that allows to set storage parameters on table creating or modifying):

 Table Wizard in SQLDEVELOPER allows the limited list of column types (built-in: varchar2, number, date, clob, blob; ANSI: bfile, binary_double, etc.):

 Table Wizard in SD allows to generate columns as built-in types (number, varchar, date, raw, long, etc.), ANSI (char, float, etc.), user defined (object types of Oracle DB schemas):

 

 

 According to Oracle Documentation a table may be created on OBJECT TYPE, QUEUE, XML TYPE, that's possible by Table Wizard in SD:

 

 

 

 Constraints may be generated in SD on table creating:

 Table data is available for selecting and editing (Insert, Delete, Update, Merge) by Data Browser (Smart Dataset with Dataset Manager in SD), Query and Dataset Builders, Data tab in SQL Editor or Table Wizard.

 Data analyzing can be done by Data Dependency Analyzer, Explain Plan window, Table/View/Partition/Index/Cluster Analyzer (collect statistics, validate structures, list chained rows). On comparing objects in SD it's possible to compare tables by object properties and number of rows. Some developer applications allow to export and import data, duplicate rows, truncate or commit data, rename or drop object without typing and executing Oracle DB commands in SQL Editor. Data viewing in such applications is easy because of visualized sorts and filters.

 

 

 

VIEWS
Data in View is "virtual", it's aggregated from tables and views:

 

 As a View is created as selection from several tables so data editing becomes hard. Data in view is changed automatically on changing it in the source tables. If you need to edit the selected data then it's better to create a Materialized View. More complex view can be created on Object or XML Type, with check constraint. In different editions since Oracle 11g the same named view can be created by different select statements. View allow to rename columns, join, sort and filter data on selecting from source tables.

MATERIALIZED VIEWS
They were named Snapshots in Oracle 7. On MatView creating its data looks like imported from the select statement into MatView with physically created Table. In other words, the MatView becomes a standalone table after creating. Data of MatView may be modified and new version will be stored in the referenced table with the same name as MatView or refreshed from source tables of the select statement.
SQLDEVELOPER / Materialized View Wizard:

 

 

 

 

 SQLDetective / Materialized View Wizard:

 

DIMENSIONS
Dimension wizard in SD helps to visualize an OLAP cube structure:

 

 

 

Please, see the "Select statement" topic of Oracle Documentation about using MODEL, MEASURES, CUBE, DIMENSIONS.