SQL Roles: Users and Security in InterBase

InterBase Security Dialog

There are two main windows dealing with user security. The first form, InterBase Security, presents a list of current users in the security database, analogous to GSEC's DISPLAY command. This form has buttons to add, modify, or delete users.

User Configuration Dialog

The second Server Manager window is the User Configuration Dialog. This dialog will display when you perform an "Add User" or "Modify User". The dialog groups all the required fields separate from the optional information. When adding a new user the User Configuration Dialog will display with all its fields empty. For a new user you must enter a username and password. Additional user information can be entered as well.

How to Assign Privileges to Users

As previously stated, only SYSDBA and the owner of the object initially have privileges to access the object. If SQL privileges stopped here, they would be useless. There needs to be a way to allow additional users to access the database objects. With SQL privileges this is done with the GRANT and REVOKE statements. The GRANT statement is used to assign privileges on tables or views to authorized users. Conversely, the REVOKE statement is used to take away privileges that were previously assigned to a user with the GRANT statement.

GRANT Statement

The GRANT statement assigns a new privilege or privileges on a database object to a user. The types of privileges that can be assigned to a user are listed in the following table.

Granting privileges to PUBLIC

SQL defines the special user PUBLIC to represent all uses. If an operation is granted to PUBLIC, then any valid user can perform the operation on the specified database object. Care should be taken when granting privileges to PUBLIC. All current and future users will be able to perform the operations that have been granted to PUBLIC.

Granting privileges to execute a Stored Procedure

To use a stored procedure a user must have the privilege to execute that stored procedure. There is a form of the GRANT statement that assigns the privilege to execute a stored procedure. The form of the statement is:

GRANT EXECUTE ON PROCEDURE procname to user;

Additionally, the stored procedure must have privileges on all relevant tables for all operations that it performs. The stored procedure must have been given the privilege to access all the tables that it uses. An example assigning the select privilege to a stored procedure would take this form:

GRANT SELECT on tablename to PROCEDURE procname;

Granting access to Views

For the most part, assigning privileges on views is the same as for tables. There are a few differences that are very important and need pointing out.

First, a view is a virtual table. A view is a query against base tables. The data that is queried from the table is NOT stored, only the query definition is stored. The data is retrieved from the base tables every time the view is queried. So, for any INSERT, UPDATE, or DELETE statement the operation is performed on the base tables. It is meaningful to grant INSERT, UPDATE, or DELETE privileges to a view only if that view is updatable. An updatable view is one that generally does not involve joins or aggregate functions. For a more thorough explanation of views see the InterBase Data Definition Guide's chapter Working with Views.

While it is possible to grant INSERT, UPDATE, or DELETE privileges to a read-only view without receiving an error, any actual write operation attempted through the view will fail because it is read-only. SELECT privileges, however, can be granted to a read-only view to control which users have read access on the view.

Granting privileges to execute a Stored Procedure

Views can also be used to control access to base table data. If a user is only required to have access to a subset of rows for a table a view can be defined to query that subset of rows from the base table. The user can then be given privileges only on the view, not the base table. This allows the user privileges to the subset of rows that are required, but no privileges to the other rows in the table.

For example, imagine a table called TEST_SCORES that contains the columns, LASTNAME, FIRSTNAME, TESTNAME, SCORE. This table contains information that can be viewed by anyone, LASTNAME, FIRSTNAME and TESTNAME. This information is made available so that everyone can query to see who has taken which test. There is also information that should not be made available to all users, SCORE. There is a need to allow everyone access to a subset of the columns in the TEST_SCORES table, while only allowing access to the SCORE column to an exclusive set of users. This can be accomplished by creating a view that queries the rows available to all users and only granting everyone privileges to the view and not the base table:

CREATE VIEW TESTTAKERS AS
  SELECT LASTNAME, FIRSTNAME, TESTNAME FROM TEST_SCORES;

GRANT SELECT ON TESTTAKERS TO PUBLIC;
GRANT ALL ON TEST_SCORES TO INSTRUCTOR;

Examples using Grant Statement

This example grants ALL privileges for the table TEST_SCORES to the user JJOHNSON. The ALL privilege grants JJOHNSON the right to insert, update, select, delete from this table:

