Total Pageviews

Sunday, January 4, 2026

Automation to execute revoke statement







#!/bin/bash

CSV_FILE="revoke_access.csv"
ORACLE_HOME="/u01/app/oracle/product/19c/dbhome_1"
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME PATH
# Skip header
tail -n +2 "$CSV_FILE" | while IFS=',' read -r CDB PDB GRANTEE OWNER OBJECT PRIV
do
  echo "Processing: $CDB | $PDB | $GRANTEE | $OWNER.$OBJECT | $PRIV"
  export ORACLE_SID=$CDB
  sqlplus -s / as sysdba <<EOF
SET FEEDBACK OFF
SET HEADING OFF
SET ECHO OFF
SET SERVEROUTPUT ON
ALTER SESSION SET CONTAINER=$PDB;
DECLARE
  v_status VARCHAR2(10);
  v_error  VARCHAR2(4000);
BEGIN
  BEGIN
    EXECUTE IMMEDIATE
      'REVOKE $PRIV ON $OWNER.$OBJECT FROM $GRANTEE';
    v_status := 'SUCCESS';
    v_error  := NULL;
  EXCEPTION
    WHEN OTHERS THEN
      v_status := 'FAILED';
      v_error  := SUBSTR(SQLERRM,1,4000);
  END;
  INSERT INTO SEC_AUDIT.REVOKE_CONTROL_LOG
  (
    CDB_NAME,
    PDB_NAME,
    GRANTEE,
    OBJECT_OWNER,
    OBJECT_NAME,
    PRIVILEGE,
    STATUS,
    ERROR_MESSAGE
  )
  VALUES
  (
    '$CDB',
    '$PDB',
    '$GRANTEE',
    '$OWNER',
    '$OBJECT',
    '$PRIV',
    v_status,
    v_error
  );
  COMMIT;
x
END;
/
EXIT;
EOF
done

No comments:

Post a Comment