NOTES

In this GitlabRepository , there is a minimal set of bash/sql scripts that are supposed to facilitate/organize the work of the operators dealing with the different database accounts/schema used in the CRAB3 project.

So far the list of valid accounts/schema is:


cms_analysis_reqmgr@int2r          - integration
cms_analysis_reqmgr@cmsr           - production
cmsweb_analysis_preprod@devdb11    - preproduction
cmsweb_analysis_dev@devdb11        - development
<private_uername>@devdb11          - private


The idea behind that is to have an encrypted pool of database credentials which is saved in a file and every operation that needs to be done on the database to be written in a sequence of sql scripts which are invoked by a single bash script. The bash scripts must follow few simple rules in order to extract the needed credential from the encrypted pool and use them for initiating the sessions to the sqlserver. It was done like that because of several reasons:

  1. To avoid keeping the passwords for the database accounts in a 'plain text' format saved in a file somewhere in /afs /eos etc .
  2. We agreed not to use complicate tools like 'SQLDeveloper' to connect to the database in production and also
  3. In order to keep track and history of all the operational work done on the database.
  4. Once the scripts are created No deep knowledge in database administration is required while using them in the everyday work (of course until something brakes).

So before the operator do any manipulation in production he/she needs to create the set of sql/bash scripts in the repository and then test on one of the development/testing schema with the appropriate credentials from the encrypted pool. After that executing on the database in production is just a matter of using the correct credential identifier when the script asks for it.

PROCEDURES

Clone

The script used to create, update and manage the encrypted pool is called ./pwstore.sh and can be found in the base directory of the gitlab repository. Here follows the instructions from the README file how to clone it and how to start using it:

!!! IMPORTANT !!!

Because the current repository contains scripts that are going to be used for
database connection automation, during the work with them an encrypted pool of
database credentials needs to be created. This file should contain usernames and
passwords for connecting to the different database schemas used in the project
(now this file is already encrypted and not kept in a plain text format).
This is the reason why the operators who are about to use these scripts are
!STRONGLY! recommended to clone the repository in a place that is !NOT! public
and is !NOT! accessible by anyone else but the operators themselves. And also
they need to be careful !NOT! to upload any version of the password file in the
repository. Please use strong encryption passwords while creating the credentials
pool.

The recommended sequence of commands in order to clone the repository and
to create and safely maintain a password file is as follows:


Clone:

git clone https://:@gitlab.cern.ch:8443/crab3/Crab3DbManScr.git
cd Crab3DbManScr

Create an empty encrypted credentials pool:
./pwstore.sh -c dbconnect.pwd

Add few credentials to the pool:
./pwstore.sh -a username[1]@DataBase[1] dbconnect.pwd
...
./pwstore.sh -a username[n]@DataBase[n] dbconnect.pwd

Add the encrypted password file, the checksum and list files to .gitignore:

echo dbconnect.pwd >> .gitignore
echo dbconnect.pwd.enc >> .gitignore
echo dbconnect.pwd.enc.md5 >> .gitignore

git rm --cached dbconnect.pwd
git rm --cached dbconnect.pwd.enc
git rm --cached dbconnect.pwd.enc.md5

git status

!!! IMPORTANT !!!

The list of files that will be created by the operator while invoking the aforementioned set of commands is as follows:

dbconnect.pwd - A file in 'plain text' format. Contains the list of all the credentials stored in the encrypted pool in the format <username>@<database>
dbconnect.pwd.enc - An encrypted file. Contains all the credentials listed in the previous file in the format <username>/<password>@<databse>
dbconnect.pwd.enc.md5 -  A file in 'plain text' fromat. Contains the checksum of the encrypted file and is updated by the ./pwstore.sh every time  the contents of the encrypted file changes.

Few examples of how to use the scripts from the repository.

Redefine and partition a table:

As an example for FILETRANSFERSDB, edit the file:

dbms_redefinition.filetransfersdb.d/dbms_redefinition.filetransfersdb.sh

In the variable $srclist are listed all the steps that needs to be executed during the procedure of redefinition:

