Stored Procedure: SQL SECURITY: How to check?

Database

Stored Procedure: SQL SECURITY: How to check?

This question is in relation to the SQL SECURITY clause which can (should be!) be specified when creating a stored procedure.

https://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1148_111A/ch0...

 

Is there any way of querying the metadata (e.g. dbc tables) to find out which stored procedures are using the various options of SQL SECURITY (?

I know you can tell by running "SHOW PROCEDURE X", and grepping for the string (or assuming the default option, DEFINER, if not found, but clearly this is cumbersome.


Accepted Solutions
Highlighted
Junior Contributor

Re: Stored Procedure: SQL SECURITY: How to check?

It's  encoded in dbc.tvm.SPObjectCodeRows:

'SQL Security ' ||
CASE SPObjectCodeRows MOD 10
  WHEN 0 THEN 'Definer'
  WHEN 1 THEN 'Invoker'
  WHEN 2 THEN 'Owner'
  WHEN 3 THEN 'Creator'
END

This might change with different TD releases...

 

1 ACCEPTED SOLUTION
2 REPLIES
Highlighted
Junior Contributor

Re: Stored Procedure: SQL SECURITY: How to check?

It's  encoded in dbc.tvm.SPObjectCodeRows:

'SQL Security ' ||
CASE SPObjectCodeRows MOD 10
  WHEN 0 THEN 'Definer'
  WHEN 1 THEN 'Invoker'
  WHEN 2 THEN 'Owner'
  WHEN 3 THEN 'Creator'
END

This might change with different TD releases...

 

Re: Stored Procedure: SQL SECURITY: How to check?

Many thanks Dieter
PS: The ranking system on this forum must be seriously faulty to show you as a "Junior Contributor"...