Add group -- Fields and controls

Database page

Schema page

Table page

Index page

View page

Table Space page

Database page

Group

Choose the appropriate authorities

OK

Show SQL

Apply

Reset

Cancel

Group

Use this box to specify the authorization name under which you want a group to use the database that you selected. Use one of these methods:

• Click on the down arrow to display a list of authorization names and select one.
• Type the authorization name in the box.

Important

You can specify an authorization name only for a group that currently has no authorities on the selected database and no privileges on the database's objects.

Choose the appropriate authorities

Under Choose the appropriate authorities to grant to the selected group, indicate which database authorities you want to grant. You can grant 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:

  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, each member of a group can create tables in a schema that he or she owns. To enable the members 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 a 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:
  • All the authorities listed above
  • All privileges on all objects in the database
  • The right to grant these privileges to other users

Schema page

Schema

Add Schema

Grant All

Revoke All

CREATEIN

DROPIN

ALTERIN

OK

Show SQL

Apply

Reset

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 privileges on. To add 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 indicating either that the group has no privileges on the schemas, or that grants of the privileges are pending, or that grants have been made. Symbols for pending grants reflect changes that you make when you use the Privileges boxes or the Grant All or Revoke All push buttons.

Add Schema

Click on this push button to open the Add Schema window, so that you can select schemas that you want to grant privileges on. When you click on Apply in 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 groups and users).

On the Table and View pages, click on this push button to grant all privileges on the selected tables and 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 cancel all requests for privileges on the selected schemas, tables, indexes, or views.

Example

Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Add Group 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 all Privileges boxes to No
3. Use the SELECT and INSERT boxes to request the SELECT and INSERT privileges

CREATEIN

This box displays a setting that indicates whether the group referenced on the Database page has the CREATEIN privilege on schemas selected in the Schema column. To change the 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 to other groups and users).

No

Initially means that the group referenced on the Database page doesn't have the CREATEIN privilege on the selected schema or schemas. To cancel a request for the CREATEIN privilege on a schema, select the schema and change the setting from Yes or Grant to No.

Grant

Grants the CREATEIN privilege with the Grant 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 and No for the others), the CREATEIN box is blank. But the settings that you specified remain in effect.

DROPIN

This box displays a setting that indicates whether the group referenced on the Database page has the DROPIN privilege on schemas selected in the Schema column. To change the 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 to other groups and users).

No

Initially means that the group referenced on the Database page doesn't have the DROPIN privilege on the selected schema or schemas. To cancel a request for the DROPIN privilege on a schema, select the schema and change the setting from Yes or Grant to No.

Grant

Grants the DROPIN privilege with the Grant 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 and No for the others), the DROPIN box is blank. But the settings that you specified remain in effect.

ALTERIN

This box displays a setting that indicates whether the group referenced on the Database page has the ALTERIN privilege on schemas selected in the Schema column. To change the 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 to other groups and users).

No

Initially means that the group referenced on the Database page doesn't have the ALTERIN privilege on the selected schema or schemas. To cancel a request for the ALTERIN privilege on a schema, select the schema and change the setting from Yes or Grant to No.

Grant

Grants the ALTERIN privilege with the Grant 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 and No for the others), the ALTERIN box is blank. But the settings that you specified remain in effect.

Table page

Table

Add Table

Grant All

Revoke All

SELECT

INSERT

UPDATE

DELETE

CONTROL

ALTER

INDEX

REFERENCES

OK

Show SQL

Apply

Reset

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 privileges on. To add 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 indicating either that the group has no privileges on the tables, or that grants of the privileges are pending, or that grants 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.

Add Table

Click on this push button to open the Add Table window, so that you can select tables that you want to grant privileges on. When you click on Apply in 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 groups and users).

On the Table and View pages, click on this push button to grant all privileges on the selected tables and 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 cancel all requests for privileges on the selected schemas, tables, indexes, or views.

Example

Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Add Group 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 all Privileges boxes to No
3. Use the SELECT and INSERT boxes to request the SELECT and INSERT privileges

SELECT

