Oracle - Add Database User To Role

To use this template in Octopus Deploy, copy the JSON below and paste it into the Library → Step templates → Import dialog.

{
  "Id": "6a0db144-3ad5-46bb-bd7d-02b22b98a559",
  "Name": "Oracle - Add Database User To Role",
  "Description": "Adds database user to a role",
  "Version": 2,
  "ExportedAt": "2020-08-21T21:54:08.753Z",
  "ActionType": "Octopus.Script",
  "Author": "twerthi",
  "Packages": [],
  "Parameters": [
    {
      "Id": "66790019-ca40-41cc-8849-5995557e34c1",
      "Name": "oracleServerName",
      "Label": "Server name",
      "HelpText": "Name of the Oracle server.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "d29f262e-74d2-4c7b-acb3-67b642b614c4",
      "Name": "oracleServerPort",
      "Label": "Port",
      "HelpText": "Port that the Oracle server listens on.",
      "DefaultValue": "1521",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "330f737b-dc2b-4ceb-9d27-168c1b5f6e18",
      "Name": "oracleServiceName",
      "Label": "Service Name",
      "HelpText": "Service name for Oracle database.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "0531e03b-c152-46e4-ab2d-7cb8093aa641",
      "Name": "oracleLoginWithAddRoleRights",
      "Label": "Login name",
      "HelpText": "Login name of a user that can add roles to other users.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "5bddbafe-0ca0-4037-9dd1-d522abc5838e",
      "Name": "oracleLoginPasswordWithAddRoleRights",
      "Label": "Login password",
      "HelpText": "Password for the login account.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    },
    {
      "Id": "2c6ee772-96f6-4803-b6a1-5d57a58b28f0",
      "Name": "oracleUsername",
      "Label": "User name",
      "HelpText": "Name of the user to add the role to.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "7f806b37-e0d5-4560-873e-de6c73f80161",
      "Name": "oracleRoleName",
      "Label": "Role name",
      "HelpText": "Name of the role to add to the user.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    }
  ],
  "Properties": {
    "Octopus.Action.Script.ScriptSource": "Inline",
    "Octopus.Action.Script.Syntax": "PowerShell",
    "Octopus.Action.Script.ScriptBody": "# Define functions\nfunction Get-ModuleInstalled\n{\n    # Define parameters\n    param(\n        $PowerShellModuleName\n    )\n\n    # Check to see if the module is installed\n    if ($null -ne (Get-Module -ListAvailable -Name $PowerShellModuleName))\n    {\n        # It is installed\n        return $true\n    }\n    else\n    {\n        # Module not installed\n        return $false\n    }\n}\n\nfunction Install-PowerShellModule\n{\n    # Define parameters\n    param(\n        $PowerShellModuleName,\n        $LocalModulesPath\n    )\n\n\t# Check to see if the package provider has been installed\n    if ((Get-NugetPackageProviderNotInstalled) -ne $false)\n    {\n    \t# Display that we need the nuget package provider\n        Write-Host \"Nuget package provider not found, installing ...\"\n        \n        # Install Nuget package provider\n        Install-PackageProvider -Name Nuget -Force\n    }\n\n\t# Save the module in the temporary location\n    Save-Module -Name $PowerShellModuleName -Path $LocalModulesPath -Force\n}\n\nfunction Get-NugetPackageProviderNotInstalled\n{\n\t# See if the nuget package provider has been installed\n    return ($null -eq (Get-PackageProvider -ListAvailable -Name Nuget -ErrorAction SilentlyContinue))\n}\n\nfunction Get-UserInRole\n{\n\t# Define parameters\n    param (\n    $Username,\n    $RoleName)\n    \n\t# Execute query\n    $userRole = Invoke-SqlQuery \"SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = '$Username' AND GRANTED_ROLE = '$RoleName'\"\n\n    # Check to see if anything was returned\n    if ($userRole.ItemArray.Count -gt 0)\n    {\n        # Found\n        return $true\n    }\n    \n\n    # Not found\n    return $false\n}\n\n# Define PowerShell Modules path\n$LocalModules = (New-Item \"$PSScriptRoot\\Modules\" -ItemType Directory -Force).FullName\n$env:PSModulePath = \"$LocalModules$([System.IO.Path]::PathSeparator)$env:PSModulePath\"\n$PowerShellModuleName = \"SimplySql\"\n\n# Set secure protocols\n[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls11 -bor [System.Net.SecurityProtocolType]::Tls12\n\n# Check to see if SimplySql module is installed\nif ((Get-ModuleInstalled -PowerShellModuleName $PowerShellModuleName) -ne $true)\n{\n    # Tell user what we're doing\n    Write-Output \"PowerShell module $PowerShellModuleName is not installed, downloading temporary copy ...\"\n\n    # Install temporary copy\n    Install-PowerShellModule -PowerShellModuleName $PowerShellModuleName -LocalModulesPath $LocalModules\n}\n\n# Display\nWrite-Output \"Importing module $PowerShellModuleName ...\"\n\n# Check to see if it was downloaded\nif ((Test-Path -Path \"$LocalModules\\$PowerShellModuleName\") -eq $true)\n{\n\t# Use specific location\n    $PowerShellModuleName = \"$LocalModules\\$PowerShellModuleName\"\n}\n\n# Import the module\nImport-Module -Name $PowerShellModuleName\n\n# Create credential object for the connection\n$SecurePassword = ConvertTo-SecureString $oracleLoginPasswordWithAddRoleRights -AsPlainText -Force\n$ServerCredential = New-Object System.Management.Automation.PSCredential ($oracleLoginWithAddRoleRights, $SecurePassword)\n\ntry\n{\n\t# Connect to MySQL\n    Open-OracleConnection -Datasource $oracleServerName -Credential $ServerCredential -Port $oracleServerPort -ServiceName $oracleServiceName\n\n    # See if database exists\n    $userInRole = Get-UserInRole -Username $oracleUsername -RoleName $oracleRoleName\n\n    if ($userInRole -eq $false)\n    {\n        # Create database\n        Write-Output \"Adding user $oracleUsername to role $oracleRoleName ...\"\n        $executionResults = Invoke-SqlUpdate \"GRANT `\"$oracleRoleName`\" TO `\"$oracleUsername`\"\"\n\n        # See if it was created\n        $userInRole = Get-UserInRole -Username $oracleUsername -RoleName $oracleRoleName\n            \n        # Check array\n        if ($userInRole -eq $true)\n        {\n            # Success\n            Write-Output \"$oracleUserName added to $oracleRoleName successfully!\"\n        }\n        else\n        {\n            # Failed\n            Write-Error \"Failure adding $oracleUserName to $oracleRoleName!\"\n        }\n    }\n    else\n    {\n    \t# Display message\n        Write-Output \"User $oracleUsername is already in role $oracleRoleName\"\n    }\n}\nfinally\n{\n    Close-SqlConnection\n}\n\n\n",
    "Octopus.Action.EnabledFeatures": ""
  },
  "Category": "Oracle",
  "HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/oracle-add-user-to-role.json",
  "Website": "/step-templates/6a0db144-3ad5-46bb-bd7d-02b22b98a559",
  "Logo": "iVBORw0KGgoAAAANSUhEUgAAAGQAAABkCAYAAABw4pVUAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAAAYdEVYdFNvZnR3YXJlAHBhaW50Lm5ldCA0LjEuMWMqnEsAAAX6SURBVHhe7df5jxRVEAdw/gwEhOVYLjkElEMEFBYUMIJHJIpR0Qge6y1RFAkgXlFRCKsoERSVYFAg4Kq/qCiiCGhgEQEjaqJiOLanZ2dm5+iZKauqZ9md6YKdEIbqH4rkk2W/8/q9N6/6ve7tAAAmRMTQ6BFDo0cMjR4xNHrE0OgRQ6NHDI0eMTR6xNDoEUOjRwyNHjE0esTQ6BFDo0cMjR4xNHrE0OgRQ6NHDI0eMTR6xNDoEUOjRwyNHjE0esTQ6BFDo0cMjR4xNHrE0OgRQ6NHDI0eMTR6AoFbMxnM+VO6/kW/kMauvcCcP8k17+GyW0FCwwoSMlaQkLGChIwVJGSsICFjBQkZK0jIWEFCxgoSMpUtSJce/s8efYO6o07di9t37yO3JdS+c6E/yYU9wek3mH+KnxOaT9XpxuiHbarB6dnfbyNdX6pTlT8vqT/KaU59Bp55TiUqVhBanObldZA7fhwgmw1Kp8Fr2A+x2ff7E8bipDZ8Irct8H45ANHJ04Lj4fWJhUsAPA+Sb78jLkDksnGQ3rwV8rGY2Hdq42ZwLx/P/088+0Lg+iLYf/TaGyCzfQfkk8lAX9wHzqfpljsg+cE6cK+YJPcjqEhBnL6DePHAy0Jm27eQXLU66N33IXvkD4BcDppfXgqNHbtB+uNNkE+lILl6rdg+d+Ik5J0I9992PHfMBC5GPpHgn9HrZxR/Pv4qyLsuFyO9aUuw75WruE93DBYE/yWWvFh0fREsRuzOOTxO7thxSK37KNBf89JlftGumwGJxc9DZORYuS/BuS8IHgvNry3nhY4/9Jh/zHTBOzYAc9zW3o4fuK07+kq/IBFc8J54fAjXNN1+F04JIHbvg0Xjed/vhHw8DtFJ13DBcn//Aw4dG9ymGrL7GvhOdsdfze2lvqltOQWhI4hujNy/R7GIeER2xmM3oO3RWi3u2NOpyA7J/vkX3/385YXP24pOnIrD4CIsWIwF2VgoCJ7jHbsGxJ9awG2bbp3lX49fNP7IXM6aX3mdx2v5PbniTf49MnQEFyP14fp251NOQaLTbuQ2me3fQXJZnQyPamfAMPH69pz7guBdn3cc8Hb/5D/0pDZtRC4dDZDPQ/NLr7YWpP8QSK1ZC96u3b4f0Z6f+WzO7j+AhShcO3QkH0XZw7/5dzndifiZt3MX5PEZ5dZMgciocXwdH4slY5cqpyBNN9/GbajIdARK0pu3lOyS8p37guBEMl9t4/M8MmSE3KYF3rGJ+YtwGDyGZs1uPbJ6XYRvOr0h/dkX/BlkMpD5Zjsk61b6b1J0bbdqHocWO/74k3huL+ezm6TWb+DLvL0N4FQPgNzJk5A9eKjdo6OcgnCBcT5pfAEJvCW2KNwwZ6MiR1Z0ynR+OGd/P8JbnAoTMHw0xOc947f79SAXsqgg1BcuIL010TMmvbUeF7c1j919H+8sethTYb1de/Ahe6zViRM4fVzcRc/xAlNbKrA7bqI4H+fi4eCOreFr6BnoCG243eDhkN7yqT/2G2/xA1tq6wy6pGhNylWRgvCC3VPLi0sTzydTAfTaSwvt7d0HEVyMxguqCgVxWxe+0Fdi/kK+K7OHDkNk2Cj+svzwPvof74BTbXHXtEV90xFCi5ZcsZKLTztKmk/my6/xxQJ3CM6X39iENiRd/zn/nZGux92L7WhefHyVtKPd3N6OlFSmIAQn4wwYyncy7YT4vAXF5s7jd/nGbr1PtafzmY4fOq6K+sId0HTTTEjgQz02p5afDfT/6NTpZ/zS9HcFjdU0Yya//dBxE3vg0eBcEBWa/jCN1z4sft7CGVi483Fcd8JkfokItHviaWikN8WS+ZSjcgUxZ8UKEjJWkJCxgoSMFSRkrCAhYwUJGStIyFhBQsYKEjJWkJCxgoRMuwUxusTQ6BFDo0cMjR4xNHrE0OgRQ6NHDI0eMTR6xNDoEUOjRwyNHjE0esTQ6BFDo0cMjR4xNHrE0OgRQ6NHDI0eMTR6xNDoEUOjRwyNHjE0esTQ6BFDo0cMjR4xNHrE0OgRQ6NHDI0eMTRaoMP/3uqi9L3vHOMAAAAASUVORK5CYII=",
  "$Meta": {
    "Type": "ActionTemplate"
  }
}

History