Current release: COOL_2_4_0a

Oracle account model for COOL deployment

This page describes the account management model that was used to deploy COOL for Atlas on an Oracle RAC server at CERN in July 2005. The model can be used as a template for creating COOL Oracle accounts and tablespaces for other projects at CERN and elsewhere.

This model was developed and tested only for Oracle. Most of the issues discussed in the following do not apply for MySQL or SQLite.

Users

In the deployment model used for Atlas, 2N+1 accounts were created for a project involving N independent "COOL databases" used by N independent subdetectors or teams of people:

one generic reader account ATLAS_COOL_READER;
one owner account ATLAS_COOL_<DET00i> and one writer account ATLAS_COOL_<DET00i>_W for each of the N subdetectors (DET001... DET00N), e.g. ATLAS_COOL_SCT and ATLAS_COOL_SCT_W for the SCT detector.

The model foresees three categories of users:

"detector managers" (detector experts with "administrative" privileges) can connect as the schema owner (e.g. ATLAS_COOL_SCT) and are responsible for creating the relevant COOL databases, folder sets and folders in the corresponding owner account (e.g ATLAS_COOL_SCT);
"detector end-users" (detector experts without "administrative" privileges) can connect as the schema writer (e.g. ATLAS_COOL_SCT_W), and are responsible for inserting, tagging and retagging data in the relevant COOL databases, folder sets and folders in the corresponding owner account (e.g ATLAS_COOL_SCT);
"generic end-users" can connect as the generic reader (e.g. ATLAS_COOL_READER), and can retrieve conditions data from all of the N owner accounts.

Tablespaces

In this model, N different data tablespaces are created and are assigned as default tablespaces for the N different schema owners. It is assumed that each schema owner writes both data and indexes to its default tablespace, as COOL does not yet support separate tablespaces for data and indexes.

Roles

In this model, privilege management is handled through Oracle roles.

For each owner account, three roles are created by the DBA at the same time as the accounts are created:

one reader role R_ATLAS_COOL_<DET00i>_R (e.g. R_ATLAS_COOL_SCT_R)
one writer role R_ATLAS_COOL_<DET00i>_W (e.g. R_ATLAS_COOL_SCT_W)
one tagger role R_ATLAS_COOL_<DET00i>_T (e.g. R_ATLAS_COOL_SCT_T)

When the accounts are created, each of the N "writer" users is granted by the DBA all of the three above roles for that detector. For instance, ATLAS_COOL_SCT_W is granted the R_ATLAS_COOL_SCT_R, R_ATLAS_COOL_SCT_W and R_ATLAS_COOL_SCT_T roles.

In addition, the generic reader user is granted the reader role for each of the N subdetectors. For instance, ATLAS_COOL_READER is granted R_ATLAS_COOL_SCT_R, R_ATLAS_COOL_PIXEL_R and so on.

Table privileges

The model assumes that all of the operations discussed above (creating users, tablespaces and roles, as well as granting roles to users) are performed by the DBA. Template scripts based on those actually used for Atlas in July 2005 are available below.

After the DBA has created the relevant accounts, the "detector managers" are responsible for creating the folder set and folder hierarchies for their subsystem. Before "detector end-users" can start inserting IOVs and tags, "detector managers" must first run some simple scripts to grant the relevant Oracle table-level privileges (i.e. the privileges to select, insert, update or delete rows from the COOL tables) to the relevant Oracle roles. This is because the Oracle privilege mechanism only allows to grant table-level privileges on existing tables, and the relevant COOL tables are created by the detector managers when they create their folder set and folder hierarchy: the DBA could not grant these table-level privileges when the accounts are created, because the relevant COOL tables do not exist yet. Instructions for detector managers for granting the relevant table-level privileges can be found below.

Creating users and tablespaces (as the Oracle DBA)

The following two scripts are templates for the scripts that should be executed by the Oracle DBA to prepare a COOL installation:

oracleCreateGenericReader.sql
This script should be run first and should be run only once. It prepares the generic reader account.
In this example, an account "TESTEXP_COOL_READER" is created, identified by "TESTEXP_COOL_READER_password", with profile "CERN_APP_PROFILE" and role "CERN_APP_ROLE", default tablespace "USERS" and temporary tablespace "TEMP".
All hardcoded defaults in the script must be changed to suit your needs.

