Create a read-only database account for safe VICIdial queries

01 / 09

1. Confirm this is the lab and access scope you intend

This recipe is for a single-server, disposable ViciBox lab with MariaDB on the same host. SELECT across a VICIdial database can expose customer information and stored secrets. On a system with real data, have its database owner choose specific tables or reporting views before creating access; the broad lab grant below is not a production access policy.

1. Open your lab’s SSH session or console and confirm the hostname against your lab inventory. Keep the terminal private while creating credentials.
2. Have an OS account with sudo and a working MariaDB administrator login. Being root on Linux does not automatically mean passwordless database access is configured.
3. Generate a unique password in your password manager: use at least 32 random letters and digits for this exercise so the SQL and option-file examples need no special-character escaping. Do not use the placeholder printed below.
4. If /etc/vicidial-readonly.cnf or vicigeek_ro@localhost already exists, inspect and verify that existing setup with its owner. Do not overwrite it or widen its permissions to make this lesson work.

Database account

vicigeek_ro@localhostA dedicated local client identity, separate from astguiclient’s application login.

Client file

/etc/vicidial-readonly.cnfRoot-owned, mode 600; treat its contents as a secret.

You should seeYou have the intended lab, administrative access and a new private password. If you only need to use an existing account, start at the file and identity checks below.

02 / 09

2. Read the database name without exposing the application password

1. On the ViciBox host, read only VARDB_database. Do not display the whole configuration file.
2. Record the database value on that line, after the configuration separator (normally =>). Do not include the setting name or separator. Use that exact value wherever this guide says ACTUAL_DATABASE; do not assume the database is named asterisk.

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

You should seeACTUAL_DATABASE now means your observed database name, not the example database in a screenshot.

03 / 09

3. Open a private administrator session and check for a name collision

MYSQL_HISTFILE=/dev/null prevents this client session from writing SQL into its normal history file. It does not disable terminal recording, database audit logs or an organization’s secret-handling controls. Do this step in a private administrative session.

1. Use the command below for a lab whose MariaDB root account supports local socket authentication. If Access denied appears, close the failed attempt and repeat with --password appended; enter the database administrator password only at its prompt. Do not use VARDB_user or VARDB_pass as a shortcut.
2. At the MariaDB prompt, enter SELECT User, Host FROM mysql.user WHERE User = 'vicigeek_ro';. Zero rows means the proposed account name is unused. If any row appears, stop the creation path and have its owner review that existing identity.
3. At the same prompt, enter SELECT @@socket;. Save this socket path locally for the option file. Keeping it explicit avoids relying on an unrelated client configuration.

sudo env MYSQL_HISTFILE=/dev/null mysql --user=root --protocol=socket

You should seeThe account name is unused and you know the local MariaDB socket path. Keep this client open for the next step.

04 / 09

4. Create the account and grant only SELECT

1. In the open MariaDB client, replace REPLACE_WITH_PRIVATE_PASSWORD with a unique password generated by your password manager (at least 32 random ASCII letters/digits for this literal template) and ACTUAL_DATABASE with the database name from step 2. Keep the single quotes around the password and backticks around the database identifier. If the database name itself contains a backtick, have the database owner quote that identifier correctly before continuing.
2. Enter the CREATE USER statement, then the GRANT statement. Each must complete successfully. Do not run them as a shell heredoc containing the password.
3. Record that only CREATE USER and the intended database SELECT grant succeeded. Step 8 independently checks the privilege metadata without displaying authentication material; do not treat statement success alone as that check.
4. Type quit to return to the shell. CREATE USER and GRANT take effect without a separate FLUSH PRIVILEGES command.

CREATE USER 'vicigeek_ro'@'localhost' IDENTIFIED BY 'REPLACE_WITH_PRIVATE_PASSWORD';GRANT SELECT ON `ACTUAL_DATABASE`.* TO 'vicigeek_ro'@'localhost';

You should seeMariaDB has a dedicated local account with the reviewed grant. The client file still needs to be created and tested.

05 / 09

5. Create an empty private file without overwriting an existing one

1. Run the command below from the shell. The no-clobber option refuses an existing file; the restrictive umask applies before the file is created.
2. Check the printed owner and mode. Continue only if the new file is root:root and 600. If it already exists, do not truncate it: have its owner verify its purpose and continue at the identity check if appropriate.

sudo sh -c 'umask 077; set -C; : > /etc/vicidial-readonly.cnf'sudo stat -c '%U %G %a %n' /etc/vicidial-readonly.cnf

You should seeThe new file is private before it contains a secret.

06 / 09

6. Enter the complete option-file contents in a private editor

The [client] group supplies the connection settings and [mysql] supplies the selected database for the mysql/mariadb interactive client. The socket value is the value you read with SELECT @@socket; it can differ between installations.

1. Run sudoedit /etc/vicidial-readonly.cnf. Paste the template below into the editor, then replace the password, socket path and database name with the values from your lab. Keep the password identical to the one used in CREATE USER.
2. Save and close the editor. With vi, press i to insert, then Esc, type :wq and press Enter to save; :q! exits without saving. If your configured editor differs, use its normal save and close controls.
3. Repeat sudo stat -c '%U %G %a %n' /etc/vicidial-readonly.cnf and require root root 600. Do not use cat, --print-defaults or a screenshot to prove the password was saved.

[client]user=vicigeek_ropassword=REPLACE_WITH_PRIVATE_PASSWORDhost=localhostprotocol=socketsocket=ACTUAL_SOCKET_PATH [mysql]database=ACTUAL_DATABASE

