D.2.j (Database Talk) [SQL Roles: Users and Security in InterBase]

SQL Roles: Users and Security in InterBase

Brett Bandy

Markus Kemper

BorCon 1998

Databases need security. Data stored in database files must be secure. InterBase provides two
levels of security for data; user validation and database privileges. This paper will discuss how
to setup security for both levels to maintain security for your database. Topics covered:

•

What an InterBase user is and how to create them

•

Basic SQL privileges and how to apply them to a database

•

How SQL Roles can make a Database Administrator's life easier

•

How to use SQL Roles in a database

What is an InterBase user?

InterBase security is based on the concept of a user. A user is the identity that all database
security is validated against. Authorized InterBase users are stored in a security database, which
is called ISC4.GDB. Each InterBase server has its own security database, which means that a
user definition is bound to the server where it is stored. The same user may exist on several
servers, but it must be created on all servers where it is required. The security database also
stores an encrypted password for each user. Users in the security database are authorized for all
databases that reside on that server.

The username can have a maximum of 31 characters. The username is NOT case-sensitive. The
password can have up to 8 characters. The password IS case-sensitive.

Common Mistake: This is a common mistake for a lot of new InterBase users. They create a user
and password, but when they try to use it they get an error message saying that they don't have a
valid username and password. Make sure that you have specified the password EXACTLY how
it was defined.

Column

Required? Data Type Description

User
Name

Yes

String

The name the user supplies when logging in.

Password Yes

String

The user's password

UID

No

Integer

Optional UserID. Currently not used by InterBase

GID

No

Integer

Optional GroupID. Currently not used by InterBase

Full Name No

String

User's real name

Extending InterBase users with Operating System users
All versions of InterBase use the security database to authenticate users. Some versions of
InterBase allow access based on operating system permissions. All InterBase kits running on
Unix will allow the use of either operating system users or InterBase users when logging into an
InterBase server or database. InterBase kits running on Win95 or NT do not make use of
operating system security database permissions. A Win95 or NT user is NOT a valid InterBase
user. InterBase relies solely on the security database for user authentication on Win95 and NT.

InterBase will treat a Unix user the same as a user stored in the InterBase security database, as
long as the server sees the client as a trusted host. The user account must exist on both the client
and the server. To establish a trusted host relationship between the client and the server, an entry
must be in the server's /etc/hosts.equiv or /etc/gds_hosts.equiv file. The hosts.equiv file will
setup an OS trusted host relationship, which extends to other services (for example rlogin, rsh,
rcp). The gds_hosts.equiv will setup an InterBase only trusted host relationship. The format of
an entry is identical for the two files. The format of an entry in either file is:

hostname [username]

It is not possible to setup a trusted host relationship between a Unix machine and a Windows
machine. To use Unix usernames, the client and the server must both be Unix machines.

Unix usernames are used only if no InterBase username is specified at database login time.

SYSDBA user
When InterBase is installed there is only one user authorized, SYSDBA. SYSDBA is a special
user that is above security restrictions and has full access to all databases on the server. The
default password for SYSDBA is masterkey. It is strongly recommended to change the
SYSDBA password, because this is the default for all InterBase kits and can easily be guessed by
anyone who has ever used InterBase. Only SYSDBA can add, modify, or delete new users.

On Unix systems the root superuser can be used as the SYSDBA user. InterBase treats the root
username as SYSDBA. When using root you will have all privileges on all databases residing
on the server.

Managing users
InterBase provides three interfaces for SYSDBA to manage users.

•

GSEC

•

Server Manager

•

InterBase API function calls

All three methods provide the same functionality. They just differ in how they are used.

Using GSEC InterBase's command-line security utility
GSEC is a command-line utility that provides an interface to InterBase's security database. You
must be SYSDBA or the superuser (on Unix) to use GSEC. GSEC can be used interactively or
from the command-line. The following table summarizes GSEC's commands.

Command

Description

di[splay]

Displays all users stored in
ISC4.GDB

di[splay] name

Displays the information for user
name

a[dd] name -pw passwd [option
argument option argument ...]

Adds user name with password
passwd and optional information

mo[dify] name [options]

Modify a user's attributes

de[lete] name

Deletes user name from ISC4.GDB

h[elp]

Display's GSEC's commands and
syntax

q[uit]

