Child table creation fails silently if user does not have admin permissions for the schema containing the parent table

[mdmelin]

Bug Report

Description

Thanks for all your work on datajoint! I noticed some strange behavior when trying to define dependent tables.

When creating a new table inside my own schema, I would like to be able to inherit from tables within a parent schema. However, this does not appear to work unless I have admin level privileges on the parent schema. This makes it hard for non-admin users to create their own tailored analysis schemas that might inherit tables from a parent schema shared by a lab.

When I run the code with the table definition, no error is thrown, but a SQL query reveals that the table is never created. When trying to query the table later, datajoint will then raise an error saying it does not exist.

I am not sure if this is a problem related to datajoint, or if I need to grant additional permissions on the database side of things. We're currently only granting SELECT and INSERT to the main schema for non-admin users.

Reproducibility

Include:

• Windows 10
• Python 3.11.5
• MySQL Version 8.0.35
• MySQL Deployment Strategy: Amazon RDS
• DataJoint Version 0.14.1

Code to reproduce problem

Here is the code to reproduce the issue

Expected Behavior

When running the attached code, the Desk table should be created. However, this silently fails and it is not created on the database. Modifying the Desk definition to not include a parent table fixes the issue.

Additional Research and Context

• Table dependencies from different schemas works fine when ALL PRIVILEGES are granted on both schemas

[dimitri-yatsenko]

Thank you. Yes, the correct behavior is to raise an error.

[mdmelin]

Hi @dimitri-yatsenko, thanks for clarifying. Are there permissions I can change that would make it possible to inherit from the main schema without granting admin-level permissions (e.g. DROP)? Thanks!

[dimitri-yatsenko]

You can have another user with CREATE privilege run the code to create the table. Then the user with SELECT and INSERT privilege can use the code.

[mdmelin]

I did some poking around and ended up giving the non-admin user the REFERENCES privilege (giving CREATE allowed the user to create the table one time, but upon dropping it, the table could not be recreated under the same name). This appears to be sufficient for developing a personal schema with full permissions that references a lab/collaboration-wide schema with only SELECT, INSERT, and REFERENCES privileges granted.

Do you think there are any security risks giving this REFERENCES permission to all users on the lab-wide database? As far as I can tell, all it allows is foreign key references to the database.

[dimitri-yatsenko]

Oh, yes, that's correct. REFERENCES should be included for the core tables where other tables can reference.

[mdmelin]

Great, thanks