I don't know whether anybody can help me with this one, but I'm working on something where I need a program to be able to create login-capable database users on-the-fly. I was hoping to be able to use either PostgreSQL or Firebird as the backend, but while Postgres is working fine Firebird is proving to be sufficiently difficult that I might just drop it.

Using isql-fb on Debian x86, I've created a database borg_ng with, among others, a user borg_um to which I've granted admin and rdb$admin rights. If I use isql-fb running either as root or as firebird with the rdb$admin role I can create a new user and verify it using gsec:

# isql-fb -user borg_um -password password -role 'rdb$admin' Use CONNECT or CREATE DATABASE to specify a database SQL> connect borg_ng;
Database: borg_ng, User: borg_um, Role: RDB$ADMIN SQL> create user test__4 password 'password';
..
GSEC> di
user name uid gid admin full name ..
TEST__4 0 0

Unfortunately I'm having a lot of difficulty doing this under program control, with the obvious difference being that FPC/Lazarus is connecting via a socket.

Has anybody had anything like this working in the past?

Have you tried the Firebird admin module that Ludo Brands wrote? It connects using the FB service interface and allows user creation.

Firebird also offers CREATE USER SQL AFAIR (since FB 2.5, perhaps earlier)

Mark,

You should be able to do this using the Firebird Services API. IBX exposes this API under the Firebird Admin tab and the TIBSecurityService class is what you are looking for.

This has methods to

"DisplayUsers", which gets info on all users and puts it into an array property.

Adduser, ModifyUser and DeleteUser - which should be self-evident.

The original Borland documentation is not great but if you search on TIBSecurityService you should get most questions answered and its worth reading the Firebird Services API documentation to understand what is going on.

Regards

Tony Whyman
MWA Software

Have you tried the fbadmin unit that comes with 2.6.2? It implements some of the interbase service routines such as user management and backup/restore. There has a convenient AddUser method.

Ludo

That is exactly what the FBAdmin unit is doing using the Services API.

Ludo

..

Have you tried the fbadmin unit that comes with 2.6.2? It implements some of the interbase service routines such as user management and backup/restore. There has a convenient AddUser method.

Thanks Ludo and Tony, I'll take a look at that a bit later in the day but I'm sure I'll have more questions.

What I'm trying to do is set up a tree of database servers and apps, and was hoping to allow cooperating admins to use Firebird at the leaf nodes on account of its perceived ease of configuration etc. Having said that, if I have to start moving outside "standard" SQL to do things like user creation, i.e. maintain both SQL-based commands for PostgreSQL and API sequences for Firebird, much of the perceived advantage disappears.

Mark,

I did something like this a few years ago and the Services API is your friend - but it is not standard SQL...

Regards

Tony Whyman
MWA

Tony Whyman wrote

Mark, I did something like this a few years ago and the Services API is your friend - but it is not standard SQL...

[Nod] I suspect that the key is the way that Firebird documents various activities as differing subsets of SQL, i.e. defining a table is one subset, accessing it is another, managing users a third and so on. I could have been naive in assuming that isql's CREATE USER was submitted as such, rather than being translated to API call(s) inside the program.

Don't know exactly what you mean by differing subsets; AFAIU they're all regular SQL as far as Firebird is concerned. They should work in e.g. Flamerobin just as they do in isql.

Management tasks that used to only be exposed by the services API are now also being offered through SQL (user management, grant management...) You do need to pay attention you're not using some prehistory FB version
- don't know when exactly CREATE USER was introduced, probably in 2.0 or 2.1 or something...

It identifies itself as 2.5, and by that I explicitly mean that I'm querying it rather than trusting any claims by the Debian package maintainer, and according to the documentation that's the version at which rdb$admin system role was added.

I suspect that I'm supposed to be doing something like granting rdb$admin to the user concerned in the security database, but so far I can't work out how.

Glad you trust the Debian maintainers then ;)

