[solved]: How to Create DB with an User with ADMIN Grant Option

[musicones]

Hi,

I'm working on an app with an Firebird DB. I want the app to do the following:

1. Check if DB exists
2. Create new User "ADMIN" with GRANT OPTION in Security.fdb
3. Create DB with the new User "ADMIN"
4. GRANT OPTION to "ADMIN" User for the new created Database
5. Create further Users using the new User "ADMIN"

Firebird Version used is 2.5.x. Server / not-Embedded.

Ok, lets see. It's the first start and database does not exist. I check if the user ADMIN exists, lets pretend user doesn't exist. I want to create the user with GRANT OPTION. How to do that?

Solution 1: FB 2.5 lets create one Users using DSQL, but FBConnection needs a existing database. Database isn't created yet because it needs to be created with the user ADMIN, who doesn't exist at the moment.

Solution 2: Service API. Ich use FBLib Components. It has a Service Component, but the AddUser method has not possiblity to Grant Option.

Solution 3: I use FBLib Components without Grant Option, create the database with the new user ADMIN, after that I Grant ADMIN Option to Security.fdb and new created Database with SYSDBA to the new user "ADMIN". I tried this method in a small test app. I get no errors, but it doesn't work.

I couldn't find a FB-Component that supports the Service API adding a User with Grant Option anlegen können, maybe i'm wrong and there is an alternative solution.

I could provide an empty database but I don't want to do that.

Thanks for your interest and help.

Antonio

[BigChimp]

Antonio,

Informative post... though a tricky problem.
Solution 4: Perhaps you could patch FBLib to support with GRANT OPTION? I suppose you got the FBLib code from the tiOPF repository?

Solution 5: Could you create a dummy database, then use solution 1, and DROP the dummy database again?

[ludob]

Create the db as SYSDBA, connect to it as SYSDBA, create user ADMIN1 (ADMIN is a keyword!!!) and grant admin rights to the user.
With IBConnection:

Code: [Select]

  IBConnection2.HostName:='localhost';
  IBConnection2.DatabaseName:='b:\test.gdb';
  IBConnection2.UserName:='SYSDBA';
  IBConnection2.Password:='masterkey';
  IBConnection2.CreateDB;
  IBConnection2.Connected:=true;
  IBConnection2.ExecuteDirect('CREATE USER ADMIN1 PASSWORD ''secret''');
  IBConnection2.ExecuteDirect('GRANT RDB$ADMIN TO ADMIN1');
  IBConnection2.Transaction.Commit;
  IBConnection2.Connected:=false;
  IBConnection2.UserName:='ADMIN1';
  IBConnection2.Password:='secret';
  IBConnection2.Connected:=true;
  IBConnection2.ExecuteDirect('CREATE TABLE test (id integer, name varchar(40))');
  IBConnection2.Transaction.Commit;


[BigChimp]

@ludo, wouldn't the db then have SYSDBA as owner instead of ADMIN1? (Haven't played with users in FB that much, so don't know)

[ludob]

wouldn't the db then have SYSDBA as owner instead of ADMIN1?

Yes, but ADMIN1 has full rights on the database. He can even drop the database if he connects with the RDB$ADMIN role:

Code: [Select]

  IBConnection2.UserName:='ADMIN1';
  IBConnection2.Password:='secret';
  IBConnection2.Role:='RDB$ADMIN';
  IBConnection2.DropDB;

To create new users the ADMIN1 account should be created with GRANT ADMIN ROLE. If the registered database owner is really that important, a simple drop create will solve that  Updated example:

Code: [Select]

  IBConnection2.HostName:='localhost';
  IBConnection2.DatabaseName:='b:\test.gdb';
  IBConnection2.UserName:='SYSDBA';
  IBConnection2.Password:='masterkey';
  IBConnection2.CreateDB;
  IBConnection2.Connected:=true;
  IBConnection2.ExecuteDirect('CREATE USER ADMIN1 PASSWORD ''secret'' GRANT ADMIN ROLE');
  IBConnection2.ExecuteDirect('GRANT RDB$ADMIN TO ADMIN1');
  IBConnection2.Transaction.Commit;
  IBConnection2.Connected:=false;
  IBConnection2.UserName:='ADMIN1';
  IBConnection2.Password:='secret';
  IBConnection2.Role:='RDB$ADMIN';
  IBConnection2.DropDB;
  IBConnection2.CreateDB;
  IBConnection2.Connected:=true;
  IBConnection2.ExecuteDirect('CREATE TABLE test (id integer, name varchar(40))');
  IBConnection2.ExecuteDirect('CREATE USER test2 PASSWORD ''secret''');
  IBConnection2.Transaction.Commit;

Also here the RDB$ADMIN role is needed to create users.

[musicones]

Thank you all,

first, i've checked the api, as possible to me, and it seems that the add user method is the only one existing and there are no further possibilities to work on that.

I worked on a test-application to solve that and refactored it after your replies, and see, it works in the end.
This test-application was based on my solution 3 but I didn't know that i had to commit the transaction after using FBConnection.ExecuteDirect. So i didn't get an error but also no changes in the database.

So this is my way to go in the end:

1. Create User "Admin" (even if ADMIN is a key word, it works because I use sql3 with double quotes) with FBService, so no FBConnection and no Database is needed, because it uses the FB Services API (also other components have this, like -unified interbase-). If you don't want to use is, as remarked before, just drop and recreate database with User 'Admin' after the user was created.

2. Create Database with the new "Admin" User.

3. Login as "SYSDBA" with RDB$ADMIN Role (thanks to the replies with example code), alter User and give GRANT OPTIONS (FBConnection.ExecuteDirect('ALTER USER "Admin" GRANT ADMIN ROLE') and FBConnection.ExecuteDirect('GRANT RDB$ADMIN TO "Admin" WITH ADMIN OPTION');
Probably RDB$Admin role on login  isn't necessary for the for SYSDBA user.

4. Login as "Admin" with RDB§ADMIN role and do whatever the the SYSDBA usually does. For me, I need to create further User who have restricted access in my application.

As stated before you wouldn't need to create the Database with the "Admin" User to have the rights like an SYSDBA in FB 2.5 anymore, as there is this new RDB$ADMIN role that gives all needed rights to an user you like. But for me it is important to have the "Admin" as database owner.

So i just post this as summary and to thank for your help. No I can go on writing my app.

My regards
Antonio