GRANT ALL ON TEST_SCORES TO JJOHNSON;

This example will grant the ALL privilege to the special user PUBLIC. The PUBLIC user will grant all existing and future users ALL privileges on table TEST_SCORES:

GRANT ALL ON TEST_SCORES TO PUBLIC;

The following example will grant ALL privileges to a stored procedure. This will give the procedure rights to access the TEST_SCORES base table. For users to be able to use the stored procedure they must be granted EXECUTE privileges to the stored procedure (see later example).

GRANT ALL ON TEST_SCORES TO PROCEDURE GET_PASSING_SCORES;

This example will grant EXECUTE privileges to the special user PUBLIC. This will allow all current and future users to execute this procedure. The procedure must be given the privileges to access any base tables that it requires.

GRANT EXECUTE ON PROCEDURE GET_PASSING_SCORES TO PUBLIC;

This example will grant INSERT privileges on table TEST_SCORES to the user JJOHNSON. JJOHNSON will be able to add new rows to the table, but will not be able to query the table.

GRANT INSERT ON TEST_SCORES TO JJOHNSON;

This example will grant UPDATE privileges on a subset of columns to the user JJOHNSON. JJOHNSON will be able to update only those rows explicitly listed in the grant statement.

GRANT UPDATE (FIRST_NAME, LAST_NAME) ON TEST_SCORES TO JJOHNSON;

Granting Right to Grant to Others

Initially, only the SYSDBA and owner can grant privileges to other users. The grant statement has a clause that allows the SYSDBA or owner to grant the right for a user to grant privileges to other users. The WITH GRANT OPTION is used to allow other users the right to grant privileges to other users. Because the grant statement works on individual tables, the WITH GRANT OPTION can only be used on one table at a time. A grant statement with the WITH GRANT OPTION clause will have to be used for every table that the SYSDBA or owner wish to allow others to grant privileges for.

Users who have be given the right to grant privileges to other users can only grant the privileges that they have been granted themselves. For instance, a user who has been granted select privilege can not grant the update privilege to other uses. That user can only grant the select privilege to other users.

The following example grant's select privilege on table employee to the user BJONES and allows BJONES to grant the select privilege for table employee to other users:

Grant SELECT on employee to BJONES WITH GRANT OPTION;

Once a user has been given the right to grant privileges, that user can give the right to grant privileges to other users. This can cause a security breach unless controlled. Every user that has been given the right to grant privileges can also pass that right on to other users. There is no way to give a user the right to grant privileges to others, but not be able to pass on that right. Because of this fact, caution should be used when using the WITH GRANT OPTION clause.

REVOKE Statement

While the GRANT statement allows privileges to be given to a user, the REVOKE statement allows privileges to be removed. Only privileges that have been previously granted can be revoked. The basic syntax of the revoke statement is of the following form:

Revoke <operation list> on tablename from user;

Only the user who originally grants a privilege can revoke that privilege. For example, if userA grants a privilege to userB, then userC does not have the right to revoke that privilege from userB. Only userA can revoke that privilege. The SYSDBA user, as previously stated, always has the right to grant and/or revoke any privilege from any user.

The ALL privilege combines the SELECT, INSERT, UPDATE, and DELETE privileges. The ALL privilege can be used as a shorthanded way of revoking multiple privileges from a user. To use the ALL privilege in a revoke statement the targeted user does not have to have all the privileges that the ALL privilege encompasses. When a user revokes the ALL privilege it will revoke any subset of the ALL privilege. For example, assume a user has insert and select privileges on a table. If SYSDBA executes a REVOKE ALL statement, the user will no longer have any privileges to that table.

As stated before, the WITH GRANT OPTION clause allows the right to grant privileges to be passed to other users. Also, users who have been given the right to grant privileges can pass on that right to other users. This can lead to a hole in database security. The right to grant may start out with only a few users having the privilege, but quickly grow to an enormous number of users who have the right to grant privileges. The revoke command can be used to stop this chain of users. Revoking privileges from a user who was granted privileges with the WITH GRANT OPTION clause also revokes privileges from all additional users that the user has granted privileges to others. Here is a sequence of events that demonstrate this scenario:

