Hi Helen,

You were very helpfull and friendly in answering me. Thank you.
Read bellow for my answer =)

-- Sérgio Marcelo
smace@...
ICQ # 127.683.347
MSN: smace_br@...
Yahoo: smace_br@...

----- Original Message -----
From: Helen Borrie
To: firebird-support@yahoogroups.com
Sent: Friday, August 13, 2004 10:17 PM
Subject: Re: [firebird-support] Security Issues .:. Execute Statement


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.

No user have direct access to the database file. It is protected. Only the root may change it directly.
I was thinking about running firebird in ISP/Hosting Servers. It should not be possible there, once diferent users (account in the server) should not have access to the metadata of other users/accounts. If Firebird works this way. It may be a problem. Once any user may list all active databases in that server and download it's metadata. I also think that a user in these servers, should only have access to active databases in his folders; (like a described above) . I've already had problems with it before. One ISP that was hosting my site didn't offered enought safety because of this Firebird characteristic. (That was in Linux)

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

Listening? You mean, this tool may find out all users logged in this server or it only logs when this user connects there? I don't know much about it. Any sugestion of this kind of tool is welcome.

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

I knew it. But what worries me is to allow a user to create new "databases objects". There are users I would like to deny this. Imagine in one ISP, users could create new "database objects" in other accounts. See the why I worry about it? I know, Firebird wasn't developed to run under ISPs but It is necessary, or I'll have to migrate to MySQL.

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

I have protected my USERS table in security.fdb using views, It may work for all other RDB$ tables. Of course, It would be something very costly and tiring. But works, at least here.

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

Fortunately only me logs in as SYSDBA here. ;)

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

You doesn't need to be SYSDBA to run "execute statement", of course you must be a SYSDBA to have access to everything that wasn't created by you.

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

I like this E.S. But I think SPs and Triggers with "EXECUTE STATEMENT" should only be created by the SYSDBA. Allowing other users do it make it very dangerous. Like I showed above. Only by seeing the data of a View a SYSDBA can commit an entire database to hackers attacks and clear his path. (The hacker only need a valid user.) Do you also agree with me? Or am I wrong again? (most time I am ;-)

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

I can't revoke access to RDB$. It doesn't seems to work here, I've tried it before, It is not like other tables. Please tell me How I do it.

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.

Where do I learn BLR? What does it mean? (B. L. R. ;-)
I may read Firebird source code and understand it =)

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

Because you answering me. And I'm a newbie. LOL


[Non-text portions of this message have been removed]