srclist="
01_can_redef_table.sql         \n
02_create_int_table.sql        \n
03_start_redef_table.sql       \n
04_copy_table_dependents.sql   \n
05_sync_interim_table.sql      \n
# 06_finish_redef_table.sql      \n
"

All but the last steps can be done 'online'. The step 06_finish_redef_table.sql swaps the two tables and needs to be performed while the service is off. So the operator needs to comment only that one, then execute:

./dbms_redefinition.filetransfersdb.d/dbms_redefinition.filetransfersdb.sh

The script will ask for which credential to use during execution like that:
Type username@database: cms_......@int2r

The script will ask for the password used to encrypt the credentials pool:
enter des-ede3-cbc decryption password: *************************

Then STOP the CRABServer (or respectively if it is the production one call the cmsweb operator to stop it for him/her). Then edit the $srclist leaving only the last two steps (the step 05_sync_interim_table.sql is used to synchronize the intermediate and the main tables for records accumulated between the moment when the redefinition previously finished and the moment when the server was actually stopped):

srclist="
# 01_can_redef_table.sql         \n
# 02_create_int_table.sql        \n
# 03_start_redef_table.sql       \n
# 04_copy_table_dependents.sql   \n
05_sync_interim_table.sql      \n
06_finish_redef_table.sql      \n
"

And re execute:

./dbms_redefinition.filetransfersdb.d/dbms_redefinition.filetransfersdb.sh

All the log files and the timings should appear in ./dbms_redefinition.filetransfersdb.d/

Until the last step is finalized the interim table is locked. So if the process has been interrupted or the last step still not executed but there is a manipulation needed on the interim table then the operator needs to stop the previously started redefinition with:

srclist="
# ...
03_01_stop_redef_table.sql       \n
# ...
"

List the the partitions older than 60 days:

./list_oldparts.d/list_oldparts.sh FILETRANSFERSDB

Type username@database: cms_...@int2r
enter des-ede3-cbc decryption password: ****************

The output that you get should be something like this:

...
PL/SQL procedure successfully completed.

old partitions list:
part_name   high_value         num_rows   table_name
===========================================================================
SYS_P32800   TIMESTAMP' 2017-12-01 00:00:00'   1615   FILETRANSFERSDB
SYS_P32801   TIMESTAMP' 2017-11-01 00:00:00'   889   FILETRANSFERSDB
SYS_P32802   TIMESTAMP' 2018-01-01 00:00:00'   30   FILETRANSFERSDB
===========================================================================

PL/SQL procedure successfully completed.

Drop all the partitions older than 60 days (one at a time):

Take the partition names to drop from the output of the previous script.

./delete_oldparts.d/delete_oldparts.sh FILETRANSFERSDB SYS_P32801
Type username@database: cms_...@int2r
enter des-ede3-cbc decryption password: ****************

The output should contain something like:

...
SQL> SQL> 
PL/SQL procedure successfully completed.

old   1: ALTER TABLE &2 DROP PARTITION (&3) UPDATE INDEXES
new   1: ALTER TABLE FILETRANSFERSDB DROP PARTITION (SYS_P32801) UPDATE INDEXES

Table altered.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Time consuming manipulations:

