IMAGE/SQL: Issues and answers concerning SQL tables

Software

Papers & Training

Java

HP Partners

News & Events

Register for more info

3.5 How is SQL security implemented by IMAGE/SQL?

Key points

• When you issue an IMAGESQL ATTACH command, certain SQL statements are executed behind the scene for you.
  • IMAGESQL essentially issues a CREATE TABLE statement for each data set. By default, the OWNER of the table is the database name. For example:
    

    Data set names are albums, composers in the TurboIMAGE database named 'music'. IMAGESQL issues the following SQL statements:

    CREATE PUBLIC TABLE music.albums ...;
    CREATE PUBLIC TABLE music.composers ...;
    

  • In the latest release of IMAGE/SQL (B.G1.04 and later), TurboIMAGE keys and third party indexes (TPI) are registered as indexes in the system catalog during the ATTACH command. IMAGESQL essentially issues a CREATE INDEX command for each key/TPI. For example:

    CREATE INDEX albumcode_m1 ON music.albums ...;
    CREATE INDEX composername_m1 ON music.composers ...;
    

• Only the TurboIMAGE/XL database creator (DBC) is defined as a user in the DBEnvironment immediately after the IMAGESQL ATTACH. The DBC must add any additional IMAGE/SQL users by using the IMAGESQL ADD USER command (which is explained on the next slide).

• The DBA can view the TurboIMAGE data sets (tables) associated with a database by issuing:

  isql => select * from system.table where owner='MUSIC';
  --------------------+--------------------+--------------------+------+
  NAME                |OWNER               |DBEFILESET          |TYPE  |
  --------------------+--------------------+--------------------+------+
  ALBUMS              |MUSIC               |SYSTEM              |     0|
  COMPOSERS           |MUSIC               |SYSTEM              |     0|
  LOG                 |MUSIC               |SYSTEM              |     0|
  SELECTIONS          |MUSIC               |SYSTEM              |     0|
  SELECTIONS_A        |MUSIC               |SYSTEM              |     0|
  SELECTIONS_A_V0     |MUSIC               |SYSTEM              |     1|
  

• The DBA can view the TurboIMAGE keys associated with a database by issuing:

  isql => select * from system.imagekey where owner='MUSIC';
  --------------------+--------------------+--------------------+------+
  INDEXNAME           |TABLENAME           |OWNER               |UNIQUE|
  --------------------+--------------------+--------------------+------+
  ALBUMCODE_M1        |ALBUMS              |MUSIC               |     1|
  COMPOSERNAME_M1     |COMPOSERS           |MUSIC               |     1|
  SELECTIONNAME_A1    |SELECTIONS_A        |MUSIC               |     1|
  ALBUMCODE_D1        |SELECTIONS          |MUSIC               |     0|
  SELECTIONNAME_D2    |SELECTIONS          |MUSIC               |     0|
  COMPOSERNAME_D3     |SELECTIONS          |MUSIC               |     0|
  ALBUMCODE_D1        |LOG                 |MUSIC               |     0|
  SELECTIONNAME_D2    |LOG                 |MUSIC               |     0|
  

  • The suffix _M1 is used by IMAGESQL when registering manual master keys.
  • The suffix _A1 is used by IMAGESQL when registering automatic master keys.
  • The suffix _D<n> is used by IMAGESQL when registering search keys on detail data sets (where n can be 1 to 16 depending on the number of keys).

• The DBA can view the third party indexes associated with a database by issuing:

  isql => select * from system.tpindex where owner='MUSIC';
  --------------------+--------------------+--------------------+------+
  INDEXNAME           |TABLENAME           |OWNER               |UNIQUE|
  --------------------+--------------------+--------------------+------+
  ALBUMCODE_T1        |SELECTIONS          |MUSIC               |     0|
  

  • The suffix _T<n> is used by IMAGESQL when registering third party indexes (where n can be 1 to 400 depending on the number of TPIs that exist on the database).

• This example was generated by using the "Practicing with IMAGE/SQL using MusicDBE" chapter of Getting Started with HP IMAGE/SQL (Customer Order Number 36385-90008).

Page last updated on November 29, 1995