Security Database / "Local Users"

[Kevin Stanton]

Greetings!

I am wondering if there is a way to determine from a query what security database is being used for a specific database.

I've also tried to set up "local users" and have been unsuccessful so far.  

This does not appear to work (database.conf file) :

ledb = e:\fb3\lumber\lumber_inv.fdb
{
  SecurityDatabase = ledb
}

I need to do some more testing here.

Thanks in advance!

Kind regards,

Kevin

[Dimitry Sibiryakov]

Kevin Stanton wrote 11.05.2023 13:17:
> I am wondering if there is a way to determine from a query what security
> database is being used for a specific database.

MON$DATABASE.MON$SEC_DATABASE.

> I've also tried to set up "local users" and have been unsuccessful so far.
>
> This does not appear to work (database.conf file) :
>
> ledb = e:\fb3\lumber\lumber_inv.fdb
> {
>   SecurityDatabase = ledb
> }

What error do you get?

--
WBR, SD.

[Mark Rotteveel]

On 11-05-2023 13:17, Kevin Stanton wrote:
> I am wondering if there is a way to determine from a query what security
> database is being used for a specific database.

Not entirely, but you can query

```
select MON$SEC_DATABASE from MON$DATABASE
```

This will return Default (using the security database referenced in
firebird.conf setting SecurityDatabase), Self (configured in
databases.conf to refer to itself) or Other (configured in
databases.conf to refer to a different database)

> I've also tried to set up "local users" and have been unsuccessful so far.
>
> This does not appear to work (database.conf file) :
>
> ledb = e:\fb3\lumber\lumber_inv.fdb
> {
>   SecurityDatabase = ledb
> }
>
> I need to do some more testing here.

What do you mean with "this does not appear to work"?

If you configure a security database like that, you will first need to
attach with an embedded connection as SYSDBA to create the first user
(which will initialize the DB with the relevant tables), otherwise it is
impossible to attach remotely as authentication will fail for lack of
existence of the required tables. And if you want to be able to create
users over a non-embedded connection, then you'll need to ensure the
user is SYSDBA, or is a user that has the RDB$ADMIN role, or - Firebird
4.0 and higher - the user needs to have the USER_MANAGEMENT system
privilege.

Mark
--
Mark Rotteveel

[Kevin Stanton]

Hi Dimitry,

Thanks kindly for the fast response.

Here is the error:

Here is my complete database.conf:

# ------------------------------
# List of known databases
# ------------------------------

#
# Makes it possible to specify per-database configuration parameters.
# See the list of them and description on file firebird.conf.
# To place that parameters in this file add them in curly braces
# after "alias = /path/to/database.fdb" line. Example:
# big = /databases/bigdb.fdb
# {
# LockMemSize = 32M # We know that bigdb needs a lot of locks
# LockHashSlots = 19927 # and big enough hash table for them
# }
#

#
# Example Database:
#
employee.fdb = $(dir_sampleDb)/employee.fdb
employee = $(dir_sampleDb)/employee.fdb

#
# Master security database specific setup.
# Do not remove it until you understand well what are you doing!
#
security.db = $(dir_secDb)/security3.fdb
{
RemoteAccess = false
DefaultDbCachePages = 50

}

ledb = e:\fb3\lumber\lumber_inv.fdb
{
  SecurityDatabase = ledb
}

#
# Live Databases:
#

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/68816348-1afe-b570-da02-7fc48fe35d3a%40ibphoenix.com.

[Dimitry Sibiryakov]

Kevin Stanton wrote 11.05.2023 14:40:
> Thanks kindly for the fast response.
>
> Here is the error:

It is logical: you cannot set as a security database a database that wasn't
initialized as a security database because it lack tables that security database
must have inside to keep users.
Read the mentioned chapter.

tl;dr: Attach to it in embedded mode and create a user.

--
WBR, SD.

[Mark Rotteveel]

On 11-05-2023 14:40, Kevin Stanton wrote:
> Hi Dimitry,
>
> Thanks kindly for the fast response.
>
> Here is the error:

> image.png

As I mentioned in my earlier reply, this means that you haven't
initialised the database yet so it *is* a security database. To do that,
you need to connect with an embedded connection and create a user.

Mark
--
Mark Rotteveel

[Kevin Stanton]

Thanks Mark and Dimitry.  I appreciate it.

Kevin