1. Grant privileges to USERA WITH GRANT OPTION
2. USERA grants privileges to USERB
3. Revoke privileges from USERA
4. Neither USERA or USERB will have privileges

Here is the sequence of GRANT and REVOKE statements that demonstrate this scenario:

Connect to database as SYSDBA:
GRANT ALL ON TEST_SOURCE TO USERA WITH GRANT OPTION;
Connect to database As USERA:
GRANT ALL ON TEST_SOURCE TO USERB;
Connect to database as SYSDBA:
REVOKE ALL ON TEST_SOURCE FROM USERA;
Connect to database as USERA:
SELECT * FROM TEST_SOURCE;
Statement failed, SQLCODE=-551
no permission for read/select access to table TEST_SOURCE
Connect to database as USERB:
SELECT * FROM TEST_SOURCE;
Statement failed, SQLCODE=-551
no permission for read/select access to table TEST_SOURCE

Revoking privileges from PUBLIC

If privileges are assigned to PUBLIC, those privileges cannot be revoked from a specific user, they must revoked from the PUBLIC user. The PUBLIC special user is used to allow any user to access a table. It does not, however, act as a group of users. Once the PUBLIC user has been assigned privileges, those privileges can only be revoked from the PUBLIC user as a whole. For example, assume that PUBLIC has been granted the ALL privilege on table TEST_SOURCE. SYSDBA cannot then revoke the ALL privilege specifically from user BJONES. If it is desired to have only a subset of users with privileges then the PUBLIC user cannot be granted rights to the table.

Revoke Examples

This example will revoke the ALL privilege from the special user PUBLIC. This revoke statement will only affect the privileges that were specifically granted to the PUBLIC user. All privileges that were granted to individual users are still assigned.

REVOKE ALL ON TEST_SOURCE FROM PUBLIC;

This example will revoke the INSERT privilege from an individual user, BJONES. Any additional privileges that BJONES has on this table will still be assigned.

REVOKE INSERT ON TEST_SOURCE FROM BJONES;

This example will revoke the EXECUTE privilege from an individual user, BJONES. BJONES will no longer have the right to execute this procedure.

REVOKE EXECUTE ON PROCEDURE GET_PASSING_SCORES FROM BJONES;

Revoking Grant Authority

The REVOKE statement will also allow a user's grant privilege to be revoked. The following example will revoke the user BJONES's authority to grant privileges to other users. BJONES will still have his assigned privileges to that table, but will no longer be able to grant privileges to other users.

REVOKE GRANT OPTION FOR ALL ON TEST_SOURCE FROM BJONES;

You can also revoke grant authority for selective operations. For example, if BJONES had grant authority for the ALL privilege, the REVOKE command can be used to revoke BJONES's grant authority for the INSERT operation:

REVOKE GRANT OPTION FOR INSERT ON TEST_SOURCE FROM BJONES;

BJONES will still have insert privileges into table TEST_SOURCE, the revoke statement just revokes BJONES's right to grant the INSERT privilege to other users.

SQL Security makes it hard to manage users

The SQL security mechanism does not make it easy for the database administrator (DBA) to maintain an existing database either. There is no provision to help the DBA add new users to an existing database. Likewise, there are no provisions to help the DBA modify existing user privileges for a set of users.

For each new user that must be added to an existing system the DBA must reuse the same grant statements that were executed for all other users. This must be done for each table in the database. This administration operation is very repetitive. SQL security has no provision for simplifying the operation. Every time a new user is added the DBA has to go through the same routine to assign privileges to the new user.

The lack of group security means that for any little change to security, privileges for each individual user must be changed to reflect the new privileges. This requires revoking the privileges that are no longer available to users and granting the new privileges to the same users.

SQL Security is not flexible

SQL security does not allow for flexibility of user privileges. It is an all or nothing mechanism. Either you have privileges or you don't. SQL security doesn't have the flexibility to allow changing user privileges without the overhead of managing each individual user change, via a grant or revoke statement. Also, there is no way to assign a set of privileges to a user without assigning them one table at a time.

Roles are an identity

