Setting up MySQL database

Creating new MySQL database

1. Choose the name of the database. For production database this would be 'CfgDB', for other databases use unique name different from the production. Examples below assume the name of the database to be 'CfgDBTest'.
2. Create empty database on some particulare database server. You'd need administrative privileges on the server. Assuming you use standard command-line client 'mysql', the command to create a database is:

   
   mysql -hserver-host -uroot -p
   mysql> CREATE DATABASE CfgDBTest ;
   
   

3. If needed create a separate administrating account for this database, say its name would be 'cfgdbtest-admin', and choose non-empty password:

   
   mysql -hserver-host -uroot -p
   mysql> GRANT ALL ON CfgDBTest.* TO 'cfgdbtest-admin'@'%' IDENTIFIED BY 'password' \
   	WITH GRANT OPTION ;
   
   

4. Initialize the authorization tables:

   
   % RdbMySQLAuthCmd -s server-host -d CfgDBTest -u cfgdbtest-admin -p ****** init yes
   
   

5. Initialize the database, create the metadata tables. Should be done using the account with sufficient privileges, the one you created in previous step. Note that user name and password is a part of the connection string and are specified in a clear text. Quote the string if password contains any special characters:

   
   % CfgMySQLInit 'CfgDBTest@server-host;user=cfgdbtest-admin;password=******'
   
   

   Check that all tables have been created with the command:

   
   % mysqlshow -hserver-host -ucfgdbtest-admin -p'******' CfgDBTest
   Database: CfgDBTest
   +---------------------+
   |       Tables        |
   +---------------------+
   | AliasContainerMap   |
   | AliasDatabaseMap    |
   | AliasHistory        |
   | CfgContainerMap     |
   | CfgDatabaseMap      |
   | RdbAuthGrants       |
   | RdbAuthTGroups      |
   | RdbAuthTable2TGroup |
   | RdbAuthUGroups      |
   | RdbAuthUser2UGroup  |
   | RdbAuthUsers        |
   | Shortcuts           |
   | TypeCatalog         |
   +---------------------+
   
   

6. Fill in the authorization database with the users and tables. Better done with the existing input file which should look like this (partial example from RdbMySQLAuthDB/cfgdb-commands.dat file):

   
   # create users
   ucreate bartoldu  "Rainer Bartoldus"
   ucreate kelsey    "Mike Kelsey"
   ucreate salnikov  "Andy Salnikov"
   
   # create user groups
   gcreate dch "Auth group for DCH"
   gcreate trg "Auth group for TRG"
   gcreate container-makers "Users who can create new containers"
   
   #create table groups
   tgcreate dch-tables "Table group for DCH"
   tgcreate top-tables "Table group for TOP"
   tgcreate trg-tables "Table group for TRG"
   tgcreate dch-alias-tables "Table group for DCH aliases"
   tgcreate top-alias-tables "Table group for TOP aliases"
   tgcreate trg-alias-tables "Table group for TRG aliases"
   tgcreate whole-db "Special group for datbase-level access"
   
   # add users to groups
   uinclude bartoldu trg
   uinclude kelsey dch
   uinclude salnikov container-makers
   
   # add existing tables to table groups
   tinclude *                   whole-db
   tinclude RdbAuthGrants       mgmt-tables
   tinclude RdbAuthTable2TGroup mgmt-tables
   
   # grants
   grant dch dch-tables       INSERT
   grant dch dch-alias-tables INSERT DELETE
   grant trg trg-tables       INSERT
   grant trg trg-alias-tables INSERT DELETE
   
   grant all-users top-tables       INSERT
   grant all-users top-alias-tables INSERT DELETE
   
   grant all-users whole-db      SELECT
   grant all-users common-tables INSERT
   
   grant container-makers whole-db    CREATE GRANT INSERT DELETE
   

   To fill the database use the command:

   
   % RdbMySQLAuthCmd -s server-host -d CfgDBTest -u cfgdbtest-admin -p ****** -f filename
   
   

   Later on when you need to add new reguler user it is done with much simple input file:

   
   # create user
   ucreate smith  "Agent"
   # add user to groups
   uinclude smith orc
   uinclude smith top
   

7. If needed, populate the configuration database from some other database (e.g. Objectivity). This has to be done by the user who belongs to container-makers group:

   
   % setenv CFG_MYSQL_DB CfgDBTest@server-host
   % CfgCopyNumDb -f Objectivity -t MySQL
   % CfgCopyAliasDb -f Objectivity -t MySQL
   

Destroying MySQL database

1. First step is to get the list of all users registered in the authorization database, this list can be useful later:

   
   % set users=`RdbMySQLAuthCmd -s server-host -d CfgDBTest -u cfgdbtest-admin -p ****** users | cut -d' ' -f1`
   

2. To remove grants from all users on all tables use the command which destroys the authorization database:

   
   % RdbMySQLAuthCmd -s server-host -d CfgDBTest -u cfgdbtest-admin -p ****** destroy
   

   Note that this command only drops users grants in CfgDBTest, it does not delete users accounts because these accounts can be used for other databases on the same server.
3. To destroy administrator account:

   
   mysql -hserver-host -uroot -p
   mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM  'cfgdbtest-admin'@'%' ;
   mysql> DROP USER 'cfgdbtest-admin'@'%' ;
   

4. If you need to destroy all other users too, then for each user in the list obtained in step 1, execute two SQL quries like the ones above (easier done with the little script)

   
   mysql -hserver-host -uroot -p
   mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM  'user'@'%' ;
   mysql> DROP USER 'user'@'%' ;
   

   And if you want to delete anonymous user too:

   
   mysql -hserver-host -uroot -p
   mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM  ''@'%' ;
   mysql> DROP USER ''@'%' ;
   

5. Destroy the database:

   
   mysql -hserver-host -uroot -p
   mysql> DROP DATABASE CfgDBTest ;