Limiting Access with Views and Schemas

To limit resource access to only specific parts of the database on the connection level, we recommend you use a combination of Schemas, Views and Grants.

Example for Postgresql

Suppose we have a table of type films, and we want to grant limited access to only films of type Comedyto the user datasiv_user. We will do so below with the following steps:

We'll first create a Schema. Schemas enable autocomplete and schema inspection in DataSiv

CREATE SCHEMA datasiv_schema;

For example, the above table creates a Schema in postgresql;

Then we'll create a View. A view abstracts away complexity and access with virtual tables

CREATE VIEW datasiv_schema.`comedies` AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

https://www.postgresql.org/docs/current/sql-createview.html

For example, the above SQL statement creates a View on a films table to filter only on films that are of kind Comedy, use the above statement with

Finally, we'll use a Grant statement to grant access to our database user, datasiv_user, only Select permission to the view we've just created.

GRANT SELECT ON datasiv_schema.`comedies` TO datasiv_user;

To then limit access to a specific user or role, apply a Grant statement on the view created above

https://www.postgresql.org/docs/7.0/sql-grant.htm

Write Back in User Interface Mode

When enabling write back into production instances of a database, you may want to disable writing raw SQL queries altogether, and only enable changesets to be created in a user interface.

To do so, when creating a resource, toggle the Gui Only option to true, as illustrated below.

Then, you're able to make updates based on an object relational mapping.

You're able to do single inserts, updates/upserts, as well as bulk inserts, updates/upserts in the system.
Note that currently user interface mode is based on primary keys. This means that an update will fail if a primary key is not provided, either in single or bulk mode. Additionally, for single operations, only at most a single row will be affected, while for bulk operations, at most the number of entries in the bulk array will be updated.

Note that for bulk mappings, for the data attribute, the data array provided must exactly match the column names. For example, if your table has ArtistId and Name as columns, the bulk data array must have ArtistId and Name as the keys in the data array.