If SQL integrity constraints or
triggers exist for any column in the underlying table and the deletion of the row violates
any of those constraints or trigger conditions, the DELETE statement fails.
For INSERT, adding a row to the view necessarily adds a row with all columns to the base
table, including those not visible to the view. Inserting a row into a view succeeds only
when:
g Data being inserted into the columns visible to the view meet all existing integrity
constraints and trigger conditions for those columns.
g All other columns of the base table are allowed to contain NULL values.
For more information about working with views, see Chapter 8, “Working with Views.”
Read-only views
When a view definition contains a join of any kind or an aggregate, it is no longer a
legally updatable view, and InterBase cannot directly update the underlying tables.
Note You can use triggers to simulate updating a read-only view. Be aware, however, that any triggers you write are subject to all the integrity constraints on the base tables. To see an example of how to use triggers to “update” a read-only view, see “Updating views
with triggers” on page 181.
For more information about integrity constraints and triggers, see Chapter 10, “Creating
Triggers.”
DATA DEFINITION GUIDE
213
CHAPTER 13 PLANNING SECURITY
Revoking user access
Use the REVOKE statement to remove privileges that were assigned with the GRANT
statement.
At a minimum, REVOKE requires parameters that specify the following:
g One access privilege to remove
g The table or view to which the privilege revocation applies
g The name of the grantee for which the privilege is revoked.
In its full form, REVOKE removes all the privileges that GRANT can assign.
REVOKE <privileges> ON [TABLE] {tablename | viewname}
FROM {<object> | <userlist> | GROUP UNIX_group};
<privileges> = {ALL [PRIVILEGES] | <privilege_list>}
<privilege_list> = {
SELECT
| DELETE
| INSERT
| UPDATE [(col [, col …])]
| REFERENCES [(col [, col …])]
[, <privilege_list> …]}}
<object> ={
PROCEDURE procname
| TRIGGER trigname
| VIEW viewname
| PUBLIC
[, <object>]}
<userlist> = [USER] username [, [USER] username …]
The following statement removes the SELECT privilege for the user, SUSAN, on the
DEPARTMENTS table:
REVOKE SELECT ON DEPARTMENTS FROM SUSAN;
The following statement removes the UPDATE privilege for the procedure,
MONEY_TRANSFER, on the ACCOUNTS table:
REVOKE UPDATE ON ACCOUNTS FROM PROCEDURE MONEY_TRANSER;
214
INTERBASE 5
REVOKING USER ACCESS
The next statement removes EXECUTE privilege for the procedure, ACCT_MAINT, on the
MONEY_TRANSFER procedure:
REVOKE EXECUTE ON PROCEDURE MONEY_TRANSER FROM PROCEDURE ACCT_MAINT;
For the complete syntax of REVOKE, see the Language Reference.
Revocation restrictions
The following restrictions and rules of scope apply to the REVOKE statement:
g Privileges can be revoked only by the user who granted them.
g Other privileges assigned by other users are not affected.
g Revoking a privilege for a user, A, to whom grant authority was given, automatically
revokes that privilege for all users to whom it was subsequently assigned by user A.
g Privileges granted to PUBLIC can only be revoked for PUBLIC.
Revoking multiple privileges
To remove some, but not all, of the access privileges assigned to a user or procedure, list
the privileges to remove, separating them with commas. For example, the following
statement removes the INSERT and UPDATE privileges for the DEPARTMENTS table from a
user, LI:
REVOKE INSERT, UPDATE ON DEPARTMENTS FROM LI;
The next statement removes INSERT and DELETE privileges for the ACCOUNTS table from a
stored procedure, MONEY_TRANSFER:
REVOKE INSERT, DELETE ON ACCOUNTS FROM PROCEDURE MONEY_TRANSFER;
Any combination of previously assigned SELECT, DELETE, INSERT, and UPDATE privileges
can be revoked.
DATA DEFINITION GUIDE
215
CHAPTER 13 PLANNING SECURITY
Revoking all privileges
The ALL privilege combines the SELECT, DELETE, INSERT, and UPDATE privileges for a table
in a single expression. It is a shorthand way to remove all SQL table access privileges from
a user or procedure. For example, the following statement revokes all access privileges
for the DEPARTMENTS table for a user, SUSAN:
REVOKE ALL ON DEPARTMENTS FROM SUSAN;
Even if a user does not have all access privileges for a table, ALL can still be used. Using
ALL in this manner is helpful when a current user’s access rights are unknown.
Note ALL does not revoke EXECUTE privilege.
Revoking privileges for a list of users
Use a comma-separated list of users to REVOKE access privileges for a number of users at