Quits interactive GSEC

Displaying users
The DISPLAY command will show all authorized users

GSEC display
user name uid gid full name
-----------------------------------------------------------------------------
SYSDBA 0 0
TEST 0 0
BBANDY 0 0
MKEMPER 0 0

Adding a user
Use the ADD command to add new users to the security database.

The following table lists all options that can be specified when adding or modifying a user.

Option

Description

-pw

User's password

-u[id]

User ID

-g[id]

Group ID

-f[name]

User's first name

-mn[ame]

User's middle name

-l[name]

User's last name

This example will add user BJONES with specified password. It also stores information for first
and last names.

GSEC add BJONES -pw blah -fname Bobby -lname Jones
GSEC display BJONES
user name uid gid full name
----------------------------------------------------------------------------
BJONES 0 0 Bobby Jones

Modifying a user
The MODIFY command is used to change existing user information.

This example updates the UserID and lastname for user BJONES

GSEC modify BJONES -uid 22 -fname Brad
GSEC display BJONES
user name uid gid full name
----------------------------------------------------------------------------
BJONES 22 0 Brad Jones

Deleting a user
This example will use the DELETE command to delete the user BJONES. To verify that the use
has been deleted the DISPLAY command will be used to show all users.

GSEC delete BJONES
GSEC display
user name uid gid full name
-----------------------------------------------------------------------------
-----
SYSDBA 0 0
TEST 0 0
BBANDY 0 0
MKEMPER 0 0

Using Server Manager to manage users
InterBase's Server Manager provides a Graphical Interface for many common database
administration tasks. The Server Manager is only available on Windows platforms. You can
manage an InterBase server on any platform InterBase supports, but the Server Manager must be
run on a windows platform. The InterBase Client for Windows must be installed on the machine
where Server Manager is being used.

Among the many tasks it performs is user management. Server Manager will perform the same
user management tasks that GSEC does, but in a Graphical Environment. Since each InterBase
server has its own security database, you must login to the server as SYSDBA before you can
manage the server's users.

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.

Using the InterBase API to Manage Users
InterBase provides a native Application Programming Interface (API) to access databases and
perform administration functions. Applications can use these API functions to provide
connectivity to InterBase databases. Among these API functions are three that allow the
application programmer to manage users. The following table summarizes the three API
functions available for user management.

API Function

Description

isc_add_user

Adds a new user to security database

isc_modify_user Modifies a users record in security database

isc_delete_user

Deletes a user from security database

For each of the three functions you will setup a USER_SEC_DATA structure. This structure is
defined in ibase.h as:

typedef struct {
short sec_flags; /* which fields are specified */
int uid; /* the user's id */
int gid; /* the user's group id */

int protocol; /* protocol to use for connection */
char ISC_FAR *server; /* server to administer */
char ISC_FAR *user_name; /* the user's name */
char ISC_FAR *password; /* the user's password */
char ISC_FAR *group_name; /* the group name */
char ISC_FAR *first_name; /* the user's first name */
char ISC_FAR *middle_name; /* the user's middle name */
char ISC_FAR *last_name; /* the user's last name */
char ISC_FAR *dba_user_name; /* the dba user name */
char ISC_FAR *dba_password; /* the dba password */
} USER_SEC_DATA;

This structure has members for each of the user attributes that can be stored in the security
database. There are additional members in the structure that are required for the server to know
which server's security database is being modified and which fields in the database are being
modified. Examples using the USER_SEC_DATA structure with the three API functions are
provide below.

For the user management functions to succeed the SYSDBA password must be supplied. This
can be done in two different ways. When setting up the USER_SEC_DATA structure, there are
two fields that can be used to specify the SYSDBA username and password. The two fields used
to specify the SYSDBA user and password are:

•

dba_user_name

•

dba_password

The dba_user_name should be set to SYSDBA, while the dba_password should be set to the
SYSDBA password (default for InterBase is masterkey).

Common Mistake: You should not hardcode the SYSDBA password into the application. Any
user can run a grep utility or hex editor on the binary and retrieve the static string. Instead you
should have a provision to pass in the SYSDBA password so that it is not statically stored with
the binary.

Following are several examples using these user management API functions. The examples are
provided in both C and Delphi.

Adding a new user in C
This example will add user BJONES with password bjones into the security database. This
example will also store the user's first and lastnames.

