search

Teradata / PyTd

A Python Module to make it easy to script powerful interactions with Teradata Database in a DevOps friendly way.
MIT License
108 stars 43 forks source link

udaexec doesn't throw exception in creating stored procedure when the permission is not granted #55

Open ljohnny1234 opened 7 years ago

ljohnny1234 commented 7 years ago

PyTd version is 15.10.0.18.

When using PyTd to create procedure, if the deployment user doesn't have permission, it doesn't fail the query but showing query successful. By drilling down, it seems like udaexec doesn't throw exception if the user permission is not granted.

On the other hand, if it is a simple SELECT statement but user has no permission, udaexec throws out exception and fails the execution.

In contrast, if you run the same REPLACE PROCEDURE script in Teradata SQL Assistant, it gets error saying: "SPL1040:E(L15), Invalid definition for stored procedure 'E(3524):The user does not have CREATE PROCEDURE access to database XXXXX.'. SPL1027:E(L46), Missing/Invalid SQL statement'E(5315):The user does not have INSERT access to .

.'."

escheie commented 7 years ago

The reason an exception is not thrown is because ODBC returns SQL_SUCCESS instead of SQL_ERROR when the REPLACE PROCEDURE statement is executed. I have a question out to the ODBC support asking if this is expected behavior.

You can get the error lines above via the python module though, as they are returned in the "REPLACE PROCEDURE" results. If you check if rows are returned from the REPLACE PROCEDURE statement, you should be able to determine if the statement was successful or not.

escheie commented 7 years ago

I followed up with the ODBC team and this is the expected behavior. The ODBC user guide states the following:

SPL Compilation Errors and Warnings

Upon successfully completing the creation of a stored procedure, SQL_SUCCESS is returned to the client application. SPL compilation errors and warnings are reported by Teradata Database as part of the SUCCESS return code only as a direct response to the CREATE PROCEDURE or REPLACE PROCEDURE request. The application has to fetch the compilation errors and warnings with SQLFetch, followed by SQLGetData, until SQLFetch returns SQL_NO_DATA_FOUND. If compilation errors are found, the stored procedure is not created or replaced.

I've requested if an ODBC feature enhancement can be opened so that applications can request to receive a SQL_ERROR from failed SP create requests so that the Python module can throw an exception rather than force users to manually check the ResultSet.

ljohnny1234 commented 7 years ago

Hi @escheie , any update from odbc team about this?

escheie commented 7 years ago

This issue is being tracked by ODBC-17116. It is in the backlog but not yet targeted for a release.

The workaround in the meantime is to check the result set returned by the CREATE PROCEDURE or REPLACE PROCEDURE command to determine if it was successful or not.

ljohnny1234 commented 7 years ago

Any update on this? @escheie

escheie commented 7 years ago

This is still on the backlog and not yet committed to a release.

  • © Githubissues.
  • Githubissues is a development platform for aggregating issues.