SQL Statements
RENAME COLUMN
Use the RENAME COLUMN statement to change the name of a column.
Syntax
Usage
You can rename a column of a table if any of the following conditions are true:
When you rename a column, choose a column name that is unique within the table.
How Views and Check Constraints Are Affected
If you rename a column that a view in the database references, the text of the view in the sysviews system catalog table is updated to reflect the new column name.
If you rename a column that a check constraint in the database references, the text of the check constraint in the syschecks system catalog table is updated to reflect the new column name.
How Triggers Are Affected
If you rename a column that appears within a trigger, it is replaced with the new name only in the following instances:
When the trigger executes, if the database server encounters a column name that no longer exists in the table, it returns an error.
Example of RENAME COLUMN
The following example assigns the new name of c_num to the customer_num column in the customer table:
References
See the ALTER TABLE, CREATE TABLE, and RENAME TABLE statements in this manual.
RENAME DATABASE
Use the RENAME DATABASE statement to change the name of a database.
Syntax
Usage
You can rename a database if either of the following statements is true:
You can only rename local databases. You can rename a local database from inside a stored procedure.
References
See the CREATE DATABASE statement in this manual.
RENAME TABLE
Use the RENAME TABLE statement to change the name of a table.
Syntax
Usage
You can rename a table if any of the following statements are true:
You cannot change the table owner by renaming the table. You can use the owner. convention in the old name of the table, but an error occurs during compilation if you try to use the owner. convention in the new name of the table.
In an ANSI-compliant database, you must use the owner. convention in the old name of the table if you are referring to a table that you do not own. 
You cannot use the RENAME TABLE statement to move a table from the current database to another database or to move a table from another database to the current database. The table that you want to rename must reside in the current database. The renamed table that results from the statement remains in the current database.
Renaming Tables That Views Reference
If a view references the table that was renamed, and the view resides in the same database as the table, the database server updates the text of the view in the sysviews system catalog table to reflect the new table name. See the Informix Guide to SQL: Syntax for further information on the sysviews system catalog table.
Renaming Tables That Have Triggers
If you rename a table that has a trigger, it produces the following results:
When the trigger executes, the database server returns an error if it encounters a table name for which no table exists.
Example of Renaming a Table
The following example reorganizes the items table. The intent is to move the quantity column from the fifth position to the third. The example illustrates the following steps:
1. Create a new table, new_table, that contains the column quantity in the third position.
2. Fill the table with data from the current items table.
3. Drop the old items table.
4. Rename new_table with the name items.
The following example uses the RENAME TABLE statement as the last step:
CREATE TABLE new_table
(
item_num SMALLINT,
order_num INTEGER,
quantity SMALLINT,
stock_num SMALLINT,
manu_code CHAR(3),
total_price MONEY(8)
)
INSERT INTO new_table
SELECT item_num, order_num, quantity, stock_num,
manu_code, total_price
FROM items
DROP TABLE items
RENAME TABLE new_table TO items
References
See the ALTER TABLE, CREATE TABLE, DROP TABLE, and RENAME COLUMN statements in this manual.
REVOKE
Use the REVOKE statement to cancel any of the following for specific users or for a role:
Syntax
Usage
You can revoke privileges if:
You cannot revoke privileges from yourself. You cannot revoke privileges you granted if you named another user as grantor, nor can you revoke the status as grantor from the other user.
Database-Level Privileges
Three concentric layers of privileges, Connect, Resource, and DBA, authorize increasing power over database access and control. Only a user with the DBA privilege can grant or revoke database-level privileges.
The following table lists the appropriate keyword for each database-level privilege.
You must revoke the most powerful privilege that a user has first:
Table-Level Privileges
In one REVOKE statement, you can list one or more of the following keywords to specify the privileges you want to revoke from the same users.
(1 of 2)
|
Privilege
|
Functions
|
|---|
INSERT
| Removes the ability to insert rows into a table, view, or synonym
|
DELETE
| Removes the ability to delete rows from a table, view, or synonym
|
SELECT
| Removes the ability to issue a SELECT statement on a table, view, or synonym
|
UPDATE
| Removes the ability to change any column of the table, view, or synonym using UPDATE statements
|
INDEX
| Removes other users' ability to create permanent indexes on your table, even if those users have the Resource privilege. Using REVOKE does not remove the ability to create indexes on temporary tables, a function of the Connect database-level privilege.
|
ALTER
| Removes the authorization to issue an ALTER statement on your table, such as the ability to:
|
REFERENCES
| Removes the ability to reference columns in your table as foreign keys. Revoke the References privilege to disallow cascading deletes.
|
ALL
| Provides all the preceding privileges. You can optionally follow ALL with the PRIVILEGES keyword.
|
If a user receives the same privilege from two different grantors and one grantor revokes the privilege, the grantee still has the privilege until the second grantor also revokes the privilege. For example, if both you and a DBA grant the Update privilege on your table to ted, both you and the DBA must revoke the Update privilege to prevent ted from updating your table.
When to Use REVOKE Before GRANT
You can use combinations of REVOKE and GRANT to replace public with specific users as the grantees and to remove some columns from table-level privileges.
Replacing PUBLIC With Specified Users
If public can select from your table, you cannot revoke the Select privilege from users by name.
For example, assume public has default Select privileges on your customer table. You issue the following statement in an attempt to exclude ted from accessing your table:
The REVOKE statement results in ISAM error message 111, No record found, because the system catalog tables (syscolauth or systabauth) contain no table-level privilege entry for a user named ted. The REVOKE does not prevent ted from having all the table-level privileges given to public on the customer table.
To restrict table-level privileges, first revoke the privileges with the PUBLIC keyword, then re-grant them to the appropriate users. The following example revokes the Index and Alter privileges from all users for the customer table and grants these privileges specifically to user mary:
Restricting Access to Specific Columns
The REVOKE statement has no syntax for revoking privileges on particular column names. When you revoke the Select, Update, or References privilege from a user, you revoke the privilege for all columns in the table. If you want a user to have some access to some, but not all the columns previously granted, issue a new GRANT statement to restore the appropriate privileges.
In the following example, mary first receives the ability to reference four columns in customer, then the table owner restricts references to two columns:
The following more typical example shows how to restrict privileges for public to certain columns:
Behavior of the ALL Keyword
The ALL keyword revokes all table-level privileges available to the users or role specified in the REVOKE statement.
The ALL keyword can execute successfully when a user does not have a table-level privilege, but the REVOKE statement returns the following SQLSTATE code:
For example, assume that the user hal has the Select and Insert privileges on the customer table. User jocelyn revokes all table-level privileges from user hal with the following REVOKE statement:
The statement succeeds in revoking the Select and Insert privileges from user hal because user hal had those privileges. Simultaneously, the statement alerts you that it could not revoke privileges implied by the ALL keyword that hal did not have, such as Delete, Update, and others.
Type-Level Privileges
Any user can reference a built-in data type in an SQL statement, but not a distinct data type based on a built-in data type. The creator of a user-defined data type or a DBA must explicitly grant the Usage privilege on that new type, including a distinct data type based on a built-in data type.
REVOKE with the USAGE ON TYPE keywords removes the Usage privilege that you granted earlier to another user or role.
Routine-Level Privileges
The generic term routine refers to both a function and a procedure. A user with the Execute privilege on your routine can invoke your routine with an EXECUTE FUNCTION or EXECUTE ROUTINE statement, or a CALL statement using SPL. If you create a function, a user with the Execute privilege on your function can also use it in an expression.
When you create a routine under any of the following circumstances, you must explicitly grant the Execute privilege before you can revoke it:
Commutators or negators for the routine require separate, explicit REVOKE statements if you granted the Execute privilege to them.
When you create a routine without any of the preceding conditions in effect, public can execute your routine without a GRANT statement. To limit who executes your routine, revoke the privilege using the keywords FROM PUBLIC and then grant it to a user list (see page 1-585) or role (see page 1-586).
If two or more routines have the same routine name, use the appropriate keyword from the following list to specify which of those routines a user can no longer execute.
User List
In the user list, you identify who loses the privileges you are revoking. The user list can consist of a single user's login or multiple users' logins, separated by commas. If you use the PUBLIC keyword as the user list, the REVOKE statement revokes privileges from all users.
When the user list contains specific logins, you can combine the REVOKE statement with the GRANT statement to selectively secure tables, columns, routines, types, and so forth. For examples, see "When to Use REVOKE Before GRANT".
Spell the user names in the list exactly as they were spelled in the GRANT statement. In a database that is not ANSI compliant, you can optionally use quotes around each user in the list.
 In an ANSI-compliant database, if you do not use quotes around user, the name of the user is stored in uppercase letters.
