| Subject | Re: Firebird ODBC driver: connection to an embedded server by using security2.fdb |
|---|---|
| Author | patrick_marten |
| Post date | 2012-02-04T11:24:59Z |
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
- 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:
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?
========
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?
========
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.
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?
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?
What if only some new fields have been added? Do I have to do anything regarding the roles in such a case?
Kind regards,
Patrick
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,you replied with
>> is that correct? I was not able to create one with another user.
> No. You should be able to connect with a different user as well. ItI've tried to set it up again and apparently something was wrong with my previous attempts. Now I can connect with every combination:
> might fail for some reason because the specified user doesn't have
> sufficient privileges on database objects ...
- 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'tI think I get it now, but one question regarding this remains:
>> checked against the security database, but SQL privileges are checked
>> for the provided user when it comes to accessing tables, views etc ...
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?
========
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?
========
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.
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?
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?
What if only some new fields have been added? Do I have to do anything regarding the roles in such a case?
Kind regards,
Patrick
--- In firebird-support@yahoogroups.com, Thomas Steinmaurer <ts@...> wrote:
>
> > 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.
>
> 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 ...
>
> > If that's not correct:
> > what can be the reason for the problem I have when trying to create a connection with a different user.
>
> Don't know without telling us the error message you get.
>
> > If that's correct:
> > the only way would be to grant SYSDBA read only rights, correct? Is that possible at all?
>
> No.
>
>
> --
> With regards,
> Thomas Steinmaurer
>
> * Upscene Productions - Database Tools for Developers
> http://www.upscene.com/
>
> * My Blog
> http://blog.upscene.com/thomas/index.php
>
> * Firebird Foundation Committee Member
> http://www.firebirdsql.org/en/firebird-foundation/
>
>
>
> > --- In firebird-support@yahoogroups.com, Thomas Steinmaurer<ts@> wrote:
> >>
> >>> how can I get Firebird ODBC driver to connect to an embedded server by using the security2.fdb?
> >>>
> >>> Is there any reason, why the security2.fdb is being ignored in that case at least by default?
> >>>
> >>> That way the ODBC connection can only be created with SYSDBA as user and there is no way to make the connection read only. I mean yes, you can check the checkbox "read" when creating the connection, but the end user can uncheck it at any time and gat a write access to the DB...
> >>>
> >>> For the ODBC connection to a normal server he could do the same of course, but since in that case the connection cen be created with a user, who has read rights only, it doesn't matter if the checkbox gets unchecked.
> >>>
> >>> Why can't it work the same with embedded server? That doesn't make sense to me... Can I accomplish it in any way?
> >>
> >> 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 ...
> >>
> >>
> >> --
> >> With regards,
> >> Thomas Steinmaurer
> >>
> >> * Upscene Productions - Database Tools for Developers
> >> http://www.upscene.com/
> >>
> >> * My Blog
> >> http://blog.upscene.com/thomas/index.php
> >>
> >> * Firebird Foundation Committee Member
> >> http://www.firebirdsql.org/en/firebird-foundation/
> >>
> >
> >
> >
> >
> > ------------------------------------
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://www.firebirdsql.org and click the Resources item
> > on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> > Also search the knowledgebases at http://www.ibphoenix.com
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > Yahoo! Groups Links
> >
> >
> >
>