Author Posts

January 1, 2012 at 12:00 am

by cweislak at 2012-11-02 21:38:27

I am having trouble formatting a report for user permissions out of Oracle. I can have it output to the screen fine as a table. I am not sure out to make it nice enough to send it to my manager or our Audit team. Basically make it more readable when I output it to a text file. It mostly is the write-report part of the script I am stumped on. Any help would be greatly appreciated...

< #
.SYNOPSIS
Get Active Permissions.
.DESCRIPTION
This will use the .net 4 or .net 2 oracle dll to query Oracle
.PARAMETER TNSName
TNSName of the Database to connect to
.PARAMETER UserName
The Username to look up.
.PARAMETER AssemblyFile
The Assembly File to use for quering Oracle by default it will use "C:\Oracle\product\11.2.0\client1\ODP.NET\bin\4\Oracle.DataAccess.dll".
you must have the data access for orcale .net installed to run this.
.EXAMPLE
Get-HTAOraclePermissions -UserName user -TNSName w3test -AssemblyFile C:\Oracle\product\11.2.0\client1\ODP.NET\bin\4\Oracle.DataAccess.dll
#>
Param (
$UserName,
$TNSName
)

Function getassembly {
Begin{}
Process{
$path = ($env:Path).split(";") |Select-String -Pattern Oracle1
if ($path -eq $null) {
$path = ($env:Path).split(";") |Select-String -Pattern product
}
$path = ($path.ToString()).Trim("\bin")
if (test-path -Path "$path\ODP.NET\bin\4\Oracle.DataAccess.dll"){
$Assembly = "$path\ODP.NET\bin\4\Oracle.DataAccess.dll"
} elseif (test-path -Path "$path\ODP.NET\bin\2.x\Oracle.DataAccess.dll"){
$Assembly = "$path\ODP.NET\bin\2.x\Oracle.DataAccess.dll"
} else {
$Assembly = $null
}
Write-Output $Assembly
}
End {}
}

