Output Oracle Permissions

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 3 months ago.

  • Author
    Posts
  • #5946

    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.

You must be logged in to reply to this topic.