Change User -- Fields and controls

Database page

Schema page

Table page

Index page

View page

Database page

Choose the appropriate authorities

OK

Cancel

Choose the appropriate authorities

Under Choose the appropriate authorities to grant to the selected user, indicate which database authorities you want a user to have or to stop exercising. You can grant or revoke authorities to perform specific activities, or the authority to administer the entire database:

• Select one or more of the following check boxes if you want to grant authority to connect to the database, create certain objects, or register functions as not fenced. Deselect the check boxes to revoke these authorities.

  Connect database

  Grants the authority to access the database (CONNECT authority)

  Create tables

  Grants:

  • The authority to create tables in the database (the CREATETAB authority). With this authority, the user can create tables in a schema that he or she owns. To enable the user to create tables in other schemas, you must also grant either of the following authorizations:

    • The authority to create tables in schemas that the user defines when he or she defines the tables (IMPLICIT_SCHEMA authority). If you want to grant this authority, select the Create schemas implicitly check box on the Database page.

    • The privilege to create tables in a specific existing schema (CREATEIN privilege). You can grant this privilege on the Schema page.

  • The CONTROL privilege on each table that the user creates in this database. All other table privileges automatically come with the CONTROL privilege. They are: SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, and REFERENCES. The user keeps all table privileges if the CREATETAB authority is revoked.

  Create packages

  Grants:

  • The authority to create packages in the database (BINDADD authority).

  • The CONTROL privilege on each package that the user creates in this database. The other two package privileges--BIND and EXECUTE--automatically come with CONTROL. The user keeps all package privileges if the BINDADD authority is revoked.

  Register functions to execute in database manager's process

  Grants the authority to register functions as not-fenced; that is, to allow them to run in the database manager's process (CREATE_NOT_FENCED authority). After the user registers a function as not-fenced, it continues to run in the database manager's process even if the CREATE_NOT_FENCED authority is revoked.

  Create schemas implicitly

  Grants the authority to create a schema for an object by specifying the schema's name in the definition of the object (IMPLICIT_SCHEMA authority)

• Select the Database administrator authority check box if you want to grant DBADM authority. This authority includes, but is not limited to:
  • Grant all the authorities listed above
  • Grant all privileges on all objects in the database
  • Give permission to grant these privileges to other users

  Deselect this check box if you want to revoke DBADM authority.

Schema page

Schema

Symbols in privileges columns

Add Schema

Grant All

Revoke All

CREATEIN

DROPIN

ALTERIN

OK

Cancel

Schema

This box consists of a Schema column and a column for each schema privilege. Use this box to list and select the schemas that you want to grant or revoke privileges on. Initially, this box lists any schemas that the user already has privileges on. To add other schemas to the box, click on Add Schema and select them on the Add Schema window.

Next to the schema names, this box displays symbols that indicate whether the user already has privileges on the schemas, and whether grants and revocations of privileges are pending or have been made. These symbols change to reflect changes that you make when you use the Privileges boxes or the Grant All or Revoke All push buttons.

Symbols in privileges columns

On the Schema, Table, Index, and View pages, symbols in the privileges columns indicate whether the user already has privileges on the objects that you selected, and whether grants and revocations of privileges are pending or have been made. The symbols are:

• Check marks
• Double check marks
• Xs

Check marks

A check mark framed by corner brackets means that a grant of a privilege, without the Grant option, is pending. An unframed check mark means that a privilege, without the Grant option, has been granted.

Example

SCHEMA1 is listed in the Schema column on the Schema page. You want to grant the CREATEIN privilege on SCHEMA1; so you select SCHEMA1 and specify Yes in the CREATEIN box. Your grant of the CREATEIN privilege is now pending. As a sign of this, a check mark framed by corner brackets appears in the CREATEIN column next to SCHEMA1.

Then you click on Apply, and the privilege, but not the Grant option, is granted. As a sign of this, the corner brackets disappear, leaving the check mark unframed.

Double check marks

A double check mark framed by corner brackets means that a grant of a privilege and the Grant option (the right to grant that privilege to other users and to groups) is pending. An unframed double check mark means that a privilege, with the Grant option, has been granted.