File: adduser.c

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

main()
{
ISC_STATUS isc_status[20];
USER_SEC_DATA user_data;

user_data.user_name = "BJONES";
user_data.password = "bjones";

user_data.protocol = sec_protocol_local;
user_data.dba_user_name = "SYSDBA";
user_data.dba_password = "masterkey"; /* Don't hardcode this */
user_data.first_name = "Bobby";
user_data.last_name = "Jones";
user_data.sec_flags = sec_password_spec | sec_dba_user_name_spec |
sec_dba_password_spec |

sec_first_name_spec |

sec_last_name_spec;

isc_add_user(isc_status, &user_data);
if(isc_status[0] == 1 && isc_status[1])
{
isc_print_status(isc_status);
return;
}

printf("Successfully added user\n");

return;
}

Adding a new user in Delphi
This example does the same thing as the previous add user example written in C.

File: Adduser.dpr

program Adduser;

uses
ibase in 'ibase.pas',
ib_externals in 'ib_externals.pas';

var
userData: TUserSecData;
userDataPtr: PUserSecData;
status: array[0..19] of ISC_STATUS;
isc_status: PISC_STATUS;

begin
{ setup isc_status pointer }
isc_status := @status;

{ setup user data pointer to point to user data structure }
userDataPtr := @userData;

{ setup user data structure }
userData.user_name := 'BJONES';
userData.password := 'bjones';
userData.protocol := sec_protocol_local;
userData.dba_user_name := 'SYSDBA';

userData.dba_password := 'masterkey';

/* Don't hardcode this */

userData.first_name := 'Bobby';
userData.last_name := 'Jones';
userData.sec_flags := sec_password_spec or sec_dba_user_name_spec or
sec_dba_password_spec
or sec_first_name_spec or sec_last_name_spec;

{ add user to security database }
isc_add_user(isc_status, userDataPtr);

end.

Modifying a user in C
This example will modify the user BJONES's password to bjones.

File: modifyuser.c

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

main()
{
ISC_STATUS isc_status[20];
USER_SEC_DATA user_data;

user_data.user_name = "BJONES";
user_data.password = "bjones";
user_data.protocol = sec_protocol_local;
user_data.dba_user_name = "SYSDBA";

user_data.dba_password = "masterkey";

/* Don't hardcode this */

user_data.sec_flags = sec_password_spec | sec_dba_user_name_spec |
sec_dba_password_spec;

isc_modify_user(isc_status, &user_data);
if(isc_status[0] == 1 && isc_status[1])
{
isc_print_status(isc_status);
return;
}

printf("Successfully modified user\n");

return;
}

Modifying a user in Delphi
This example will modify the user BJONES's password to newpass and also change the UID to
22.

File: ModifyUser.dpr

program ModifyUser;

uses
ib_externals in 'ib_externals.pas',
ibase in 'ibase.pas';

var
userData: TUserSecData;
userDataPtr: PUserSecData;
status: array[0..19] of ISC_STATUS;
isc_status: PISC_STATUS;

begin
{ setup isc_status pointer }
isc_status := @status;

{ setup user data pointer to point to user data structure }
userDataPtr := @userData;

{ setup user data structure }
userData.user_name := 'BJONES';
userData.password := 'newpass';
userData.uid := 22;
userData.protocol := sec_protocol_local;
userData.dba_user_name := 'SYSDBA';

userData.dba_password := 'masterkey';

/* Don't hardcode this */

userData.sec_flags := sec_uid_spec or sec_password_spec or
sec_dba_user_name_spec or

sec_dba_password_spec;

{ add user to security database }
isc_modify_user(isc_status, userDataPtr);

end.

Deleting a user in C
This example will delete the user BJONES from the security database. As shown in the
examples, the only fields required are the user to be deleted and the SYSDBA user and
password.

File: deleteuser.c

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

main()
{
ISC_STATUS isc_status[20];
USER_SEC_DATA user_data;

user_data.user_name = "BJONES";
user_data.protocol = sec_protocol_local;
user_data.dba_user_name = "SYSDBA";

user_data.dba_password = "masterkey";

/* Don't hardcode this */

user_data.sec_flags = sec_dba_user_name_spec | sec_dba_password_spec;

isc_delete_user(isc_status, &user_data);
if(isc_status[0] == 1 && isc_status[1])
{
isc_print_status(isc_status);
return;
}

printf("Successfully deleted user\n");

return;
}

