HP 3000 Manuals HP 3000 Manuals
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:[REV BEG]
* 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.[REV
END]
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
{ALL [PRIVILEGES] }
{[SELECT ] }
{[INSERT ] }
REVOKE {[DELETE ] } ON
{[ALTER ] |,...|}
{[INDEX ] }
{[UPDATE [({ColumnName} [,...])] ] }
{[REFERENCES [({ColumnName} [,...])]] }
{DBEUserID}
{[Owner.]TableName} FROM {GroupName} [,...] [CASCADE]
{[Owner.]ViewName } {ClassName}
{PUBLIC }
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).[REV BEG] 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.[REV END]
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 ] FROM
[EXECUTE ON PROCEDURE [Owner.]ProcedureName]
{{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.
[REV BEG]
SQL Syntax--Revoke CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority
{CONNECT }
{DBA } {DBEUserID}
REVOKE {INSTALL [AS OwnerID]} FROM {GroupName} [,...]
{MONITOR } {ClassName}
{RESOURCE }
Parameters--Revoke CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority
[REV END]
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.[REV
BEG]
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.[REV END]
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.
[REV BEG]
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
[REV END]
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} |,...| ON DBEFILESET DBEFileSetName FROM
{TABLESPACE }
{{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
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 Mgr@Account
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.
DROP TABLE VendorPerf
3. 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
4. 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
[REV BEG]
5. 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;
[REV END]