Referring to tables owned by groups

Groups are used for finding tables and procedures in the database. For example, the query

SELECT * FROM SYSGROUPS

will always find the table SYSGROUPS, because all users belong to the PUBLIC group and PUBLIC belongs to the SYS group which owns the SYSGROUPS table. (The SYSGROUPS table contains a list of group_name, member_name pairs representing the group memberships in your database.)

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”.