Well...
Background: Create user via SQL:
http://www.firebirdsql.org/refdocs/langrefupd25-security-sql-user-mgmt.html CREATE USER mark PASSWORD '8charmax' -- if you want to let that user add other users, add GRANT ADMIN ROLE

GRANT ADMIN ROLE gives the new user the RDB$ADMIN role in the security database. This allows him to manage user accounts, but doesn't give him any special privileges in regular databases.

Yes granting rdb$admin for full control seems like a good idea: http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-security-rdbadmin.html Connect to the target db
GRANT RDB$ADMIN TO mark
(execute as e.g. SYSDBA)

Don't forget this:
To make use of his RDB$ADMIN privileges, the grantee simply specifies the role when connecting to the database.
i.e. TIBConnection.Role:='RDB$ADMIN'

..

I think gmail's delaying things :-) I've not tried that yet, but it's on the list. I've just spent a while checking that some related SQL works with PostgreSQL (i.e. getting quoting correct etc.) since it's easier to debug.

Reinier occasionally replies via gmane to some randomly mangled address ( in this case), which causes such posts to be held for moderation.

Jonas

<snip>

Very polite of you; actually I think it's PICNIC/PEBKAC at my end ;)

I've not tried that yet, but it's on the list. I've just spent a while checking that some related SQL works with PostgreSQL (i.e. getting quoting correct etc.) since it's easier to debug.

As mentioned in other posts: you should be able to create users and assign database permissions using SQL only (have confirmed the CREATE USER/GRANT ROLE commands execute correctly with a third party tool, FlameRobin (highly recommended for FB work) against a Firebird 2.5 db).

As you're using SQL for PostgreSQL as well, I'd suggest not using the fbadmin module but doing everything in SQL.

If you hit problems, please feel free to contact me off list.

Thanks,
Reinier

Yes, Jonas, you're right. Sorry about that - I have Thunderbird set up to follow the gmane NNTP server and sometimes respond that way which requires me to manually change the addresses.

Looking forward to Graeme's newsreader, I guess ;)

If I can get this code to fly, I'm anticipating that at least some people will want to set up their own servers acting in concert with the remainder of the tree. That implies that I've got to document e.g. initial user and table creation properly (i.e. to allow enough software to run that it can take over management), and also that I've got to be very careful checking server versions etc.

So in this context I trust package maintainers about as far as i can spit a rat :-)

But I specified that when using isql to set up the initial users, and (in the context of isql) it didn't appear to have any effect until I also explicitly added rdb$admin.

(From isql) I'm currently getting failures like "no permission for direct access to security database..."

Don't forget this: To make use of his RDB$ADMIN privileges, the grantee simply specifies the role when connecting to the database. i.e. TIBConnection.Role:='RDB$ADMIN'

Yes, I'd got there after finding that it was required by isql etc. My current position appears to be that

* Having run isql as root and set up a user borg_um with admin rights

* and having added rdb$admin to it

* I can then tell isql to run as borg_um and create unprivileged users.

But I can't do that last step from a TIBConnection. There's obviously a possibility that I'm Doing It Wrong: what's the Firebird equivalent of PQExec(), I'm using isc_dsql_execute_immediate()?

I'd very much like to be able to use SQL commands for this, since it makes management and logging far easier.

Thanks, noted.

As you're using SQL for PostgreSQL as well, I'd suggest not using the fbadmin module but doing everything in SQL.

I'm trying to :-) But I was hoping that Firebird would be easy after the number of people who compliment it, and that it would be a good fit for "leaf" servers since- like Postgres- it has a notification mechanism.

If you hit problems, please feel free to contact me off list.

Thanks very much, noted.

Note that you have
- GRANT ADMIN ROLE when creating the user (which is created in the FB security db). This allows that user to add users etc. as you demonstrated in your initial post
- GRANT rdb$admin to whomever when connected to a certain db: this gives the user the permission to do anything he wants with that db/grants admin permissions.

