|
SQL Server T-SQL User-Defined Function of the WeekCheck PERMISSIONS on a UDFVolume 1 Number 51 November 4, 2003Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm
The PERMISSIONS function can be used to tell the caller whether the current user (the caller) has a particular permission on a specified database object. The syntax is:
ojbectid Is the id from sysobjects of the object that PERMISSIONS is being asked about. If it's omitted, PERMISSIONS returns a bit field about the statement permissions granted to the user. 'column' Is the name of the column, if a column permission is requested. The return value is a 32 bit integer representing 32 separate bits. The lower 16 bits are the permissions for the user. The upper 16 bits are the permissions that the user can grant to other users. When an object_id is provided the 16 bits of the permission are interpreted by this table:
Bit (dec) Bit (hex) Statement permission
-------------- -------------- --------------------------
1 0x1 SELECT ALL
2 0x2 UPDATE ALL
4 0x4 REFERENCES ALL
8 0x8 INSERT
16 0x10 DELETE
32 0x20 EXECUTE (procedures only)
4096 0x1000 SELECT ANY (at least one column)
8192 0x2000 UPDATE ANY
16384 0x4000 REFERENCES ANY
For the purpose of PERMISSIONS, user-defined functions are considered procedures. To construct a function that checks to see if the current user can execute a function requires checking the bit 5 (when numbered from 0) of the result.
Here's the CREATE FUNCTION script:
/------- Start copying below this line -\
SET QUOTED_IDENTIFIER ON --
SET ANSI_NULLS ON --
GO
CREATE FUNCTION dbo.udf_Func_MayIExec (
@Function_Name nvarchar(776) -- Function Name to Check
) RETURNS BIT -- 1 if Current User can Execute @Function_Name
-- No SCHEMABINDING due to use of system metadata function
/*
* Returns 1 if the current user may execute the function given
* by @Function_Name.
*
* Equivalent Template: CASE WHEN 0x20=
* PERMISSIONS(OBJECT_ID(@Function_Name)) & 0x20 THEN 1 ELSE 0 END
*
* Example:
select dbo.udf_Func_MayIExec('udf_Func_MayIExec') as [Can Exec Self]
* Test:
PRINT 'Test 1 ' + CASE WHEN 1=
dbo.udf_Func_MayIExec ('udf_Func_MayIExec')
THEN 'Worked' ELSE 'ERROR' END
*
* History:
* When Who Description
* ---------- ------- -----------------------------------------
* 2003-05-23 ASN Initial Coding
****************************************************************/
AS BEGIN
RETURN CASE
WHEN 0x20=PERMISSIONS(OBJECT_ID(@Function_Name)) & 0x20
THEN 1
ELSE 0
END
END
GO
GRANT SELECT on dbo.udf_Func_MayIExec to PUBLIC
GO
\-------Stop copying above this line ----/
Let's see if it works by testing to see if the current user can execute the new udf?
select dbo.udf_Func_MayIExec('udf_Func_MayIExec') as [Can Exec Self]
GO
(Results)
Can Exec Self
-------------
1
(End of Results)
And of course, you can use this with any UDF or all of them as in this query
SELECT ROUTINE_NAME
, dbo.udf_func_mayiexec (ROUTINE_NAME) as [Can Execute]
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='FUNCTION'
GO
(Results)
ROUTINE_NAME Can Execute ---------------------------------- ----------- udf_Tbl_RowCOUNT 1 udf_SQL_UserMessagesTAB 1 udf_SQL_StartDT 1 udf_SQL_VariantToDatatypeName 1 udf_SQL_DefaultsTAB 0 udf_DT_DaysInMonth 1 udf_Txt_FullLen 1 udf_SQL_SearchDBObjectsTAB 1 udf_DT_NthDayInMon 1 ... ...You might notice the Equivalent Expression section in the comment block of the function. Because udf_Func_MayIExec is so simple, it can be replaced with an expression; if you can remember it. That's why it's in the comment block, so it's easy to find. Here's the previous query with the equivalent expression substituted for the UDF:
SELECT ROUTINE_NAME
, CASE WHEN 0x20 = PERMISSIONS(OBJECT_ID(ROUTINE_NAME)) & 0x20
THEN 1 ELSE 0 END as [Can Execute]
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='FUNCTION'
GO
(Results)
ROUTINE_NAME Can Execute ---------------------------------- ----------- udf_Tbl_RowCOUNT 1 udf_SQL_UserMessagesTAB 1 udf_SQL_StartDT 1 udf_SQL_VariantToDatatypeName 1 udf_SQL_DefaultsTAB 0 udf_DT_DaysInMonth 1 udf_Txt_FullLen 1 udf_SQL_SearchDBObjectsTAB 1 udf_DT_NthDayInMon 1 ... ...Of course, the results are identical. I wouldn't worry about doing the substitution because the relatively small number of records means that the time to execute the query isn't going to be much different with the UDF or the expression version. However, when the number of rows grows larger, the performance difference can be substantial: 100's of times. Chapter 11 of Transact-SQL User-Defined Functions has a through discussion of performance issues with UDFs. Last week I mentioned that sales of my book have started on Amazon. Although they've got the title incorrect and the wrong picture for the cover, Amazon is already taking pre-shipment orders.
But for a few dollars more, I'll be selling it bundled with an extra library of 100 UDFs that don't appear in the book and won't be published here. Stay tuned, I should have the details ready next week. Of course, I thought that last week but the books haven't arrived yet. Regards,
Do you have a great UDF that you'd like to share? Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com and they might be published in this newsletter. I try and respond to every request that I get. Thanks, Andrew Novick +--------------------------------------------------------------+
|
|
