Spectrum Reports Database Authorization Guide¶
This document outlines the minimum required privileges (least privilege) that must be assigned for the Spectrum Reports application to read system, metadata, and user statistics from supported database platforms.
Note
Replace placeholders like [username], [host], [password] with your actual system credentials before executing any commands.
1. MSSQL Server Authorization Script¶
The following commands allow the reporting tool to access all databases (system and user DBs) on the server.
USE [master];
GO
GRANT VIEW ANY DATABASE TO [username];
GRANT VIEW SERVER STATE TO [username];
GRANT CONNECT ANY DATABASE TO [username];
GO
USE [master];
GO
GRANT SELECT ON sys.sysdatabases TO [username];
GRANT SELECT ON sys.server_principals TO [username];
GRANT SELECT ON sys.server_role_members TO [username];
GRANT SELECT ON sys.dm_exec_sessions TO [username];
GRANT SELECT ON sys.dm_exec_connections TO [username];
GRANT SELECT ON sys.database_principals TO [username];
GRANT SELECT ON sys.database_permissions TO [username];
GRANT SELECT ON sys.database_role_members TO [username];
GO
2. MongoDB Authorization Script¶
To read system collections (system.users, system.roles) and list databases, create a custom role. Execute this script in the admin database with a user that has admin privileges.
use admin
db.createRole({
role: "SpectrumReportRole",
privileges: [
{
resource: { cluster: true },
actions: [ "listDatabases" ]
},
{
resource: { db: "", collection: "" },
actions: [ "find", "listCollections", "viewUser", "viewRole" ]
},
{
resource: { db: "local", collection: "" },
actions: [ "find", "listCollections", "viewUser", "viewRole" ]
},
{
resource: { db: "config", collection: "" },
actions: [ "find", "listCollections", "viewUser", "viewRole" ]
},
{
resource: { db: "admin", collection: "system.users" },
actions: [ "find" ]
},
{
resource: { db: "admin", collection: "system.roles" },
actions: [ "find" ]
}
],
roles: [
{
role: "readAnyDatabase",
db: "admin"
}
]
})
3. MySQL Authorization Script¶
Connect using a user with root or SUPER privileges. The [host] placeholder can usually be % or localhost.
GRANT SELECT ON mysql.user TO '[username]'@'[host]';
GRANT SELECT ON information_schema.USER_PRIVILEGES TO '[username]'@'[host]';
GRANT SELECT ON performance_schema.accounts TO '[username]'@'[host]';
GRANT SELECT ON performance_schema.host_cache TO '[username]'@'[host]';
GRANT SELECT ON performance_schema.events_statements_history TO '[username]'@'[host]';
GRANT SELECT ON performance_schema.global_status TO '[username]'@'[host]';
GRANT SHOW DATABASES ON *.* TO '[username]'@'[host]';
GRANT SELECT ON information_schema.TABLES TO '[username]'@'[host]';
FLUSH PRIVILEGES;
4. Oracle Authorization Script¶
Run this script as a user with SYS, SYSTEM, or DBA privileges. Oracle usernames are usually uppercase.
To simplify access to system views, and if permitted by organizational security policies, you may grant the following single privilege instead of applying all individual scripts:
GRANT SELECT ANY DICTIONARY TO [username];
Script
GRANT SELECT ON dba_users TO [username];
GRANT SELECT ON all_users TO [username];
GRANT SELECT ON dba_roles TO [username];
GRANT SELECT ON dba_role_privs TO [username];
GRANT SELECT ON dba_sys_privs TO [username];
GRANT SELECT ON dba_tab_privs TO [username];
GRANT SELECT ON all_tables TO [username];
5. PostgreSQL Authorization Script¶
Run these commands as the postgres user or a superuser. For PostgreSQL 14+, an alternative is GRANT pg_read_all_data TO [username];.
GRANT SELECT ON pg_roles TO [username];
GRANT SELECT ON pg_user TO [username];
GRANT SELECT ON pg_auth_members TO [username];
GRANT SELECT ON pg_class TO [username];
GRANT SELECT ON pg_namespace TO [username];
GRANT pg_read_all_stats TO [username];
6. SAP HANA Authorization Script¶
Access to system views under the SYS schema is required. Execute using a user with SYSTEM, USER ADMIN, or ROLE ADMIN privileges. SAP HANA usernames are usually uppercase.
GRANT SELECT ON SYS.USERS TO [username];
GRANT SELECT ON SYS.GRANTED_ROLES TO [username];
GRANT SELECT ON SYS.GRANTED_PRIVILEGES TO [username];
GRANT SELECT ON SYS.TABLES TO [username];
GRANT CATALOG READ TO [username];
Alternative: GRANT CATALOG READ TO [username]; can simplify access.
7. Vertica Authorization Script¶
Run with dbadmin, SYSMONITOR, or DBA privileges. A professional alternative is to assign the SYSMONITOR role:
GRANT SELECT ON v_catalog.users TO [username];
GRANT SELECT ON v_catalog.grants TO [username];
GRANT SELECT ON v_catalog.tables TO [username];
Alternative:
GRANT SYSMONITOR TO [username];
ALTER USER [username] DEFAULT ROLE SYSMONITOR;
8. Couchbase Authorization Guide¶
Assign roles using Full Admin credentials via Web Console or CLI.
Recommended Roles:
-
Read-Only Admin: View security settings and user list
-
Query Select: Run queries on all buckets
-
Query Manage: Access query management and index info
Text Onlycouchbase-cli user-manage -c [cluster_address] -u [admin_user] -p [admin_password] \ --set --rbac-username [username] --rbac-password [password] \ --rbac-name "Spectrum Reporter" \ --roles read_only_admin,query_select[*],query_manage[*] \ --auth-domain local
9. Elasticsearch Authorization Guide¶
Assign privileges using a user with the superuser role (typically elastic). Create a custom SpectrumReportRole to enable access to users, roles, and index metadata.
POST /_security/role/SpectrumReportRole
{
"cluster": [
"monitor",
"manage_security",
"manage_index_templates"
],
"indices": [
{
"names": [ "*" ],
"privileges": [ "view_index_metadata", "read" ]
}
]
}
POST /_security/user/[username]
{
"password" : "password_here",
"roles" : [ "SpectrumReportRole" ],
"full_name" : "Spectrum Reporting User"
}
10. Teradata Authorization Script¶
Run using a user with DBC or necessary administrative privileges. These grants allow reading user, role, privilege, and session information from system catalogs.
GRANT SELECT ON DBC.UsersV TO [username];
GRANT SELECT ON DBC.RolesV TO [username];
GRANT SELECT ON DBC.RoleMembersV TO [username];
GRANT SELECT ON DBC.AllRightsV TO [username];
GRANT SELECT ON DBC.AccessRightsV TO [username];
GRANT SELECT ON DBC.TablesV TO [username];
GRANT SELECT ON DBC.LogOnOffV TO [username];
GRANT SELECT ON DBC.DatabasesV TO [username];