SYSTEM.COLAUTH contains records of
authorities granted on specific columns in a table or
view; it does not contain records of UPDATE authorities
granted on an entire table or view.
UPDATE and REFERENCES authorities are the only authorities which can be
granted on specific columns.
UPDATE and REFERENCES authorities granted on a table or view basis
instead of a column-by-column basis are recorded in
SYSTEM.TABAUTH (refer to SYSTEM.TABAUTH).
ALLBASE/SQL uses SYSTEM.COLAUTH to verify a user's
UPDATE authority prior to executing an UPDATE statement.
If the user's UPDATE authority was not found in
SYSTEM.TABAUTH or SYSTEM.COLAUTH, ALLBASE/SQL will issue an error message.
ALLBASE/SQL uses SYSTEM.COLAUTH to verify a user's
REFERENCES authority prior to executing a CREATE TABLE statement
containing referential constraint definitions. If the user's
REFERENCES authority was not found
in SYSTEM.TABAUTH or SYSTEM.COLAUTH, ALLBASE/SQL will issue an error message.
SYSTEM.COLAUTH is initially empty, and is revised
whenever an UPDATE authority on specific columns is granted or revoked.
Each row specifies a particular table (or view) column on
which UPDATE authority has been granted.
If no column is entered, the authority is granted for all of
the columns in the table or view. For example,
if you grant UPDATE authority to
PETER@CRANE:
isql=> GRANT UPDATE (BinNumber, QtyOnHand, LastCountDat)
> ON Inventory TO PETER@CRANE;
|
SYSTEM.TABAUTH would contain a row with a DBEUserID of
PETER@CRANE,
a TableName of Inventory, a C in the UPDATE column,
and a 3 in the NCOL column. SYSTEM.COLAUTH would contain
three entries for DBEUserID
PETER@CRANE;
one for each of the three
listed columns.
If UPDATE authority was granted without specifying specific columns,
SYSTEM.TABAUTH would contain
a row with a Y in the update column and SYSTEM.COLAUTH would not
contain any rows.
This view, along with SYSTEM.MODAUTH, SYSTEM.PROCAUTH,
SYSTEM.SPECAUTH, and SYSTEM.TABAUTH, contains
the security scheme for the DBEnvironment.
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-5 System.Colauth
|
Column Name | Type | Length | Description |
|---|
| USERID | CHAR | 20 |
Authorized DBEUserID
or authorization group
|
| TABLENAME | CHAR | 20 |
Name of the table or view on
which the user or
authorization group has
update authority
|
| OWNER | CHAR | 20 | Owner of the
table or view on which the
user or authorization group has
update authority
|
| COLNAME | CHAR | 20 |
Name of a table or view column
on which the user or
authorization group has
update authority
|
| TYPE | CHAR | 2 | Type of authority the user or group has:
- U
UPDATE authority
- R
REFERENCES authority
|
| GRANTOR | CHAR | 20 | Name of the grantor of the privilege described in this row
|
| GRANTABLE | CHAR | 2 |
GRANTABLE privilege on the column:
- Y
for yes, the user can grant this privilege
to others - N
for no, the user cannot grant this privilege to others
|
Example |
 |
SELECT * FROM System.Colauth;
------------+--------------------+--------------------+------------+--------
USERID |TABLENAME |OWNER |COLNAME |TYPE
------------+--------------------+--------------------+------------+--------
KELLY@COTA |INVENTORY |PURCHDB |BINNUMBER |U
KELLY@COTA |INVENTORY |PURCHDB |QTYONHAND |U
KELLY@COTA |INVENTORY |PURCHDB |LASTCOUNTDAT|U
PETER@CRANE |INVENTORY |PURCHDB |BINNUMBER |U
PETER@CRANE |INVENTORY |PURCHDB |QTYONHAND |U
PETER@CRANE |INVENTORY |PURCHDB |LASTCOUNTDAT|U
KAREN@RIZZO |VENDORS |PURCHDB |PHONENUMBER |U
KAREN@RIZZO |VENDORS |PURCHDB |VENDORSTREET|U
KAREN@RIZZO |VENDORS |PURCHDB |VENDORCITY |U
KAREN@RIZZO |VENDORS |PURCHDB |VENDORSTATE |U
KAREN@RIZZO |VENDORS |PURCHDB |VENDORZIPCOD|U
JIM@FRANCIS |VENDORS |PURCHDB |PHONENUMBER |U
JIM@FRANCIS |VENDORS |PURCHDB |VENDORSTREET|U
JIM@FRANCIS |VENDORS |PURCHDB |VENDORCITY |U
JIM@FRANCIS |VENDORS |PURCHDB |VENDORSTATE |U
JIM@FRANCIS |VENDORS |PURCHDB |VENDORZIPCOD|U
----------------------------------------------------------------------------
Number of rows selected is 16
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+--------------------+---------
|GRANTOR |GRANTABLE
+--------------------+---------
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
---------------------------------------------------------------------------
Number of rows selected is 16
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
|
