SQL Procedure Privileges

Database
Enthusiast

SQL Procedure Privileges

Definer vs Invoker vs Owner Vs Creator    --- SQL Privileges

 

We are relatively new to using stored procedures in our data load processes (just in the past couple of years).

 

Today we've learned the disadvantage of the default 'definer' priviledge by removing privledges from a user who created a procedure and no longer works at our organization.

 

First question --- Is there a method to determine what privilege was used when the procedure was compiled besides using a 'show procedure' statement and then reviewing the output?  We've been looking at the DBC columns and haven't found anything yet.  

 

Second question --- what were your factors in deciding to use  'invoker' vs 'owner' priviledge?  The 'invoker' option will require more rights granted to the user executing (call) the stored procedure -- which could be defined in a role.  The 'owner' option requires an extract right to create an SP with that option and seems more like a macro.  Anybody executing the stored procedure only needs the 'execute procedure' right.

 

Most of our stored procedures are compliled on the database where the target object is.     So for example if the SP does update/delete/inserting into a table, the SP is compiled on the database where table is located. 

 

3rd QUestion - Have you used the 'Creator' option and why?

   

 

  

2 REPLIES
Teradata Employee

Re: SQL Procedure Privileges

I don't know of any way to determine the SQL SECURITY value other than to inspect the code.

 

Oversimplified (and colored by my own opinions):

  • Definer - backward compatible with the original Teradata implementation of SQL Stored Procedures
  • Invoker - should generally be the default when developing a stored procedure, doesn't "indirectly grant" accesess the caller doesn't already have
  • Owner - particularly useful to encapsulate shared application "services"; you don't have to figure out who is going to call the SP and grant them access to the underlying objects
  • Creator - particularly useful for administrative functions (often using dynamic SQL) where the SP is defined by some highly privileged user, e.g. suppose you want to let certain users refresh stats on certain tables but not drop stats or add new stats.
Enthusiast

Re: SQL Procedure Privileges

Is their a system level setting for changing the default from definer to invoker?

 

So far --  I thinking ==

           For development work - leaving it at invoker

                    -- a developer shouldn't be creating SP's for objects they don't have access to.

 

           For production migration - change it to owner. 

                    -- would make granting rights for service accounts easier and less direct rights needed.