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

1

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"

c#
.net
firebird
firebird-3.0
asked on Stack Overflow Apr 24, 2019 by Muhammad Waqas • edited Apr 24, 2019 by Arioch 'The

1 Answer

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.

answered on Stack Overflow Apr 24, 2019 by Arioch 'The • edited Apr 24, 2019 by Arioch 'The

User contributions licensed under CC BY-SA 3.0