ALLBASE/SQL Reference Manual (36216-90216)

Chapter 11 493
SQL Statements E - R
REVOKE
• If a REVOKE SECTIONSPACE statement completes successfully, the STOREDSECT
table for the specified DBEFileSet is automatically dropped if it is empty and if no other
user has SECTIONSPACE authority on the DBEFileSet.
• The execution of this statement causes modification to the HPRDBSS.SPACEAUTH
system catalog table. Refer to the ALLBASE/SQL Database Administration Guide
"System Catalog" chapter.
Authorization — Revoke DBEFileSet Authority
To revoke SECTIONSPACE or TABLESPACE, you must have DBA authority. If you have
DBA authority, you can issue the REVOKE statement for any DBEFileSet.
Examples
1. Explicitly revoking authority
A public table is accessible to any user or program that can start a DBE session. It is
also accessible by concurrent transactions.
CREATE PUBLIC TABLE PurchDB.Parts
(PartNumber CHAR(16) NOT NULL,
PartName CHAR(30),
SalesPrice DECIMAL(10,2))
IN WarehFS
REVOKE ALL PRIVILEGES ON PurchDB.Parts FROM PUBLIC
GRANT SELECT,UPDATE ON PurchDB.Parts TO Accounting
Now only the DBA and members of authorization group Accounting can access the
table. Later, the accounting department manager is given control over this table.
TRANSFER OWNERSHIP OF PurchDB.Parts TO MgrAccount
2. Implicitly revoking authority
The table is private by default.
CREATE TABLE VendorPerf
(OrderNumber INTEGER NOT NULL,
ActualDelivDay SMALLINT,
ActualDelivMonth SMALLINT,
ActualDelivYear SMALLINT,
ActualDelivQty SMALLINT
Remarks VARCHAR(60) )
IN Miscellaneous
CREATE UNIQUE INDEX VendorPerfIndex
ON VendorPerf (OrderNumber)
Only the table creator and members of authorization group Warehse can update table
VendorPerf.
GRANT UPDATE ON VendorPerf TO Warehse
The table and the index are both deleted, and the grant is revoked.