|
|
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.
ISQL or Application Programs
REVOKE {ALL [PRIVILEGES]
[SELECT
INSERT
DELETE
ALTER
INDEX
UPDATE [({ColumnName}[,...])]
REFERENCES [({ColumnName}[,...])] ][,...]}
ON {[Owner.]TableName
[Owner.]ViewName } FROM {DBEUserID
GroupName
ClassName
PUBLIC }[,...][CASCADE]
- 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..
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.
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.
REVOKE [RUN ON [Owner.]ModuleName
EXECUTE ON PROCEDURE [Owner.] ProcedureName] FROM
{{DBEUserID
GroupName
ClassName}[,...]
PUBLIC }
- 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.
REVOKE {CONNECT
DBA
INSTALL [AS OwnerID]
MONITOR
RESOURCE } FROM {DBEUserID
GroupName
ClassName}[,...]
- 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
ALLBASE/ISQL Reference Manual 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.
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.
If you have OWNER or DBA authority for a module, you can issue REVOKE
statements for that module.
REVOKE {SECTIONSPACE
TABLESPACE } [,...] ON DBEFILESET DBEFileSetName FROM
{ {DBEUserID
GroupName
ClassName}[,...]
PUBLIC }
- 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.
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.
To revoke SECTIONSPACE or TABLESPACE, you must have DBA authority. If you have
DBA authority, you can issue the REVOKE statement for any DBEFileSet.
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
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 grants Clem privileges with the ability to grant them to
others. Now Clem 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 WITH GRANT OPTION
Clem grants Amanda 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 WITH GRANT OPTION
The DBA revokes privileges from both Clem and Amanda.
REVOKE ALL
ON PurchDB.Inventory
FROM Clem 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 the
ability to run SQLMON.
REVOKE MONITOR FROM George;
Revoke from Clem the ability to create modules having any owner name.
REVOKE INSTALL FROM Clem;
Revoke from Clem the ability to create modules owned by JOHN@BROCK.
REVOKE INSTALL AS John FROM Clem;
|