Deleting a user in Delphi
This example will delete the user BJONES from the security database.

File: DeleteUser.dpr

program DeleteUser;

uses
ibase in 'ibase.pas',
ib_externals in 'ib_externals.pas';

var
userData: TUserSecData;
userDataPtr: PUserSecData;
status: array[0..19] of ISC_STATUS;
isc_status: PISC_STATUS;
errorBuffer: array[0..256] of char;
buffer: PChar;

begin
{ setup isc_status pointer }
isc_status := @status;

{ setup user data pointer to point to user data structure }
userDataPtr := @userData;

{setup pointer for error buffer }
buffer := errorBuffer;

{ setup user data structure }
userData.user_name := 'BJONES';
userData.protocol := sec_protocol_local;
userData.dba_user_name := 'SYSDBA';

userData.dba_password := 'masterkey';

/* Don't hardcode this */

userData.sec_flags := sec_dba_user_name_spec and sec_dba_password_spec;

{ add user to security database }
isc_delete_user(isc_status, userDataPtr);

end.

SQL Privileges: The second level of security

InterBase implements two levels of security. The first is user validation. This is done at
database connection time. Users are validated against InterBase's security database (See
previous section for discussion of authorized users). The second level of security is implemented
at the database level. All privileges for this level are stored in the database itself. An authorized
user does not have privileges to data stored in the database unless that user is explicitly assigned
privileges. Authorized users are allowed to connect to databases, but unless they have privileges
they cannot access any of the objects or data stored in the database.

SQL privileges are controlled on a table level. Each user has a list of operations that he or she is

allowed to perform on a given table or view. This list of operations makes up that user's access
privileges.
Additionally, InterBase puts restrictions on the use of stored procedures as well. A user cannot
execute a stored procedure unless that user has been assigned the privilege to do so.

When a database object is created only the SYSDBA user and the owner of the object have
privileges to access that object. The user that creates the database object is the owner of that
object. SYSDBA or the owner of the object can explicitly grant privileges to other users.
Additionally, the privilege of assigning privileges to other users can also be assigned to a user
other than SYSDBA or the object's owner.

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.

Common Mistake: A common mistake developers make is to create database objects and not
assign privileges to any users. Since the developer is the owner of the object there are no
privilege problems. Being the owner allows the developer to perform any and all operations on
the database object. When it comes time to deploy the application, no other users are allowed
access, because they have not been granted privileges on the database object.

For all security operations, SYSDBA always has the right to grant or revoke privileges from
users. The SYSDBA user always assumes total control over a database. SYSDBA is the
superuser, there is no way to deny access from the SYSDBA user. The GRANT and REVOKE
statements have no effect on the SYSDBA user.

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.

Privilege Definition of Privileges

Insert

Allows insertion of new rows into table

Update

Allows existing rows to be updated

Delete

Allows existing rows to be deleted

Select

Allows user to view/query the rows in a table

Execute Allows a user to execute a procedure

Referenc Allows server to lookup rows in a primary/foreign key

es

relationship

All

Shortcut to assign insert, update, delete, select, references to a
user

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 is inefficient

The SQL security mechanism will get the job done. It is, however, inefficient for database
administrators to setup and manage. The SQL security mechanism works on individual users.
Outside of the PUBLIC user, there is no way to assign privileges at a group level. Additionally,
SQL security works on a table by table basis. Privileges must be assigned for each table in the
database.

For example, assume a database administrator is trying to setup SQL security for 100 users on a
database with 100 tables in it. With SQL security the database administrator will have to execute
10000 grant statements to setup the security for the users on this database. For each user there is
one grant statement per table. This comes out to 100 grant statements per user. For 100 users
that totals the 10000 grant statements required to setup SQL security. For each table you can
grant privileges to a list of users, but this approach leads to many errors. The database
administrator has to partition the users among the grant statements and ensure that each user is
granted privileges for each table. In most cases, database administrators usually stick to one user
per grant statement.

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.

What are SQL Roles?

