Аудит баз Oracle



Здрастуйте, дорогі хабрачитатели! Природно бажання кожного наймача оцінити ефективність та якість виконуваної роботи співробітників, помножити прибуток і скоротити витрати. Підтримка ІТ-інфраструктури завжди є «чорним ящиком». За що заплачено гроші, адже ще нічого не зламалося? Так як вникати в проблематику, швидше за все, жоден керівник не захоче, з високою ймовірністю незабаром виникне необхідність у звіті про виконану роботу, і бажано — щоденному. Розглядаючи красиві циферки і стрункі графіки, замовник незмінно «входить у смак». Поступово з'являються звіти моніторингу інфраструктури, стану бекапів, втрачених інцидентів, роботи DLP-систем. І чим далі, тим страшніше. ІТ-підтримка починає програвати в ефективності, змінюється графік роботи команди, адже звіт потрібен вже вранці. Подібна практика неймовірно стимулює розумову активність в напрямку оперативного надання даних, до яких маєш майже ексклюзивний доступ. Мій спосіб вирішити дану проблему постараюся описати далі.

Звіт звітом поганяє
Замовник — дуже велика виробнича компанія з величезною кількістю магазинів і складів. Обожнює Oracle у Windows-середовищі (що взагалі рідкість). Заводи ми не розглядаємо, наша мета – склади і магазини, і всі СУБД, що там крутяться.

Відомо, що нові инстансы СУБД створюються на регулярній основі розробниками або тестерами – вони запросто можуть і не поставити нікого до відома про існування своєї тестового середовища, але запанікує, коли її випадково видалять у зв'язку зі списанням сервера або черговий віртуалізацією. Є і богом забуті сервера з базами 10-річної давності. З ними досі працює якийсь магазин або склад. Де розташовані бази (хоча б географічно) – ніхто не знає, в тому числі замовник та система моніторингу за 10К$. Ці СУБД ніколи туди не вносилися. Як уже стверджувалося, специфіка така, що велика частина СУБД Oracle розгорнута у Windows-середовищі. Win-инстансов вже близько 200 і зібрати подібну інформацію про них досить важко. Також є і Oracle під Linux. Таких баз всього-то нічого – 40 штук. Є ще один серйозний плюс – сервера мають Name convention по локації: знайдемо ім'я сервера – знайдемо і його розташування.

Для розробки звіту будемо використовувати PowerShell. Чому? Бо:

  • Робота ведеться з термінальною машини Windows Server 2008. Ззовні доступу до інших серверів немає.
  • Там є Excel! PowerShell пречудесно з ним працює як з com-об'єктом. Не потрібно шукати модулі, як, наприклад, з Python, так як всі вже вшито в NET.
  • Велика частина серверів у нас все-таки Windows.
  • Досвіду роботи з PowerShell у мене побільше.
Для доступу та отримання інформації з Linux-хостів все-таки поставимо Cygwin. Всі скрипти і звіти тоді будуть в одному місці, і це добре. Завдання звіту: відбувається довгостроковий аудит безпеки баз даних Oracle у зв'язку з міграцією СУБД у віртуальне середовище.

Необхідно визначити:

  • скільки у нас баз,
  • в якому вони стані на поточний момент,
  • на яких серверах розташовані, запущені вони взагалі,
  • як споживають ресурси, яка розгорнута версія Oracle.
Почнемо з Linux
Місцеві розробники їх чомусь бояться, так що всі СУБД у production. Сервера відомі, їх мало. Скануємо список Linux-хостів і отримуємо підсумковий файл в своєму каталозі. Для пошуку инстансов Oracle шукаємо запущений процес Pmon простим однострочным bash-скриптом.

Скрипт 1:

for line in $(cat file.txt)
do ssh oracle@$line '$(ps -e -o cmd | grep ora_pmon |grep -v grep|cut -c 10-19 > /tmp/result.txt) ; while i read ; do my_var=$(echo $i ); echo $(hostname -s)";"${my_var##*_}";;;"; done < /tmp/result.txt ; rm /tmp/result.txt' >>script_files/FileOra2.csv
done

Windows наше все
Тут ми Pmon не знайдемо, весь Oracle реалізований як один багатопотоковий процес. Windows-хостингу будемо обходити за допомогою Windows Management Interface. Інстанси Oracle ж буде знайдений у службах Windows. Використовуємо PowerShell:

Скрипт 2

