Managing groups

User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 21. Managing User IDs and Permissions

Managing groups

Once you understand how to manage permissions for individual users (as described in the previous section) working with groups is straightforward. A group is identified by a user ID, just like a single user, but this user ID is granted the permission to have members.

DBA, RESOURCE, and GROUP permissions

When permissions on tables, views, and procedures are granted to or revoked from a group, all members of the group inherit those changes. The DBA, RESOURCE, and GROUP permissions are not inherited: they must be assigned individually to each individual user ID requiring them.

A group is simply a user ID with special permissions. Granting permissions to a group and revoking permissions from a group are done in exactly the same manner as any other user, using the commands described in Managing individual user IDs and permissions.

A group can also be a member of a group. A hierarchy of groups may be constructed, each inheriting permissions from its parent group.

A user ID may be granted membership in more than one group, so the user-to-group relationship is many-to-many.

The ability to create a group without a password enables you to prevent anybody from signing on using the group user ID. This security feature is discussed in Groups without passwords.

Creating groups

To create a group with a name and password:

Connect to the database as a user with DBA authority.
Create the group's user ID just as you would any other user ID, using the following SQL statement:
```
GRANT CONNECT
TO personnel
IDENTIFIED BY group_password
```
Give the personnel user ID the permission to have members, with the following SQL statement:
```
GRANT GROUP TO personnel
```

The GROUP permission, which gives the user ID the ability to have members, is not inherited by members of a group. If this were not the case, every user ID would automatically be a group as a consequence of its membership in the special PUBLIC group.

Creating groups in Sybase Central

To create a group in Sybase Central:

Connect to the database.
Click the Users and Groups folder for that database.
Double-click Add Group. A Wizard is displayed, which leads you through the process.

For more information, see the Sybase Central online Help.

Granting group membership to users

Making a user a member of a group is done with the GRANT statement. Membership in a group can be granted only by the DBA. You can grant user M_Haneef membership in a group personnel as follows:

Connect to the database as a user with DBA authority, or as the group user ID personnel.
Grant membership in the group to M_Haneef with the following SQL statement:
```
GRANT MEMBERSHIP 
IN GROUP personnel
TO M_Haneef
```

When a user is assigned membership in a group, they inherit all the permissions on tables, views, and procedures associated with that group.

Adding users to groups in Sybase Central

To add a user to a group in Sybase Central:

Connect to the database.
Double-click the Users and Groups folder for that database, to open it. Groups are displayed in the left panel, and both users and groups are displayed in the right panel.
In the right panel, select the users you want to add to a group, and drag them to the group.

For more information, see the Sybase Central online Help.

Permissions of groups

Permissions may be granted to groups in exactly the same way as to any other user ID. Permissions on tables, views, and procedures are inherited by members of the group, including other groups and their members. There are some complexities to group permissions that database administrators need to keep in mind.

Notes

The DBA, RESOURCE, and GROUP permissions are not inherited by the members of a group. Even if the personnel user ID is granted RESOURCE permissions, the members of personnel do not have RESOURCE permissions.

Ownership of database objects is associated with a single user ID and is not inherited by group members. If the user ID personnel creates a table, then the personnel user ID is the owner of that table and has the authority to make any changes to the table, as well as to grant privileges concerning the table to other users. Other user IDs who are members of personnel are not the owners of this table, and do not have these rights. If, however, SELECT authority is explicitly granted to the personnel user ID by the DBA or by the personnel user ID itself, all group members do have select access to the table. In other words, only granted permissions are inherited.

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 named employees is owned by the user ID personnel, 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. However, 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.

Special groups

When a database is created, two groups are also automatically created. These are SYS and PUBLIC. Neither of these groups has passwords, so it is not possible to connect to the database as either SYS or as PUBLIC. The two groups serve important functions in the database.

The SYS group

The SYS group is owner of the system tables and views for the database, which contain the full description of database structure, including all database objects and all user IDs.

For a description of the system tables and views, together with a description of access to the tables, see the chapters System Tables, and also System Views.

The PUBLIC group

When a database is created, the PUBLIC group is automatically created, with CONNECT permissions to the database and SELECT permission on the system tables.

The PUBLIC group is a member of the SYS group, and has read access for some of the system tables and views, so that any user of the database can find out information about the database schema. If you wish to restrict this access, you can REVOKE PUBLIC's membership in the SYS group.

Any new user ID is automatically a member of the PUBLIC group and inherits any permissions specifically granted to that group by the DBA. You can also REVOKE membership in PUBLIC for users if you wish.