Explainer
SQL Historian database permissions: the full six rights
Short answer
Grant the Ignition historian login all six rights — SELECT, INSERT, UPDATE, DELETE, CREATE and DROP — but only inside a dedicated historian schema or database. UPDATE is required because Ignition maintains rows in sqlth_partitions and sqlth_te even though logged sample values are append-only.
On this page
- What permissions does the Ignition historian login need?
- Which historian operation uses each right?
- Why is UPDATE required when logged samples are append-only?
- What breaks if the login has only CREATE, INSERT and SELECT?
- Where should the six rights be granted?
- Can the login just be a database owner or server administrator instead?
- How do I verify the grants before trusting the historian?
- Questions asked
The Ignition historian has no storage of its own: it connects to an external SQL database and uses it as the time-series store, so the database login defines what the historian can actually do. The working set is six rights — SELECT, INSERT, UPDATE, DELETE, CREATE and DROP — granted to a dedicated login inside a dedicated historian schema or database. The one engineers drop is UPDATE, because logged samples are append-only; the failure arrives later, in metadata maintenance and partition lifecycle, not in the write path. This page gives the full set, what each right does, where to grant it, and how to prove all six before the first partition rollover.
What permissions does the Ignition historian login need? #
Six rights inside one dedicated historian schema or database: SELECT, INSERT, UPDATE, DELETE, CREATE and DROP, granted to a login used by nothing else. That is the complete working set, and anything less passes commissioning and fails a later lifecycle step.
Ignition's documentation for the historian module describes connecting it to an external database and turning that database into the tag historian, and its database-connection pages note that each connection needs an account whose rights the database platform enforces. Permissions are therefore not an afterthought to the deployment — they are the boundary the historian operates inside. When a grant is missing, the failure surfaces as a database denial on a specific operation, which is exactly the evidence you use to size the grant.
Which historian operation uses each right? #
SELECT reads samples and metadata, INSERT adds rows, UPDATE maintains metadata records, DELETE removes rows during pruning, CREATE builds tables and new partitions, and DROP removes the tables behind retired partitions.
| Right | Where it applies | What the historian does with it |
|---|---|---|
| SELECT | Historian tables | Reads stored samples, tag metadata and partition records. |
| INSERT | Sample and metadata tables | Adds history samples and new metadata rows. |
| UPDATE | Metadata tables | Amends rows in sqlth_te and sqlth_partitions that change after creation. |
| DELETE | Historian tables | Removes old rows when pruning runs. |
| CREATE | Historian schema or database | Creates the table set and each new partition table. |
| DROP | Historian schema or database | Removes retired partition tables and other managed objects. |
Two documented tables carry the metadata: per Ignition's database table reference, sqlth_te stores the metadata for each historical tag, and sqlth_partitions defines the partitions — each one a table — used to store data, the time frames they cover, and each partition's duration. Where those generated tables end up on an Edge gateway is covered in where the Ignition Edge tag historian writes its data.
Why is UPDATE required when logged samples are append-only? #
Because the historian updates metadata, not samples: rows in sqlth_te and sqlth_partitions change after they are first written, and UPDATE is the right those changes run under.
Sample values land in the generated partition tables and are never rewritten, so the append-only intuition is correct about the data and wrong about the workload. When a tag's logging configuration changes, its row in sqlth_te changes with it. When one partition closes and the next opens, the records in sqlth_partitions that define the time frames and durations are amended. The inspection trap is predictable: an engineer opens a sample table, sees nothing but inserts, and concludes UPDATE is dead weight — the operations that need it are running one table over, in metadata the sample table never shows.
What breaks if the login has only CREATE, INSERT and SELECT? #
Provisioning and first writes succeed, then the first partition rollover, pruning run or tag edit fails: those three rights cover half of the six the historian uses.
Take a worked example, illustrative but typical of commissioning. The test lets the historian build its tables (CREATE), logs one known value (INSERT) and reads it back (SELECT), so it exercises 3 of the 6 required rights. 6 − 3 = 3 rights — UPDATE, DELETE and DROP — go untested, which is 3/6 = 50% of the grant set. The system is signed off on half the evidence, and the first denial lands on the first lifecycle event that needs one of the untested three.
Each missing right fails a different operation later. Without DELETE, pruning cannot clear old rows. Without DROP, retired partition tables cannot be removed. Without UPDATE, metadata maintenance fails while logging quietly continues, which is why the fault reads as intermittent. Backend-specific faults sit downstream of the grant: when table creation itself fails over ODBC, historian table creation failing on MySQL over ODBC covers that path.
Where should the six rights be granted? #
In a dedicated historian database or schema, assigned to a dedicated login: the account gets all six rights there and no rights anywhere else on the server.
The six rights include CREATE and DROP — data-definition power. Confined to a historian-only database, that power can only touch historian objects. Granted inside a shared application database, the same login can create, alter and drop objects other applications depend on. This is least privilege as the IEC 62443 series frames access control: an account receives only the privileges its function requires, which for this login is six rights in one scope.
The mechanics differ by platform: MySQL takes the whole set in one GRANT statement against a database, SQL Server grants per schema and separates object rights from ownership. The target is the same everywhere — one scope, one owning login, six rights. When a denial does appear, read the platform's native error first: it tells you whether the failed operation was against a table or against a schema-level object, which is the difference between a data right and a definition right.
Can the login just be a database owner or server administrator instead? #
It works, and it is more than the historian needs: ownership or server administration adds rights far beyond the six, so keep the login scoped to the dedicated database and grant only those six.
If your platform's role model makes a precise grant awkward, the dedicated database still contains the exposure — an account that owns one database holding only historian objects is a small problem next to a server administrator. The auditable minimum stays six rights in one scope, and the first question in any access review of the account is what the extra rights are for.
How do I verify the grants before trusting the historian? #
Run each of the six operations once as the historian login itself, not as an administrator: six successful operations confirm the set, and any denial names the right that is missing.
- Open the database connection the historian uses and identify the exact login; every test runs as that account, never as an administrative one that masks a missing grant.
- Confirm the historian's objects — sqlth_te, sqlth_partitions and the generated partition tables — sit inside the intended schema or database.
- Grant the six rights in that scope using the platform's own privilege model.
- Reconnect the database connection so the historian's active sessions run under the corrected grants.
- Force a known tag value, log it, and read it back from a trend or query; that proves INSERT and SELECT end to end.
- Keep the database's error log open through one partition rollover and one pruning run — no denials there means UPDATE, DELETE, CREATE and DROP are proven, not assumed.
Pruning is also the operation that leans hardest on DELETE and DROP together, and the retention settings behind it are covered in configuring Ignition pruning for old data partitions. Where two historian connections share one database, why two Ignition historian connections share one pruning boundary explains the boundary they inherit.
When all six operations succeed under the historian login, the permission question is closed. Anything that fails afterward is a configuration fault, not a rights fault.
Questions asked
Should the six rights be granted per table or at the schema level?
At the schema or database level. Partitions are tables that did not exist when you granted, so per-table grants are stale the moment the historian creates the next partition, and the rollover fails with a denial on a table your grant never covered. A scope-level grant covers every object the historian generates afterward.
Can the historian share a SQL login with other plant applications?
No. A shared login couples the historian to every other application's failures, makes it impossible to attribute changes in the audit log to one system, and forces you to break other applications whenever the historian's access must be revoked or rotated. Give the historian its own login in its own scope.
What does a missing UPDATE right look like when it fails?
Sample logging keeps working. The denial lands in the database's native error log against the metadata row being maintained — in sqlth_te or sqlth_partitions — not against the sample being written, so the fault looks intermittent until you read the object name in the error.
Does the historian login need anything beyond the six object rights?
Connection rights. The login must be allowed to connect from the Ignition server's host, and host-based allowlists and firewall rules count as part of the deployment. Those govern whether a session opens at all; the six rights govern what the session may do once it is open.