Function getrole {
Param(
$User,
$ConnectionString
)
Process {
$SQL = @("select *
From dba_role_privs
Where GRANTEE = `'$User`'")
$OracleCommand = New-Object -TypeName Oracle.DataAccess.Client.OracleCommand
$OracleCommand.CommandText = $SQL
$OracleCommand.Connection = $OracleConnection
$OracleDataAdapter = New-Object -TypeName Oracle.DataAccess.Client.OracleDataAdapter
$OracleDataAdapter.SelectCommand = $OracleCommand
$DataSet = New-Object -TypeName System.Data.DataSet
$OracleDataAdapter.Fill($DataSet) | Out-Null
$OracleDataAdapter.Dispose()
$OracleCommand.Dispose()
$DataSet.Tables[0]
}
}

Function getpriv {
Param(
$User,
$ConnectionString
)
Process {
$SQL = @("select *
From dba_sys_privs
Where GRANTEE = `'$User`'")
$OracleCommand = New-Object -TypeName Oracle.DataAccess.Client.OracleCommand
$OracleCommand.CommandText = $SQL
$OracleCommand.Connection = $OracleConnection
$OracleDataAdapter = New-Object -TypeName Oracle.DataAccess.Client.OracleDataAdapter
$OracleDataAdapter.SelectCommand = $OracleCommand
$DataSet = New-Object -TypeName System.Data.DataSet
$OracleDataAdapter.Fill($DataSet) | Out-Null
$OracleDataAdapter.Dispose()
$OracleCommand.Dispose()
$DataSet.Tables[0]
}
}

Function Get-Role {
[CmdletBinding()]
Param (
[String[]]$User,
[String]$ConnectionString
)
$Role = Foreach ($u in $User){
$uname = $u.ToUpper()
getrole -User $uname -ConnectionString $ConnectionString
}
Write-Output $Role
}

Function Get-Privilege {
[CmdletBinding()]
Param (
[String[]]$User,
[String]$ConnectionString
)
$Privilege = Foreach ($u in $User){
$uname = $u.ToUpper()
getpriv -User $uname -ConnectionString $ConnectionString
}
Write-Output $Privilege
}

Function Get-Nested {
Param (
[String[]]$rolePriv
)
$npriv = Get-Privilege -User $r -ConnectionString $ConnectionString
$nplist = $npriv | Select-Object -ExpandProperty PRIVILEGE
$combined = $nplist |Foreach ($_){ Write-Output "$r-$_"}
Write-Output $combined
}

Function Get-HTAOraclePermissions {
< #
.SYNOPSIS
Get Active Permissions.
.DESCRIPTION
This will use the .net 4 or .net 2 oracle dll to query Oracle
.PARAMETER TNSName
TNSName of the Database to connect to
.PARAMETER UserName
The Username to look up. Use ActiveDirectory ID with the Domain Name Domain\userid
.PARAMETER AssemblyFile
The Assembly File to use for quering Oracle by default it will use "C:\Oracle\product\11.2.0\client1\ODP.NET\bin\4\Oracle.DataAccess.dll".
you must have the data access for orcale .net installed to run this.
.EXAMPLE
Get-HTAOraclePermissions -UserName user -TNSName w3test -AssemblyFile C:\Oracle\product\11.2.0\client1\ODP.NET\bin\4\Oracle.DataAccess.dll
#>
[CmdletBinding()]
Param(
[String]$TNSName,
[String]$AssemblyFile = "C:\Oracle\product\11.2.0\client1\ODP.NET\bin\4\Oracle.DataAccess.dll",
[String]$UserName
)
Begin {
if (-not (Test-Path $AssemblyFile)) {
$AssemblyFile = getassembly
if ($AssemblyFile -ne $null){
[Reflection.Assembly]::LoadFile($AssemblyFile) | Out-Null
}
} else {
[Reflection.Assembly]::LoadFile($AssemblyFile) | Out-Null
}

}
Process {
$continue = $True
try {
Get-Item -Path $AssemblyFile -ErrorAction Stop |Out-Null
} Catch {
Write-Host "Missing Assembly File"
$continue = $false
}
if ($UserName -eq $null){
$UserName = Read-Host "Enter UserName"
}
if ($TNSName -eq $null){
$TNSName = Read-Host "Enter TNSName"
}
if ($continue -eq $true){
$User = $UserName.ToUpper()
[String]$ConnectionString = "User ID=/;Data Source=$TNSName"
$OracleConnection = New-Object -TypeName Oracle.DataAccess.Client.OracleConnection
$OracleConnection.ConnectionString = $ConnectionString
$OracleConnection.Open()
$priv = Get-Privilege -User $User -ConnectionString $ConnectionString
$plist = $priv | Select-Object -ExpandProperty PRIVILEGE
$phash = $null
$phash = @{}
$phash.Add("UserName",$User)
$phash.Add("PRIVILEGE",$plist)
$role = Get-Role -User $User -ConnectionString $ConnectionString
$rlist = $role | Select-Object -ExpandProperty GRANTED_ROLE
$phash.Add("GRANTED_ROLE",$rlist)
$Nested = foreach ($r in $rlist){
get-nested -rolePriv $r
}
$phash.Add("NESTED_PRIVILEGES",$Nested)
$userpriv = New-Object -TypeName PSObject -Property $phash
}
Write-Output $userpriv
}
END {}
}

Function Write-Report {
Param (
$Users
)
BEGIN {}
PROCESS {
Foreach ($User in $Users){
$uname = $User |Select-Object -ExpandProperty UserName |fl * |Out-String
$urole = $User |Select-Object -ExpandProperty GRANTED_ROLE | Sort-Object | fl * |Out-String
$upriv = $User |Select-Object -ExpandProperty PRIVILEGE | Sort-Object | fl * |Out-String
$unest = $User |Select-Object -ExpandProperty NESTED_PRIVILEGES | Sort-Object | fl * |Out-String
$Rep = @("
Username: $uname
Assigned Roles:
$urole
Assigned Privlege:
$upriv
Nested Privileges (Formated Role - Privilege):
$unest
")
Write-Output $Rep
}
}
END {}

}

Function Get-HTAReport {
Param (
[String[]]$usernames,
[String]$TNSName
)
Process {
foreach ($UserName in $usernames){
$u = Get-HTAOraclePermissions -UserName $UserName -TNSName $TNSName
$urep = Write-Report -Users $u
$urep | Out-File C:\$TNSName.txt -Append
Write-Output $urep
}
}
}

Get-HTAReport -Usernames $Username -TNSName $TNSName
Get-HTAOraclePermissions -UserName $UserName -TNSName $TNSName

by poshoholic at 2012-11-05 06:58:27

That's a lot of script to dig through. Rather than do that, I want to suggest a few possibilities to consider when generating reports for others from PowerShell.

1. You can get nice-looking tabular output in text format following a technique that is highlighted in this blog post: http://poshoholic.com/2010/11/11/powershell-quick-tip-creating-wide-tables-with-powershell/
2. You really, really should read this free eBook: http://powershellbooks.com/HTMLReportsinPowerShell.zip

Both of those two will probably give you ideas on how you can solve this problem.

by cweislak at 2012-11-05 21:57:10

Thank you I just downloaded the ebook this morning hoping that would help. Thank you for the posts I will read them and make it work.