Oracle

-- Set your PDB as the active container
ALTER SESSION SET CONTAINER = {db_name};
 
-- Create the Solid user
CREATE USER solid_read_only_user IDENTIFIED BY "{password}";
 
-- Allow the user to log in
GRANT CREATE SESSION TO solid_read_only_user;
 
-- Allow access to database structure and metadata
GRANT SELECT ANY DICTIONARY TO solid_read_only_user;
 
-- Allow read-only access to table data
GRANT SELECT ANY TABLE TO solid_read_only_user;
 
-- Lock the user to this PDB only (prevents cross-container access)
ALTER USER solid_read_only_user SET CONTAINER_DATA = CURRENT;

-- Step 1: Create the user and allow login
ALTER SESSION SET CONTAINER = {db_name};
CREATE USER solid_read_only_user IDENTIFIED BY "{password}";
GRANT CREATE SESSION TO solid_read_only_user;
ALTER USER solid_read_only_user SET CONTAINER_DATA = CURRENT;

-- Step 2: Grant SELECT on each dictionary view Solid uses
-- (replaces SELECT ANY DICTIONARY)
GRANT SELECT ON SYS.DBA_TAB_COLUMNS     TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_TABLES          TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_OBJECTS         TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_TAB_MODIFICATIONS TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_TAB_COMMENTS    TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_COL_COMMENTS    TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_IND_COLUMNS     TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_CONSTRAINTS     TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_CONS_COLUMNS    TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_USERS          TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_ROLE_PRIVS      TO solid_read_only_user;
GRANT SELECT ON SYS.V_$PDBS             TO solid_read_only_user;

-- AWR views (requires Oracle Diagnostics Pack license)
GRANT SELECT ON SYS.DBA_HIST_SQLSTAT    TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT   TO solid_read_only_user;
GRANT SELECT ON SYS.DBA_HIST_SQLTEXT    TO solid_read_only_user;

-- Step 3: Grant schema-scoped SELECT for data profiling
-- (replaces SELECT ANY TABLE)
-- Run this block once per schema Solid should monitor.
-- Replace {schema_name} with each schema name (uppercase).
BEGIN
  FOR t IN (
    SELECT owner, table_name
    FROM dba_tables
    WHERE owner = UPPER('{schema_name}')
      AND table_name NOT LIKE 'BIN$%'
  ) LOOP
    EXECUTE IMMEDIATE
      'GRANT SELECT ON ' || t.owner || '.' || t.table_name ||
      ' TO solid_read_only_user';
  END LOOP;
END;
/

SELECT username, common, oracle_maintained
FROM dba_users
WHERE username = 'SOLID_READ_ONLY_USER';

ALTER SESSION SET CONTAINER = CDB$ROOT;

SELECT owner, table_name
FROM dba_tables
WHERE owner = '{schema}'
AND ROWNUM <= 5;

SELECT *
FROM {schema}.{table}
WHERE ROWNUM <= 1;

SELECT * FROM dba_sys_privs
WHERE grantee = 'SOLID_READ_ONLY_USER';

SELECT MIN(end_interval_time), MAX(end_interval_time)
FROM dba_hist_snapshot;

SELECT username, account_status, lock_date
FROM dba_users
WHERE username = 'SOLID_READ_ONLY_USER';

SELECT
    (SELECT NAME FROM V$PDBS) AS TABLE_CATALOG,
    atc.OWNER AS TABLE_SCHEMA,
    atc.TABLE_NAME,
    atc.COLUMN_NAME,
    atc.COLUMN_ID AS ORDINAL_POSITION,
    atc.DATA_TYPE,
    CASE WHEN atc.NULLABLE = 'Y' THEN 'YES' ELSE 'NO' END AS IS_NULLABLE,
    atc.DATA_DEFAULT AS COLUMN_DEFAULT,
    tc.COMMENTS AS TABLE_COMMENT,
    cc.COMMENTS AS COLUMN_COMMENT,
    ao.CREATED AS TABLE_CREATED_AT,
    ao.CREATED AS COLUMN_CREATED_AT,
    COALESCE(atm.TIMESTAMP, ao.CREATED) AS TABLE_UPDATED_AT,
    NVL(cons.IS_PK, 'FALSE') AS IS_PK,
    NVL(cons.IS_FK, 'FALSE') AS IS_FK,
    CASE
        WHEN idx.COLUMN_NAME IS NOT NULL THEN 'TRUE'
        ELSE 'FALSE'
    END AS IS_INDEX
