Step1: Execute the following
GRANT CREATE JOB TO SYSADM;
GRANT MANAGE SCHEDULER TO SYSADM;
GRANT CREATE EXTERNAL JOB TO SYSADM;
Step2: create a delegated Active Directory service account
BEGIN
DBMS_SCHEDULER.CREATE_CREDENTIAL (
credential_name => 'AD_SVC_CRED',
username => 'StudentManagement',
password => 'Mk#kfWi$5jDncD23$5kf'
);
END;
credential_name => 'AD_SVC_CRED'
Step 3: Create the PL/SQL procedure to execute the PowerShell Script providing the required parameters
CREATE OR REPLACE PROCEDURE ad_enable_student_if_clear (
p_identity IN VARCHAR2, -- e.g., 'TESTSTD09'
p_enable IN VARCHAR2 -- '0' = disable, '1' = enable
) AS
v_job VARCHAR2(64);
BEGIN
-- Generate unique job name for tracking
v_job := 'AD_ENABLE_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF3');
-- Create the executable DBMS_SCHEDULER job
DBMS_SCHEDULER.CREATE_JOB(
job_name => v_job,
job_type => 'EXECUTABLE',
job_action => 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe',
number_of_arguments => 7, -- 7 arguments now
enabled => FALSE,
auto_drop => TRUE,
comments => 'Enable/Disable AD account for student'
);
-- PowerShell parameters
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 1, '-NoProfile');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 2, '-ExecutionPolicy');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 3, 'Bypass');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 4, '-File');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 5, 'C:\account_lock\Student-Status1.ps1');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 6, p_identity); -- Username
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job, 7, p_enable); -- 0 or 1
-- Run job using AD credential
DBMS_SCHEDULER.SET_ATTRIBUTE(v_job, 'credential_name', 'AD_SVC_CRED');
-- Enable and start the job
DBMS_SCHEDULER.ENABLE(v_job);
-- Optional: log to DBMS output for confirmation
DBMS_OUTPUT.PUT_LINE('Job ' || v_job || ' created and started successfully for ' ||
p_identity || ' with status flag ' || p_enable);
END;
/
Step4: Execute the procedure
BEGIN
ad_enable_student_if_clear('TESTSTD10', 0); -- 1 = Enable, 0 = Disable
END;
Step5: Execute the below query to get the Job Status ( Success , Failed )
SELECT job_name, status, additional_info
FROM dba_scheduler_job_run_details
WHERE job_name LIKE 'AD_ENABLE_%'
ORDER BY actual_start_date DESC;
Step 6 : Execute the procedure from Peoplecode
SQLExec("BEGIN ad_enable_student_if_clear(:1, :2); END;", &email_id, &Islock);
Following is PowerShell Script to Enable / Disable Accounts from Active Directory
------------------------------------------------------------------------------------------------------------------------
[CmdletBinding()]
param(
[Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$true)]
[string]$Username,
[Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$true)]
[string]$Action,
[switch]$TriggerSync,
[string]$LogPath = "E:\account_lock\Activitylog.log",
[string]$Server
)
# ---------------- Robust logging (pre-create + mutex) ----------------
function Initialize-LogFile {
try {
$dir = Split-Path -Path $LogPath -Parent
if ($dir -and -not (Test-Path -LiteralPath $dir)) {
New-Item -ItemType Directory -Path $dir -Force | Out-Null
}
if (-not (Test-Path -LiteralPath $LogPath)) {
New-Item -ItemType File -Path $LogPath -Force | Out-Null
}
} catch {
Write-Warning "Init log failed: $($_.Exception.Message)"
}
}
# Correlate lines from the same script instance (optional but helpful)
$script:RunId = ([guid]::NewGuid().ToString('N')).Substring(0,8)
function Log-Message {
param([string]$message)
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
$line = "$timestamp [$PID][$script:RunId] - $message"
# Named mutex per-log-file to serialize writers across processes/sessions
$mutexName = "Global\MBSC-SetAdUserStatus-" + ($LogPath -replace '[\\/:*?""<>|]', '_')
$mutex = New-Object System.Threading.Mutex($false, $mutexName)
$maxAttempts = 10
for ($i=1; $i -le $maxAttempts; $i++) {
$gotLock = $false
try {
$gotLock = $mutex.WaitOne(2000) # wait up to 2s for the lock
if (-not $gotLock) { throw "Timeout waiting for log mutex." }
# Append using FileStream + StreamWriter (UTF8 no BOM), allow readers
$fs = [System.IO.File]::Open($LogPath,
[System.IO.FileMode]::Append,
[System.IO.FileAccess]::Write,
[System.IO.FileShare]::ReadWrite)
try {
$sw = New-Object System.IO.StreamWriter($fs, [System.Text.UTF8Encoding]::new($false))
$sw.AutoFlush = $true
$sw.WriteLine($line)
$sw.Flush()
} finally {
$sw.Dispose()
$fs.Dispose()
}
return
} catch {
if ($i -eq $maxAttempts) {
Write-Warning "Log write failed after $maxAttempts attempts to '$LogPath': $($_.Exception.Message)"
return
}
Start-Sleep -Milliseconds ([int][math]::Min(1600, 100 * [math]::Pow(2, $i-1)))
} finally {
if ($gotLock) { $mutex.ReleaseMutex() | Out-Null }
}
}
}
# ---------------------------------------------------------------------
# Ensure the log file exists before any writes
Initialize-LogFile
# Module load
try {
if (-not (Get-Module -ListAvailable -Name ActiveDirectory)) {
throw "ActiveDirectory module not found."
}
Import-Module ActiveDirectory -ErrorAction Stop
} catch {
Log-Message "Failed to import ActiveDirectory module. $($_.Exception.Message)"
Write-Error "Failed to import ActiveDirectory module. $_"
exit 1
}
function Resolve-Action {
param([Parameter(Mandatory)][string]$RawAction)
switch -Regex ($RawAction.Trim().ToLower()) {
'^(1|enable|enabled|true|on)$' { return 'Enable' }
'^(0|disable|disabled|false|off)$' { return 'Disable' }
default { return $null }
}
}
function Set-UserAccountStatus {
[CmdletBinding()]
param(
[Parameter(Mandatory)][string]$User,
[Parameter(Mandatory)][ValidateSet('Enable','Disable')]$Desired,
[string]$Server
)
$serverSplat = @{}
if ($Server) { $serverSplat.Server = $Server }
try {
$adUser = Get-ADUser -Identity $User -Properties Enabled -ErrorAction Stop @serverSplat
if ($Desired -eq 'Enable' -and $adUser.Enabled) {
Log-Message "No change: $User already enabled."
return
}
if ($Desired -eq 'Disable' -and -not $adUser.Enabled) {
Log-Message "No change: $User already disabled."
return
}
if ($Desired -eq 'Enable') {
Enable-ADAccount -Identity $adUser.SamAccountName -ErrorAction Stop @serverSplat
Log-Message "User $($adUser.SamAccountName) enabled."
} else {
Disable-ADAccount -Identity $adUser.SamAccountName -ErrorAction Stop @serverSplat
Log-Message "User $($adUser.SamAccountName) disabled."
}
} catch {
Log-Message "Failed to change status for user '$User'. Error: $($_.Exception.Message)"
throw
}
}
# Input validation (for param-based usage)
if ([string]::IsNullOrWhiteSpace($Username) -or [string]::IsNullOrWhiteSpace($Action)) {
Write-Error "Username and Action are required."
exit 1
}
$resolved = Resolve-Action -RawAction $Action
if (-not $resolved) {
Log-Message "Invalid action '$Action' for user '$Username'. Use 1/enable/on/true or 0/disable/off/false."
Write-Error "Invalid Action: $Action"
exit 1
}
$processedAny = $false
try {
Set-UserAccountStatus -User $Username -Desired $resolved -Server $Server
$processedAny = $true
} catch {
Write-Error $_
}
if ($TriggerSync -and $processedAny) {
try {
if (Get-Command -Name Start-ADSyncSyncCycle -ErrorAction SilentlyContinue) {
Start-ADSyncSyncCycle -PolicyType Delta
Log-Message "Started AAD Connect delta sync."
} else {
Log-Message "Start-ADSyncSyncCycle not found. Skipped sync."
}
} catch {
Log-Message "Failed to start AAD Connect delta sync. Error: $($_.Exception.Message)"
Write-Error "Failed to start AAD Connect delta sync. See log for details."
}
}
No comments:
Post a Comment