Example

TABLE2 is listed in the Table column on the Table page. You want to grant:

• The INSERT privilege on TABLE2
• The right to grant the INSERT privilege on TABLE2 to other users

Accordingly, you select TABLE2 and specify Grant in the INSERT box. Your grant of the INSERT privilege and the Grant option is now pending. As a sign of this, a double check mark framed by corner brackets appears across from TABLE2 in the INSERT column.

Then you click on Apply, and the privilege, with the Grant option, is granted. As a sign of this, the corner brackets disappear, leaving the double check mark unframed.

Xs

An X framed by corner brackets means that the revocation of a privilege is pending (see Example 1), or that the override of a request to grant a privilege is pending (Example 2). An unframed X means that the user doesn't have a particular privilege (Examples 1, 3).

Example 1

INDEX3 is listed in the Index column on the Index page. The selected user has the CONTROL privilege on INDEX3, but no longer needs it. So you decide to revoke it. Accordingly, you select INDEX3 and specify No in the CONTROL box. The revocation that you want is now pending. As a sign of this, an X framed by corner brackets appears across from INDEX3 in the CONTROL column.

Then you click on Apply, and the privilege is revoked. As a sign of this, the corner brackets disappear, leaving the X unframed.

Example 2

You want to grant the DELETE privilege on VIEW4. So, on the View page, you select VIEW4 in the View column and specify Yes in the DELETE box. Then you determine that it would be better to grant the DELETE privilege to a group rather than to individual users. So you override the Yes, by selecting VIEW4 again and specifying No in the DELETE box. As a sign that this override is pending, an X framed by corner brackets appears in the DELETE column across from VIEW4.

Example 3

On the View page, you click on the Add View push button. Then, on the Add View window, you select the schema that contains VIEW5, select VIEW5, and click on Add and Close. Consequently, VIEW5 appears in the View column; and unframed Xs appear across from it in the view privileges columns. These unframed Xs mean that the selected user doesn't have any privileges on VIEW5.

Add Schema

Click on this push button to open the Add Schema window, so that you can select schemas that you want to grant or revoke privileges on. When you click on Apply on this window, the selected schemas are added to the Schema box on the Schema page.

Grant All

On the Schema page, click on this push button to open the Select Grant Option window, so that you can grant all privileges on the selected schemas either with or without the Grant option (the right to grant the privileges to other users and to groups).

On the Table and View pages, click on this push button to grant all privileges on the selected tables or views, and to provide the Grant option with all privileges except CONTROL.

On the Index page, click on this push button to grant the CONTROL privilege on the selected indexes.

Revoke All

Click on this push button to:

1. Revoke all privileges on all objects that are currently selected.

2. Cancel all requests for privileges on objects that are currently selected.

   Example

   Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Change User notebook, you decide it would be better to grant only the SELECT and INSERT privileges on this table. To do this, you could:

   1. Select TABLE1 in the Table column.

   2. Click on Revoke All to reset allPrivileges boxes to No.

   3. Use the SELECT and INSERT boxes to grant the SELECT and INSERT privileges.

CREATEIN

This box indicates whether the selected user has the CREATEIN privilege on the schema or schemas selected in the Schema column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the CREATEIN privilege without the Grant option (the right to grant the CREATEIN privilege on the selected schema or schemas to other users or to groups). If the user already has the CREATEIN privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the CREATEIN privilege.

Grant

Grants the CREATEIN privilege with the Grant option. If the user already has CREATEIN without the Grant option, Grant provides the option.