Are you saying you had to run a GRANT rdb$admin to whoever when connected to the security db? IIRC (and as you probably found out) connecting to the security db with isql/regular SQL clients has been blocked.
The only way to connect is through the services API (or indirectly via CREATE USER etc)

Yep, you need to connect to the target/newly created db, not the security db.

Yes, I'd got there after finding that it was required by isql etc. My current position appears to be that * Having run isql as root and set up a user borg_um with admin rights

By default [1], the account under which you run isql has no impact on FB security (in contrast to postgresql where you have a dedicated postgresql account).
Using user SYSDBA would be the "regular" way to run isql

[1] not really true, there is a trusted authentication mode where the OS username is used in FB 2+ (IIRC the version). Haven't tried that.

Strange.
Setting the user to borg_um, the role to RDB$ADMIN, correct password, the db to the newly created db, then connecting with the right transactions etc and doing something like TIBConnection.Execute('CREATE USER...') (or whatever the actual command is) should work.

I'd very much like to be able to use SQL commands for this, since it makes management and logging far easier.

If you don't get it sorted, let me knoe & I'll try and set up a demo program.

<snip>

I've written a demo application that:
uses regular TIBConnection and SQL commands to
1. create a new db on a server
2. create a user with GRANT ADMIN ROLE and full control over the new db

Source:
https://bitbucket.org/reiniero/fpc_laz_patch_playground/downloads/firebirduser.zip and in the fpc_laz_patch_playground repository itself (directory fbcreatedb)

Comments welcome.

I think I've got it, the correct procedure for a resultless SQL command is ExecuteDirect(). The reason that I'd been trying to use a different function was as a legacy of experimentation a year or so ago with Firebird events, i.e. finding how compatible I could make them with PostgreSQL async notifications, where if sharing a connection object with queries etc. one has to be careful about the handle's lifetime.

So thanks for the moral support, once I've checked over the documentation I should be able to press on.

As background, what I'm trying to do is set up a tree of servers and apps with messages passed around by journal tables and notifications. Only hitch is that a user has to have an initial ID before he can start using that type of arrangement, which I'm allocating by what is basically a fancy finger daemon.

Looks good :-)

Subject to a maximum user name length of 31 characters on Firebird. The manual suggests that PostgreSQL has a limit of 64.

<snip>

Subject to a maximum user name length of 31 characters on Firebird. The manual suggests that PostgreSQL has a limit of 64.

If you're going to list all differences between PG and FB as you hit them, you might put them on a wiki page for easy retrieval by others. The table/trigger/sp/view/general object name max length in FB is about 32 bytes IIRC.

I suppose you found out FB has an 8 char effective password? You can use more letters but MASTERKE is the same as MASTERKEY. Of course this goes away if you use trusted authentication/OS authentication.

I suppose things'll change in FB 3 though. When that is released... probably around the release of FPC 2.8 :)

True, but I thought that a detail relating to something that people were likely to do comparatively infrequently (i.e. user management by a custom app rather than by something like Flamerobin) was worth getting into the record.

I suppose you found out FB has an 8 char effective password? You can use more letters but MASTERKE is the same as MASTERKEY. Of course this goes away if you use trusted authentication/OS authentication.

I hadn't yet, but I was going to hash (etc.) the user's submission anyway so that it could be stored and moved around without grave risk. Which obviously introduces issues when communities with different salt are merged... and so on :-)

Mark Morgan Lloyd wrote

Reinier Olislagers wrote: >> Subject to a maximum user name length of 31 characters on Firebird. The >> manual suggests that PostgreSQL has a limit of 64.

<snip>

True, but I thought that a detail relating to something that people were likely to do comparatively infrequently (i.e. user management by a custom app rather than by something like Flamerobin) was worth getting into the record.

FlameRobin etc have the exact same limitations though...

Anyway, hope your project goes well... and it's always good to have somebody tell about his experiences with a new technology so missing/incomplete docs etc can be fixed.