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". Отслеживайте по ролям излишнюю выдачу грантов на свои же объекты.

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.

CRUD matrices

CRUD = Create (Insert), Read (Select), Update, Delete
CRUD matrices show the list of DML commands used in pl/sql code. Grid of data objects and stored programs may be generated by ClearSQL Analyzer.
Here is an example from ClearDB Docu:

For more details you may read my notes in Russian - "CRUD matrices in Conquest products".