DROP USER using the MOLDatabaseQuery functions

This topic contains 8 replies, has 2 voices, and was last updated by Profile photo of David Schmidtberger David Schmidtberger 5 months ago.

Viewing 9 posts - 1 through 9 (of 9 total)
  • Author
    Posts
  • #38159

    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
    #38161
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    
    #38163

    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)

    #38164
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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.

    #38167

    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()

    #38175
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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;
    
    #38179

    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)

    #38213

    maybe i should ask a different way.

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

    #38220

    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)

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.