← Back to the journal

DataFocus · Journal

Connect MySQL, Oracle or SQL Server to OT Flash with a read-only account

OT Flash's r57 manual lists 26 external source types, from MySQL and Oracle to Hive and Iceberg. The secure path is a dedicated read-only account, a tested connection, and a scheduled import into OT Flash's own store. Here is each step, and what the manual leaves to you.

OT Flash connects to 26 external database types, including PostgreSQL, MySQL and MariaDB, Oracle, IBM DB2, SQL Server, ClickHouse, Hive, Iceberg, TiDB, OceanBase and SAP HANA, and it reads them the same way in every case: a JDBC-style connection, then a scheduled copy of the selected tables into OT Flash's own store. The secure way to connect one is to give OT Flash an account that can do nothing but SELECT on the tables you intend to analyze, test the connection from the New Data Source dialog before saving it, and import tables through a three-step wizard you can put on a schedule. The r57 user manual recommends the read-only account itself. This tutorial walks through each step, shows the account pattern on MySQL, and names what the manual leaves open so you can settle it before the first import runs.

Which databases are on the list, and what the list does not say

The data source chapter of the OT Flash user manual (r57, October 2026) lists under "Supported types include": PostgreSQL, MySQL (including MariaDB), Oracle, IBM DB2, GaussDB 100, SQL Server, Sybase, Greenplum, ClickHouse, Kudu, ElasticSearch, TeraData, MongoDB, Prometheus, Hive, Delta Lake, Iceberg, TiDB, SelectDB / Doris, OceanBase, SAP HANA, ClickZetta, Impala, DM8 (Dameng), ArgoDB and KingBase. Twenty-six entries, from the common commercial engines to the lakehouse formats and the domestic Chinese databases a mixed estate accumulates.

Read the list for what it is. It names types, not versions, drivers or configuration limits, and the manual gives no minimum version for any entry. If your Oracle or SQL Server is several releases old, a trial connection answers the question, not the list.

Two things on the same screen are easy to misread. "DataFocus system Doris" is the built-in store, not something you connect to. And sources are grouped as Database, API source and HTTP source, with local CSV, Excel and JSON files on a separate path; this post covers only the database path.

The connection form, field by field

Open the External Data Source tab and click New Data Source. The dialog asks for the following, in the manual's own terms:

FieldWhat to enterNote
Data Source TypeOne of the 26 typesDrives which driver is used
Source NameA label you will recognize laterAppears in the table list and import wizard; name it for the system, not the host
Server, PortHost and port of the databaseReachability from the OT Flash host is your job
DatabaseThe database to readOne source per database
SchemaOptional"If Schema is left empty, the default is used"
JDBC ParamOptional, "if needed"The manual does not list accepted parameters per driver
Username, PasswordThe account OT Flash will useThis is where the read-only account goes

The manual's wording is exact: "Click Connect to test the connection. When it succeeds, click Submit." Treat Connect as a gate, not a courtesy. Anything in JDBC Param should come from your vendor's JDBC documentation and is only trustworthy once Connect has succeeded with it.

One check precedes the form. OT Flash is self-hosted and runs on your own servers, so the first failure mode is routing, not credentials: the OT Flash host must reach the database host on the given port. The manual has no network checklist because nothing about it is product-specific. Open one firewall rule, from the OT Flash host range to the database port, and nothing else.

Why the account must be read-only, and what that does and does not protect

The manual's recommendation sits in a note under the dialog: create "a dedicated read-only database account for OT Flash, with access only to the databases and tables you want to analyze." The sentence does two jobs, and it pays to separate them.

First, scope. Whatever the account can see is the universe a sufficiently privileged OT Flash user can later import. Connect with a shared application login and that universe becomes everything the application can read, and possibly write. A dedicated account makes the database itself enforce the boundary, independent of any setting inside OT Flash, and gives your DBA one principal to audit, rotate or disable.

Second, direction. SELECT-only means a misconfiguration, a careless import or a compromised OT Flash host cannot alter the source. The import path copies data out; nothing in the manual describes writing back, and a read-only grant makes that true at the database layer whatever the application does.

On MySQL the standard pattern is a user plus a database-level SELECT grant, following the MySQL 8.4 reference manual for GRANT. Account name, host range, database and password are placeholders; the snippet is illustrative and was not run against OT Flash:

-- illustrative, not run against OT Flash
CREATE USER 'otflash_ro'@'10.0.0.%' IDENTIFIED BY '<strong password>';
GRANT SELECT ON sales_db.* TO 'otflash_ro'@'10.0.0.%';
SHOW GRANTS FOR 'otflash_ro'@'10.0.0.%';

The MySQL manual describes ON db_name.* as a database-level privilege and SHOW GRANTS as the way to verify what an account holds. If only a handful of tables are needed, grant at table level (ON sales_db.orders) rather than the whole schema. Oracle, SQL Server, PostgreSQL and the rest have their own syntax; the target is identical: SELECT only, on the objects you intend to analyze, from the OT Flash host range only.

Two boundaries most teams blur:

  1. The credentials are a secret. They go into the dialog and your secret store, not into tickets, chats or runbooks.
  2. A read-only account limits what OT Flash can read. It does not limit who inside OT Flash can see the imported copy. That is governed by OT Flash roles and resource permissions, covered below. The manual does not say source-side restrictions carry over; assume they do not.

Import, not live query: where the data actually goes

The manual documents one path after a source is connected: Import Table > Import table from external data source, a wizard in three steps.

  1. Select a data source. Choose it from the drop-down, or click "Go to create new Data Source" if none exists.
  2. Select tables. Tick the tables to import.
  3. Config load tables. Set the import type and whether to import on a schedule.

