Patrick,

> Thomas? Anyone else? :)
>
> I would like to get those 3 parts sorted and need some help here...

Things are getting hard to follow, if top-posting is used in replies.
See my comments inline ...

> --- In firebird-support@yahoogroups.com, "patrick_marten"<patrick_marten@...> wrote:
>>
>> Hello Thomas,
>>
>> sorry for the late reply. I got distracted by other things :(
>>
>> Now that some time has passed, I'm having trouble to remember the last status on this issue, unfortunately.
>> Trying to restore it in my memory, but the result is not the same, I believe. So I'm more or less starting from zero again.
>>
>> I've tried to divide the post into separate parts to keep some kind of structure...
>>
>>
>> ========
>> Part 1:
>> ========
>> To my question
>>
>>>> creation of a ODBC connection to an embedded server works with SYSDBA only,
>>>> is that correct? I was not able to create one with another user.
>>
>> you replied with
>>
>>> No. You should be able to connect with a different user as well. It
>>> might fail for some reason because the specified user doesn't have
>>> sufficient privileges on database objects ...
>>
>> I've tried to set it up again and apparently something was wrong with my previous attempts. Now I can connect with every combination:
>> - SYSDBA + (correct / wrong password)
>> - any other users with random passwords
>> - and also without any users / passwords at all
>>
>> so in the end it comes down to what you said:
>>
>>>> Embedded works that way: The provided user/password combination isn't
>>>> checked against the security database, but SQL privileges are checked
>>>> for the provided user when it comes to accessing tables, views etc ...
>>
>> I think I get it now, but one question regarding this remains:
>> If I'm not totaly wrong, there is no real user management for the embedded solution. How can the provided user / his privileges be checked then?
>>
>> Is it enough to say
>>
>> CREATE ROLE READACCESS;
>> GRANT READACCESS TO SOMEUSERNAME;
>>
>> although the user "SOMEUSERNAME" doesn't really exist?

Yes. If you then use SOMEUSERNAME at connect time. But, it doesn't
automatically mean, that a user being a member of a role, automatically
inherits the role SQL privileges on database objects. You have to
provide the appropriate role name at connect time as well. While a user
can be a member of several roles, it only can connect/work in context of
a single role.

With embedded, the user/password combination isn't checked against a
security database, because embedded doesn't use any security database at
all, but providing a user name at connect time is important, because the
user/role information is used to enforce SQL privileges defined in the
database, even with embedded.

>> ========
>> Part 2:
>> ========
>> When I said "Now I can connect with every combination" above, I meant that the test connection works from the ODBC interface. When I try to access the "database" in Excel, I'm getting the error message "bad parameters on attach or create database CHARACTER SET ISO8859_1 is not defined". My database is created with this character set and that's also the character set of the ODBC entry.
>>
>> There are several topics on several forums regarding this issue, but most are pretty old and somehow there are tons of attempts to fix it and as far as I've seen no proper solution. Files are being copied back and forth, path variables etc...
>>
>> Can't believe it's that complicated and weird...
>>
>> In my case the end user has our application installed. It uses a firebird database. It can be installed as an embedded solution or as a client-server solution. At this point ODBC isn't relevant yet.
>> It can be set up additionaly, in case the end user needs some additional data for exports etc.
>>
>> How has it to be set up correctly, so that he can still use the "normal" application and also ODBC?

* How does your embedded deployment look like? Directory layout ...
* Do you explicitely tell the ODBC entry where to look for the embedded
engine DLL?
* What Firebird version do you use?

>> ========
>> Part 3:
>> ========
>> Assuming everything above is sorted and set up properly, so that also access via excel works. The roles become relevant. As far as I see, the user, which was used for creation of the database (the owner) automatically has full access / all privileges.

Correct. Additionally, if a user different to the database owner has
created the table, this user has full rights on that table as well.

>> If I add another user to security.fdb - let's call him "ODBCREADUSER", create a role "ODBCREADROLE" and grant this role to this user, it is not enough. I still need to grant the "select"-rights for desired tables to this role (GRANT SELECT ON CUSTOMERS TO ODBCREADROLE; etc.) - so far correct?

Correct. You need to define that the role is allowed to access the table.

>> If at some point I need to apply some changes to the structure of my database, i.e. add some new tables, the owner has full access / all privileges on the new tables automatically, but for the additional user "ODBCREADUSER" or better to say for the role used by that user, i.e. "ODBCREADROLE", it doesn't happen automatically, so I need to call "GRANT SELECT ON NEW_TABLE1 TO ODBCREADROLE;" explicitely for all new tables everytime I change the structure, right?

Correct.

>> What if only some new fields have been added? Do I have to do anything regarding the roles in such a case?

No, except you use fine-grained UPDATE privileges on particular table
fields and you want to extend that to new fields as well.

Again: Don't forget to provide the role at connect time.



With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/

Do you care about the future of Firebird? Join the Firebird Foundation:
http://www.firebirdsql.org/en/firebird-foundation/