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