When the wizard finishes, the tables appear in the table list, and any scheduled import shows up as a task on the Table Task tab.

Where does the copy live? The OT Flash README on GitHub states that Doris stores imported business data in internal tables, and the user manual lists "DataFocus system Doris" as the built-in data source. The r56 Agent and MCP manual confirms it from the other side: table queries over MCP run as Doris SQL, and the original Trino, Hive and Hadoop services are not among Flash's agent entry points. OT Flash does not federate a question out to your Oracle or SQL Server at query time; it answers from its Doris copy. The manual documents no live-query mode, so this post claims none.

That one fact decides two things to settle before clicking Connect:

  • Freshness is bounded by your import schedule, not by the source. A table refreshed nightly answers yesterday's question. DataFocus's edition notes tell planners to work out how data reaches Doris and how often it is refreshed.
  • Governance of the copy is OT Flash's, not the source's. Whoever is granted an imported table in OT Flash sees it, whatever the source database would have allowed them.

A worked example: one source, three tables, one schedule

Consider a hypothetical mid-size distributor with a MySQL order-management database, sales_db, holding orders, order_lines and customers alongside forty other tables, some with payroll and bank details. The analytics team wants the three sales tables in OT Flash, refreshed nightly. The sequence (illustrative; nothing here was run):

  1. DBA creates the account. otflash_ro, restricted to the OT Flash host range, with GRANT SELECT on exactly sales_db.orders, sales_db.order_lines and sales_db.customers. Not sales_db.*: the payroll tables stay invisible at the database layer, and SHOW GRANTS is kept as the record.
  2. Network opens one rule. OT Flash host range to the MySQL host and port. Nothing from the MySQL host back.
  3. Administrator connects the source. Type MySQL, Source Name OMS sales (MySQL), Server and Port, Database sales_db, Schema and JDBC Param empty on the first attempt, the read-only credentials. Connect succeeds; Submit.
  4. Administrator imports. Import Table > Import table from external data source; select the OMS source; tick the three tables; in step 3 set the import type and a nightly schedule after the OMS batch finishes, not during it.
  5. Next morning, verify. Table Task should show three tasks with Data Source, Import Type, Scheduled import time, Status and Last Import Time. Asset Catalog should show three tables under the new source, with their relationship and isolated counts.
  6. Grant access deliberately. Create a custom role for the sales analysts with permissions on these three tables only; leave import rights with SOURCE_MANAGER.

Step 1 is the one that saves you later. Whatever gets joined to customers down the line, the blast radius is three tables, because that is all the account can ever see.

Scheduled imports and the Table Task tab

Scheduled imports are managed in one place, the Table Task tab under Tables, which lists each task's Data Source, Import Type, Scheduled import time, Status and Last Import Time. The manual does not enumerate the import types, so read the drop-down on your own instance rather than assuming what "full" or "incremental" means there.

A routine that holds up:

  1. After the first scheduled run, open Table Task and check Status and Last Import Time against the schedule you set.
  2. Place the schedule outside the source's own load windows so the import never reads a table halfway through a nightly batch.
  3. When a table is retired, remove it from OT Flash and from the read-only account's grants. A grant nobody uses is still a grant.

The manual does not describe retry behavior, alerting on failed imports, or incremental import semantics. Test each on your instance before depending on it, and decide who watches Table Task when Status is not what you expected.

Who can see the imported tables

Connecting a source is an administrative act, and OT Flash keeps the controls in its roles. The preset roles include SOURCE_MANAGER, which manages all resources and grants permissions on them, SYSTEM_MANAGER for global configuration and users, and LOG_MANAGER for log auditing. Custom roles get permissions per resource, listed by category (All Resources, Ontology, Data Entry). This is the second boundary above: the database account decides what OT Flash can fetch; roles decide who sees what was fetched.

Two screens close the loop. Operate Logs records operations including logins and table imports, with Host, Type, Status, Detail, Operate User and Operate Time, filterable by keyword, user, status, type and time range; viewing it needs LOG_MANAGER or SYSTEM_MANAGER. The Asset Catalog groups tables by data source and shows how many have relationships and how many are isolated, the fastest way to see what a new connection brought in and whether it is wired to anything.

Decision rule before you connect

QuestionWhere the answer comes from
Is my database type supported?The 26-entry list in the r57 manual
Is my database version supported?Not stated; a trial Connect on your instance
What can OT Flash read?Your read-only account's grants, verified with the engine's own tool (SHOW GRANTS on MySQL)
Who can see the imported copy?OT Flash roles and per-resource permissions
How fresh is the data?Your import schedule in step 3 of the wizard
Did last night's import run?Table Task: Status and Last Import Time
Who imported what, and when?Operate Logs (LOG_MANAGER or SYSTEM_MANAGER)
What did the connection bring in?Asset Catalog, grouped by data source

Where the answer is "not stated", that is a test to run, not a gap to paper over.

When this is not the right fit

  • You need every question answered from live source data with no copy. The manual documents import, not live querying.
  • Your database type is not among the 26. The file, API or HTTP paths may serve; the manual does not say they are equivalent.
  • You need a tested version matrix. The manual lists types, not versions.

Next step

Start with one source and one schema: create the read-only account, connect it, import two or three tables, and watch Table Task through one full schedule cycle before adding more. The screens are in the OT Flash user manual; the manual center is at datafocus.ai/docs/ot-flash-r56. OT Flash is free to download from the editions page or the GitHub repository, where installers and release notes are published; system requirements and supported platforms are in the release notes.