tjmateus opened a new pull request #9405: Added support for impersonation on SQL Server databases using the pym… URL: https://github.com/apache/incubator-superset/pull/9405 …ssql adapter. ### CATEGORY Choose one - [ ] Bug Fix - [ x] Enhancement (new features, refinement) - [ ] Refactor - [ ] Add tests - [ ] Build / Development Environment - [ ] Documentation ### SUMMARY By default, the impersonation on Superset for SQL Server connections is done by replacing the user of the connection string by the logged in user name. This was not the behaviour we were expecting at DefinedCrowd. We want to have an application user capable of impersonating the logged in user. The solution I'm proposing is to use the mssql+pymssql SQLAlchemy adapter you already support and use the conn_properties query parameter, which allows us to send SQL queries to the server upon the connection establishment. This query would be EXECUTE AS USER = '<USER_TO_IMPERSONATE>', which will run all the following queries as the logged in user. ### TEST PLAN ####Exploratory test 1. Create a database user for your logged in user. They **must** share the same username; 2. Create an application user on a SQL Server database. This user must be granted with the `IMPERSONATE` permission as below: ``` GRANT IMPERSONATE ON USER::[<USER_TO_IMPERSONATE>] TO <APPLICATION_USER>; ``` 3. On Superset UI, create a new database source with a connection with the following syntax: ``` mssql+pymssql://<username>:<password>@<server>:<port>/<database_name> ``` 4. Enable the `Impersonate the logged on user` flag; 5. Run the query `SELECT CURRENT_USER as username;` on SQL Lab; 6. The expected output should be your logged in user and not the application one. ####Unit test If you think this feature would be useful for community, I will invest time on this kind of tests.
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
