Database Best Practices
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 Comedy
to 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.
Updated about 1 year ago