search

rudolphi / open_enventory

PHP/MySQL-based chemical inventory/Electronic Lab Notebook for chemistry
https://sourceforge.net/projects/enventory/
GNU Affero General Public License v3.0
9 stars 6 forks source link

One User Multiple Databases #14

Open lcnittl opened 4 years ago

lcnittl commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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

lcnittl commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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

Sciflection looks cool. what is it about, Felix?

rudolphi commented 4 years ago

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

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 commented 4 years ago

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:

😄 Khoi

rudolphi commented 4 years ago

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 commented 4 years ago

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

rudolphi commented 4 years ago

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