FROM DBA_TAB_COLUMNS atc
JOIN DBA_TABLES at ON atc.OWNER = at.OWNER AND atc.TABLE_NAME = at.TABLE_NAME
JOIN DBA_OBJECTS ao ON atc.TABLE_NAME = ao.OBJECT_NAME AND atc.OWNER = ao.OWNER
LEFT JOIN DBA_TAB_MODIFICATIONS atm ON atc.OWNER = atm.TABLE_OWNER AND atc.TABLE_NAME = atm.TABLE_NAME
LEFT JOIN DBA_TAB_COMMENTS tc ON atc.OWNER = tc.OWNER AND atc.TABLE_NAME = tc.TABLE_NAME
LEFT JOIN DBA_COL_COMMENTS cc ON atc.OWNER = cc.OWNER AND atc.TABLE_NAME = cc.TABLE_NAME AND atc.COLUMN_NAME = cc.COLUMN_NAME
LEFT JOIN (
    SELECT DISTINCT TABLE_OWNER, TABLE_NAME, COLUMN_NAME
    FROM DBA_IND_COLUMNS
) idx ON atc.OWNER = idx.TABLE_OWNER
     AND atc.TABLE_NAME = idx.TABLE_NAME
     AND atc.COLUMN_NAME = idx.COLUMN_NAME
LEFT JOIN (
    SELECT
        acc.OWNER,
        acc.TABLE_NAME,
        acc.COLUMN_NAME,
        MAX(CASE WHEN ac.CONSTRAINT_TYPE = 'P' THEN 'TRUE' ELSE 'FALSE' END) AS IS_PK,
        MAX(CASE WHEN ac.CONSTRAINT_TYPE = 'R' THEN 'TRUE' ELSE 'FALSE' END) AS IS_FK
    FROM DBA_CONSTRAINTS ac
    JOIN DBA_CONS_COLUMNS acc ON ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME AND ac.OWNER = acc.OWNER
    WHERE ac.CONSTRAINT_TYPE IN ('P', 'R')
    GROUP BY acc.OWNER, acc.TABLE_NAME, acc.COLUMN_NAME
) cons
    ON atc.OWNER = cons.OWNER
    AND atc.TABLE_NAME = cons.TABLE_NAME
    AND atc.COLUMN_NAME = cons.COLUMN_NAME
WHERE (SELECT NAME FROM V$PDBS) = '{db_name}'
  AND atc.owner IN ({schema_list})
ORDER BY atc.OWNER, atc.TABLE_NAME, atc.COLUMN_ID;

SELECT
    (SELECT NAME FROM V$PDBS) AS fk_database_name,
    acc.OWNER AS fk_schema_name,
    acc.TABLE_NAME AS fk_table_name,
    acc.COLUMN_NAME AS fk_column_name,
    (SELECT NAME FROM V$PDBS) AS pk_database_name,
    p_acc.OWNER AS pk_schema_name,
    p_acc.TABLE_NAME AS pk_table_name,
    p_acc.COLUMN_NAME AS pk_column_name,
    ac.R_CONSTRAINT_NAME AS pk_name,
    acc.POSITION AS key_sequence,
    'NO ACTION' AS update_rule,
    ac.delete_rule,
    ac.CONSTRAINT_NAME AS fk_name,
    ac.deferrable,
    ac.deferred,
    ac.rely,
    ao.CREATED AS created_on
FROM DBA_CONS_COLUMNS acc
JOIN DBA_CONSTRAINTS ac
    ON acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME AND acc.OWNER = ac.OWNER
JOIN DBA_OBJECTS ao
    ON acc.OWNER = ao.OWNER AND acc.TABLE_NAME = ao.OBJECT_NAME
JOIN DBA_CONS_COLUMNS p_acc
    ON ac.R_CONSTRAINT_NAME = p_acc.CONSTRAINT_NAME
    AND ac.R_OWNER = p_acc.OWNER
    AND acc.POSITION = p_acc.POSITION
WHERE (SELECT NAME FROM V$PDBS) = '{db_name}'
  AND acc.OWNER IN ({schema_list})
  AND ac.CONSTRAINT_TYPE = 'R'
  AND ao.OBJECT_TYPE = 'TABLE'
  AND acc.TABLE_NAME NOT LIKE 'BIN$%'
ORDER BY acc.OWNER, acc.TABLE_NAME, ac.CONSTRAINT_NAME, acc.POSITION;