Sometimes the manipulations (like partition dropping and data deletions) may take too long time - quite longer than the lifetime of the Kerberos token assigned to the login session used to initiate the procedure. In these cases the use of `screen' is helpful:

source ./krenew.sh
screen -ls
screen -r <screen id to attach to>
cd CrabDbManScr

./delete_oldparts.d/delete_oldparts.sh FILETRANSFERSDB SYS_P32801
Type username@database: cms_...@int2r
enter des-ede3-cbc decryption password: ****************

^A ? # for help on the available screen control commnds
^A d # to detach from the screen back to the console
^A k # to kill the current screen once the procedure has been completed

Few more hints on how to use the ./pwstore.sh script

Direct database connections:

For direct connections to the database you need to pass the credentials to the sqlPlus client. Because of security reasons You ! MUST ! do it like that:

source /afs/cern.ch/project/oracle/script/setoraenv.sh -s prod
pass=`./pwstore.sh -r cms_analysis_reqmgr@int2r dbconnect.pwd`; ( rlwrap sqlplus /nolog <<EOF 
connect $pass
select table_name from user_tables;
EOF
) ; unset pass

!!! IMPORTANT !!!

The following way of doing this is quite WRONG no mater if the ./pwscript.sh is to be used or not:

source /afs/cern.ch/project/oracle/script/setoraenv.sh -s prod
rlwrap  sqlplus `./pwstore.sh -r cms_....@int2r ./dbconnect.pwd`

!!! IMPORTANT !!!

The reason for this quite uncomfortable way of redirecting is well explained in the following post . Basically the main one is the fact that:

/proc/$pid/cmdline   !!! IS READABLE BY EVERYONE !!!

So passing the credentials in the command line and following execution of `ps` reveals the password to everyone!

ps auxf
...
crab3  3411  0.0  0.0 123044  2204 pts/2    Ss   12:52   0:00  \_ -bash
crab3  10402  0.0  0.0 113892  1360 pts/2    S+   16:28   0:00      \_ rlwrap sqlplus cms_analysis_reqmgr/< !!! password revealed here !!!>@int2r
crab3  10403  0.1  0.1  82960 13088 pts/4    Ss+  16:28   0:00          \_ sqlplus                                           

The alternative way of connecting safely to the database is using 'external (system) authentication' and is well explained here. But the fact that it requires a dedicated machine in which the system accounts must be mapped to the oracle database privileges, makes this approach unusable for the operator's needs and ergo we need to stick to doing the things on the edge between 'security' vs. 'obscurity'.

Protect the operator from HumanErrors:

The script always needs to have the file with the list of credentials provided and if the credential that it tries to read is not in the list then the script will not proceed to the encrypted pool and will complain even though the password may exist in the encrypted file. This is useful when the operator wants to protect him/herself from making a mistake and execute something in the production database while testing. All that is needed is just to comment the credential identifier related to the production database in the list file during the process of testing && developing.

Print the password for a specific credential on the screen:

The script is designed in a way that if it is executed directly from a terminal but not invoked by another script through a pipe it will not print out the credential fetched from the encrypted file. There are use cases when someone may need to check the stored password. Then all that is needed is to pipe the output of the script through `cat':

./pwstore.sh -r cms_...@int2r dbconnect.pwd |cat

Using ./pwstore.sh to exchange passwords:

The script was created using the instructions for password file encryption and exchange between operators from the documentation in CMSWeb. So it can easily be used for the same purpose when a password needs to be changed and communicated between the operators in a safe manner.

For example if the operator needs to change the password for the account cms_test@devdb11 follow the procedure bellow.

  1. First read the old password from the already existing pool (it will be printed on the screen):
./pwstore.sh -r cms_test@devdb11 dbconnect.pwd |cat
enter des-ede3-cbc decryption password: *****
The output should be: cms_test/@devdb11

  1. Create an empty credential pool using the old_password for encryption:
./pwstore.sh -c new_cred_file.pwd
enter des-ede3-cbc decryption password: <old_password>

  1. Generate the new password for the account:
pwgen 16 1 
# or some equivalent method

  1. Add the new_password to the empty credential pool:
./pwstore.sh -a cms_test@devdb11 new_cred_file.pwd
enter des-ede3-cbc decryption password: <old_password>

  1. Send the encrypted file to the person who is supposed to read it.

  1. Add/Replace the old password with new one in the operator's working pool for further use:
./pwstore.sh -a cms_test@devdb11 dbconnect.pwd
enter des-ede3-cbc decryption password: <the password used to maintain the operator's working pool>
# The script should ask if you want to replace the old password.

Session manager

All the sessions to the database may be monitored through this link.

-- TodorTrendafilovIvanov - 2018-04-17

Edit | Attach | Watch | Print version | History: r4 < r3 < r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r4 - 2018-05-22 - TodorTrendafilovIvanov
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    CMSPublic All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback