Changing the Administrator for your Database

If you run MetriQ standalone, then there will in general be little point in you changing any of the advanced administration security features that come with Firebird and Interbase. However, if you share you computer with a co-worker, or are responsible for the maintenance of MetriQ in a client/server environment, then making your database more secure is certainly something to consider.

Currently, when MetriQ installs, the default settings for the Firebird database are left untouched. However, at some point you may decide to add some of the more advanced security options for your database system.

Using GSEC the 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 super user (on Unix) to use GSEC. GSEC can be used interactively or from the command-line. The following table summarizes GSEC's commands.

Displaying users - The DISPLAY command will show all authorized users:

First open GSEC - You must be the root administrator to perform this task!

"C:\Program Files\FireBird\bin\gsec.exe" -user sysdba -password masterkey
GSEC> display
GSEC> quit

Changing the password:

"C:\Program Files\FireBird\bin\gsec.exe" -user sysdba -password masterkey
GSEC> modify sysdba -pw pwd
GSEC> quit

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

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

GSEC add BJONES -pw mypassword -fname Bobby -lname Jones
GSEC display BJONES

user name uid gid full name
-------------------------------------------------------------------------
SYSDBA 0 0 Sql Server Administrator
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 user 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 Sql Server Administrator

Using Roles in conjunction with a User

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; //FULL_ACCESS in this instance is the name of the role we are creatng.

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.

SQL connect tempemployee.gdb user bjones password bjones;
Database: tempemployee.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 tempemployee.gdb user bjones password bjones role full_access;

Database: tempemployee.gdb, User: bjones, Role: full_access
SQL select * from test_scores;

See also MetriQ Applet, Database Security, Connection Issues,Installing MetriQ, Firewalls, System Requirements, Troubleshooting, Multi-User Environment

Next: License Agreement

©Copyright 2004-2007 MetriQ Limited All Rights Reserved. MetriQ Software is Patent Pending

site map