oracleCreateSchemaOwner.sql
This script should be run second and should be run once for each detector account required. It prepares the detector schema owner account, the detector writer account and the corresponding tablespace and roles, and grants all required role privileges to the relevant users.
In this example, accounts "TESTEXP_COOL_TESTDET" and "TESTEXP_COOL_TESTDET_W" are created, identified by "TESTEXP_COOL_TESTDET_password" and "TESTEXP_COOL_TESTDET_W_password" respectively, with profiles "CERN_APP_PROFILE" and "CERN_DEV_PROFILE" respectively, with roles "CERN_APP_ROLE" and "CERN_DEV_ROLE" respectively, both with default tablespace "TESTEXP_COOL_TESTDET_DATA01" and temporary tablespace "TEMP".
Tablespace "TESTEXP_COOL_TESTDET_DATA01" is created with a single datafile assuming that Oracle ASM is used.
Three roles "R_TESTEXP_COOL_TESTDET_R" (reader), "R_TESTEXP_COOL_TESTDET_W" (writer) and "R_TESTEXP_COOL_TESTDET_T" (tagger/retagger) are also created: user "TESTEXP_COOL_TESTDET_W" is granted all three roles, while generic reader "TESTEXP_COOL_READER" is granted only "R_TESTEXP_COOL_TESTDET_R".
All hardcoded defaults in the script must be changed to suit your needs.

Granting read/write/tag privileges (as the Oracle schema owner)

As discussed above, after the DBA has created the relevant accounts, the "detector managers" are responsible for creating the folder set and folder hierarchies for their subsystem. In the example above, let's assume that user TESTEXP_COOL_TESTDET creates a COOL database called "MYCOOLDB". Assuming that the Oracle server where COOL is deployed has a TNS entry "coolprod" in the Oracle tnsnames.ora, user TESTEXP_COOL_TESTDET can acces the "MYCOOLDB" COOL database in its own schema using the URL 'oracle://coolprod;schema=TESTEXP_COOL_TESTDET;dbname=MYCOOLDB;user=TESTEXP_COOL_TESTDET' (assuming that the password is specified in the COOL authentication.xml file).

After creating ALL folders and foldersets, the detector manager TESTEXP_COOL_TESTDET should run the following executables to allow detector end-users to connect as TESTEXP_COOL_TESTDET_W and freely insert, tag and retag IOVs:

  coolPrivileges \
    'oracle://coolprod;schema=TESTEXP_COOL_TESTDET;dbname=MYCOOLDB;user=TESTEXP_COOL_TESTDET' \
    GRANT READER R_TESTEXP_COOL_TESTDET_R

  coolPrivileges \
    'oracle://coolprod;schema=TESTEXP_COOL_TESTDET;dbname=MYCOOLDB;user=TESTEXP_COOL_TESTDET' \
    GRANT WRITER R_TESTEXP_COOL_TESTDET_W

  coolPrivileges \
    'oracle://coolprod;schema=TESTEXP_COOL_TESTDET;dbname=MYCOOLDB;user=TESTEXP_COOL_TESTDET' \
    GRANT TAGGER R_TESTEXP_COOL_TESTDET_T

Detector end-users may now connect to the "MYCOOLDB" COOL database in the TESTEXP_COOL_TESTDET schema from the TESTEXP_COOL_TESTDET_W Oracle account, using the COOL URL 'oracle://coolprod;schema=TESTEXP_COOL_TESTDET;dbname=MYCOOLDB;user=TESTEXP_COOL_TESTDET_W'. They now have all relevant table privileges to insert, tag or retag IOVs, but lack the relevant privileges to create new folders or drop existing ones.

Generic end-users may now connect to the "MYCOOLDB" COOL database in the TESTEXP_COOL_TESTDET schema from the TESTEXP_COOL_READER Oracle account, using the COOL URL 'oracle://coolprod;schema=TESTEXP_COOL_TESTDET;dbname=MYCOOLDB;user=TESTEXP_COOL_READER'. They now have all relevant table privileges to read IOVs, but lack the relevant privileges to do any data modification.

Note that, unfortunately, COOL does not yet provide a way to reuse an existing Oracle/RAL connection. This means that, if the TESTEXP_COOL_READER needs to read data from COOL databases defined in several schemas (for instance, TESTEXP_COOL_TESTDET1 and TESTEXP_COOL_TESTDET2), the C++ client will establish as many independent connections to the server as are the separate schemas from which it needs to read data. This will be changed in a next version of COOL (see Savannah task #2202).

For any questions please contact Andrea Valassi