Groups of Users

From InterBase

Go Up to Database User Management

InterBase implements features for assigning SQL privileges to groups of users. SQL roles are implemented on a per-database basis. UNIX groups are implemented on a server-wide basis, using the UNIX group mechanism.

SQL Roles

InterBase supports SQL group-level security as described in the ISO-ANSI Working Draft for Database Language. For syntax of SQL ROLE, see Language Reference Guide and Data Definition Guide.

Implementing roles is a four-step process:

1. Declare the role with CREATE ROLE.
2. Assign privileges on specific tables and columns to the role using the GRANT statement.
GRANT UPDATE ON table1 TO sales;
3. Grant the role to users, again with the GRANT statement.
GRANT sales TO user1, user2, user3;
4. Finally, to acquire the privileges assigned to a role, users must specify the role when connecting to a database.
CONNECT 'foo.ib' USER 'user1' PASSWORD 'peanuts' ROLE sales;

User1 now has update privileges on TABLE1 for the duration of the connection.

A user can belong to only one role per connection to the database and cannot change role while connected. To change role, the user must disconnect and reconnect, specifying a different role name.

You can adopt a role when connecting to a database by any one of the following means:

  • To specify a role when attaching to a database through IBConsole isql, display the Database Connect dialog and type a rolename in the Role field.
  • To specify a role programmatically upon connection using the InterBase API, use the dpb parameter isc_dpb_sql_role_name. See the API Guide.
  • To specify a role for a connection made by an embedded SQL application or isql session, use the ROLE <rolename> clause of the CONNECT statement. See the statement reference for CONNECT in the Language Reference Guide.
Applications using BDE version 5.02 or later, including Delphi, JBuilder, and C++Builder, have a property by which they can specify a role name. Also, the ODBC driver that currently ships with InterBase also recognizes roles.

UNIX Groups

Operating system-level groups are implicit in InterBase security on UNIX, similarly to the way UNIX users automatically supplement the users in the InterBase security database. For full description of usage and syntax of using UNIX groups with InterBase security, see Language Reference Guide and Data Definition Guide.

Integration of UNIX groups with database security is not a SQL standard feature.

See Also

Advance To: