One User Multiple Databases

[lcnittl]

Is there a way to allow one user to access multiple databases?

It is disallowed to create a user in DB2, with already taken username in DB1. Any way to still grant permissions/add DB1-user to DB2?

[khoivan88]

you can grant read-access for all users in one database to another one. What happens is that user can find chemicals in another lab as well. However, a full user permission (create, borrow, return, etc. ), I don't think is possible. I have a feeling that you want full access for 1 users for both DB1 and DB2?

[lcnittl]

I have a feeling that you want full access for 1 users for both DB1 and DB2?

Exactly! Hmm, then I have to look into it. Eventually, DB permissions are organized by the SQL server, so maybe I can allow that user to access multiple DBs.

Thanks for your answer!

[khoivan88]

would be interested when you find the answer! Could be a cool integration for more user permission levels!

[lcnittl]

TL;DR: Does not work to my knowledge.

What I did: Delete the user from mysql.users, create a new user in the second database. After making the new account (same username of course), the user can only access the second database. If an now recreates the user in the first DB, it is working in the first only.

I think DB access is stored in mysql.users or somewhere else in the mysql tables. Certainly one could grant access to another DB by running SQL commands, however, recreation of users would then revoke this access. So for now I did not try to tweak that.

[rudolphi]

Hi folks,

sorry for my late reply. I had this on my agenda for quite a while, as many users asked for it. However, there are substantial problems. Users in OE consist of two parts, an entry in the "person" table and the MySQL user with permissions generated according to what the user may do or not (some restrictions must be implemented on application level, though).

When being logged into database A, users != root have no access to other databases, which would be required to create/update the "person" entries in other databases and to know which permissions the MySQL user should have, not for just one database.

One would need a central coordination database, and permissions at root level to make changes to the users, quite complicated from my POV. My next OE-related project will be an interface to https://sciflection.com - allowing users to upload their experiments and also to import experiments from sciflection.com back to their own databases. In theory, I believe to have solved everything:-)

[khoivan88]

@rudolphi yeah, I agree, from my limited experience, I can already see the complications.

Sciflection looks cool. what is it about, Felix?

[rudolphi]

It took me a while to reply, as I was working hard to be able to have something "presentable" and not just a dream. The idea behind https://www.sciflection.com is to give people the option

• to share primary data when publishing,
• to make this data searchable by chemical structure, parameters and full-text,
• and to allow people to import a "recipe" into their own database, repeat it or create their own variations.
  If people contribute, it could become a free alternative to Scifinder or Reaxys one day, or to Organic Syntheses. It depends on what we make out of it.

Since a few months, users of Sciformation ELN can upload experiments and import others', now I have written an update for open enventory to achieve the same. Important: it is still beta, we need to guide users much more through the process, explain how everything works, etc. The files to add the interface to the newest version of OE can be found here, use at your own risk:
https://sciformation-demo.eu/intern/update_2020-05-03_sciflection_beta.zip
After login using root or an admin user, it will propose to create some new tables and columns that are necessary for the new features.

If you are courageous, you may want to try it with real experiments (if your bosses agree), otherwise I will set up a test server in the next weeks, so that you can upload some test data first. Here, I have some slides explaining how it works:
https://sciformation-demo.eu/intern/2007Sciflection/sciflection_short_v.1.pdf
It may not yet be perfect, but I think the process is already quite simple and robust - some users may feel better if there are some explanations and guidance, though. Tell me what you think about it and how we can make it better.

Best regards,

Felix

[khoivan88]

Hi @rudolphi . it is a really cool idea.

If I understand it correctly, it is similar to Zenodo but specialized for experimental chemistry?

I'm a bit busy at the moment with another project (making https://organicchemistrydata.org/) but I will spread your idea to my circle and have a more detailed look later :)!

Great idea for sharing your published research but also streamlined it into ELN. Some quick thoughts:

• Could we could add data from other databases such as OrgSyn?
• I think this is a centralized database that hosted by you? I assume your server is more than capable of handle data when more and more get deposited?

😄
Khoi

[rudolphi]

Hi @khoivan88,

there are certainly overlaps with Zenodo or similar platforms, but unlike using PDF as lowest common denominator, we want to show the data like it was written in the ELN, with as much structure (fields for parameters like temperature, MOLfiles, amounts, peaks in spectra) as possible. Uploading and downloading entries is based on JSON, just open
https://sciflection.com/performSearch?table=ElnReaction&UUID=1c2f0d62-adc3-499e-b505-78476785bce5&viewMode=1&format=jsonRaw
as example (you have to load it twice, as firstly a session cookie must be set).

Sciformation ELN is not limited to synthesis & catalysis, it serves as technical basis. In principle, other programs can integrate the signup and upload process as well, the new OE code should give a good demonstration of how it works. We will publish proper documentation when everything is sufficiently stable.

Having http://www.orgsyn.org data integrated would be amazing, and it would certainly be a leap ahead if one could search these protocols using structures or parameters like temperature. The data is so precious, and there is so much room for improvement in terms of findability and accessibility.

With regard to server performance and resources: I do not know how quickly usage and storage demand will go up. Currently, it is about as strong as typical VMs serving a university ELN, but I can quickly increase the power if needed. Costs are negligible.

Fingers crossed that it will be adopted by users, in both directions:-)

Felix

[khoivan88]

Felix, I think this is an amazing idea and implementation 😄 . I will definitely spread the word!!

[rudolphi]

Hi again, I added https://momentjs.com as Javascript date/time library and started to use PHP DateTime functions, so that displayed dates are now possible wth locale, i.e. if you log in in German language, you get German dates, otherwise yyyy-mm-dd like in Khoi's version. Maybe this is a step ahead in merging our two, then working a bit on improved UI, whatever comes next:-)
If you like, do test the files (requiring the newest release as base, also including fixes for Sigma and Oakwood):
https://sciformation-demo.eu/intern/update_2020-07-13_to_date_format.zip