InterBase 5.0 introduced an extension to SQL security called SQL Roles. SQL Roles implement
the concept of group level security. Roles also act as templates for predefined sets of privileges.
SQL Roles are an extension to the standard SQL security mechanism implemented in InterBase.
A Role defines a set of SQL privileges on one or more tables in a database. Roles work with
normal SQL security, but add the benefit of group level security.

For example, a programmer must assume many roles during the life cycle of a product. Initially,
the programmer must assume the role of a marketing person to define what the product is and
what its capabilities are. Next, the programmer must assume the role of the developer while
engineering the product. Finally, the programmer must assume the role of the end user and QA
the product to assure its quality. SQL Roles do just this for database security.

A role acts as a group template for users. In the above example, the programmer can be one user
or many users. Roles also allow users to assume different roles (different sets of privileges).
The programmer in the example can take on different privileges as he is assuming a different
role. The marketing role has a set of privileges defined for it, while the QA role has an entirely
different set of privileges defined for it.

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.

Advantages of Roles

Roles have several advantages over the basic SQL security model. These advantages make it
easier for a DBA to administer a database's security. As stated earlier, Roles provide much
needed group level security for InterBase. This makes it easy for the DBA to add security for
multiple users. Roles also provide a degree of flexibility that is not easy attainable with the basic
SQL security model.

Lets revisit the example of the DBA setting up security for 100 users on a database with 100
tables. With basic SQL security we concluded that the DBA would have to execute 10000 grant
statements to fully setup security for these 100 users on all 100 tables in the database. Using
roles, the best case scenario would only require 200 grant statements to be executed. The best
case would be where every user requires the same privileges on all tables. In this scenario the
DBA could setup one role, which would require 100 grant statements. To add the 100 users to
the role would require an additional 100 grant statements, which equals the 200 grant statements
stated above. The more general case would require 100x + 100 grant statements, where x equals
the number of different roles that are required. For example, if 5 roles were required then a total
of 600 grant statements would have to be executed by the DBA to setup security. Clearly, using
Roles has made setting up security for this database much easier for the DBA.

Roles make it easier to manage user
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.

SQL create table test_scores (i1 integer);
SQL create role FULL_ACCESS;
SQL grant all on test_scores to full_access;
SQL 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

SQL grant FULL_ACCESS to TEST;
SQL 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.

SQL connect \temp\employee.gdb user test password test;
Database: \temp\employee.gdb, User: test
SQL select * from test_scores;
Statement failed, SQLCODE = -551

no permission for read/select access to table TEST_SCORES
SQL connect \temp\employee.gdb user test password test role full_access;
Database: \temp\employee.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.

SQL show grant test_scores;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TEST_SCORES TO
FULL_ACCESS
SQL revoke insert on test_scores from full_access;
SQL show grant test_scores;
GRANT DELETE, SELECT, UPDATE, REFERENCES ON TEST_SCORES TO FULL_ACCESS

SQL connect \temp\employee.gdb user test password test role full_access;
Database: \temp\employee.gdb, User: test, Role: full_access
SQL select * from test_scores;
SQL 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.

Using Roles

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

SQL connect \temp\employee.gdb user bjones password bjones;
Database: \temp\employee.gdb, User: bjones
SQL 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.

SQL connect \temp\employee.gdb user bjones password bjones role full_access;
Database: \temp\employee.gdb, User: bjones, Role: full_access
SQL 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.

Common Mistake: The most common misconception regarding roles is that when a role has been
granted to a user, that user will immediately have all the privileges that the role has been
granted. This is incorrect. For a user to acquire the privileges that have been granted to a role,
the user must specify the role when connecting to the database. If a user does not specify the
role when connecting, that user will have none of the privileges that have been granted to the
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.

SQL connect \temp\employee.gdb user bjones password bjones role full_access;
Database: \temp\employee.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 <stdio.h
#include <string.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 <privileges 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;

Where and How Roles are Supported

For roles to be of value, client tools must be able to specify a role upon database connection.
The first step is for InterBase to surface the ability to use roles while connecting to databases.
The second step is for development tools to use one of the methods that InterBase surfaced to
implement roles in the client application. It is a two step process for end users to use roles with
their database applications.

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.

Roles make security manageable

In summary, database security is a must. The standard SQL security model is adequate for
securing your data. Roles build on the standard model and add efficiency to security
management. Roles provide group level security, which is missing in the standard SQL security
model. Using roles makes your life easier.