To reiterate, a role is like a template, or set of privileges. The role is first created, then it is granted privileges to database objects. Users are then granted the right to assume a role upon connection. When a connection is attempted the user can specify a role that the user is to assume when connected. When a user assumes a role, that user is given all the privileges that are assigned to that role.

Roles are additive

As stated earlier, roles are an extension of basic SQL security. Privileges assigned when assuming a role are added to the privileges that are granted explicitly to a user. For example, assume the role TEST_ADMIN has been granted the INSERT privilege on table TEST_SCORES. USERA has explicitly been granted the SELECT privilege, and has been granted the right to assume the role TEST_ADMIN. When USERA connects specifying role TEST_ADMIN, USERA will have the accumulated privileges: INSERT and SELECT on table TEST_SCORES.

Which InterBase kits support Roles?

SQL Roles are available with InterBase v5.0. If upgrading from a previous version of InterBase, you must backup your database and restore onto the server which is running InterBase v5.0. It is not enough to move your database to the v5.0 server, you must also backup and restore to enable the database to support SQL Roles.

Here are some general role requirements and conditions:

• A role is bound to the database that it is created in.
• Role names must be unique with respect to other roles and usernames.
• Roles must be assumed at connection time. A user cannot switch roles without disconnecting and reconnecting to the database.
• A database must be backed up and restored with a V5.0 server to use roles. The InterBase V5.0 server can access V4.x databases, but unless the database is restored with a V5.0 server roles cannot be used in that database.

Roles make it easier to manage users

Roles also make database security easier to maintain for an existing database. Since roles extend basic SQL security there is no need to redesign the database's security to use roles. Roles will allow the DBA to keep the already defined privileges intact and build new privileges along side these. There are a couple key areas where Roles really make the DBA's job easier when it comes to maintaining security for an existing database. One, roles allow a new user's privileges to be easily added to the database. Second, Roles implement group level security, which makes the job of modifying existing privileges for a large number of users manageable.

With basic SQL security adding a new user's privileges to a database requires the DBA to grant privileges to the user for each table in the database. This is a very repetitive task, because user privileges fall into categories and the same set of privileges are defined for several users. Roles can act as templates when setting up privileges for new users. A role can be defined in the database for a common set of privileges. When a new user is added the DBA must only grant the new user privileges to assume the role that was defined for the user's required set of privileges.

One common method DBA's use to work around the basic SQL security model's lack of groups is to grant privileges to one generic user and allow all users to connect with this same username. This makes setting up and maintaining privileges easy to manage. The major drawback to this method is that when connected, every user has the same username. There is no way to differentiate between the users, because they are all using the same username. To InterBase, they are all effectively the same user, because the username is the only way to differentiate users. Using roles as templates alleviates this issue. The DBA still only has to setup privileges once, this time for the role instead of the generic user. When users connect they still provide their unique username, but also add the role that they are assuming. This allows InterBase to maintain security privileges, but still differentiate between the individual users. There are only two differences between using roles and using the generic user. One, users must specify the role when connecting. Two, the DBA must grant to all users the right to assume the role.

The second major advantage with using roles is the management of user groups. Roles are defined around the concept of group level security. Roles allow DBAs to manage the privileges for entire groups of users, not one user at a time like the basic SQL security model. Roles allow the DBA to modify privileges at a group level instead of an individual user level. When the DBA modifies the privileges of a role, the effective privileges of all users is modified as well. The privileges are modified for the entire group, everyone who is granted privileges to the role.

For example, suppose a role FULL_ACCESS has been defined with the ALL privilege on table TEST_SCORES.

create table test_scores (i1 integer);
create role FULL_ACCESS;
grant all on test_scores to full_access;
show grant test_scores;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES
  ON TEST_SCORES TO FULL_ACCESS

Users TEST and TEST2 have been granted the privilege to assume that role

grant FULL_ACCESS to TEST;
grant FULL_ACCESS to TEST2;

Users TEST and TEST2 now have the ALL privilege for table TEST_SCORES when they connect assuming the FULL_ACCESS role. If TEST or TEST2 tries to connect to the database without specifying the FULL_ACCESS role they will not have privileges on table TEST_SCORES.

