Is there a permission that will allow a particular user (ME) to execute an EXPLAIN of Sql that updates or inserts into tables or selects from tables that I don't have update or insert or select permissions on? (or creates a table in a database that the "Explaining user" doesn't have permission to)? I deal with a lot of different groups across the company & need to evaluate queries and performance issues on a number of databases across the organization.
The problem is -- I'm not a DBA. I don't have any permission to the majority of the data or the tables that I work with. I can't even execute an explain that inserts or deletes data because my account doesn't have access. I either need to go thru the painful and time consuming process of requesting access to data that I don't really need access to -- or I need to *try* to work thru other people who have access to the data. In addition, in my industry, i techincally shouldn't have access to data that's not necessary. An explain really doesn't require access to insert or delete -- it just needs access to a certain level of meta-data -- that the optimizer requires to figure out how to execute the query. Rowcounts would be similar as well to this as well.
It's interesting that I CAN execute a SHOW + sql statement & get back all of the structures... but i just can't do the EXPLAIN + sql statement if i don't have access to the tables.
does anyone have any suggestions? (I'm hoping that either someone will say -- YES -- request permission X ---- or that perhaps Teradata will take note & create a Permission which allows someone to see more metadata type infromation -- like row counts & explains & data structures & stats, etc);
Thanks All for the help in advance.
you don't want to hear that answer:
-- NO -- , at least for the Explain part
This would be a really nice feature, i know that problem, too :-(
You could create an enhancement request, but this usually needs year(s) to be implemented and will not solve your current problem.
SHOW currently works because got a select right on dbc.tvm.
But if you're on TD13 you should ask your friendly DBA to get the new SHOW right for all those databases, which allows any kind of SHOW and HELP including HELP STATS.
I think i have those permissions. Just that I can't explain anything that i don't have access to... Wish i would have thought of this a few years ago, so that I could have it NOW! :)
any idea where to create an enhancement request?
I created DBS RFC 154806 -- "Define new privilege GRANT EXPLAIN SELECT".
In the Justification section, I wrote:
Some database users need to be able to do an EXPLAIN on a query, see the explain, and recommend query changes; but those users should not have permission to actually execute the query and see the data.
It's easy to create an RFC, but unfortunately, as Dieter said, it's a lot more difficult to get the RFC on the Teradata Database product roadmap for inclusion in a future release.
No activity has occurred on that DBS RFC since I created it in 2011.
Pierre, your profile says that you are with Orange France. If that's still true, create an incident with Teradata Customer Support and ask that DBS RFC 154806 be tagged with your incident number. That will convert the DBS RFC into a "Customer" RFC, and will make it more likely to be scheduled for work.
Was not able to find anything in the 16.20 documents. So I guess this is still not implemented?
It would be still very vital to be able to analyse performance issues...
No, not in 16.20.
The closest you can probably come to this is for the query to be run (which I know won't always be possible) with the DBQL XMLPLAN or EXPLAIN logging in place. At least then you can look at the plan.
yes, that would be nice...
BUT explain logging is activated for all user but it looks like not all sqls are logging the explain :-(
Seems like SQLs which are called within a SP are not storing the explain. All other things are logged in DBQL...
So we need to run the explains our own, which we can't due to missing rights...