Hide definition

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Enthusiast

Hide definition

 

Hi,

 

Is there a way to hide the defintion of the table /macro/procedure created by us or to give access to only selected users only.

 

Thanks 

6 REPLIES
Teradata Employee

Re: Hide definition

Probably not in the way you intend.

 

When you compile a SQL stored procedure, you can opt not to store the source code - but people can still access the DDL.

Other than that...

 

You can theoretically limit access to the "normal" DBC metadata views (revoke from PUBLIC and grant to certain users / databases / roles) and force everyone (else) to configure the "X views" driver option. That way they can only access the definition if they hold some access right on the object. But be aware that this requires client side configuration changes, adds overhead, and some third-party tools may query DBC views directly (which would fail) rather than using the metadata API calls (which the driver would map to X views). It's not too bad if you impose this restriction from "day 1", but tends to be a major effort to change after the fact.

Junior Contributor

Re: Hide definition

When an SP is created with NO SPL a SHOW results in 5535 No SPL source text available. And dbc.tvm.RequestText never stores info for SPs.

Teradata Employee

Re: Hide definition

Thanks for the correction. I was thinking "parameter metadata" (e.g. HELP PROCEDURE); should not have said "DDL". The actual CREATE statement is not stored except as part of the SPL text.

Enthusiast

Re: Hide definition

 

Thanks Fred & Dnoeth for the details. 

Enthusiast

Re: Hide definition

 

Could you please help as to where exactly i need to use NO SPL statement in procedure.

 

Thanks

Tags (1)
Teradata Employee

Re: Hide definition

Depends on how you are compiling the Stored Procedure.

In BTEQ, add WITH NOSPL to the end of the .COMPILE statement.

For ODBC: set ODBC configuration dialog Advanced Option ProcedureWithSPLSource (Windows), or ini file (non-WIndows) / connection string keyword SplOption=N

For JDBC: use connection string keyword SP_SPL=NOSPL