DROP USER using the MOLDatabaseQuery functions

PowerShell Help
1

I’m having issues utilizing the MOLDatabaseQuery functions to drop users from oracle databases.
i’ve written a script utilizing the functions from the MOL toolmaking book.
the first half of the script does work fine, it searches and does identify when an account exists. the issue is when i try to invoke-moldatabasequery it fails, no error displayed, no real info for trouble-shooting so i’m just completely stumped
any assitstance would be greatly appreciated

function Get-MOLDatabaseData {
    [CmdletBinding()]
    param (
        [string]$connectionString,
        [string]$query,
        [switch]$isSQLServer
    )
    if ($isSQLServer) {
        Write-Verbose 'in SQL Server mode'
        $connection = New-Object -TypeName `
            System.Data.SqlClient.SqlConnection
    } else {
        Write-Verbose 'in OleDB mode'
        $connection = New-Object -TypeName `
            System.Data.OleDb.OleDbConnection
    }
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    if ($isSQLServer) {
        $adapter = New-Object -TypeName `
        System.Data.SqlClient.SqlDataAdapter $command
    } else {
        $adapter = New-Object -TypeName `
        System.Data.OleDb.OleDbDataAdapter $command
    }
    $dataset = New-Object -TypeName System.Data.DataSet
    $adapter.Fill($dataset)
    $dataset.Tables[0]
    $connection.close()
}

function Invoke-MOLDatabaseQuery {
    [CmdletBinding(SupportsShouldProcess=$True,
                   ConfirmImpact='Low')]
    param (
        [string]$connectionString,
        [string]$query,
        [switch]$isSQLServer
    )
    if ($isSQLServer) {
        Write-Verbose 'in SQL Server mode'
        $connection = New-Object -TypeName `
            System.Data.SqlClient.SqlConnection
    } else {
        Write-Verbose 'in OleDB mode'
        $connection = New-Object -TypeName `
            System.Data.OleDb.OleDbConnection
    }
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    if ($pscmdlet.shouldprocess($query)) {
        $connection.Open()
        $command.ExecuteNonQuery() 
        $connection.close()
    }
}


$path         = Split-Path -parent $MyInvocation.MyCommand.Definition
$input  = $path + "\oracledblist.csv"
$cred = get-credential
$pw = $cred.getnetworkcredential().password
$csv = Import-Csv $input
Start-Transcript oracle.txt
$user = Read-Host "User to process"


foreach ($db in $csv)
{
$database = $db.Database
$string = $db.connection_string + "User ID=$($cred.Username);Password=$pw;"
$dbdata = Get-MOLDatabasedata -connectionstring $string -query "select * from dba_users where username = '$user'"

if ($dbdata)
{
Write-Host "$($dbdata.username) found on $database"
try
{
$query = "DROP USER $($dbdata.username) CASCADE"
Write-Host $query
$drop = Invoke-MOLDatabaseQuery -connectionstring $string -query $query
Write-Verbose $drop
Write-Host "$user dropped"
}
catch
{
Write-Host "Error Dropping user"
write-host "$_"
}
}
else
{
Write-Host no account found on $database
}

}
Stop-Transcript
2

Two things to try. First try Write-Host $drop to see if anything is being captured from your Invoke-MOLDatabaseQuery command. Second, Invoke-MOLDatabaseQuery with a -Verbose switch may provide additional output coming from serverside as it does with Invoke-SQLCmd.

Also, this line need qoutes:

Write-Host $dbdata.username found on $database
3

no go for verbose
the quotes on those lines didn’t make a difference (it works fine with or without the quotes)
i did update the previous post to include the 2 moldatabase functions that i’m trying to use from the learn toolmaking in a month of lunches book.

the lookup function performs fine, identifies the account.
when it steps into the drop user section nothing is returned. (the invoke function does not return anything to the pipeline by design is my understanding so there isn’t anything to be seen)

4

Your not using the pipeline in the code above. A function returns something or doesn’t, this has nothing to do with a pipeline. When you called the ExecuteNonQuery() method, it should return an integer (e.g. 1, -1). If you want additional information, you can try ExecuteScalar(). Use Write-Verbose to help make sure the function is executing like you expect.

5

ok…
i updated to this:
$drop = Invoke-MOLDatabaseQuery -connectionstring $string -query $query -verbose
Write-Verbose $drop

same result, all that is returned when it hits the drop is:
VERBOSE: in OleDB mode
VERBOSE: Performing the operation “Invoke-MOLDatabaseQuery” on target "DROP USER “USERID” CASCADE;’

it does contain the proper userid in the drop statement

and appears immediately to drop to the catch at that point and states Error Dropping User

the same behaviour occured when i changed to $command.ExecuteScalar()

6

This command looks incorrect with double qoutes around it:

DROP USER "USERID" CASCADE;

If you copy and paste that into a query program like TOAD, I don’t think that command will execute. Most variables in commands are surrounded by single qoutes. In most of the examples, I see for the DROP User, there are no qoutes. Possibly try this:

$user = "Rob"
$query = "DROP USER '$user' CASCADE;"
$query

DROP USER 'Rob' CASCADE;
7

nope, the double quotes is a standard in oracle to denote exact match.

we have occasional user-id’s with non-standard chars in them, and without double-quotes oracle will not drop the user.

(as well i’d already tested with and without the double quotes, as well as with the single quotes, same behaviour no matter what)

8

maybe i should ask a different way.

does anyone have working powershell code to drop a user in an oracle database?

9

updated the original post with functioning code…
what was discovered was that this method of calling oracle, did “NOT” like the ;
(determined by write-host “$_” in the catch on the try)