Thomas? Anyone else? :) I would like to get those 3 parts sorted and need some help here...
--- In [email protected], "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? > > > ======== > 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 [email protected], 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 [email protected], 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 > > > > > > > > > > > >