connect tempemployee.gdb user test password test;
Database: tempemployee.gdb, User: test
select * from test_scores;
Statement failed, SQLCODE=-551
no permission for read/select access to table TEST_SCORES

connect tempemployee.gdb user test password test role full_access;
Database: tempemployee.gdb, User: test, Role: full_access
SQL select * from test_scores;

Now, for the DBA to modify the privileges for both TEST and TEST2 all that is required is to modify the role FULL_ACCESS. In this example the INSERT privilege is taken away from the role. TEST and TEST2 can assume the role and have the UPDATE, DELETE, SELECT, and, REFERENCES privileges on table TEST_SCORES.

show grant test_scores;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES
  ON TEST_SCORES TO FULL_ACCESS

revoke insert on test_scores from full_access;

show grant test_scores;
GRANT DELETE, SELECT, UPDATE, REFERENCES ON TEST_SCORES TO FULL_ACCESS

connect tempemployee.gdb user test password test role full_access;
Database: tempemployee.gdb, User: test, Role: full_access
select * from test_scores;
insert into test_scores values (96);
Statement failed, SQLCODE=-551
no permission for insert/write access to table TEST_SCORES

The select, while not returning any rows, was successful. The insert, however, received the no permission error message. While this example demonstrates a group with only 2 users, the same example can easily be extended to incorporate hundreds of users.

Roles are flexible

Roles also add much needed flexibility to the basic SQL security model. To reiterate, the SQL model doesn't have the flexibility to allow changing user privileges without the overhead of managing each individual user change, via a grant or revoke statement. Roles allow a user to change the privileges assigned by assuming a different role when connecting to the database. This allows for a secure database, while still having the flexibility to accommodate a users changing needs. Revisiting the product life cycle example presented earlier will demonstrate how roles add the flexibility required for this scenario. For each role the programmer assumes, there are a predefined set of privileges that are assigned. When the programmer is assuming the marketing role, he should not have any privileges that are granted to a developer. Likewise, when the programmer is working as the developer role, he should not have any of the privileges granted to the QA role. The point is each role has its own predefined set of privileges, and the programmer when assuming that role should only have those privileges granted to that role.

Moving this example into the database realm, there should be three roles defined on the database; Marketing, development, and QA. Each role will have its own set of privileges to the tables in the database. When a user connects to the database assuming one of the roles, that user will only have the privileges granted to that role.

Roles also provide the flexibility, because they work in conjunction with the basic SQL security model. As explained earlier, the total privileges for a user are cumulative:

Total Privileges=Privileges explicitly granted to user +
Privileges granted to role being assumed

Continuing the product life cycle example, assume user BJONES has been granted privileges to assume the QA role. Each month BJONES is responsible for accumulating a count of all the developer source code changes that have occurred. Since BJONES has only been granted privileges to assume the QA role, he cannot access the tables that are used during development. The DBA does not want to give BJONES the privileges to assume the developer role, because the privileges BJONES requires are a subset of the total privileges that have been granted to the developer role. The DBA can explicitly grant privileges on the subset of tables directly to BJONES. When BJONES connects assuming the QA role he will have privileges to access the subset of development tables as well as the tables for QA, because of the conjunctive nature of roles with the basic SQL security model. BJONES has privileges granted to him through the role, plus all privileges explicitly granted to him by the DBA.

Creating a Role

To create a role you must use the CREATE ROLE statement. By default a role has no privileges when it is created. The role must be granted privileges to database objects before it can access them. Any user can create a role, since creating a role assigns no privileges. Also, since roles are defined and stored in a database, you must be connected to a database before you can define a role. Here is an example of creating a role:

CREATE ROLE FULL_ACCESS;

Granting Privileges to a Role

When a role is created it has no privileges to any objects in the database. This follows the SQL security model of providing no access unless explicitly granted. To assign privileges to a role you must use the grant statement. The basic syntax for granting privileges to a role is:

GRANT <priviliges> ON [TABLE] {tablename | viewname} TO rolename;

To grant privileges to a role you must be:

• SYSDBA
• The owner of the table or view
• A user that has been granted the right to assign privileges for this table or view (i.e. WITH GRANT OPTION)