WITH USER_ROLES_CTE AS (
    SELECT
        GRANTEE,
        LISTAGG(DISTINCT GRANTED_ROLE, ', ') WITHIN GROUP (ORDER BY GRANTED_ROLE) AS ASSIGNED_ROLES
    FROM DBA_ROLE_PRIVS
    GROUP BY GRANTEE
)
SELECT
    stat.SQL_ID AS QUERY_ID,
    q.SQL_TEXT AS QUERY_TEXT,
    p.NAME AS DATABASE_NAME,
    stat.PARSING_SCHEMA_NAME AS SCHEMA_NAME,
    CASE q.COMMAND_TYPE
        WHEN 3   THEN 'SELECT'
        WHEN 2   THEN 'INSERT'
        WHEN 6   THEN 'UPDATE'
        WHEN 7   THEN 'DELETE'
        WHEN 189 THEN 'MERGE'
        ELSE 'OTHER (' || q.COMMAND_TYPE || ')'
    END AS QUERY_TYPE,
    u.USERNAME AS USER_NAME,
    r.ASSIGNED_ROLES AS ROLE_NAME,
    CASE
        WHEN NVL(stat.ROWS_PROCESSED_DELTA, 0) > 0 THEN 'SUCCESS'
        ELSE 'FAILED'
    END AS EXECUTION_STATUS,
    snaps.END_INTERVAL_TIME - NUMTODSINTERVAL(stat.ELAPSED_TIME_DELTA / 1000000, 'SECOND') AS START_TIME,
    snaps.END_INTERVAL_TIME AS END_TIME,
    ROUND(NVL(stat.ELAPSED_TIME_DELTA, 0) / 1000, 2) AS TOTAL_ELAPSED_TIME_MS,
    u.ORACLE_MAINTAINED AS IS_ORACLE_MAINTAINED
FROM DBA_HIST_SQLSTAT stat
JOIN DBA_HIST_SNAPSHOT snaps ON stat.SNAP_ID = snaps.SNAP_ID AND stat.DBID = snaps.DBID
JOIN DBA_HIST_SQLTEXT q ON stat.SQL_ID = q.SQL_ID AND stat.DBID = q.DBID AND stat.CON_DBID = q.CON_DBID
JOIN DBA_USERS u ON stat.PARSING_USER_ID = u.USER_ID
JOIN V$PDBS p ON stat.CON_ID = p.CON_ID
LEFT JOIN USER_ROLES_CTE r ON u.USERNAME = r.GRANTEE
WHERE p.NAME = '{db_name}'
  AND (u.ORACLE_MAINTAINED = 'N' OR u.USERNAME != 'SYS')
  AND snaps.END_INTERVAL_TIME > TIMESTAMP '{last_collect}'
  AND q.COMMAND_TYPE = 3
ORDER BY snaps.END_INTERVAL_TIME DESC, stat.SQL_ID
FETCH FIRST 1000000 ROWS ONLY;

SELECT
    user_id,
    username AS name,
    created AS created_on,
    '' AS deleted_on,
    CASE
        WHEN account_status LIKE '%LOCKED%' THEN 'LOCKED'
        WHEN account_status = 'OPEN' THEN 'ACTIVE'
        ELSE account_status
    END AS status,
    username AS login_name,
    username AS display_name,
    last_login,
    '' AS first_name,
    '' AS last_name,
    '' AS email,
    CASE WHEN oracle_maintained = 'N' THEN 'HUMAN' ELSE 'MACHINE' END AS user_type
FROM dba_users
ORDER BY username;

SELECT
    (SELECT NAME FROM V$PDBS) AS DATABASE_NAME,
    at.OWNER AS SCHEMA_NAME,
    at.TABLE_NAME AS TABLE_NAME,
    TO_CHAR(TRUNC(atm.TIMESTAMP), 'YYYY-MM-DD') AS DAY,
    SUM(atm.INSERTS) AS TOTAL_ROWS_ADDED,
    SUM(atm.DELETES) AS TOTAL_ROWS_REMOVED,
    SUM(atm.UPDATES) AS TOTAL_ROWS_UPDATED,
    (SUM(atm.INSERTS) + SUM(atm.DELETES) + SUM(atm.UPDATES)) AS TOTAL_COUNT,
    MAX(at.LAST_ANALYZED) AS LAST_ANALYZED
FROM DBA_TABLES at
JOIN DBA_TAB_MODIFICATIONS atm
    ON atm.TABLE_OWNER = at.OWNER AND atm.TABLE_NAME = at.TABLE_NAME
WHERE (SELECT NAME FROM V$PDBS) = '{db_name}'
  AND at.OWNER IN ({schema_list})
  AND atm.TIMESTAMP BETWEEN TIMESTAMP '{start_day_to_collect}' AND TIMESTAMP '{end_day_to_collect}'
GROUP BY at.OWNER, at.TABLE_NAME, TRUNC(atm.TIMESTAMP)
HAVING (SUM(atm.INSERTS) + SUM(atm.DELETES) + SUM(atm.UPDATES)) > 0
ORDER BY DAY, SCHEMA_NAME, TABLE_NAME;