REVOKE

The REVOKE statement takes away authority that was previously granted by means of the GRANT statement.The following forms of the REVOKE statement are described individually:

Revoke table or view authority.
Revoke RUN or EXECUTE authority.
Revoke CONNECT, DBA, INSTALL, MONITOR, or RESOURCE authority.
Revoke SECTIONSPACE or TABLESPACE authority for a DBEFileSet.

For detailed information about security schemes, refer to the "DBEnvironment Configuration and Security" chapter of the ALLBASE/SQL Database Administration Guide.

Scope

ISQL or Application Programs

SQL Syntax—Revoke Table or View Authority

REVOKE { ALL [PRIVILEGES] [ SELECT INSERT DELETE ALTER INDEX UPDATE [({ColumnName} [,...])] REFERENCES [({ColumnName} [,...])] ] |,...| } ON {[Owner.] TableName [Owner.] ViewName } FROM { DBEUserID GroupName ClassName PUBLIC } [,...] [CASCADE]

Parameters—Revoke Table or View Authority

ALL [PRIVILEGES]

is the same as specifying the SELECT, INSERT, DELETE, ALTER, INDEX, UPDATE, and REFERENCES options all at one time. The word PRIVILEGES is not required; you can include it if you wish to improve readability. ALTER, INDEX, and REFERENCES are not applied when using REVOKE ALL on views.

SELECT

revokes authority to retrieve data.

INSERT

revokes authority to insert rows.

DELETE

revokes authority to delete rows.

ALTER

revokes authority to add new columns.

INDEX

revokes authority to create and drop indexes.

UPDATE

revokes authority to change data in existing rows. A list of column names can be specified to revoke UPDATE authority for only those columns if the columns were named in a GRANT statement UPDATE clause. Omitting the list of column names revokes authority to update all columns.

REFERENCES

revokes authority to reference columns in the table from foreign keys in another table. A list of column names can be specified to revoke REFERENCES authority for only those columns if the columns were named in a GRANT statement REFERENCES clause. Omitting the list of column names revokes REFERENCES authority on all columns.

[Owner.]TableName

designates the table for which authority is to be revoked.

[Owner.]ViewName

designates the view for which authority is to be revoked.

FROM

The FROM clause designates the users, authorization groups, and classes whose authority is to be revoked. PUBLIC is specified to revoke authority previously granted to PUBLIC. You cannot revoke table or view authorities from the current owner of a table or view.

CASCADE

If the revoked privilege was grantable (granted with the WITH GRANT OPTION clause), then any grants of the privilege by the revokee will also be revoked. However, if a grantee is DBA or owner of an object, cascading stops at that point for the grantee, and any grants and subsequent chains issued by him or her are still in effect. CASCADE can be specified by any user who can revoke authorities on the table or view.

If CASCADE is not specified and you are not DBA, you cannot revoke a grantable privilege if it had been granted to another user (as this would create an orphaned privilege). For more information on privileges, refer to "Using the GRANT OPTION Clause" in the "Database Creation and Security" chapter of the ALLBASE/SQL Database Administration Guide.

Description—Revoke Table or View Authority

If a view relies on a SELECT authority on a table and the REVOKE with CASCADE option is issued against that table, then the view is destroyed and a warning is returned. If the CASCADE option is not specified, the view remains, but you will receive authority errors when you try to use it.
If a referential constraint relies on a REFERENCES privilege on a table, and the REVOKE REFERENCES with the CASCADE option is issued against that table or column in it, then that particular REFERENCES privilege is destroyed. This can include any REFERENCES in the chain of privileges that are revoked in the CASCADE. A warning is returned when a constraint is destroyed.

Authorization—Revoke Table or View Authority

If you are DBA, the owner, or the grantor of table privileges and still have that grantability, you can issue the REVOKE statement and optionally the CASCADE option.

SQL Syntax—Revoke RUN or EXECUTE or Authority

REVOKE [ RUN ON [Owner.] ModuleName EXECUTE ON PROCEDURE [Owner.] ProcedureName ] FROM { { DBEUserID GroupName ClassName } [,...] PUBLIC }

Parameters--Revoke RUN or EXECUTE Authority

RUN: revokes authority to access the DBEnvironment using the specified module.
[Owner.]ModuleName: specifies the module for which RUN authority is to be revoked.
EXECUTE: revokes authority to execute the specified procedure.
[Owner.]ProcedureName: specifies the procedure for which EXECUTE authority is to be revoked.
FROM: The FROM clause lists the users, authorization groups, and classes that were previously granted the authority that is now to be revoked. PUBLIC can be specified to revoke authority that was previously granted to PUBLIC.

