Create a read-only database account for safe VICIdial queries

01 / 09

Why not just use astguiclient's own database login

`/etc/astguiclient.conf` already holds a working database login — the one astguiclient's own daemons use every second to run your lab. It is tempting to reuse it for a quick manual query, but that account typically has far more privilege than a single SELECT needs, because VICIdial's own software genuinely has to write to its database: recording call results, updating hopper state, and logging agent activity all require write access that a human running an ad hoc lookup does not.

Reusing a high-privilege login for ad hoc, hand-typed queries turns an ordinary typo into a real risk: a stray character in a WHERE clause, a copy-paste mistake, or an unfamiliar command can change data instead of merely reading it. A scoped, SELECT-only account removes that entire category of mistake, because even a mistake cannot write — the database itself refuses the attempt, regardless of what the command actually says.

There is a second reason, separate from accident prevention: every time you type a password into a terminal, or paste one into a script, it can end up in shell history, in a process listing another user on the same box can see, or in a screenshot shared for help. A dedicated account used only for reading, and only through one option file, keeps that exposure contained to a login that cannot do damage even if it leaks.

This same reasoning applies well beyond this one lab. Any time software or a person needs to read data but never change it, granting only SELECT is the correct default, not a beginner's simplification of a more sophisticated real answer — professional database administrators apply exactly this principle under the name of least privilege.

By the end of this lesson, you will have practiced the entire lifecycle of a scoped credential once: creating it, locking its storage down, proving it does what it should, proving it cannot do what it should not, and rolling it back. That lifecycle repeats, in outline, any time you provision access for a new tool or a new person on a real system.

This lesson is also the foundation the rest of this library depends on. Once this account exists, every later lesson that shows you a SQL query assumes you already have it — none of them create it again, and none of them ask you to type astguiclient's own credentials into a terminal.

02 / 09

Step 1: Find your database name

astguiclient.conf already names the database VICIdial itself uses. Read that one key rather than guessing or hardcoding a name that might not match your build — the file stores every setting as a plain key=value pair, one per line, so a targeted grep is enough; you never need to open the whole file.

grep -E '^VARDB_database\b' /etc/astguiclient.conf

03 / 09

Step 2: Create the read-only account

Create a new MariaDB account scoped to SELECT only, on the database you just found. Generate a real password for it — do not reuse any password from anywhere else in this lab. `IDENTIFIED BY` sets that new account's password at creation time; it does not touch any other account, including the one astguiclient itself uses.

The account name `vicigeek_ro` and the host qualifier `@'localhost'` both matter. The host qualifier restricts this account to connections originating on the same machine — it cannot be used to connect from anywhere else on the network, which narrows its exposure even further than the SELECT-only privilege already does on its own.

mysql <<'SQL'CREATE USER 'vicigeek_ro'@'localhost' IDENTIFIED BY '<A_STRONG_PASSWORD>';GRANT SELECT ON <VARDB_DATABASE>.* TO 'vicigeek_ro'@'localhost';FLUSH PRIVILEGES;SQL

04 / 09

Step 3: Build the option file

An option file is a small text file the MariaDB client reads on startup for connection settings, so you never have to type them on the command line. Every later SQL example in this library reads through one option file rather than putting a username and password on the command line, where they would appear in shell history and process listings that other users on the same box could potentially see.

The command below uses a heredoc — the `<<'EOF'` syntax — to write several lines to a file in one step. The quotes around EOF matter: they tell the shell to treat every line up to the closing EOF as literal text, with no variable substitution, which is exactly what you want for a file containing a password.

cat <<'EOF' > /etc/vicidial-readonly.cnf[client]user=vicigeek_ropassword=<A_STRONG_PASSWORD>[mysql]database=<VARDB_DATABASE>EOF

05 / 09

Step 4: Lock down the option file

A file holding a password must not be world-readable, even on a disposable lab, and even though the account it unlocks can only read. Mode 600 means only the file's owner can read or write it at all — no group access, no access for anyone else on the box — and pairing that with root:root ownership means only root can read this particular password.

Read-only is not the same as harmless, which is exactly why this lockdown matters. On a build where password hashing is disabled at the system-settings level, as it is on our ViciBox 12 lab, a user's login password sits in plain text in its own table, and a phone's registration secret sits in plain text in its own table too — and a SELECT-only account granted across the whole database can read both, in full, alongside the campaign and lead data it was actually created for. That is why the option file guarding this account's password is root-only mode 600, and why no SQL sample anywhere in this library selects a password or secret column, even through this account.

chmod 600 /etc/vicidial-readonly.cnfchown root:root /etc/vicidial-readonly.cnf

06 / 09

Step 5: Verify the lockdown took effect

Confirm the previous step actually applied, rather than assuming it did.

stat -c '%A %a %U:%G %n' /etc/vicidial-readonly.cnf

07 / 09

Step 6: Verify the account reads

Confirm the account and the option file actually work together, end to end, before relying on either in a later lesson.

SELECT DATABASE(), CURRENT_USER();

08 / 09

Step 7: Verify the account cannot write

The entire point of this lesson is an account that cannot change data even by mistake. Prove that directly, rather than trusting the GRANT statement alone — a permission model is only as trustworthy as your last test of it, and a five-second check here is cheap insurance against a typo in Step 2 that granted more than intended.

mysql --defaults-extra-file=/etc/vicidial-readonly.cnf -e "UPDATE vicidial_campaigns SET active=active WHERE 1=0;"

09 / 09

Step 8: Know the rollback, then reuse it everywhere

If you ever need to remove this account — replacing the password, decommissioning the lab, or starting over — dropping it is the complete rollback; nothing else in this lesson needs to be undone separately, since the option file alone grants no access once the account behind it is gone. Removing a SELECT-only account carries none of the risk of removing a production account with real dependents, which is one more reason to prefer this shape of account for learning.

Once this account and option file exist, every later lesson in this library that reads VICIdial's database from a terminal assumes exactly this setup: the same `/etc/vicidial-readonly.cnf` path, invoked with `--defaults-extra-file`, never a username or password typed on the command line. You will not see this account created again in any later lesson — you built it once, here, and it is reused by name for the rest of this curriculum.

If you ever rebuild this lab from scratch, repeat this entire lesson before attempting any SQL example elsewhere in this library. A SQL sample that references `/etc/vicidial-readonly.cnf` is not broken if that file does not exist yet on a fresh install — it is simply waiting for you to have completed this lesson first.

mysql <<'SQL'DROP USER 'vicigeek_ro'@'localhost';SQL