13-05 Controls catalog SQL export

The OSA control catalog is now available as a relational database that can be imported directly to MySQL or another database of your choice, and gives the full details of the OSA control set plus the mappings to COBIT 4.1, PCI-DSS v2 and ISO 17799.

Controls catalog with ISO 17799, PCI-DSS v2 and COBIT 4.1 relational mappings

mysql> SHOW TABLES;
+------------------------+
| Tables_in_osa_controls |
+------------------------+
| cobit41                |
| controls               |
| enhancements           |
| iso17799               |
| pci2                   |
+------------------------+
mysql> DESCRIBE controls;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| c_id                  | char(5)       | NO   | PRI |         |       | 
| NIST_id               | char(5)       | YES  |     | NULL    |       | 
| alias                 | varchar(20)   | YES  |     | NULL    |       | 
| name                  | varchar(100)  | YES  |     | NULL    |       | 
| description           | varchar(5000) | YES  |     | NULL    |       | 
| supplemental_guidance | varchar(5000) | YES  |     | NULL    |       | 
| baseline              | varchar(100)  | YES  |     | NULL    |       | 
| family                | varchar(100)  | YES  |     | NULL    |       | 
| class                 | varchar(100)  | YES  |     | NULL    |       | 
| URL                   | varchar(200)  | YES  |     | NULL    |       |
+-----------------------+---------------+------+-----+---------+-------+

mysql> DESCRIBE enhancements;
+-------------+----------------------+------+-----+---------+----------------+
| Field       | Type                 | Null | Key | Default | Extra          |
+-------------+----------------------+------+-----+---------+----------------+
| e_id        | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment | 
| c_id        | char(5)              | YES  |     | NULL    |                | 
| enhancement | varchar(5000)        | YES  |     | NULL    |                | 
+-------------+----------------------+------+-----+---------+----------------+

mysql> DESCRIBE cobit41;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| cobit_id | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment | 
| c_id     | char(5)              | YES  |     | NULL    |                | 
| cobit41  | varchar(10)          | YES  |     | NULL    |                | 
+----------+----------------------+------+-----+---------+----------------+

mysql> DESCRIBE iso17799;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| iso_id   | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment | 
| c_id     | char(5)              | YES  |     | NULL    |                | 
| iso17799 | varchar(10)          | YES  |     | NULL    |                | 
+----------+----------------------+------+-----+---------+----------------+

mysql> DESCRIBE pci2;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| pci_id   | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment | 
| c_id     | char(5)              | YES  |     | NULL    |                | 
| pci2     | varchar(10)          | YES  |     | NULL    |                | 
+----------+----------------------+------+-----+---------+----------------+

The following instructions assume you are using a *nix shell.

To import to MySQL, log on from the command line, and create a new database (in this example osa_controls) ready for the tables to be inserted:

mysql -u [username] -p
CREATE DATABASE osa_controls;
QUIT;

From the command line import the tables and data into the new database you have just created:

mysql -u [username] -p [database] < osa_controls_13_05_29052013.sql

...where [username] is either root or another account with suitable privileges and [database] is the database you wish to import into, in this case osa_controls.

Now you are ready to go! To link data elements between the tables you can query the database using an inner join between the tables on c_id. The following example retrieves the control name and ISO mappings against the control AC-01

mysql> select controls.name, iso17799.iso17799 from controls, iso17799
where controls.c_id=iso17799.c_id and controls.c_id='AC-01';
+--------------------------------------+----------+
| name                                 | iso17799 |
+--------------------------------------+----------+
| Access Control Policy And Procedures | 11.1.1   | 
| Access Control Policy And Procedures | 11.4.1   | 
| Access Control Policy And Procedures | 15.1.1   | 
+--------------------------------------+----------+
3 rows in set (0.00 sec)

Need help or advice? Drop us a line at the usual address :-)

A useful SQL reference is available from http://www.techonthenet.com/sql/index.php

The official MySQL reference http://dev.mysql.com/doc/refman/5.1/en/index.html