You should seeThe protected client file contains the dedicated local identity, actual socket and selected database.

07 / 09

7. Prove the file logs in as the intended account and can read

Put --defaults-file immediately after mysql. It selects this one option file; --defaults-extra-file also reads other defaults. In either case, CURRENT_USER() is the identity check that prevents you from mistaking a successful root query for a successful read-only login.

1. Run the exact command below. It prints account identity, database name and a metadata count; it does not print leads, users or secrets.
2. Compare authenticated_account with vicigeek_ro@localhost and selected_database with the value from step 2. visible_tables should be greater than zero on an installed VICIdial database.
3. If the account, database or count differs, stop before running a later lesson and use the matching recovery row below.

sudo mysql --defaults-file=/etc/vicidial-readonly.cnf --batch --execute="SELECT CURRENT_USER() AS authenticated_account, DATABASE() AS selected_database; SELECT COUNT(*) AS visible_tables FROM information_schema.tables WHERE table_schema=DATABASE();"

You should seeThe file works end to end for the intended local identity. Authentication success alone does not establish its privilege scope; check that next.

08 / 09

8. Check privilege scope without making a write attempt

Use the private administrator client from step 3 for this metadata review. The read-only lesson account must not be granted access to the mysql grant tables just to run an audit. These SELECT statements choose privilege fields, not authentication strings.

1. Reopen the private administrator client from step 3. The queries below target exactly vicigeek_ro at localhost; if you deliberately chose another unused name, replace both account literals consistently.
2. Run the four-scope privilege query. Accept only the intended database SELECT with IS_GRANTABLE NO, and a global USAGE row if your version reports it. USAGE alone is not a data-access privilege. No global SELECT, other database, table/column grant, administrative privilege or grantable privilege belongs to this new-account recipe.
3. Run the role, routine and proxy queries. Each must return zero rows for this newly created account. These checks matter because a role, executable routine or proxy identity can confer access beyond the direct database grant.
4. If a metadata table is unavailable, permissions are insufficient or any row is unexpected, stop and have the database owner review the installed version and that account. Do not classify an incomplete query as zero privileges and do not broaden the lesson account permissions.
5. If scope is wider than intended, stop using the account. Review the exact mistaken grant or remove only this newly created unused account through the recovery step; do not alter an unknown shared identity.

SELECT 'GLOBAL' AS privilege_scope, '*' AS object_name, PRIVILEGE_TYPE, IS_GRANTABLEFROM information_schema.USER_PRIVILEGES WHERE GRANTEE = CONCAT(QUOTE('vicigeek_ro'), '@', QUOTE('localhost'))UNION ALLSELECT 'DATABASE', TABLE_SCHEMA, PRIVILEGE_TYPE, IS_GRANTABLEFROM information_schema.SCHEMA_PRIVILEGES WHERE GRANTEE = CONCAT(QUOTE('vicigeek_ro'), '@', QUOTE('localhost'))UNION ALLSELECT 'TABLE', CONCAT(TABLE_SCHEMA, '.', TABLE_NAME), PRIVILEGE_TYPE, IS_GRANTABLEFROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE = CONCAT(QUOTE('vicigeek_ro'), '@', QUOTE('localhost'))UNION ALLSELECT 'COLUMN', CONCAT(TABLE_SCHEMA, '.', TABLE_NAME, '.', COLUMN_NAME), PRIVILEGE_TYPE, IS_GRANTABLEFROM information_schema.COLUMN_PRIVILEGES WHERE GRANTEE = CONCAT(QUOTE('vicigeek_ro'), '@', QUOTE('localhost')); SELECT Role, Admin_option FROM mysql.roles_mappingWHERE User = 'vicigeek_ro' AND Host = 'localhost';SELECT Db, Routine_name, Routine_type, Proc_priv FROM mysql.procs_privWHERE User = 'vicigeek_ro' AND Host = 'localhost';SELECT Proxied_user, Proxied_host, With_grant FROM mysql.proxies_privWHERE User = 'vicigeek_ro' AND Host = 'localhost';

You should seeIdentity, selected database, successful read and privilege scope have each been checked separately. No test write or authentication-hash output was needed.

09 / 09

9. Use the account in the next lesson, or remove a new unused setup

1. To use a later SQL example, run sudo mysql --defaults-file=/etc/vicidial-readonly.cnf, paste its SELECT or SHOW statement at the MariaDB prompt, then type quit. A SQL block is input for this client, not for the Linux shell.
2. Before your first query in a new terminal workflow, check SELECT CURRENT_USER(), DATABASE();. Later articles may use --defaults-extra-file for the same path; keep that first among client options and verify the identity because other defaults can also apply.
3. If account creation was abandoned and this is the new unused account you just created, reconnect as the database administrator and enter DROP USER 'vicigeek_ro'@'localhost';. First confirm no other process has started depending on it. Never drop astguiclient’s application identity.
4. After removal, the read-only login should be refused. The client file still contains a secret: retire that specific file through your lab’s credential-removal process. If creation never succeeded, do not remove an unrelated existing account to make the cleanup appear complete.

You should seeYou now have a reusable lab connection with verified identity and scope, or a clear record of the check that prevented completion.

Use the connection to investigate an idle campaign →

• File: /etc/vicidial-readonly.cnf, root:root, mode 600.
• Identity: vicigeek_ro@localhost; database: the observed VARDB_database.
• Read check: positive visible-table count; no private rows printed.
• Scope: intended database SELECT and USAGE only; grants reviewed privately.
• Next SQL lesson opens this client before entering its SELECT/SHOW example.