Sharing data to other users?



I understand that we can set Record Level access in a Role. But is it possible to share data to other users so that they can also update these records?



One way would be with triggers and having separate permissions on the tables.



How would you suggest one would go about this? I’m using MySQL database.



I have something somewhat similar.

  1. Table Foo. Define table Foo using Schema. Under Roles/Access, put in the service = Database and the component = Foo and give the role permissions to POST.

  2. Table Bar. Define table Bar using Schema. Under Roles/Access, put service = Database and the component = Bar and give the other role permissions to GET.

  3. In MySQL, create an INSERT trigger on Foo to copy appropriate columns and values of data to the Bar table for its reading.

You might need UPDATE and DELETE triggers too, depending.

I do something similar where the specific transactional info corresponds to Foo and there’s a summarized leaderboard that all can read, Bar.



I’m looking into this. Just have one concern for now. If there are millions of records, using triggers might affect performance will it not?


Also, is it possible to do something like this:

Where is_shared is a stored procedure/function that checks if the user has access to the record based on record_id? Then I can have a table where I can enter relationships between records and users.


DB Function in Schema -> Table -> Field?

The performance-related effects of the trigger(s) could be minimal. Depends upon a few things.

If you don’t know how the index design would need to support this technique, you might find yourself wanting to benefit from an experienced database person with or without this trigger-based technique.

You could also implement what you are interested in here with event scripts (and still have your database performance problems if you don’t know how to index well…).

DB Function in Schema -> Table -> Field?