SQL Syntax—Revoke CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority

REVOKE { CONNECT DBA INSTALL [AS OwnerID] MONITOR RESOURCE } FROM { DBEUserID GroupName ClassName } [,...]

Parameters—Revoke CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority

CONNECT

revokes authority to use the CONNECT statement.

DBA

revokes the authority which exempts a user from all authorization restrictions. You can never revoke DBA authority from the DBECreator.

INSTALL

revokes authority to INSTALL modules where the owner name equals OwnerID. If the "AS OwnerID" clause is omitted, then revokes authority to INSTALL modules having any owner name.

Modules for an application are created and installed when that application is preprocessed using one of the SQL preprocessors. Modules can also be installed by using the ISQL INSTALL command. See the ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL for more details.

MONITOR

revokes authority to run SQLMON.

RESOURCE

revokes authority to create tables and authorization groups.

FROM

The FROM clause specifies the users, authorization groups, and classes whose authority is to be revoked.

Description—Revoke CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority

The REVOKE statement may invalidate stored sections. Refer to the VALIDATE statement and to the ALLBASE/SQL Database Administration Guide for additional information on the validation of stored sections.
Issue a REVOKE INSTALL FROM DBEUserID statement that omits the "AS OwnerID" clause to remove all INSTALL authorities for a particular user.

Authorization—Revoke CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority

If you have OWNER or DBA authority for a module, you can issue REVOKE statements for that module.

SQL Syntax—Revoke DBEFileSet Authority

REVOKE { SECTIONSPACE TABLESPACE } |,...| ON DBEFILESET DBEFileSetName FROM {{ DBEUserID GroupName ClassName } [,...] PUBLIC }

Parameters—Revoke DBEFileSet Authority

SECTIONSPACE: revokes authority to store sections in the specified DBEFileSet.
TABLESPACE: revokes authority to store table and long column data in the specified DBEFileSet.
DBEFileSetName: designates the DBEFileSet for which authority is to be revoked.

Description—Revoke DBEFileSet Authority

In order for the statement to complete successfully, the authority being revoked must have been previously granted to the specific user. In addition, the DBEFileSet cannot be the current default for that user.
When SECTIONSPACE authority is revoked, current stored section information for the DBEFileSet remains (and thus any section revalidation continues to use that DBEFileSet). No new sections for the user(s) whose authority was revoked can be placed there.
When TABLESPACE authority is revoked, table and long column data currently in the DBEFileSet remain there. No new tables or long columns for the user(s) whose authority was revoked can be place there.
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

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 Mgr@Account

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.

   DROP TABLE VendorPerf

Using CASCADE
The DBA gives CLEM@DBMS grantable privileges. Now CLEM@DBMS has all privileges on the Inventory table as well as the authority to grant any of the privileges to individual users or a class.
GRANT ALL ON PurchDB.Inventory TO CLEM@DBMS WITH GRANT OPTION
CLEM@DBMS grants AMANDA@DBMS all privileges on the Inventory table as well as the authority to grant any of the privileges to individual users or a class.
GRANT ALL ON PurchDB.Inventory TO AMANDA@DBMS WITH GRANT OPTION
The DBA revokes privileges from both CLEM@DBMS and AMANDA@DBMS.
REVOKE ALL ON PurchDB.Inventory FROM CLEM@DBMS CASCADE
REVOKE on DBEFileSet
Revoke from PUBLIC the ability to store sections in DBEFileSet1.
REVOKE SECTIONSPACE ON DBEFILESET DBEFileSet1 FROM PUBLIC
Revoke from PUBLIC the ability to store tables and long column data in DBEFileSet2.
REVOKE TABLESPACE ON DBEFILESET DBEFileSet2 FROM PUBLIC
Revoke INSTALL or MONITOR authority.
Revoke from GEORGE@DBMS the ability to run SQLMON.
REVOKE MONITOR FROM GEORGE@DBMS;
Revoke from CLEM@DBMS the ability to create modules having any owner name.
REVOKE INSTALL FROM CLEM@DBMS;
Revoke from CLEM@DBMS the ability to create modules owned by JOHN@BROCK.
REVOKE INSTALL AS JOHN@BROCK FROM CLEM@DBMS;