At 12:07 AM 21/02/2006, you wrote:

>Hello Fellows!
>I work on ministry of education of Brazilian government.
>I'm trying to use firebird 1.5.3 like a departmental database server
>instead of MS Sql Server 2000.
>But we are having a security problem with users permission!
>
>1-I created a user SYSDBSIMAP_DEV, with password sysdbsimap_dev;
>
>2-I revoked its permissions as listed below:
>
>Revoke Insert , delete , update On RDB$RELATIONS from SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$RELATION_CONSTRAINTS from
>SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$RELATION_FIELDS from
>SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$ROLES from SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$USER_PRIVILEGES from
>SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$PROCEDURES from SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$PROCEDURE_PARAMETERS from
>SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$FUNCTIONS from SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$FUNCTION_ARGUMENTS from
>SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$GENERATORS from SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$INDEX_SEGMENTS from SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$INDICES from SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$EXCEPTIONS from SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$DATABASE from SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$TRIGGERS from SYSDBSIMAP_DEVP
>Revoke Insert , delete , update On RDB$TRIGGER_MESSAGES from
>SYSDBSIMAP_DEVP
>
>
>3-I created a new database named sysdbsimap_dev using the sysdba
>permission.
>
>4-I generated the tables, procedures and several indexes on that
>database connected as sysdba.
>
>5-Then i registered the database using the sysdbsimap_dev user.
>
>I noticed that the user sysdbsimap_dev doesn't have permission to
>alter the objects created by sysdba.

All of that was unnecessary. An ordinary user doesn't have update or
delete permissions on any existing object (including the system
tables) unless a) that user is the database owner AND b) that user is
the owner of the object.

It's a VERY bad idea to mess around with the data in the system
tables, or with the permissions on them. Behind the SQL privileges
language interface is an access control subsystem that you can't get
at. It is written to as a result of GRANTS and REVOKES on user
objects. The system tables belong to the engine, not to users.

>Unfortunately, the user can create new objects on database( tables,
>procedures,etc)!
>
>How can I do to deny those permissions to user sysdbsimap_dev? I want
>the user only have permissions for insert, update, delete and execute
>procedures on my database.
>
>Please, help me!
>If I can't assure that an application user can not create objects on
>my database, my boss will stop to use firebird anymore! He will use
>postgres...

An ordinary user doesn't have access to anything that already exists
unless SYSDBA or the object owner specifically GRANTs the required
access to that user. If there are multiple object owners, then being
an owner of one object doesn't give that user any privileges on
objects owned by another user. So, while you can't prevent an
ordinary user from creating new objects, those objects are available
only to the SYSDBA and to that user. The user could try to write
triggers or SPs that would access other objects, but they would fail
if the owner of those objects did not grant the needed permissions to
the triggers and procedures.

The main risk would be denial of service: a malicious user that had
access to the database could create a table and keep loading it with
millions of records, until the hard disk partition was full; or
could create a table and then alter it 255 times. At that point, the
database would become unavailable and would have to be backed up and restored.

You should study the documentation for user privileges. As a
minimum, revoke all GRANTs you might have made to PUBLIC, other than
the SELECT on RDB$ROLES.

Then design all of your user privilege "packages" around roles. That
should be standard practice in any multi-user system anyway.

Since you are using IBO, follow up Set's suggestion, and make use of
the TIB_Connection's ability to hide usernames, roles and passwords
behind application-level "proxies", to ensure that human users can't
reach the database except through the application.

I don't think you're doing yourself any favours by trying to mix up
the BDE, IBX and IBO in a single application. OK for development
(maybe), hopeless for deployment (certainly).