How to create Firebird database user and grant some privileges to that user

Question

I am creating a firebird database dynamically using SYSDBA. I want to create a user with some privileges and then open this database using the new user. Can anyone help, please?

var csb = new FbConnectionStringBuilder(@"DataSource=localhost;User=SYSDBA;Password=mw;Database=test.fdb;") { Pooling = false };

FbConnection.CreateDatabase(csb.ConnectionString, 16384, false, true);

FbConnection myConn = new FbConnection(csb.ConnectionString);

try
{
    myConn.Open();
    string sql = "CREATE USER user123 PASSWORD 'user123pass';
    FbCommand cmd = new FbCommand(sql, myConn);
    cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
    error = ex.ToString();
}
finally
{
    if (myConn.State == ConnectionState.Open)
    {
        myConn.Close();
    }
 }

When executing this code, it throws the following exception.

FirebirdSql.Data.FirebirdClient.FbException (0x80004005): add record error violation of PRIMARY or UNIQUE KEY constraint "INTEG_5" on table "PLG$SRP"

Arioch 'The · Answer 1 · 2019-04-24T14:38:59.023

1

violation of PRIMARY or UNIQUE KEY - that means you are trying to insert a row into a table that already exists.

in your specific case, the user (by SRP plugin) with such a name was already created, and you can not create two users (by one and the same plugin) with the same name.

Check users existing: https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-pseudo-users.html

 select * from sec$users

Then either drop user before re-creating him, or set some new, not yet used user name

P.S. note that by default users are created in the server instance, so it is not like you create one and the same user for every database again and again. Once you created user - it exists ON THE SERVER for all the databases there. This is default configuration albeit it can be overrode using Firebird's databases.conf file. Sadly, you can not change this configuration by your application's connection string, while you are creating your new databases.

P.P.S. personally I would not create the database command by command but rather would restore the ready-made database from pre-prepared backup file. YMMV.

edited Apr 24 '19 at 14:38

answered Apr 24 '19 at 09:35

Arioch 'The

15,799
35
62

Thanks for your help it works, so is it possible to grant some privileges to the user like CREATE, SELECT, etc. for that created database only, and that user is unable to perform any operations on any other database.? – Muhammad Waqas Apr 24 '19 at 09:58
By default users should not have grants on already existing objects in other databases. But, at least in Firebird 2 users could create NEW databases or new OBJECTS (like tables, procedures, etc) in other databases. I do not know if that changed in FB3, I am not versed with it. See Release Noted on FB3, what changed with regard to user grants. Anyway that is DIFFERENT question than the one you asked and which now works. – Arioch 'The Apr 24 '19 at 10:02
1

Users can't execute `select * from PLG$SRP` unless they are connected to the security database. – Mark Rotteveel Apr 24 '19 at 13:05
The OP could use `create or alter user` instead of `create user`. – Mark Rotteveel Apr 24 '19 at 13:07
....with a chance he would screw some other database on the computer that relied upon that user as it was before the altering. Well, it is possible, it it is risky habit to edit data just because you can edit data. – Arioch 'The Apr 24 '19 at 14:40

How to create Firebird database user and grant some privileges to that user

1 Answers1