DataMosaicDocs · Administration
Read-only access to your systems
How Mosaic makes sure it only ever reads, and the read-only login to give it.
#The promise
Mosaic reads your systems and compares what they say. It has no reason to change one, ever.
The systems it is pointed at are your live POS, your ERP, your warehouse. So "only reads" is not left to good behaviour. It is enforced in three layers, and the third is yours.
#Layer 1: the query is checked
A statement that is not a read is refused, twice: in the editor as you type it (the editor reverts, under the heading "This is not a read"), and again on the server after every parameter has been filled in, so a value like 1; DROP TABLE sales cannot slip past.
Refused: INSERT, UPDATE, DELETE, MERGE, TRUNCATE, DROP, CREATE, ALTER, GRANT, EXEC, CALL, COMMIT and the rest of the family; SELECT … INTO a table; MySQL's INTO OUTFILE; OPENQUERY and OPENROWSET, which run a statement hidden inside a string; a SQL Server batch that opens with a procedure name; and anything that would switch a session out of read-only.
Allowed, because they change nothing: SET NOCOUNT ON, DECLARE, SELECT … INTO @variable, SELECT … INTO #temp and INTO TEMP.
MongoDB pipelines are refused if they contain $out or $merge, the two stages that write.
#Layer 2: the database is told
A keyword list cannot see everything. A function can write while looking like a SELECT. So every run also asks the database itself to refuse writes on the session Mosaic opens:
| Database | How |
|---|---|
| PostgreSQL, Vertica | The session is read-only: SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY |
| MySQL, MariaDB | SET SESSION TRANSACTION READ ONLY |
| ClickHouse | The readonly setting on every query |
| Oracle | SET TRANSACTION READ ONLY |
| SQL Server | The query runs in a transaction that is always rolled back |
| Snowflake | The query runs in a transaction that is always rolled back |
| SQLite | PRAGMA query_only, even if the file was opened for writing |
On a database that only speaks another's protocol — Redshift, CockroachDB, a MySQL older than 5.6 — the database may not accept the statement. There Layer 1 and Layer 3 stand without it, and the run is not stopped.
#REST APIs
GET, HEAD and OPTIONS read, and run. PUT, PATCH and DELETE exist to change things, and are refused. POST runs only once somebody has ticked This POST only reads on the source — for a search or query endpoint that answers with data and changes nothing. A POST source set up before that question existed keeps running, with a warning on every run until it is confirmed.
#Layer 3: a read-only login
The strongest guarantee is a login that cannot write in the first place.
Layers 1 and 2 are Mosaic's; this one is yours, and it is the one an auditor accepts, because it holds whatever any software does. Create a login for Mosaic that can read the tables your checks use and nothing else. The grants to run, engine by engine, are on The read-only login. That page is written to be forwarded to whoever administers the database — usually not the same person who builds the Check — and it also answers what Mosaic will read, where it connects from, where the password ends up, and how to revoke the login again.
#What Mosaic does write
Nothing into your systems. What it can send outward is results, and only where somebody set that up: a schedule's email, file or webhook, and a page published for readers. Those are described under Outputs and Published pages, and each one is in the audit trail.