Subject Re: [firebird-support] Permissions problems with Embedded Firebird
Author Helen Borrie
At 07:34 PM 2/10/2005 +0000, you wrote:
>I'm not succeeding in understanding permissions on Embedded Firebird.
>I created a DB successfully, but now, although I can access its
>metadata (e.g. table definitions) in a tool such as the EMS IB/FB
>Manager Lite, if I try to access the data ("select * from CATEGORY"),
>I get the message:
>"This user does not have privilege to perform this operation on this
> permission for read/select access to COLUMN categorynum."

At the point where you create a database, it becomes "owned" by the server
user that created it. Most likely, that was SYSDBA. Let's assume also
that you created some tables while logged in as SYSDBA. Therefore, SYSDBA
owns those tables, too.

>I don't seem to be able to create users (which is what I expected,
>from the embedded readme), though I can create roles.

"Users" are a server-wide thing. They are not created in the context of a
user database at all. They are created in the server-wide security
database (security.fdb, if you are using v.1.5.)

You don't say what platform you are on. The way things work with
permissions (overall) is somewhat different, depending on whether you are
on Windows or any non-Windows platform.

Assuming it's Windows Embedded you are playing with...

>I tried to grant permission on the table "category" to public,
>thinking that might work, but I get the following error:
>"This operation is not defined for system tables.unsuccessful
>metadata update.
>no S privilege with grant option on table/view category."
>The "category" table I'm trying to set permission on is a user-
>defined table, not a system table.

The first thing I'd want to do is get the right firebird.msg file into the
app directory.

>Where can I learn about how permissions work on the embedded DB?

The first thing to understand is that "users" belong to server-level
authentication, while SQL permissions belong to individual databases. The
database *owner* can grant permissions on any object it owns to any user
name - including user names that don't exist in the security DB. The
latter isn't much use to a "full server" installation, since a non-existent
*server* user can't get access to *any* database.

"Embedded" doesn't mean an embedded DB, it refers to an embedded server
Actually, it's quite a silly name, since it's really "a server with its own
dedicated client".

Unlike with the "full server", embedded does not require (or use)
server-level authentication. However, database-level permission is still
required to access objects inside a database. The problem you are having
is that "any old user" was able to "access the server", i.e. load the
fbembed.dll, but the engine doesn't know anything about the user if your
application didn't pass that information in the connection string.

So - assuming sysdba is the owner of the objects - you'll need to load the
library, passing sysdba in the USER_NAME parameter, in order for sysdba to
grant the permissions to other users.

>I am really hoping that in my delivered application using embedded
>FB, I will be able to prevent the users from updating the DB via any
>method other than using my program, but from what I've been reading
>it seems like it will be entirely open. Is that right?

Entirely open? Mostly Yes and conditionally No. Any old Joe Blow will be
able to create objects; and, if you grant access to everything in the
database to PUBLIC, any old Joe Blow will be able to open your application
(or a suitably located DB admin tool) and do stuff, because the "embedded"
library doesn't require server-level authentication.

The database isn't special in any way. It's "just a Firebird
database". So, if it's not physically protected, anyone can access it from
the app directory, using some db admin tool, passing sysdba as user_name,
and do what he wants to it. Also, like any Firebird database, it can be
"stolen", i.e. copied to a host that has a server and log into it using the
sysdba credentials of that server.

On the other hand, as long as your app is running, nothing else can connect
to your database (including Gbak); and any filesystem copy taken has a
reasonable chance of being corrupt.