At 05:08 PM 13/08/2004 -0300, you wrote:

>Subject: Security Issues
>
>I am a Newbie. Please, be patience with me! :)
>I have 6 doubts about using Firebird.
>
>1) How do I allow a user to only have access to just some folders in the
>firebird server?
>ie. User GUEST1 only may have access to: C:\DB\GUEST1.
>ie. User GUEST2 only may have access to: C:\DB\GUEST2 and C:\DB\Group2.
>I know already the variable "DatabaseAccess" but it hasnt solved it.

Firebird is not a filesystem, it is a database management system. Except
in the Windows embedded model, database files are not accessed by users,
but by the owner of the server process. In theory, any user that has
access to the server has access to any database. However, no user, other
than sysdba and the database owner (and the root user on Posix systems) can
access any object inside a database unless that user has SQL permissions
for that object.

Don't give filesystem access to database files to ordinary users.

>2) How do I log/get the IP of USERS logged in the database?

Firebird has no way to get that for you. Some network tool might be able
to deliver a report on the IP addresses that are listening to port 3050.

>3) How do I prevent a user of chaging the Database Structure of a specific
>database, ie. Do not allowing him to create new tables, triggers, domains etc.

Only sysdba or the owner of an object can change an object. However, any
user that is able to log in to the server can create a new table and
triggers, indexes and SQL permissions for it, in any database. However,
this "rogue user" can't write triggers, stored procedures or constraints
that refer to objects it does not own. If you are careless about your SQL
permissions, with respect to ALL and PUBLIC, you can make some of your
databases objects vulnerable.

>Using views?

Views, in concert with SQL permissions, provide a way to restrict a user's
access to underlying tables and columns. They don't protect a badly
secured system from abuse.

>4) "Execute Statement" seems to be very dangerous.

It is possible to travel from an aeroplane to the ground during flight by
opening the door and jumping out. Most reasonable people would want to use
a parachute and also to know how to open and control the parachute.

EXECUTE STATEMENT is dangerous. It is NOT RECOMMENDED unless you are very,
very clear about its implications.

> Once all users may change DB structure.

Not true. A procedure has to have permissions to operate on objects. Of
course, you will be in trouble if you let everyone log in as sysdba.

>I was thinking about one situation, if we create one view and in this view
>Execute one SP like this:
>SP.... If (User = 'SYSDBA' OR User='SMACE') then EXECUTE STATEMENT 'DROP
>TBLX;'; ... END;
>If the SYSDBA or any other powerfull user see this view, the "execute
>statement" will be triggered.

True. So don't write SPs that change metadata and be careful about who
gets sysdba access.

>In this SP/Trigger we may clear our path (if we're SYSDBA). I've done it
>already, and it seems to work.
>So, does anybody have more info about it? Am I wrong? Please, say yes! =)

Unfortunately, you are right. It's up to the database designer to design
systems that can't get broken by bad guys and idiots. Don't write SPs that
can change metadata. By design, procedure language (PSQL) does not support
DDL statements at all. By stupidity, you can now use EXECUTE STATEMENT to
get past that design restriction.

>5) How do I prevent some specific users of viewing the full structure of
>my databases?
>please, don't tell me to create one view of each table...

Creating views doesn't prevent any users from seeing the metadata tables
(currently). However, metadata tables are just database objects. You can
revoke the default PUBLIC permissions on the metadata tables and then put
back the required permissions for those users that do need to see them. As
things stand currently, you will need to write a script to do this, because
restoring a gbak file will re-install those default permissions.

Another "gotcha" is that, if you store gbak or database files in places
where bad guys can copy them, they can be restored or opened, respectively,
by anyone onto another server installation where that person has sysdba
access. So, for example, if you distribute your application, your
customers have full access to just about everything. You can "slightly
hide" the source code to stored procedures and triggers by removing
it; but a DBA with knowledge of BLR can still look at your code with any
blob reader.

Gee, why do I feel as if I'm starting to write The Firebird Book all over
again?
:-)

/heLen