Here are some examples of granting privileges to a role:

GRANT ALL ON TEST_SCORES TO FULL_ACCESS;
GRANT INSERT, SELECT ON TABLE EMPLOYEE TO BJONES;

Granting a Role to a User

After a role has been created and privileges have been assigned to it, you must grant the role to users. A user can only assume a role if that user has been granted the privilege to use it. When a user connects to a database and specifies a role, that user acquires all the privileges that have been granted to the role.

The syntax for granting a role to a user is:

GRANT {rolename [, rolename ...]} TO {PUBLIC| {[USER] username
[, [USER] username ...]} } [WITH ADMIN OPTION];

The WITH ADMIN OPTION clause permits users to grant the role to other users. If it is added to the end of the grant role statement then that user that has just been granted the role can grant the role to other users. For example, if USERA is granted the role DEVELOPER with the WITH ADMIN OPTION clause, then USERA can grant the DEVELOPER role to other users. The following example creates the role FULL_ACCESS, grants ALL privileges on the TEST_SCORES table to this role, and grants the role to user BJONES.

CREATE ROLE FULL_ACCESS;
GRANT ALL ON TEST_SCORES TO FULL_ACCESS;
GRANT FULL_ACCESS TO BJONES;

BJones is connecting to the database without specifying the role FULL_ACCESS. BJones is denied access to the table TEST_SCORES, because only the role was granted privileges on table TEST_SCORES.

connect tempemployee.gdb user bjones password bjones;
Database: tempemployee.gdb, User: bjones
select * from test_scores;
Statement failed, SQLCODE=-551
no permission for read/select access to table TEST_SCORES

BJones now connects and specifies the FULL_ACCESS role. Since the role was granted ALL privileges on table TEST_SCORES, Bjones is allowed to select from the table.

connect tempemployee.gdb user bjones password bjones role full_access;
Database: tempemployee.gdb, User: bjones, Role: full_access
select * from test_scores;

Assuming a Role

We have already touched on a user assuming a role. When a user connects to a database he can assume a role by specifying the role in the connection string. This is the only place where a user can specify a role to assume. InterBase does not provide a way for a user to switch roles while staying connected to a database. The user must disconnect and reconnect with the new role.

Here is an example of the user BJONES connecting to a database and assuming the role FULL_ACCESS. In this example the user is using ISQL, InterBase's interactive SQL processing utility.

connect tempemployee.gdb user bjones password bjones role full_access;
Database: tempemployee.gdb, User: bjones, Role: full_access

Here is an example of the same user connecting, but using DSQL instead if the ISQL utility.

File: sampleapi.c

#include <string.h>
#include <stdio.h>
#include "ibase.h"

int main()
{ ISC_STATUS isc_status[20];
  isc_db_handle db_handle;
  char *dpb;
  short dpb_length;
  int i;

  /* clear database handle */
  db_handle=0L;

  /* add dpb version to dpb */
  dpb=(char *)0;
  dpb_length=0;

  /* add user, password, and role to dpb */
  isc_expand_dpb(&dpb, &dpb_length,
    isc_dpb_user_name, "bjones",
    isc_dpb_password, "bjones",
    isc_dpb_sql_role_name, "full_access",
    NULL);

  /* attach to database */
  isc_attach_database(isc_status, 0, "testdb.gdb", &db_handle,
    dpb_length, dpb);
}

Revoking Privileges from a Role

For roles that have been granted privileges, you can revoke those privileges using the REVOKE command. If privileges are revoked from a role, all users who have been granted the role will no longer have the privileges that are being revoked. The syntax for revoking privileges from a role are:

REVOKE <priviliges> ON [TABLE] tablename FROM rolename;

Here is an example that revokes the INSERT privilege on table TEST_SCORES from the role FULL_ACCESS.

REVOKE INSERT ON TEST_SCORES FROM FULL_ACCESS;

Revoking a Role from a User

Just as a role can be granted to a user, a role can also be revoked from a user. When a role is revoked from a user it means that user can no longer connect and specify that role. That user no longer has the privileges that were assigned to the role. The syntax for revoking a role from a user is:

REVOKE <rolename> FROM username;

