HP 3000 Manuals HP 3000 Manuals
Views owned by SYSTEM and CATALOG (Cont.)
Locking of the System Catalog (Cont.)
System.Spaceauth.
SYSTEM.SPACEAUTH lists all users and authorization groups for which
TABLESPACE or SECTIONSPACE authority has been granted.
Initially SYSTEM.SPACEAUTH contains a row having the column values
PUBLIC, SYSTEM, Y, Y. That is, all users have TABLESPACE and SECTIONSPACE
authority in the SYSTEM DBEFileSet. A DBA can modify these authorities
with further revokes and grants as desired.
ALLBASE/SQL accesses SYSTEM.SPACEAUTH whenever a user attempts to put a
table or long column or a section (generated by a PREPARE or DECLARE
CURSOR statement) into an explicit DBEFileSet. The owner of the table or
section must have TABLESPACE or SECTIONSPACE authority, respectively, on
the DBEFileSet.
Table 8-33. System.Spaceauth
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | Authorized DBEUserID or authorization group |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFILESET | CHAR | 20 | DBEFileSet name for SECTIONSPACE or |
| | | | TABLESPACE authority |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TABLESPACE | CHAR | 2 | Resource authority: |
| | | | |
| | | | Y (The user has authority to store table |
| | | | and long |
| | | | column data in this DBEFileSet.) |
| | | | |
| | | | |
| | | | N (The user cannot store table and long |
| | | | column |
| | | | data in this DBEFileSet.) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SECTIONSPACE | CHAR | 2 | Resource authority: |
| | | | |
| | | | Y (The user has authority to store |
| | | | sections |
| | | | in this DBEFileSet.) |
| | | | |
| | | | |
| | | | N (The user cannot store sections in this |
| | | | DBEFileSet.) |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Spaceauth;
--------------------+----------+----------+------------
USERID |DBEFILESET|TABLESPACE|SECTIONSPACE
--------------------+----------+----------+------------
PUBLIC |SYSTEM |Y |Y
PUBLIC |PURCHFS |Y |Y
PUBLIC |WAREHFS |Y |Y
PUBLIC |ORDERSFS |Y |Y
PUBLIC |FILESFS |Y |Y
PUBLIC |RECFS |Y |Y
--------------------------------------------------------------------------
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
System.Spacedefault.
SYSTEM.SPACEDEFAULT contains the default TABLESPACE and SECTIONSPACE
DBEFileSets for PUBLIC.
Initially SYSTEM.SPACEDEFAULT contains two rows, one having the column
values PUBLIC, SYSTEM, 1 and the other having the column values PUBLIC,
SYSTEM, 2. Thus SYSTEM is the default DBEFileSet for table, long column,
and section data for PUBLIC.
ALLBASE/SQL accesses SYSTEM.SPACEDEFAULT whenever a table, long column,
rule, check constraint, procedure, or section (generated by a PREPARE,
DECLARE CURSOR, or CREATE VIEW statement) is created without the IN
DBEFileSetName clause.
Table 8-34. System.Spacedefault
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | Authorized DBEUserID or authorization group |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFILESET | CHAR | 20 | Name of a DBEFileSet |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SPACETYPE | SMALLINT | 2 | Type of default DBEFileSet: |
| | | | |
| | | | 1 (SECTIONSPACE) |
| | | | |
| | | | |
| | | | 2 (TABLESPACE) |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Spacedefault;
--------------------+--------------------+---------
USERID |DBEFILESET |SPACETYPE
--------------------+--------------------+---------
PUBLIC |SYSTEM | 1
PUBLIC |SYSTEM | 2
--------------------------------------------------------------------------
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
System.Specauth.
SYSTEM.SPECAUTH lists all users and authorization groups and the special
authorities they have been granted. Because a user must be granted the
special authority CONNECT before being able to access the DBEnvironment,
every user or group that has CONNECT authority to the DBEnvironment is
listed.
ALLBASE/SQL updates SYSTEM.SPECAUTH when processing a GRANT or REVOKE
statement specifying a special authority, or when dropping a group with
special authority.
[REV BEG]
SYSTEM.SPECAUTH, along with SYSTEM.COLAUTH, SYSTEM.INSTALLAUTH,
SYSTEM.MODAUTH, SYSTEM.PROCAUTH, SYSTEM.SPACEAUTH, SYSTEM.SPACEDEFAULT,
and SYSTEM.TABAUTH, contains the security scheme for the DBEnvironment.
CATALOG.SPECAUTH is identical in format to SYSTEM.SPECAUTH; it permits
users without DBA authority or SELECT authority on SYSTEM.SPECAUTH to
examine rows to which they have access.[REV END]
Table 8-35. System.Specauth
----------------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | Authorized DBEUserID or authorization group |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| DBA | CHAR | 2 | DBA authority: |
| | | | |
| | | | Y for yes |
| | | | |
| | | | N for no |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| RESOURCE | CHAR | 2 | Resource authority: |
| | | | |
| | | | Y for yes |
| | | | |
| | | | N for no |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| CONNECT | CHAR | 2 | Connect Authority: |
| | | | |
| | | | Y for yes |
| | | | |
| | | | N for no |
| | | | |
----------------------------------------------------------------------------------------------------------
| [REV BEG] | | | |
| | | | |
| MONITOR | CHAR | 2 | Connect Authority: |
| | | | |
| | | | Y for yes |
| | | | |
| | | | N for no[REV END] |
| | | | |
----------------------------------------------------------------------------------------------------------
Example. [REV BEG]
SELECT * FROM System.Specauth;
--------------------+---+--------+-------+-------
USERID |DBA|RESOURCE|CONNECT|MONITOR
--------------------+---+--------+-------+-------
HPRDBSS |N |N |N |N
TEMP |N |N |N |N
PUBLIC |N |N |N |N
SYSTEM |N |N |N |N
JOHN@BROCK |Y |N |N |N
PURCHDBMAINT |N |Y |N |N
PURCH |N |N |Y |N
MICHELE@DING |Y |N |N |N
GEORGE@DBMS |N |N |Y |Y
--------------------------------------------------------------------------
Number of rows selected is 8
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
[REV END]
System.Tabauth.
SYSTEM.TABAUTH contains the authorities for operations on tables and
views. If a user has been granted authority to operate on only specific
columns instead of the entire table or view, you must also use
SYSTEM.COLAUTH to determine the actual columns.
This view initially contains entries for the CATALOG views. The view is
updated whenever ALLBASE/SQL processes a GRANT or REVOKE statement
containing a table or view related authority, and when PUBLIC or
PUBLICREAD tables are created. It is also updated when a DROP TABLE,
DROP VIEW, or table-related TRANSFER OWNERSHIP statement is processed.
SYSTEM.TABAUTH, along with SYSTEM.COLAUTH, SYSTEM.MODAUTH,
SYSTEM.PROCAUTH, and SYSTEM.SPECAUTH, contains the security scheme for
the DBEnvironment. ALLBASE/SQL verifies a user's authority in
SYSTEM.TABAUTH if the appropriate authority was not contained in
SYSTEM.SPECAUTH. If the UPDATE or REFERENCES column contains a C,
ALLBASE/SQL verifies the user's UPDATE authority in SYSTEM.COLAUTH.
When you create a PUBLIC or PUBLICREAD table, ALLBASE/SQL implicitly
grants table authorities to the special user PUBLIC. In this case, the
GRANTOR column contains the table owner name and the GRANTABLE column
contains an N to indicate that privileges cannot be granted.
Table 8-36. System.Tabauth
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | Authorized DBEUserID or authorization group |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NAME | CHAR | 20 | Name of the table or view on which the user |
| | | | has one or more authorities |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the table or view on which the |
| | | | user has one or more authorities |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NCOL | INTEGER | 4 | Number of columns the user has update |
| | | | authority on (0 if the UPDATE column |
| | | | contains a Y) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NRCOL | INTEGER | 4 | Number of columns the user has references |
| | | | authority on (0 if the REFERENCES column |
| | | | contains a Y or N) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SELECT | CHAR | 2 | SELECT authority on the table or view: |
| | | | |
| | | | Y for yes |
| | | | N for no |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| INSERT | CHAR | 2 | INSERT authority on the table or view: |
| | | | |
| | | | Y for yes |
| | | | N for no |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| UPDATE | CHAR | 2 | UPDATE authority on the table or view: |
| | | | |
| | | | Y for yes on all columns |
| | | | N for no on all columns |
| | | | C for yes on specific columns only |
| | | | |
-------------------------------------------------------------------------------------------------
Table 8-36. System.Tabauth (cont.)
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DELETE | CHAR | 2 | DELETE authority on the table or view: |
| | | | |
| | | | Y for yes |
| | | | N for no |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| ALTER | CHAR | 2 | ALTER authority on the table (for adding |
| | | | columns): |
| | | | |
| | | | Y for yes |
| | | | N for no |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| INDEX | CHAR | 2 | INDEX authority on the table (for creating |
| | | | indexes): |
| | | | |
| | | | Y for yes |
| | | | N for no or for a view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| REFERENCES | CHAR | 2 | REFERENCES authority on the table (for |
| | | | creating referential constraints that refer |
| | | | to this table): |
| | | | |
| | | | Y for yes on all columns |
| | | | N for no on all columns or for a view |
| | | | C for yes on specific columns only |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| GRANTOR | CHAR | 20 | Name of the grantor of the privileges |
| | | | described in this row or blank if column |
| | | | privileges have differing grantabilities |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| GRANTABLE | CHAR | 2 | GRANTABLE privilege on the table or view: |
| | | | |
| | | | Y for yes, the user can grant these |
| | | | privileges to others |
| | | | N for no, the user cannot grant these |
| | | | privileges to others |
| | | | blank if column privileges have differing |
| | | | grantabilities |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Tabauth;
--------------------+--------------------+--------------------+-----------
USERID |NAME |OWNER |NCOL
--------------------+--------------------+--------------------+-----------
PUBLIC |PARTS |PURCHDB | 0
PUBLIC |INVENTORY |PURCHDB | 0
PUBLIC |SUPPLYPRICE |PURCHDB | 0
PUBLIC |VENDORS |PURCHDB | 0
PUBLIC |ORDERS |PURCHDB | 0
PUBLIC |ORDERITEMS |PURCHDB | 0
PUBLIC |MESSAGE |PURCHDB | 0
--------------------------------------------------------------------------
Number of rows selected is 7
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+-----------+------+------+------+------+-----+-----+----------+-------
|NRCOL |SELECT|INSERT|UPDATE|DELETE|ALTER|INDEX|REFERENCES|GRANTOR
+-----------+------+------+------+------+-----+-----+----------+-------
| 0|Y |Y |Y |Y |Y |Y |N |PURCHDB
| 0|Y |Y |Y |Y |Y |Y |N |PURCHDB
| 0|Y |Y |Y |Y |Y |Y |N |PURCHDB
| 0|Y |Y |Y |Y |Y |Y |N |PURCHDB
| 0|Y |Y |Y |Y |Y |Y |N |PURCHDB
| 0|Y |Y |Y |Y |Y |Y |N |PURCHDB
| 0|Y |Y |Y |Y |Y |Y |N |PURCHDB
| 0|Y |Y |Y |Y |Y |Y |N |PURCHDB
---------------------------------------------------------------------------
Number of rows selected is 7
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>r
+---------
|GRANTABLE
+---------
|N
|N
|N
|N
|N
|N
|N
|N
----------------------------------------
First 7 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e