$MLpath= 'c:\scripts\DBA\script_files\ML.txt'
$MLdir= [System.IO.Path]::GetDirectoryName($MLPath)
$outfile=$($MLdir +'\'+'FileOra.csv')
$Dbfile= $($MLdir +'\'+'Dblіst.csv')
$hosts=get-content $MLpath -Force 
$a= foreach ($pc in $hosts){
write-host "test $pc"
try{
<#TO display
gwmi -Class win32_service -computername "$pc"|where { $_.name -like "OracleService*" } -ErrorAction SilentlyContinue|format-table "$pc", name, state, pathname, StartMode -autosize|out-host#>
$colItems = gwmi -Class win32_service -computername "$pc"|where { $_.name -like "OracleService*" } -ErrorAction SilentlyContinue
foreach ($objItem in $colItems) {$($pc +";" +($objItem.name).trimstart("OracleService") +";" +$objitem.state +";" +$ObjITem.pathname +";" +$ObjITem.startmode) >> $outfile
}
}
catch {
Write-Output $("$pc" + $_.Exception.Message)
} 
}

Що ж далі
Зібравши список хостів і баз даних, зроблено перший крок до актуалізації інформації. Першим ділом я завів єдиного користувача в кожній СУБД, від якого виконував подальші дії. Настав час збору інформації. Можна було б використовувати SQL*Plus, але раз вже ми працюємо з com-об'єктами, краще використовувати OLEDB для Oracle. Для цього довстановимо на наш термінал OLEDB-провайдер і виконаємо нас цікавить запит в кожній СУБД. Завантажити його можна, наприклад, з офіційного сайту Oracle. У системних вимогах до OLEDB бачимо приблизно наступне:

— Access to an Oracle Database (Oracle 9.2 or later)
— Oracle Client release 11.1 or later and Oracle Net Services (included with Oracle OLE DB Provider for installation).

Тепер можна абстрагуватися від операційної системи на серверах. Створюємо коннектор, виконуємо запит в кожній окремій базі і зберігаємо результати в файлик. Скрипт 3 втім, я використовую окремо, виконуючи будь-які довільні запити до списку СУБД, такі як кількість вільного місця, параметри SGA, PGA, списки користувачів і криптостійкості їх паролів (HASH для Oracle паролів можна без зусиль знайти в Інтернеті). Деякі символи запитів зажадають екранування в PowerShell – в цьому випадку зручно скористатися Oracle-функцією CHR, повертає символ з таблиці кодування ASCII. Також на виході отримаємо окремий список хостів, яким не вдалося підключитися із зазначенням кодів помилок для подальшого аналізу.

Скрипт 3

function Get-OLEDBData ($connectstring, $sql) { 
$OLEDBConn = New Object System.Data.OleDb.OleDbConnection($connectstring) 
$OLEDBConn.open() 
$readcmd = New Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn) 
$readcmd.CommandTimeout = '10' 
$da = New Object system.Data.OleDb.OleDbDataAdapter($readcmd) 
$dt = New Object system.Data.datatable 
[void]$da.fill($dt) 
$OLEDBConn.close() 
return $dt 
}
$date=(get-date).toshortdatestring().replace("/",".")
$log = "$("$date" +"_"+ 'error')"
$db = "$("$date" +"_"+ 'Dblіst')"
$qry= 'select INSTANCE_NAME,HOST_NAME,VERSION from V$INSTANCE'
gc c:\_tir\fileORA.csv| % {
$row = $_.split(";") 
$hostname = $row[0] 
$service = $row[1]
$connString = "password=xxxxXXXxxx;User ID=ORAUSER;Data Source=$hostname/$service;Provider=OraOLEDB.Oracle" 
try { Get-OLEDBData $connString $qry}
catch {Write-Output $("$Compname" +';'+ $_.Exception.Message) >> C:\_tir\$log.log
}
}|Export-Csv c:\_tir\$db.csv -delim ';'

Наводимо красу
Текстові файли – це негарно. З'єднуємо всі отримані результати в каталозі щоденний звіт Excel. Працюємо з аркушем Excel як із звичайним об'єктом. Приховуємо лист, щоб операція йшла швидше. Звіт відправляємо до себе на пошту. Нарешті, оновимо наш TNSNAMES-файл для зручності подальшого підключення до баз через SQL*Plus. Використовуємо правильний синтаксис файлу (ніколи не могла запам'ятати).

Скрипт 4

$date=(get-date).toshortdatestring().replace("/",".")
$MLpath= 'c:\scripts\DBA\script_files\ML.txt'
$MLdir= [System.IO.Path]::GetDirectoryName($MLPath)
$outfile=$($MLdir +'\'+'FileOra.csv')
$Dbfile= $($MLdir +'\'+'Dblіst.csv')
$Dbfilexls= $($MLdir +'\'+'Dblіst'+ $date +'.xlsx')
#$logFile= [System.IO.Path]::Combine($MLdir,$("{0}.log" -f $sourceFileName ))
gc $outfile|Sort-Object -Unique|out-file $Dbfile -Force
<#creating excel doc#>
$excel = new-object -comobject excel.application
$excel.visible = $false
$workbook = $excel.workbooks.add()
$workbook.workSheets.item(3).delete()
$workbook.WorkSheets.item(2).delete()
$workbook.WorkSheets.item(1).Name = "Databases"
$sheet = $workbook.WorkSheets.Item("Databases")
$x = 2
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]
For($b = 1 ; $b -le 5 ; $b++)
{
$sheet.cells.item(1,$b).font.bold = $true
$sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
$sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}
$sheet.cells.item(1,1) = "Hostname"
$sheet.cells.item(1,2) = "Instance"
$sheet.cells.item(1,3) = "state"
$sheet.cells.item(1,4) = "path"
$sheet.cells.item(1,5) = "autorun"
Foreach ($row in $data=Import-Csv $Dbfile -Delimiter ';' -Header name, value, path, state, start)
{ 
$sheet.cells.item($x,1) = $row.name
$sheet.cells.item($x,2) = $row.value
$sheet.cells.item($x,3) = $row.path
$sheet.cells.item($x,4) = $row.state
$sheet.cells.item($x,5) = $row.start
$x++
} 
$range = $sheet.usedRange
$range.EntireColumn.AutoFit() | Out-Null
$Excel.ActiveWorkbook.SaveAs($Dbfilexls)
if($workbook -ne $null)
{
$sheet = $null
$range = $null

$workbook.Close($false)
}
if($excel -ne $null)
{
$excel.Quit()
$excel = $null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()
}
IF(Test-Path $MLdir\tnsnames.ora )
{

remove-item $MLdir\tnsnames.ora -Force 

}
ELSE
{
Write-Host "new tnsora" 
}

<# Update TNSORA file#>
gc $Dbfile| % { 
$row = $_.split(";") 
$hostname = $row[0] 
$service = $row[1] 
$name=$service+'_'+$hostname
"$name =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = $hostname)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = $service)
)
)">> $MLdir\tnsnames.ora 
}
<#Mail report to #>
$filename= $Dbfilexls= $($MLdir +'\'+'Dblіst'+ $date +'.xlsx')
$smtpServer = "server_name"
$smtp = new object Net.Mail.SmtpClient($smtpServer)
$credentials=new object system.net.networkcredential("server_name\mail","Dfgtnb451")
$smtp.credentials=$credentials.getcredential($smtpserver,"25","basic")
$msg= New Object net.Mail.MailMessage
$att = new object Net.Mail.Attachment($filename)
$msg.from = "user@yourdomain.com"
$msg.to.add("timur@rrrr.xxx, valentin@rrrr.xxx")
$msg.subject = "Database_Report"
$msg.body = "DAtabase sample report body"
$msg.isbodyhtml= "false"
$msg.Attachments.Add($att)
$smtp.Send($msg)

Залишилося внести наші скрипти в Windows-планувальник. Спочатку збираємо інформацію про інстансах Oracle Linux і Windows-хостах (1 і 2 скрипти). Далі підключаємося до кожної СУБД і збираємо інформацію (сценарій 3). Після чого будуємо агрегований Excel-звіт і відправляємо його поштою (скрипт 4).

Висновки: за що боролися – на те й напоролися
  • Навели порядок. Знайшлися стародавні бази з Oracle 9 і нічийні інсталяції Oracle 12. Нарешті від них позбулися.
  • Налаштував автоматичне пересилання листа зі свого Exchange-скриньки зацікавленим особам. Завжди в курсі змін в інфраструктурі.
  • Знаю, коли і де база впала, порівнявши два звіту.
  • Частини цього рішення я поступово розніс по іншим областям застосування. Отримав завжди актуальний список Tnsnames
  • У ході створення скрипта звіту виявилися дірки по входу зі стандартними паролями Oracle, начебто scott/tiger з підвищеними привілеями доступу. Тому після створення звіту довелося провести окремий аудит безпеки з перебором стандартних паролів і створити звіт за списком паролів. Таким чином, рішення згодилося двічі.
Вдалою автоматизації!
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.