In this example the user Bjones is being revoked from the role FULL_ACCESS. BJones will no longer be able to connect specifying the role FULL_ACCESS.

REVOKE FULL_ACCESS FROM BJONES;

Dropping a Role

When a role is no longer needed it can be dropped from the database. A role can be dropped by either SYSDBA or the creator of the role. When a role is dropped all the privileges that the role has been granted are removed from the database. The syntax for dropping a role is:

DROP ROLE rolename;

The following example drops the role FULL_ACCESS.

DROP ROLE FULL_ACCESS;

InterBase provides three interfaces to roles

InterBase allows client applications and development tools to use SQL Roles. InterBase surfaces roles via the native API, embedded SQL, and the interactive query tools. All tools that are built using one of these access methods will be able to support roles.

The InterBase native API is a set of functions that allow developers to programmatically construct and send SQL statements to the InterBase engine and receive results back. All database work can be performed through calls to the API. The InterBase API provides the most flexibility and power of any access method. This flexibility and power comes at the expense of using a lower level interface. The API relies on the developer to manage more of the burden when communicating with the InterBase engine. The developer is required to allocate and populate underlying data structures, which are hidden in the other higher-level interfaces. The following example will use InterBase's native API to attach to a database. The role full_access will be used for the connection so that user bjones will have the privileges that have been granted to the role.

File: sampleapi.c

#include <stdio.h>
#include "ibase.h"

int main()
{ ISC_STATUS isc_status[20];
  isc_db_handle db_handle;
  char *dpb;
  short dpb_length;
  int i;

  /* clear database handle */
  db_handle=0L;

  /* add dpb version to dbp */
  dpb=(char *)0;
  dpb_length=0;

  /* add user, password, and role to dpb */
  isc_expand_dpb(&dpb, &dpb_length,
    isc_dpb_user_name, "bjones",
    isc_dpb_password, "bjones",
    isc_dpb_sql_role_name, "full_access",
    NULL);

  /* attach to database */
  isc_attach_database(isc_status, 0, "testdb.gdb", &db_handle,
    dpb_length, dpb);
}

Embedded SQL allows the developer to embed SQL statements in an application. Embedded SQL provides an easy route for developers to execute SQL statements without having to learn the complexities of the InterBase API. Much of the burden of dealing with memory allocation and data structures is hidden from the developer. The compromise for using embedded SQL is that the developer does not have as much flexibility or control when communicating with the InterBase engine.

The following example will connect to the database testdb.gdb using the role full_access. Once it is connected it will insert into the table test_scores.

File: sampleesql.e

#include <stdio.h>

EXEC SQL
SET DATABASE db="testdb.gdb";
int main()
{ EXEC SQL
  CONNECT db USER "bjones" PASSWORD "bjones" ROLE "full_access";
  EXEC SQL
  INSERT INTO TEST_SCORES VALUES ("History", 83, "Bobby","Jones");
  EXEC SQL
  COMMIT;
  return;
}

InterBase provides interactive utilities for a developer to work with databases. ISQL and WISQL both provide an interface for developers to easily enter SQL statements to execute against databases. ISQL is a command-line utility, while WISQL is the graphical based version of the same utility. Both ISQL and WISQL handle the details of managing transactions and statements. The user must only enter the SQL statement to be executed. Both versions of the tool also allow scripts containing SQL statements to be executed against a database. This is an example ISQL script that connects to the database testdb.gdb using the role full_access.

File: sampleisql.sql

connect "testdb.gdb" user "bjones" password
  "bjones" role "full_access";
select * from test_scores;
commit;

Here is a screen shot showing the WISQL connection dialog. There is a separate box to specify a role upon connection to a database.

See the InterBase Operations Guide for more details regarding the InterBase API, embedded SQL, and the InterBase interactive utilities.

InterBase clients must do their part

The second step in the process of using roles is the responsibility of the client tools. The client tools must allow applications to use roles. It is not enough for the InterBase engine to provide an interface to roles. The client tool must also support roles. For an end-user application to work with roles, both parts must comply. Since SQL roles are a new feature in InterBase v5.0, many client tools do not currently support InterBase roles.