Role Name
Only the DBA or a user granted a role with the WITH GRANT OPTION can revoke a role or its privileges. Users cannot revoke roles from themselves.
When you revoke a role that was granted with the WITH GRANT OPTION, both the role and grant option are revoked. "Revoking Privileges Granted WITH GRANT OPTION" explains revoking such a role.
The following examples show the effects of REVOKE with role name:
When you revoke table-level privileges from a role, you cannot use the RESTRICT or CASCADE clauses.
Revoking Privileges Granted WITH GRANT OPTION
If you revoke from user the privileges that you granted using the WITH GRANT OPTION keywords, you sever the chain of privileges granted by that user.
Thus, when you revoke privileges from users or a role, you also revoke the same privilege resulting from GRANT statements:
The following examples illustrate this situation. You, as the owner of the table items, issue the following statements to grant access to the user mary:
The user mary uses her new privilege to grant users cathy and paul access to the table.
Later you revoke privileges on the items table to user mary.
This single statement effectively revokes all privileges on the items table from the users mary, cathy, and paul.
The CASCADE keyword has the same effect as this default condition.
Controlling the Scope of REVOKE with the RESTRICT Option
The RESTRICT keyword causes the REVOKE statement to fail when any of the following dependencies exist:
A REVOKE statement does not fail if it pertains to a user who has the right to grant the privilege to any other user but does not exercise that right, as the following example shows:
Assume that the user clara uses the WITH GRANT OPTION clause to grant the Select privilege on the customer table to the user ted.
Assume that user ted, in turn, grants the Select privilege on the customer table to user tania. The following REVOKE statement issued by clara fails because ted used his authority to grant the Select privilege:
By contrast, if user ted does not grant the Select privilege to tania or any other user, the same REVOKE statement succeeds.
Even if ted does grant the Select privilege to another user, either of the following statements succeeds:
References
See the GRANT, GRANT FRAGMENT, and REVOKE FRAGMENT statements in this manual.
For information about roles, see the CREATE ROLE, DROP ROLE, and SET ROLE statements in this manual.
See the discussion of privileges and security in the Informix Guide to SQL: Tutorial.
REVOKE FRAGMENT
The REVOKE FRAGMENT statement enables you to revoke privileges that have been granted on individual fragments of a fragmented table. You can use this statement to revoke the Insert, Update, and Delete fragment-level privileges from users.
Syntax
Usage
Use the REVOKE FRAGMENT statement to revoke the Insert, Update, or Delete privilege on one or more fragments of a fragmented table from one or more users.
The REVOKE FRAGMENT statement is only valid for tables that are fragmented according to an expression-based distribution scheme. See the ALTER FRAGMENT statement on page 1-29 for an explanation of expression-based distribution schemes.
You can specify one fragment or a list of fragments in the REVOKE FRAGMENT statement. To specify a fragment, name the dbspace in which the fragment resides.
You do not have to specify a particular fragment or a list of fragments in the REVOKE FRAGMENT statement. If you do not specify any fragments in the statement, the specified users lose the specified privileges on all fragments for which the users currently have those privileges.
Fragment-Level Privileges
You can revoke fragment-level privileges individually or in combination. List the keywords that correspond to the privileges that you are revoking from user. The keywords are described in the following list.
If you specify the ALL keyword in a REVOKE FRAGMENT statement, the specified users lose all fragment-level privileges that they currently have on the specified fragments.
For example, assume that a user currently has the Update privilege on one fragment of a table. If you use the ALL keyword to revoke all current privileges on this fragment from this user, the user loses the Update privilege that he or she had on this fragment.
Examples of the REVOKE FRAGMENT Statement
The examples that follow are based on the customer table. All the examples assume that the customer table is fragmented by expression into three fragments that reside in the dbspaces that are named dbsp1, dbsp2, and dbsp3.
Revoking One Privilege
The following statement revokes the Update privilege on the fragment of the customer table in dbsp1 from the user ed:
Revoking More Than One Privilege
The following statement revokes the Update and Insert privileges on the fragment of the customer table in dbsp1 from the user susan:
Revoking All Privileges
The following statement revokes all privileges currently granted to the user harry on the fragment of the customer table in dbsp1.:
Revoking Privileges on More Than One Fragment
The following statement revokes all privileges currently granted to the user millie on the fragments of the customer table in dbsp1 and dbsp2:
Revoking Privileges from More Than One User
The following statement revokes all privileges currently granted to the users jerome and hilda on the fragment of the customer table in dbsp3:
Revoking Privileges Without Specifying Fragments
The following statement revokes all current privileges from the user mel on all fragments for which this user currently has privileges:
References
See the REVOKE and GRANT FRAGMENT statements in this manual.
ROLLBACK WORK
Use the ROLLBACK WORK statement to cancel a transaction and undo any changes that occurred since the beginning of the transaction.
Syntax
Usage
The ROLLBACK WORK statement is valid only in databases with transactions.
In a database that is not ANSI compliant, start a transaction with a BEGIN WORK statement. You can end a transaction with a COMMIT WORK statement or cancel the transaction with a ROLLBACK WORK statement. The ROLLBACK WORK statement restores the database to the state that existed before the transaction began. Use the ROLLBACK WORK statement only at the end of a multistatement operation.
The ROLLBACK WORK statement releases all row and table locks that the cancelled transaction holds. If you issue a ROLLBACK WORK statement when no transaction is pending, an error occurs.
In an ANSI-compliant database, transactions are implicit. Transactions start after each COMMIT WORK or ROLLBACK WORK statement. If you issue a ROLLBACK WORK statement when no transaction is pending, the statement is accepted but has no effect.
The ROLLBACK WORK statement closes all open cursors except those that are declared with hold, which remain open despite transaction activity.
If you use the ROLLBACK WORK statement within a routine that a WHENEVER statement calls, specify WHENEVER SQLERROR CONTINUE and WHENEVER SQLWARNING CONTINUE before the ROLLBACK WORK statement. Specifying these before the ROLLBACK WORK statement prevents the program from looping if the ROLLBACK WORK statement encounters an error or a warning.
References
See the BEGIN WORK and COMMIT WORK statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of ROLLBACK WORK in Chapter 5.
|
