HP 3000 Manuals HP 3000 Manuals
Examining The Database
In this section, you will examine the objects that were created within
MusicDBE--tables, views, and authority structures. Information about all
these objects is in the system catalog, which is automatically created by
IMAGE/SQL when the DBEnvironment is created and configured.
Run ISQL, then connect to MusicDBE using this CONNECT command:
isql=>CONNECT TO 'musicdbe'; Return
Now examine the system catalog by creating queries on the system views.
Use the following query exactly as shown to look at all the tables and
views created by the attach function. Type the query exactly as shown
because the owner name is case-sensitive.
isql=>SELECT NAME, OWNER, Return
>DBEFILESET, TYPE Return
>FROM SYSTEM.TABLE Return
>WHERE OWNER = 'MUSIC'; Return
The result table is shown below.
____________________________________________________________________________________
| |
| |
| select name, owner, dbefileset, type 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 |
| --------------------------------------------------------------------------- |
| Number of rows selected is 6 |
| U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >e |
| |
____________________________________________________________________________________
Figure 6-2. Information on Tables and Views
Each table is listed in the NAME column. The OWNER column specifies the
database to which the table belongs. If you are not the table owner
(that is, if it is part of the TurboIMAGE/XL database), you must prefix
the table name with its owner name whenever you refer to it. In this
example, a reference to the ALBUMS table would be MUSIC.ALBUMS.
The DBEFILESET column contains the name of the DBEFileSet an entry has
been associated with. IMAGE/SQL tables are always associated with the
SYSTEM DBEFileSet.
The TYPE column indicates whether the entry is a table or a view.
Entries with type 0 are tables and entries with type 1 are views.
Examining Table Descriptions
You can see individual table descriptions by using the INFO command,
which returns the column definition of a table. Use the following
command for the Albums table:
isql=>INFO music.albums; Return
The output from this ISQL command is shown below:
_____________________________________________________________________________________
| |
| |
| Column Name Data Type (length) Nulls Allowed Language |
| ---------------------------------------------------------------------------- |
| ALBUMCODE Integer NO |
| ALBUMTITLE Char ( 40) NO NATIVE-3000 |
| MEDIUM Char ( 2) NO NATIVE-3000 |
| ALBUMCOST Decimal ( 7, 0) NO |
| RECORDINGCO Char ( 10) NO NATIVE-3000 |
| DATERECORDED Char ( 16) NO NATIVE-3000 |
| MFGCODE Char ( 40) NO NATIVE-3000 |
| COMMENT Char ( 80) NO NATIVE-3000 |
| |
_____________________________________________________________________________________
Figure 6-3. Column Definitions of a Table
The Column Name column lists the names of all the columns in the table.
The Data Type column shows the SQL data type for each column and its size
(in parentheses).
The third column, Nulls Allowed, indicates whether or not null values are
permitted in this column. IMAGE/SQL tables do not allow null values, so
this column will always contain NO.
The Language column indicates which language is applicable for the column
if it is a character type. This corresponds with the Language value in
the MUSIC root file.
Examining the Authority Structure
An authority structure consists of many elements. Some of these elements
are shown below:
* Group definitions
* Table authorization for select, insert, update, and delete
operations on tables
* Column authorization for permission to update specific columns
Use the following query to examine each authorization group in MusicDBE
and their members:
isql=>SELECT * FROM SYSTEM.GROUP; Return
The query result is shown below:
____________________________________________________________________________________
| |
| |
| select * from system.group; |
| --------------------+--------------------+--------------------+----------- |
| USERID |GROUPID |OWNER |NMEMBERS |
| --------------------+--------------------+--------------------+----------- |
| MUSIC_0 |MUSIC_0 |MGR@ACCOUNT | 0 |
| MUSIC_64 |MUSIC_64 |MGR@ACCOUNT | 0 |
| |
| |
| --------------------------------------------------------------------------- |
| Number of rows selected is 2 |
| U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e |
| |
____________________________________________________________________________________
Figure 6-4. Groups in the System Catalog
Figure 6-4 shows two UserID values, MUSIC_0 and MUSIC_64, that
correspond with TurboIMAGE/XL password levels of 0 and 64. Level 0 is
assigned to items and sets that do not have levels assigned to them.
Users accessing the database without a password are given this level.
Level 64 is the creator level. Notice that the OWNER for both is
MGR@ACCOUNT, the database creator.
Passwords in TurboIMAGE/XL are case-sensitive. Also, you need to allow
these users mode 1 access to TurboIMAGE/XL to permit database updates.
The information above is set up by the ATTACH command. There are,
however, three levels of security defined in the TurboIMAGE/XL database:
MGR, DIR, and ANNCR. To use these levels, you must add additional users
with the IMAGE/SQL utility as shown below:
:RUN IMAGESQL.PUB.SYS Return
HP36385B X.F0.13 IMAGE/SQL Utility SAT, JUN 5, 1993, 6:23 PM
(C) COPYRIGHT HEWLETT-PACKARD COMPANY 1993
>>SET TURBODB music Return
>>SET SQLDBE musicdbe Return
>>UPDATE USER MGR@ACCOUNT TO PASS = MGR, MODE=1 Return
Warning: command containing a password has been logged (ATCWARN 32069).
ALLBASE/SQL group MUSIC_10 created.
View MUSIC.ALBUMS_V10 created.
View MUSIC.COMPOSERS_V10 created.
View MUSIC.SELECTIONS_A_V10 created.
View MUSIC.SELECTIONS_V10 created.
View MUSIC.LOG_V10 created.
>>ADD USER DIR@ACCOUNT WITH PASS = DIR, MODE=1 Return
Warning: command containing a password has been logged (ATCWARN 32069).
ALLBASE/SQL group MUSIC_20 created.
View MUSIC.ALBUMS_V20 created.
View MUSIC.COMPOSERS_V20 created.
View MUSIC.SELECTIONS_A_V20 created.
View MUSIC.SELECTIONS_V20 created.
View MUSIC.LOG_V20 created.
>>ADD USER ANNCR@ACCOUNT WITH PASS = ANNCR, MODE=1 Return
Warning: command containing a password has been logged (ATCWARN 32069).
ALLBASE/SQL group MUSIC_30 created.
View MUSIC.ALBUMS_V30 created.
View MUSIC.COMPOSERS_V30 created.
View MUSIC.SELECTIONS_A_V30 created.
View MUSIC.SELECTIONS_V30 created.
View MUSIC.LOG_V30 created.
You can view these changes and additions with the DISPLAY USERS command:
>>DISPLAY USERS Return
__________________________________________________________________
| |
| |
| ATTACHED BASES : MUSIC.PUB.ACCOUNT |
| DBEnvironment : MUSICDBE.PUB.ACCOUNT |
| |
| USER LOGON DBOPEN MODE USER PASSWORD USER CLASS |
| ---------- ----------- ------------- ---------- |
| |
| MGR@ACCOUNT 1 MGR 10 |
| DIR@ACCOUNT 1 DIR 20 |
| ANNCR@ACCOUNT 1 ANNCR 30 |
| |
__________________________________________________________________
Figure 6-5. Changes to Levels of Security
Re-invoke ISQL and enter the following command to look at the UserID
information:
isql=>SELECT * FROM system.group; Return
____________________________________________________________________________________
| |
| |
| select * from system.group; |
| --------------------+--------------------+--------------------+----------- |
| USERID |GROUPID |OWNER |NMEMBERS |
| --------------------+--------------------+--------------------+----------- |
| MUSIC_0 |MUSIC_0 |MGR@ACCOUNT | 0 |
| MUSIC_64 |MUSIC_64 |MGR@ACCOUNT | 0 |
| MUSIC_10 |MUSIC_10 |MGR@ACCOUNT | 0 |
| MUSIC_20 |MUSIC_20 |MGR@ACCOUNT | 1 |
| DIR@ACCOUNT |MUSIC_20 |MGR@ACCOUNT | 0 |
| MUSIC_30 |MUSIC_30 |MGR@ACCOUNT | 1 |
| ANNCR@ACCOUNT |MUSIC_30 |MGR@ACCOUNT | 0 |
| |
| |
| |
| |
| |
| --------------------------------------------------------------------------- |
| Number of rows selected is 7 |
| |
____________________________________________________________________________________
Figure 6-6. UserID Information
New UserID values now exist for DIR and ANNCR. However, no UserID exists
for MGR because MGR is the OWNER of these GroupIDs and UserIDs. Now that
all these UserIDs are established, you can access the database using any
of these MPE user logins.