Filter
Top Products
Managing groups
366
If a table employees is owned by the personnel user ID, and if M_Haneef is a
member of the
personnel group, then M_Haneef can refer to the employees table
simply as
employees in SQL statements. Users who are not members of the
personnel group need to use the qualified name personnel.employees.
Creating a group to
own the tables
It is advisable that you create a group whose only purpose is to own the tables.
Do not grant any permissions to this group, but make all users members of the
group. This allows everyone to access the tables without qualifying names.
You can then create permission groups and grant users membership in these
permission groups as warranted. For an example of this, see the section
“Database object names and prefixes”.
Groups without passwords
Users connected to a group's user ID have certain permissions. This user ID can
grant and revoke membership in the group. Also, this user would have
ownership permissions over any tables in the database created in the name of
the group's user ID.
It is possible to set up a database so that all handling of groups and their
database objects is done by the DBA, rather than permitting other user IDs to
make changes to group membership.
This is done by disallowing connection as the group's user ID when creating
the group. To do this, the GRANT CONNECT statement is typed without a
password. Thus:
GRANT CONNECT
TO personnel
creates a user ID personnel. This user ID can be granted group permissions, and
other user IDs can be granted membership in the group, inheriting any
permissions that have been given to
personnel, but nobody can connect to the
database using the
personnel user ID, because it has no valid password.
The user ID
personnel can be an owner of database objects, even though no
user can connect to the database using this user ID. The CREATE TABLE
statement, CREATE PROCEDURE statement, and CREATE VIEW statement
all allow the owner of the object to be specified as a user other than that
executing the statement. This assignment of ownership can be carried out only
by the DBA.