This box displays a setting that indicates whether the group referenced on the Database page has the SELECT privilege on tables selected in the Table column. To change the 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 groups and users).

No

Initially means that the group referenced on the Database page doesn't have the SELECT privilege on the selected table or tables. To cancel a request for the SELECT privilege on a table, select the table and change the setting from Yes or Grant to No.

Grant

Grants the SELECT privilege with the Grant 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 and No for the others), the SELECT box is blank. But the settings that you specified remain in effect.

INSERT

This box displays a setting that indicates whether the group referenced on the Database page has the INSERT privilege on tables selected in the Table column. To change the 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 groups and users).

No

Initially means that the group referenced on the Database page doesn't have the INSERT privilege on the selected table or tables. To cancel a request for the INSERT privilege on a table, select the table and change the setting from Yes or Grant to No.

Grant

Grants the INSERT privilege with the Grant 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 and No for the others), the INSERT box is blank. But the settings that you specified remain in effect.

UPDATE

This box displays a setting that indicates whether the group referenced on the Database page has the UPDATE privilege on tables selected in the Table column. To change the 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 groups and users).

No

Initially means that the group referenced on the Database page doesn't have the UPDATE privilege on the selected table or tables. To cancel a request for the UPDATE privilege on a table, select the table and change the setting from Yes or Grant to No.

Grant

Grants the UPDATE privilege with the Grant 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 and No for the others), the UPDATE box is blank. But the settings that you specified remain in effect.

DELETE

This box displays a setting that indicates whether the group referenced on the Database page has the DELETE privilege on tables selected in the Table column. To change the 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 groups and users).

No

Initially means that the group referenced on the Database page doesn't have the DELETE privilege on the selected table or tables. To cancel a request for the DELETE privilege on a table, select the table and change the setting from Yes or Grant to No.

Grant

Grants the DELETE privilege with the Grant 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 and No for the others), the DELETE box is blank. But the settings that you specified remain in effect.

CONTROL

This box displays a setting that indicates whether the group referenced on the Database page has the CONTROL privilege on tables selected in the Table column. To change the setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the CONTROL privilege.

No

Initially means that the group referenced on the Database page doesn't have the CONTROL privilege on the selected table or tables. To cancel a request for the CONTROL privilege on a table, select the table and change the setting from Yes or Grant to No.

With the CONTROL privilege on a table, a user:

• Acquires all other privileges on the table as well -- 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 and No for the others), the CONTROL box is blank. But the settings that you specified remain in effect.

ALTER

This box displays a setting that indicates whether the group referenced on the Database page has the ALTER privilege on tables selected in the Table column. To change the 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 groups and users).

No

Initially means that the group referenced on the Database page doesn't have the ALTER privilege on the selected table or tables. To cancel a request for the ALTER privilege on a table, select the table and change the setting from Yes or Grant to No.

Grant

Grants the ALTER privilege with the Grant 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 the table

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

INDEX

This box displays a setting that indicates whether the group referenced on the Database page has the INDEX privilege on tables selected in the Table column. To change the 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 groups and users).

No

Initially means that the group referenced on the Database page doesn't have the INDEX privilege on the selected table or tables. To cancel a request for the INDEX privilege on a table, select the table and change the setting from Yes or Grant to No.

Grant

Grants the INDEX privilege with the Grant 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 and No for the others), the INDEX box is blank. But the settings that you specified remain in effect.

REFERENCES

This box displays a setting that indicates whether the group referenced on the Database page has the REFERENCES privilege on tables selected in the Table column. To change the 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 groups and users).

No

Initially means that the group referenced on the Database page doesn't have the REFERENCES privilege on the selected table or tables. To cancel a request for the REFERENCES privilege on a table, select the table and change the setting from Yes or Grant to No.

Grant

Grants the REFERENCES privilege with the Grant 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 and No for the others), the REFERENCES box is blank. But the settings that you specified remain in effect.

Index page

Index

Add Index

Grant All

Revoke All

CONTROL

OK

Show SQL

Apply

Reset

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. To add 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 indicating either that the group doesn't have the CONTROL privilege on the indexes, or that grants of the privileges are pending, or that grants 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.