The CREATEIN privilege, along with authorization to create an object, allows a user to create the object in a schema. (Exception: If a user owns a schema and has CREATETAB authority, the user doesn't need the CREATEIN privilege on the schema to create tables in it.)

If you select multiple schemas for which you specified different settings (for example, Yes for one schema andNo for the others), the CREATEIN box is blank. But the settings that you specified remain in effect.

DROPIN

This box indicates whether the selected user has the DROPIN privilege on the schema or schemas selected in the Schema column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the DROPIN privilege without the Grant option (the right to grant the DROPIN privilege on the selected schema or schemas to other users and to groups). If the user already has the DROPIN privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the DROPIN privilege.

Grant

Grants the DROPIN privilege with the Grant option. If the user already has DROPIN without the Grant option, Grant provides the option.

The DROPIN privilege on a schema allows a user to drop any object that belongs to that schema.

If you select multiple schemas for which you specified different settings (for example, Yes for one schema andNo for the others), the DROPIN box is blank. But the settings that you specified remain in effect.

ALTERIN

This box indicates whether the selected user has the ALTERIN privilege on schemas selected in the Schema column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the ALTERIN privilege without the Grant option (the right to grant the ALTERIN privilege on the selected schema or schemas to other users and to groups). If the user already has the ALTERIN privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the ALTERIN privilege.

Grant

Grants the ALTERIN privilege with the Grant option. If the user already has ALTERIN without the Grant option, Grant provides the option.

The ALTERIN privilege on a schema allows a user to alter the objects that belong to the schema, and to change the comments on these objects.

If you select multiple schemas for which you specified different settings (for example, Yes for one schema andNo for the others), the ALTERIN box is blank. But the settings that you specified remain in effect.

Table page

Table

Symbols in privileges columns

Add Table

Grant All

Revoke All

SELECT

INSERT

UPDATE

DELETE

CONTROL

ALTER

INDEX

REFERENCES

OK

Cancel

Table

This box consists of a Table column and a column for each table privilege. Use this box to list and select the tables that you want to grant or revoke privileges on. Initially, this box lists any tables that the user already has privileges on. To add other tables to the box, click on Add Table and select them on the Add Table window.

Next to the table names, this box displays symbols that indicate whether the user already has privileges on the tables, and whether grants and revocations of privileges are pending or have been made. These symbols change to reflect changes that you make when you use the Privileges boxes or the Grant All or Revoke All push buttons.

Symbols in privileges columns

On the Schema, Table, Index, and View pages, symbols in the privileges columns indicate whether the user already has privileges on the objects that you selected, and whether grants and revocations of privileges are pending or have been made. The symbols are:

• Check marks
• Double check marks
• Xs

Check marks

A check mark framed by corner brackets means that a grant of a privilege, without the Grant option, is pending. An unframed check mark means that a privilege, without the Grant option, has been granted.

Example

SCHEMA1 is listed in the Schema column on the Schema page. You want to grant the CREATEIN privilege on SCHEMA1; so you select SCHEMA1 and specify Yes in the CREATEIN box. Your grant of the CREATEIN privilege is now pending. As a sign of this, a check mark framed by corner brackets appears in the CREATEIN column next to SCHEMA1.

Then you click on Apply, and the privilege, but not the Grant option, is granted. As a sign of this, the corner brackets disappear, leaving the check mark unframed.

Double check marks

A double check mark framed by corner brackets means that a grant of a privilege and the Grant option (the right to grant that privilege to other users and to groups) is pending. An unframed double check mark means that a privilege, with the Grant option, has been granted.

Example

TABLE2 is listed in the Table column on the Table page. You want to grant:

• The INSERT privilege on TABLE2
• The right to grant the INSERT privilege on TABLE2 to other users

Accordingly, you select TABLE2 and specify Grant in the INSERT box. Your grant of the INSERT privilege and the Grant option is now pending. As a sign of this, a double check mark framed by corner brackets appears across from TABLE2 in the INSERT column.

Then you click on Apply, and the privilege, with the Grant option, is granted. As a sign of this, the corner brackets disappear, leaving the double check mark unframed.

Xs

An X framed by corner brackets means that the revocation of a privilege is pending (see Example 1), or that the override of a request to grant a privilege is pending (Example 2). An unframed X means that the user doesn't have a particular privilege (Examples 1, 3).

Example 1

INDEX3 is listed in the Index column on the Index page. The selected user has the CONTROL privilege on INDEX3, but no longer needs it. So you decide to revoke it. Accordingly, you select INDEX3 and specify No in the CONTROL box. The revocation that you want is now pending. As a sign of this, an X framed by corner brackets appears across from INDEX3 in the CONTROL column.

Then you click on Apply, and the privilege is revoked. As a sign of this, the corner brackets disappear, leaving the X unframed.

Example 2

You want to grant the DELETE privilege on VIEW4. So, on the View page, you select VIEW4 in the View column and specify Yes in the DELETE box. Then you determine that it would be better to grant the DELETE privilege to a group rather than to individual users. So you override the Yes, by selecting VIEW4 again and specifying No in the DELETE box. As a sign that this override is pending, an X framed by corner brackets appears in the DELETE column across from VIEW4.

Example 3

On the View page, you click on the Add View push button. Then, on the Add View window, you select the schema that contains VIEW5, select VIEW5, and click on Add and Close. Consequently, VIEW5 appears in the View column; and unframed Xs appear across from it in the view privileges columns. These unframed Xs mean that the selected user doesn't have any privileges on VIEW5.

Add Table

Click on this push button to open the Add Table window, so that you can select tables that you want to grant or revoke privileges on. When you click on Apply on this window, the selected tables are added to the Table box on the Table page.

Grant All

On the Schema page, click on this push button to open the Select Grant Option window, so that you can grant all privileges on the selected schemas either with or without the Grant option (the right to grant the privileges to other users and to groups).

On the Table and View pages, click on this push button to grant all privileges on the selected tables or views, and to provide the Grant option with all privileges except CONTROL.

On the Index page, click on this push button to grant the CONTROL privilege on the selected indexes.

Revoke All

Click on this push button to:

1. Revoke all privileges on all objects that are currently selected.

2. Cancel all requests for privileges on objects that are currently selected.

   Example

   Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Change User notebook, you decide it would be better to grant only the SELECT and INSERT privileges on this table. To do this, you could:

   1. Select TABLE1 in the Table column.

   2. Click on Revoke All to reset allPrivileges boxes to No.

   3. Use the SELECT and INSERT boxes to grant the SELECT and INSERT privileges.

SELECT

This box indicates whether the selected user holds the SELECT privilege, with the Grant option, on the table or tables selected in the Table column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the SELECT privilege without the Grant option (the right to grant the SELECT privilege on the selected table to other users and to groups). If the user already has the SELECT privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the SELECT privilege.

Grant

Grants the SELECT privilege with the Grant option. If the user already has SELECT without the Grant option, Grant provides the option.

The SELECT privilege allows a user to retrieve rows from a table and to create views on a table.

If you select multiple tables for which you specified different settings (for example, Yes for one table andNo for the others), the SELECT box is blank. But the settings that you specified remain in effect.

INSERT

This box indicates whether the selected user holds the INSERT privilege, with the Grant option, on the table or tables selected in the Table column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the INSERT privilege without the Grant option (the right to grant the INSERT privilege on the selected table to other users and to groups). If the user already has the INSERT privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the INSERT privilege.

Grant

Grants the INSERT privilege with the Grant option. If the user already has INSERT without the Grant option, Grant provides the option.

The INSERT privilege allows a user to insert rows into a table.

If you select multiple tables for which you specified different settings (for example, Yes for one table andNo for the others), the INSERT box is blank. But the settings that you specified remain in effect.

UPDATE

This box indicates whether the selected user holds the UPDATE privilege, with the Grant option, on the table or tables selected in the Table column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the UPDATE privilege without the Grant option (the right to grant the UPDATE privilege on the selected table to other users and to groups). If the user already has the UPDATE privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the UPDATE privilege.

Grant

Grants the UPDATE privilege with the Grant option. If the user already has UPDATE without the Grant option, Grant provides the option.

The UPDATE privilege allows a user to update a table.

If you select multiple tables for which you specified different settings (for example, Yes for one table andNo for the others), the UPDATE box is blank. But the settings that you specified remain in effect.

DELETE

This box indicates whether the selected user holds the DELETE privilege, with the Grant option, on the table or tables selected in the Table column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the DELETE privilege without the Grant option (the right to grant the DELETE privilege on the selected table to other users and to groups). If the user already has the DELETE privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the DELETE privilege.

Grant

Grants the DELETE privilege with the Grant option. If the user already has DELETE without the Grant option, Grant provides the option.

The DELETE privilege allows a user to delete rows from a table.

If you select multiple tables for which you specified different settings (for example, Yes for one table andNo for the others), the DELETE box is blank. But the settings that you specified remain in effect.

CONTROL

This box indicates whether the selected user holds the CONTROL privilege on the on the table or tables selected in the Table column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the CONTROL privilege.

No

Revokes the CONTROL privilege.

With the CONTROL privilege on a table, a user:

• Acquires all other privileges on the table-- SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, and REFERENCES

• Can grant these other privileges to other users of the table

• Can drop the table

• Can execute the RUNSTATS utility against the table and its indexes

If you select multiple tables for which you specified different settings (for example, Yes for one table andNo for the others), the CONTROL box is blank. But the settings that you specified remain in effect.

ALTER

This box indicates whether the selected use holds the ALTER privilege, with the Grant option, on the on the table or tables selected in the Table column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the ALTER privilege without the Grant option (the right to grant the ALTER privilege on the selected table to other users and to groups). If the user already has the ALTER privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the ALTER privilege.

Grant

Grants the ALTER privilege with the Grant option. If the user already has ALTER without the Grant option, Grant provides the option.

The ALTER privilege on a table allows a user to:

• Add columns to the table

• Create or drop a primary key, a check constraint, or a trigger

• Create or drop a foreign key, provided that the user also has the REFERENCES privilege on the parent table

• Add or change a comment on a table

If you select multiple tables for which you specified different settings (for example, Yes for one table andNo for the others), the ALTER box is blank. But the settings that you specified remain in effect.

INDEX

This box indicates whether the selected user holds the INDEX privilege, with the Grant option, on the on the table or tables selected in the Table column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the INDEX privilege without the Grant option (the right to grant the INDEX privilege on the selected table to other users and to groups). If the user already has the INDEX privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the INDEX privilege.

Grant

Grants the INDEX privilege with the Grant option. If the user already has INDEX without the Grant option, Grant provides the option.

The INDEX privilege allows a user to create an index on a table.

If you select multiple tables for which you specified different settings (for example, Yes for one table andNo for the others), the INDEX box is blank. But the settings that you specified remain in effect.

REFERENCES

This box indicates whether the selected user holds the REFERENCES privilege, with the Grant option, on the on the table or tables selected in the Table column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the REFERENCES privilege without the Grant option (the right to grant the REFERENCES privilege on the selected table to other users and to groups). If the user already has the REFERENCES privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the REFERENCES privilege.

Grant

Grants the REFERENCES privilege with the Grant option. If the user already has REFERENCES without the Grant option, Grant provides the option.

The REFERENCES privilege allows a user to create and drop a foreign key.

If you select multiple tables for which you specified different settings (for example, Yes for one table andNo for the others), the REFERENCES box is blank. But the settings that you specified remain in effect.

Index page

Index

Symbols in privileges columns

Add Index

Grant All

Revoke All

CONTROL

OK

Cancel

Index

This box consists of an Index column and a column for the CONTROL privilege. Use this box to list and select the indexes on which you want the CONTROL privilege granted or revoked. Initially, this box lists any indexes on which the user already has the CONTROL privilege. To add other indexes to the box, click on Add Index and select them on the Add Index window.

Next to the index names, this box displays symbols that indicate whether the user already has the CONTROL privilege on the indexes, and whether grants and revocations of this privilege are pending or have been made. These symbols change to reflect changes that you make when you use the CONTROL box or the Grant All or Revoke All push buttons.

Symbols in privileges columns

On the Schema, Table, Index, and View pages, symbols in the privileges columns indicate whether the user already has privileges on the objects that you selected, and whether grants and revocations of privileges are pending or have been made. The symbols are:

• Check marks
• Double check marks
• Xs

Check marks

A check mark framed by corner brackets means that a grant of a privilege, without the Grant option, is pending. An unframed check mark means that a privilege, without the Grant option, has been granted.

Example

SCHEMA1 is listed in the Schema column on the Schema page. You want to grant the CREATEIN privilege on SCHEMA1; so you select SCHEMA1 and specify Yes in the CREATEIN box. Your grant of the CREATEIN privilege is now pending. As a sign of this, a check mark framed by corner brackets appears in the CREATEIN column next to SCHEMA1.

Then you click on Apply, and the privilege, but not the Grant option, is granted. As a sign of this, the corner brackets disappear, leaving the check mark unframed.

Double check marks

A double check mark framed by corner brackets means that a grant of a privilege and the Grant option (the right to grant that privilege to other users and to groups) is pending. An unframed double check mark means that a privilege, with the Grant option, has been granted.

Example

TABLE2 is listed in the Table column on the Table page. You want to grant:

• The INSERT privilege on TABLE2
• The right to grant the INSERT privilege on TABLE2 to other users

Accordingly, you select TABLE2 and specify Grant in the INSERT box. Your grant of the INSERT privilege and the Grant option is now pending. As a sign of this, a double check mark framed by corner brackets appears across from TABLE2 in the INSERT column.

Then you click on Apply, and the privilege, with the Grant option, is granted. As a sign of this, the corner brackets disappear, leaving the double check mark unframed.

Xs

An X framed by corner brackets means that the revocation of a privilege is pending (see Example 1), or that the override of a request to grant a privilege is pending (Example 2). An unframed X means that the user doesn't have a particular privilege (Examples 1, 3).

Example 1

INDEX3 is listed in the Index column on the Index page. The selected user has the CONTROL privilege on INDEX3, but no longer needs it. So you decide to revoke it. Accordingly, you select INDEX3 and specify No in the CONTROL box. The revocation that you want is now pending. As a sign of this, an X framed by corner brackets appears across from INDEX3 in the CONTROL column.

Then you click on Apply, and the privilege is revoked. As a sign of this, the corner brackets disappear, leaving the X unframed.

Example 2

You want to grant the DELETE privilege on VIEW4. So, on the View page, you select VIEW4 in the View column and specify Yes in the DELETE box. Then you determine that it would be better to grant the DELETE privilege to a group rather than to individual users. So you override the Yes, by selecting VIEW4 again and specifying No in the DELETE box. As a sign that this override is pending, an X framed by corner brackets appears in the DELETE column across from VIEW4.

Example 3

On the View page, you click on the Add View push button. Then, on the Add View window, you select the schema that contains VIEW5, select VIEW5, and click on Add and Close. Consequently, VIEW5 appears in the View column; and unframed Xs appear across from it in the view privileges columns. These unframed Xs mean that the selected user doesn't have any privileges on VIEW5.

Add Index

Click on this push button to open the Add Index window, so that you can select indexes for which you want to grant or revoke the CONTROL privilege. When you click on Apply on this window, the selected indexes are added to the Index box on the Index page.

Grant All

On the Schema page, click on this push button to open the Select Grant Option window, so that you can grant all privileges on the selected schemas either with or without the Grant option (the right to grant the privileges to other users and to groups).

On the Table and View pages, click on this push button to grant all privileges on the selected tables or views, and to provide the Grant option with all privileges except CONTROL.

On the Index page, click on this push button to grant the CONTROL privilege on the selected indexes.

Revoke All

Click on this push button to:

1. Revoke all privileges on all objects that are currently selected.

2. Cancel all requests for privileges on objects that are currently selected.

   Example

   Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Change User notebook, you decide it would be better to grant only the SELECT and INSERT privileges on this table. To do this, you could:

   1. Select TABLE1 in the Table column.

   2. Click on Revoke All to reset allPrivileges boxes to No.

   3. Use the SELECT and INSERT boxes to grant the SELECT and INSERT privileges.

CONTROL

This box indicates whether the user referenced on the Database page has the CONTROL privilege on the index or indexes selected in the Index column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the CONTROL privilege.

No

Revokes the CONTROL privilege.

The CONTROL privilege on an index allows a user to drop the index. Unless the user has SYSADM or DBADM authority, he or she cannot grant this privilege to other users or to groups.

If you select multiple indexes for which you specified different settings (for example, Yes for one index andNo for the others), the CONTROL box is blank. But the settings that you specified remain in effect.

View page

View

Symbols in privileges columns

Add View

Grant All

Revoke All

SELECT

INSERT

UPDATE

DELETE

CONTROL

OK

Cancel

View

This box consists of a View column and a column for each view privilege. Use this box to list and select the views that you want to grant or revoke privileges on. Initially, this box lists any views that the user already has privileges on. To add other views to the box, click on Add View and select them on the Add View window.

Next to the view names, this box displays symbols that indicate whether the user already has privileges on the views, and whether grants and revocations of privileges are pending or have been made. These symbols change to reflect changes that you make when you use the Privileges boxes or the Grant All or Revoke All push buttons.

Symbols in privileges columns

On the Schema, Table, Index, and View pages, symbols in the privileges columns indicate whether the user already has privileges on the objects that you selected, and whether grants and revocations of privileges are pending or have been made. The symbols are:

• Check marks
• Double check marks
• Xs

Check marks

A check mark framed by corner brackets means that a grant of a privilege, without the Grant option, is pending. An unframed check mark means that a privilege, without the Grant option, has been granted.

Example

SCHEMA1 is listed in the Schema column on the Schema page. You want to grant the CREATEIN privilege on SCHEMA1; so you select SCHEMA1 and specify Yes in the CREATEIN box. Your grant of the CREATEIN privilege is now pending. As a sign of this, a check mark framed by corner brackets appears in the CREATEIN column next to SCHEMA1.

Then you click on Apply, and the privilege, but not the Grant option, is granted. As a sign of this, the corner brackets disappear, leaving the check mark unframed.

Double check marks

A double check mark framed by corner brackets means that a grant of a privilege and the Grant option (the right to grant that privilege to other users and to groups) is pending. An unframed double check mark means that a privilege, with the Grant option, has been granted.

Example

TABLE2 is listed in the Table column on the Table page. You want to grant:

• The INSERT privilege on TABLE2
• The right to grant the INSERT privilege on TABLE2 to other users

Accordingly, you select TABLE2 and specify Grant in the INSERT box. Your grant of the INSERT privilege and the Grant option is now pending. As a sign of this, a double check mark framed by corner brackets appears across from TABLE2 in the INSERT column.

Then you click on Apply, and the privilege, with the Grant option, is granted. As a sign of this, the corner brackets disappear, leaving the double check mark unframed.

Xs

An X framed by corner brackets means that the revocation of a privilege is pending (see Example 1), or that the override of a request to grant a privilege is pending (Example 2). An unframed X means that the user doesn't have a particular privilege (Examples 1, 3).

Example 1

INDEX3 is listed in the Index column on the Index page. The selected user has the CONTROL privilege on INDEX3, but no longer needs it. So you decide to revoke it. Accordingly, you select INDEX3 and specify No in the CONTROL box. The revocation that you want is now pending. As a sign of this, an X framed by corner brackets appears across from INDEX3 in the CONTROL column.

Then you click on Apply, and the privilege is revoked. As a sign of this, the corner brackets disappear, leaving the X unframed.

Example 2

You want to grant the DELETE privilege on VIEW4. So, on the View page, you select VIEW4 in the View column and specify Yes in the DELETE box. Then you determine that it would be better to grant the DELETE privilege to a group rather than to individual users. So you override the Yes, by selecting VIEW4 again and specifying No in the DELETE box. As a sign that this override is pending, an X framed by corner brackets appears in the DELETE column across from VIEW4.

Example 3

On the View page, you click on the Add View push button. Then, on the Add View window, you select the schema that contains VIEW5, select VIEW5, and click on Add and Close. Consequently, VIEW5 appears in the View column; and unframed Xs appear across from it in the view privileges columns. These unframed Xs mean that the selected user doesn't have any privileges on VIEW5.

Add View

Click on this push button to open the Add View window, so that you can select views that you want to grant privileges on. When you click on Apply on this window, the selected views are added to the View box on the View page.

Grant All

On the Schema page, click on this push button to open the Select Grant Option window, so that you can grant all privileges on the selected schemas either with or without the Grant option (the right to grant the privileges to other users and to groups).

On the Table and View pages, click on this push button to grant all privileges on the selected tables or views, and to provide the Grant option with all privileges except CONTROL.

On the Index page, click on this push button to grant the CONTROL privilege on the selected indexes.

Revoke All

Click on this push button to:

1. Revoke all privileges on all objects that are currently selected.

2. Cancel all requests for privileges on objects that are currently selected.

   Example

   Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Change User notebook, you decide it would be better to grant only the SELECT and INSERT privileges on this table. To do this, you could:

   1. Select TABLE1 in the Table column.

   2. Click on Revoke All to reset allPrivileges boxes to No.

   3. Use the SELECT and INSERT boxes to grant the SELECT and INSERT privileges.

SELECT

This box indicates whether the selected user holds the SELECT privilege, with the Grant option, on the view or views selected in the View column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the SELECT privilege without the Grant option (the right to grant the SELECT privilege on the selected view to other users and to groups). If the user already has the SELECT privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the SELECT privilege.

Grant

Grants the SELECT privilege with the Grant option. If the user already has SELECT without the option, Grant provides the option.

The SELECT privilege allows a user to retrieve rows from a view and to create views on a view.

If you select multiple views for which you specified different settings (for example, Yes for one view andNo for the others), the SELECT box is blank. But the settings that you specified remain in effect.

INSERT

This box indicates whether the selected user holds the INSERT privilege, with the Grant option, on the view or views selected in the View column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the INSERT privilege without the Grant option (the right to grant the INSERT privilege on the selected view to other users and to groups). If the user already has the INSERT privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the INSERT privilege.

Grant

Grants the INSERT privilege with the Grant option. If the user already has INSERT without the option, Grant provides the option.

The INSERT privilege allows a user to insert rows into an updatable view.

If you select multiple views for which you specified different settings (for example, Yes for one view andNo for the others), the INSERT box is blank. But the settings that you specified remain in effect.

UPDATE

This box indicates whether the selected user holds the UPDATE privilege, with the Grant option, on the view or views selected in the View column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the UPDATE privilege without the Grant option (the right to grant the UPDATE privilege on the selected view to other users and to groups). If the user already has the UPDATE privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the UPDATE privilege.

Grant

Grants the UPDATE privilege with the Grant option. If the user already has UPDATE without the option, Grant provides the option.

The UPDATE privilege allows a user to update rows in an updatable view.

If you select multiple views for which you specified different settings (for example, Yes for one view andNo for the others), the UPDATE box is blank. But the settings that you specified remain in effect.

DELETE

This box indicates whether the selected user holds the DELETE privilege, with the Grant option, on the view or views selected in the View column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the DELETE privilege without the Grant option (the right to grant the DELETE privilege on the selected view to other users and to groups). If the user already has the DELETE privilege with the Grant option, Yes revokes the privilege and re-grants it without the option.

No

Revokes the DELETE privilege.

Grant

Grants the DELETE privilege with the Grant option. If the user already has DELETE without the option, Grant provides the option.

The DELETE privilege allows a user to delete rows from an updatable view.

If you select multiple views for which you specified different settings (for example, Yes for one view andNo for the others), the DELETE box is blank. But the settings that you specified remain in effect.

CONTROL

This box indicates whether the selected user holds the CONTROL privilege on the view or views selected in the View column. To change the box's setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the CONTROL privilege.

No

Revokes the CONTROL privilege.

With the CONTROL privilege on a view, a user:

• Acquires all other privileges on the view -- SELECT, INSERT, UPDATE, and DELETE

• Can grant these other privileges to other users of the view

• Can drop the view

If you select multiple views for which you specified different settings (for example, Yes for one view andNo for the others), the CONTROL box is blank. But the settings that you specified remain in effect.