It is true that you cannot grant EXEC
permissions on a function that returns a table. This type of function is effectively more of a view than a function. You need to grant SELECT instead, e.g.:
GRANT SELECT ON dbo.Table_Valued_Function TO [testuser];
So your script would look more like this (sorry, but I absolutely loathe INFORMATION_SCHEMA
and much prefer to use the catalog views, which also don't need functions like OBJECTPROPERTY
):
DECLARE
@sql NVARCHAR(MAX) = N'',
@username VARCHAR(255) = 'testuser';
SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT ' + CASE
WHEN type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION'
OR type_desc = 'VIEW'
THEN ' SELECT ' ELSE ' EXEC ' END
+ ' ON ' + QUOTENAME(SCHEMA_NAME([schema_id]))
+ '.' + QUOTENAME(name)
+ ' TO ' + @username + ';'
FROM sys.all_objects
WHERE is_ms_shipped = 0 AND
(
type_desc LIKE '%PROCEDURE'
OR type_desc LIKE '%FUNCTION'
OR type_desc = 'VIEW'
);
PRINT @sql;
-- EXEC sp_executesql @sql;
Now you can grant EXEC
on a schema, and always create these procedures in that schema (actually one of the purposes of schemas!), which @jgardner04 already suggested, however in order for this solution to apply to table-valued functions as well, you'd also have to grant SELECT
. Which is okay if you're not storing any data in tables in that schema (or at least that you want to hide from them), but it will apply to any tables and views as well, which might not be your intention.
Another idea (e.g. if you can't, or don't want to, use schemas) is to write a DDL Trigger that captures the CREATE_PROCEDURE
, CREATE_FUNCTION
and CREATE_VIEW
events, and grants permissions to a user (or a set of users, if you want to store them in a table):
CREATE TRIGGER ApplyPermissionsToAllProceduressAndFunctions -- be more creative!
ON DATABASE FOR CREATE_PROCEDURE, CREATE_FUNCTION, CREATE_VIEW
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@sql NVARCHAR(MAX),
@EventData XML = EVENTDATA();
;WITH x ( sch, obj )
AS
(
SELECT
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)')
)
SELECT @sql = N'GRANT ' + CASE
WHEN o.type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION'
OR o.type_desc = 'VIEW'
THEN ' SELECT '
ELSE ' EXEC ' END
+ ' ON ' + QUOTENAME(x.sch)
+ '.' + QUOTENAME(x.obj)
+ ' TO testuser;' -- hard-code this, use a variable, or store in a table
FROM x
INNER JOIN sys.objects AS o
ON o.[object_id] = OBJECT_ID(QUOTENAME(x.sch) + '.' + QUOTENAME(x.obj));
EXEC sp_executesql @sql;
END
GO
The drawback I find with DDL Triggers is that you can quickly forget that they're there. So a year down the road when you decide to stop granting these permissions to all new objects, it might take a while to troubleshoot why it's still happening. At my last job we logged all actions invoked by DDL triggers to a central "event log" of sorts, and that was our go-to place for tracking down any actions that happened on the server that nobody seems to remember (and it was a DDL trigger about half the time). So you may consider adding some additional logic that will help with that.
EDIT
Adding code for schema-based, and I'll mention again that this will grant permissions on any procedures, functions and tables created in the foo schema.
CREATE SCHEMA foo;
GRANT EXEC, SELECT ON SCHEMA::foo TO testuser;
Now if you create the following procedure, testuser will be able to execute:
CREATE PROCEDURE foo.proc1
AS
BEGIN
SET NOCOUNT ON;
SELECT 1;
END
GO
PL/PgSQL and plain SQL functions are both part of a larger tool set, and should be viewed in that context. I tend to think of it in terms of an ascending scale of power matched by ascending complexity and cost, where you should use the simplest tool that'll do the job well:
- Use views where possible
- Where a view is not suitable, use an SQL function
- Where an SQL function isn't suitable, use PL/PgSQL.
- Where PL/PgSQL is too limited or not expressive enough, use PL/Perl, PL/Python, PL/V8, PL/Java, or whatever your preference is
- ... and where no PL will do the job, use an external program and possibly
LISTEN
and NOTIFY
to talk to it.
Very frequently a view is sufficient when you think a function is needed. Even if it's extremely expensive to SELECT
the whole view, WHERE
clauses in the query referencing the view are usually pushed down into the view and may result in very different query plans. I've often had big performance improvements from converting SQL functions into views.
The main time you find you can't use a view and should consider an SQL function is when:
- Parameters that can't be expressed as simple
WHERE
clauses are needed, like a parameter within a WITH
expression
- You want a security barrier via a
SECURITY DEFINER
function, and the security_barrier
views in PostgreSQL 9.2 and above aren't sufficient for your needs;
- You need parameters that aren't pushed down into sub-clauses of a view by the optimizer and want to control it more directly; or
- There are lots of params or there's lots of repetition of the params, so it's impractical to write the query as a view.
For most of those tasks a plain SQL function works fine, and is often easier to read than PL/PgSQL. SQL functions declared STABLE
or IMMUTABLE
(and not also declared STRICT
or SECURITY DEFINER
) can also be inlined into the calling statement. That gets rid of the function call overhead and can also sometimes result in huge performance benefits when a WHERE condition in the calling function gets pushed down into the SQL function by the optimizer. Use SQL functions whenever they're sufficient for the task.
The main time SQL functions won't do the job is when you need lots of logic. If/then/else operations that you can't express as CASE
statements, lots of re-use of calculated results, building values up from chunks, error handling, etc. PL/PgSQL comes in handy then. Choose PL/PgSQL when you can't use SQL functions or they're a poor fit, like for:
- Dynamic SQL and dynamic DDL via the
EXECUTE
statement
- When you want to
RAISE
errors/warnings for the logs or client
- When you need exception handling - you can trap and handle errors with
EXCEPTION
blocks instead of having the whole transaction terminate on error
- Complex conditional logic that doesn't fit
CASE ... WHEN
very well
- Lots of re-use of calculated values that you can't do fit into
WITH
and CTEs
- Building dynamic records
- You need to perform an action after producing the result set
With common table expressions (CTEs), especially writable CTEs and WITH RECURSIVE
I find I use PL/PgSQL a lot less than I used to because SQL is so much more expressive and powerful. I use views and plain SQL functions a lot more now. It's worth remembering that plain SQL functions can contain more than one statement; the last statement is the function's result.
Best Answer
Officially, PostgreSQL only has "functions". Trigger functions are sometimes referred to as "trigger procedures", but that usage has no distinct meaning. Internally, functions are sometimes referred to as procedures, such as in the system catalog
pg_proc
. That's a holdover from PostQUEL. Any features that some people (possibly with experience in different database systems) might associate with procedures, such as their relevance to preventing SQL injections or the use of output parameters, also apply to functions as they exist in PostgreSQL.Now, when people in the PostgreSQL community talk about "stored procedures" or "real stored procedures", however, they often mean a hypothetical feature of a function-like object that can start and stop transactions in its body, something that current functions cannot do. The use of the term "stored procedure" in this context appears to be by analogy to other database products. See this mailing list thread for a vague idea.
In practice, however, this distinction of function versus procedure in terms of their transaction-controlling capabilities is not universally accepted, and certainly many programmers without database bias will take a Pascal-like interpretation of a procedure as a function without return value. (The SQL standard appears to take a middle ground, in that a procedure by default has a different transaction behavior than a function, but this can be adjusted per object.) So in any case, and especially when looking at questions on Stack Exchange with a very mixed audience, you should avoid assuming too much and use clearer terms or define the properties that you expect.