Add Index

Click on this push button to open the Add Index window, so that you can select indexes on which you want to grant the CONTROL privilege. When you click on Apply in 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 groups and users).

On the Table and View pages, click on this push button to grant all privileges on the selected tables and 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 cancel all requests for privileges on the selected schemas, tables, indexes, or views.

Example

Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Add Group 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 all Privileges boxes to No
3. Use the SELECT and INSERT boxes to request the SELECT and INSERT privileges

CONTROL

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

Yes

Grants the CONTROL privilege.

No

Initially means that the group referenced on the Database page doesn't have the CONTROL privilege on the selected index or indexes. To cancel a request for the CONTROL privilege on an index, select the index and change the setting from Yes or Grant to No.

Grant

Grants the CONTROL privilege with the Grant option.

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 and to groups.

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

View page

View

Add View

Grant All

Revoke All

SELECT

INSERT

UPDATE

DELETE

CONTROL

OK

Show SQL

Apply

Reset

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 privileges on. To add 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 indicating either that the group has no privileges on the views, or that grants of the privileges are pending, or that grants 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.

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 in 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 groups and users).

On the Table and View pages, click on this push button to grant all privileges on the selected tables and 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 cancel all requests for privileges on the selected schemas, tables, indexes, or views.

Example

Suppose that on the Table page, you use the Grant All push button to request all privileges on TABLE1. Then, before closing the Add Group 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 all Privileges boxes to No
3. Use the SELECT and INSERT boxes to request the SELECT and INSERT privileges

SELECT

This box displays a setting that indicates whether the group referenced on the Database page has the SELECT privilege on views selected in the View column. To change the 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 groups and users).

No

Initially means that the group referenced on the Database page doesn't have the SELECT privilege on the selected view or views. To cancel a request for the SELECT privilege on a view, select the view and change the setting from Yes or Grant to No.

Grant

Grants the SELECT privilege with the Grant option.

The SELECT privilege allows a user can 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 and No for the others), the SELECT box is blank. But the settings that you specified remain in effect.

INSERT

This box displays a setting that indicates whether the group referenced on the Database page has the INSERT privilege on views selected in the View column. To change the 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 groups and users).

No

Initially means that the group referenced on the Database page doesn't have the INSERT privilege on the selected view or views. To cancel a request for the INSERT privilege on a view, select the view and change the setting from Yes or Grant to No.

Grant

Grants the INSERT privilege with the Grant 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 and No for the others), the INSERT box is blank. But the settings that you specified remain in effect.

UPDATE

This box displays a setting that indicates whether the group referenced on the Database page has the UPDATE privilege on views selected in the View column. To change the 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 groups and users).

No

Initially means that the group referenced on the Database page doesn't have the UPDATE privilege on the selected view or views. To cancel a request for the UPDATE privilege on a view, select the view and change the setting from Yes or Grant to No.

Grant

Grants the UPDATE privilege with the Grant 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 and No for the others), the UPDATE box is blank. But the settings that you specified remain in effect.

DELETE

This box displays a setting that indicates whether the group referenced on the Database page has the DELETE privilege on views selected in the View column. To change the 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 groups and users).

No

Initially means that the group referenced on the Database page doesn't have the DELETE privilege on the selected view or views. To cancel a request for the DELETE privilege on a view, select the view and change the setting from Yes or Grant to No.

Grant

Grants the DELETE privilege with the Grant 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 and No for the others), the DELETE box is blank. But the settings that you specified remain in effect.

CONTROL

This box displays a setting that indicates whether the group referenced on the Database page has the CONTROL privilege on views selected in the View column. To change the setting, click on the down arrow and select the setting that you want. You can select:

Yes

Grants the CONTROL privilege.

No

Initially means that the group referenced on the Database page doesn't have the CONTROL privilege on the selected view or views. To cancel a request for the CONTROL privilege on a view, select the view and change the setting from Yes or Grant to No.

With the CONTROL privilege on a view, a user:

• Acquires all other privileges on the view as well -- 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 and No for the others), the CONTROL box is blank. But the settings that you specified remain in effect.