Prompting User to Log in to Database in Python Addin

[MikeMacRae]

I have built a python addin where a user selects a bunch of polygon features in ArcMap which then queries an Oracle database and provides a spreasheet of the results.

This works well but the final peice of the puzzle is I need to be able to prompt my users to enter their credentials for the oracle database login so that the script can query the database.

For the record, I am using the python module:

import cx_Oracle

to connect to the database using the syntax:

db = cx_Oracle.connect('{0}/{1}@//random.company:1234/db1.company.com'.format(username, password)

to login. To explain, I am passing the username and password into the connection string via the .format() method. Which ever way I design a login prompt, the script will grab the users credentials and pass them into the connection string.

In the python addin help documentation, I do not see a function or property anywhere to pop up a dialogue that will accept some sort of textual input. For example, a text box with 2 entry fields. One for the username and one for the password and then a button to accept the credentials. Or some other option that will allow this.

Couple answers to questions you may have:

Q: Why don't you set the environment workspace to the oracle database connection which gives an automatic dialogue to prompt for credential:

(i.e arcpy.env.workspace = r"Database Connections\oracleDB.sde")

A: This works fine, however, after logging into the database, I need to query it. Arcpy offers the 'Make Query Layer' tool to build and compile a query in Oracle. After working with this tool for a week or so, it turns out it has a bug which populates incorrect query results (see bug number below). ArcGIS and arcpy do not offer any other option to submit complex queries to Oracle sde connection via python. This is why I choose to use cx_Oracle for this portion of my script. That and it executes much much faster!

BUG-000090452 : The Make Query Layer tool does not respect the Unique fields variable in Model builder, or the oid_fields keyword in Python arcpy.MakeQueryLayer_management() command.

Q: Why don't you use some third party GUI builder like Tkinter?

A: Any use of Tkinter in ArcGIS (Map, Catalogue, etc) will cause it to crash. From what I've read, ArcGIS will not support third party GUI builders. Also, I did test a tkinter widget this and it crashed everytime when using in ArcMap. outside of ArcMap, it worked fine.

Q: Why not just use a combobox as a textual input for a user name and/or password?

A: The problem I have with this is, when the user types into the combobox, I can't mask the characters the user is entering for the password (i.e. when the user types into the box, only asterix appears). I don't want to expose their password to a lurking employee looking over their shoulders.

Anyways, what I am looking for is a long shot I think. if anyone can throw an idea or 2 out there, I'm willing to test it.

[DanPatterson_Retired]

I don't suppose that there is anything in the Arcgis PRO conda installation that may be of use

/blogs/dan_patterson/2016/07/17/anaconda-spyder-and-arcgis-pro 

or if you do you should be able to add new modules easily if they are within the full stack.... check the 3.4 link in this post Anaconda package list | Continuum Analytics: Documentation 

It will just simplify patching up a 2.7 version.... you will eventually have to move anyway

[RebeccaStrauch__GISP]

Is the main need to be able to pass the credentials each time?  Although this may seem off subject, read thru this thread  https://community.esri.com/thread/180549    And think about reading a settings.ini file to grab the credentials and pass it as needed. I'm using this method to give admin rights for my SQL, for my purposes, without prompting or having the user/pass hard coded in the results or other log file.  I keep the settings.ini file in a more secure location.  I would think you could do something similar for each user with the permissions they need.

Not sure if that could be a solution or not.....I'm not an oracle person so not sure about security and access, etc.

[MikeMacRae]

Thanks for the iidea, Rebecca. I like this idea and it might work for a personal project I am working on but in this case, I don't have that option. I work for a very large organization with a central IT and security department who's best practice manual indicates that we are not to store username and password information. This also includes exercising any option on a given GUI that allows gives you the option to do so.

[RebeccaStrauch__GISP]

Hi Mike,

Warning...make sure to always check the Results tab to make sure it isn't being stored there, which was my issue. I started looking at the stop/start AGS admin toolbox by khibma-esristaff‌ and using that type of dialog, it does store it so you can rerun it. I know he is working on a new version, and maybe he'll address this.  ??

In my case it is for getting a token, and I have a static user/pass with limited network privileges and access. I have the settings file on a drive that needs increased priveldges, so only someone with admin rights can run the scripts. Of course nothing is 100% secure, so I understand if you can't work with this, especially if each user needs separate login creds.   Maybe if there is a way to tap info the "current user creds" ?  I haven't figured it out, but I don't manage our network/domain users.  I'm always interested in alternatives too.

[MikeMacRae]

Ha, I like your train of thought! You can get the users user name by using the getuser method in the getpass module:

from getpass import getuser

getuser()

unfortunately, I'd need a lot of hacking knowledge to get someones password from the system/network in order to automate the connection to oracle.

[DanPatterson_Retired]

checkbox... did you look over your shoulder?   is looking better

[RebeccaStrauch__GISP]

Here's another thought, as mentioned by others by creating a connection file.  I have a connection called

"me@myDatabaseName" and it uses domain credentials, not hard coded.  That way, I can put it on everyone's machine and it takes their credentials automatically.

Create ArcSDE Connection File—Help | ArcGIS for Desktop 

OPERATING_SYSTEM_AUTH —Use Operating system authentication. You do not need to type a user name and password. The connection will be made with the credentials used to log in to the operating system. If the login used for the operating system is not a valid geodatabase login, the connection will fail. Also note you cannot make an ArcSDE service connection using operating system authentication to a geodatabase stored in Oracle, DB2, or Informix.

.....oops, just noticed it says not Oracle.  Oh, well, I'll leave this in case it sparks another idea.