Cannot create database user with an admin user

[Thea Braun]

Hi,

I have a problem, maybe anyone can help me.

I create a new database with grant admin role. I tried to create with this user new database use (CREATE USER testuser password 'test1234';) but I receive this error message:

SQL Error [335544352] [HY000]: Resource Exception. add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352]¶Reason: add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352], error code: HY000¶Reason: add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352]¶Reason: add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352]

Is this scenario supported by firebird? Or can only the sysdba create new database user?

I hope this is the right mailinglist for this problem.

Thanks, Thea

[Dimitry Sibiryakov]

24.01.2021 17:21, Thea Braun wrote:
> I create a new database with grant admin role.

Did you provide the role parameter with value RDB$ADMIN for the connection where you
tried to create a user?

--
WBR, SD.

[Mark Rotteveel]

On 24-01-2021 17:21, Thea Braun wrote:
> I have a problem, maybe anyone can help me.
> I create a new database with grant admin role. I tried to create with

> this user new database use *(CREATE**USER*testuser password 'test1234';)

> but I receive this error message:
>
> SQL Error [335544352] [HY000]: Resource Exception. add record error; no
> permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC
> error code:335544352]¶Reason: add record error; no permission for INSERT
> access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352],
> error code: HY000¶Reason: add record error; no permission for INSERT
> access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error
> code:335544352]¶Reason: add record error; no permission for INSERT
> access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352]
>
> Is this scenario supported by firebird? Or can only the sysdba create
> new database user?

To be able to create a user, you need to have the admin role in the
security database *and* the current database, and you need to specify
the RDB$ADMIN role when connecting to the current database (this
restriction will be lifted in Firebird 4, then you'll only need the
admin role in the security database).

So, make sure your user has admin rights in the security database:

- As SYSDBA (or another user with admin rights in the security
database), enable the security database admin role for your user:

ALTER <your-user> GRANT ADMIN ROLE;

- Give your user the RDB$ADMIN role in the current database

GRANT ROLE RDB$ADMIN TO <your-user>;

- Connect to that database with your user and specify the RDB$ADMIN role
on connect

- Execute the desired CREATE USER statement.

See also
https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-security-rdbadmin05

Mark
--
Mark Rotteveel

[Thea Braun]

Hi all,

thanks for replying on my questions. I tried the command "GRANT ROLE RDB$ADMIN TO <your-user>;" but I got the error:

ERROR: SQL Error [335544634] [42000]: Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 12; RDB$ADMIN [SQLState:42000, ISC error code:335544634].

So I tried the following, I write down my steps:

1. Connect with SYSDBA to EMPLOYEE.FDB

2. Execute the following commands:

CREATE USER MYUSER password '1234';
ALTER USER MYUSER GRANT ADMIN ROLE;
GRANT RDB$ADMIN TO MYUSER;

3. Connect with MYUSER to EMPLOYEE.FDB

4. Execute:
CREATE USER testuser password '1234';
ERROR:

SQL Error [335544352] [HY000]: Resource Exception. add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352]¶Reason: add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352], error code: HY000¶Reason: add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352]¶Reason: add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352]

Am I missing one step in beetween? What does this mean " Connect to that database with your user and specify the RDB$ADMIN role
on connect ". Or do I have to start somewhere else then on this default database EMPLOYEE.FDB  from FIREBIRD, as I understood.

Thanks again,
Thea

[Thea Braun]

Hi all,

thanks for replying on my questions. I tried the command "GRANT ROLE RDB$ADMIN TO <your-user>;" but I got the error:

ERROR: SQL Error [335544634] [42000]: Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 12; RDB$ADMIN [SQLState:42000, ISC error code:335544634].

So I tried the following, I write down my steps:

1. Connect with SYSDBA to EMPLOYEE.FDB

2. Execute the following commands:

CREATE USER MYUSER password '1234';
ALTER USER MYUSER GRANT ADMIN ROLE;
GRANT RDB$ADMIN TO MYUSER;

3. Connect with MYUSER to EMPLOYEE.FDB

4. Execute:
CREATE USER testuser password '1234';
ERROR:

SQL Error [335544352] [HY000]: Resource Exception. add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352]¶Reason: add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352], error code: HY000¶Reason: add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352]¶Reason: add record error; no permission for INSERT access to TABLE PLG$SRP_VIEW [SQLState:28000, ISC error code:335544352]

Am I missing one step in beetween? What does this mean " Connect to that database with your user and specify the RDB$ADMIN role
on connect ". Or do I have to start somewhere else then on this default database EMPLOYEE.FDB  from FIREBIRD, as I understood.

Thanks again,
Thea

søndag 24. januar 2021 kl. 17:31:32 UTC+1 skrev ma...@lawinegevaar.nl:

[Dimitry Sibiryakov]

26.01.2021 11:46, Thea Braun wrote:
> *Am I missing one step in beetween? *What does this mean " Connect to that database with
> your user *and specify the RDB$ADMIN role
> on connect* ". Or do I have to start somewhere else then on this default database

> EMPLOYEE.FDB  from FIREBIRD, as I understood.

You'd better to start from Firebird Quick Start Guide. Search for keyword "role".

--
WBR, SD.

[Mark Rotteveel]

On 26-01-2021 11:46, Thea Braun wrote:
> Hi all,
> thanks for replying on my questions. I tried the command "GRANT ROLE
> RDB$ADMIN TO <your-user>;" but I got the error:
>
> ERROR: SQL Error [335544634] [42000]: Dynamic SQL Error; SQL error code
> = -104; Token unknown - line 1, column 12; RDB$ADMIN [SQLState:42000,
> ISC error code:335544634].

I'm sorry, I made mistake typing up my answer. It should be GRANT
RDB$ADMIN TO <your-user>; (so without ROLE).

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 26-01-2021 11:46, Thea Braun wrote:

> *Am I missing one step in beetween? *What does this mean " Connect to
> that database with your user *and specify the RDB$ADMIN role
> on connect* ". Or do I have to start somewhere else then on this default

> database EMPLOYEE.FDB  from FIREBIRD, as I understood.

Yes, the step you're missing is "Connect to that database with your user
and specify the RDB$ADMIN role on connect", specifically the "specify
the RDB$ADMIN role on connect" part.

For example, from ISQL, you'd would either specify the `-role` switch on
the command line, or include the ROLE clause in the CONNECT command:
CONNECT <your-database> USER <your-user> PASSWORD <your-password> ROLE
RDB$ADMIN;

If you use something else to connect, check its documentation how to
specify the role.

Mark
--
Mark Rotteveel

[Thea Braun]

Hi Mark,

thanks a lot, that was the missing step :-) Now it works!

Hi SD,

thank you too :-)

I was really impressed how fast you both answered me.

Have a nice day,

Thea