Joining a User-defined table function
-
I want to be wrong - please tell me I am mistaken
I am trying to use Table Function to filter data based on rights. According to rules set up in other tables, the table function returns a True or False that a given person can access a specific object. I.E.
--Function definition
CREATE FUNCTION faCanAccessObject (
@personID INT,
@objectID INT
) RETURNS @AccessRights TABLE (
CanAccess TINYINT
) AS
BEGIN
INSERT INTO @AccessRights
<Lots of code>
RETURN
END
I would like to do this:
SELECT P.PName, D.DocumentName, A.CanAccess
FROM Person P,
INNER JOIN Document D ON 1=1
INNER JOIN faCanAccessObject( P.PersonID, D.DocID ) A
There will be an incredibily huge volume so it needs to be a subselect and not the main table (85 million rows - 100,000+ requests per day)
Ideas?
Thanks
Guarddata-
-
You don't want to use : PERMISSIONS() from T-SQL?
Tim C.
//Will write code for food
Tim C //Will code for food -
I may not be understanding the PERMISSIONS function, but was under the impression that is only identifies that I have rights to select from the Database Table. I need data level access where some items can be selected and others can't based on group membership and other rules.
TABLE - Document
TABLE - Members of a group
TABLE - DocsRights ( DocID, GroupID )
SELECT D.DocName, R.CanAccess
FROM Document D
INNER JOIN DocRights( PersonID, D.DocID) R
WHERE ( D.CreateDate + 7 ) > GetDate()
Enough info?
Guarddata-
-
based upon from BOL :
quote:
Bit (dec) Bit (hex) Statement permission1 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
and your query, Would
SELECT D.DocName
FROM Document D
WHERE PERMISSIONS(OBJECT_ID('Document'))&1=1
AND (D.CreateDate + 7) > GetDate()Not work for you? It even take an optional second parameter for the column, so you could test column permissions.
Tim C.
//Will write code for food
Tim C //Will code for food -
Kind of a different need I think.
Consider this:
CREATE TABLE Document (
DocID INT,
DocName VARCHAR(30)
)
INSERT INTO Document VALUES ( 1, 'First Test' )
INSERT INTO Document VALUES ( 2, 'Second Test' )
INSERT INTO Document VALUES ( 3, 'Third Test' )
My rules are set so that I can access #s 1 and 3 but not 2.
Can the PERMISSIONS function still resolve this?
Guarddata-
-
I see said the blind man. Interesting issue. How do you store the permissions? Can you post the DDL? I have never tried to do row permissions before... I'm actually at a loss to think of a clean / fast way to do this. Hopefully the gurus on this site can help. Sorry guard.Tim C.
//Will write code for food
Tim C //Will code for food -
It would take about 3 pages to fully describe the process (and this thread is getting close
)The permission rules are in a table like:
TABLE permissions
ObjectID INT, --Document being secured
IsPerson TINYINT, --Person can override group settings
SecureID INT, --ID of the person or group
SeeRights,
ChgRights,
DelRights
The Rights are TINYINT values. 1 means Grant, 2=Revoke, NULL (for Person only) accept group setting.
I can write a function that gives me the result I need - but cannot join it. For now, I will probably just create a trigger that produces the privileges when the rule is changed and keeps it in a table used for the joins. A bit messy - but probably better in the long run since permissions should change that much.
Thanks for looking at it.
Guarddata-
-
Ok, you can join a table UDF to a query. Try this :
USE PUBS
GO
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'fnPubsSyobjectsTable')
DROP FUNCTION fnPubsSyobjectsTable
GO
CREATE FUNCTION fnPubsSyobjectsTable()
RETURNS @table_var TABLE(name sysname,
id int)
AS
BEGIN
INSERT INTO @table_var
SELECT name, id
FROM sysobjects
RETURN
END
GO
SELECT SO.name As SOname,
PSO.name As PSOname
FROM sysobjects SO
INNER JOIN [dbo].[fnPubsSyobjectsTable]() PSO
ON SO.id = PSO.id
DROP FUNCTION fnPubsSyobjectsTableHope this helps with the main question you had, Sorry for the tangent.
Tim C.
//Will write code for food
Tim C //Will code for food -
Thanks for taking the time on this one Tim.
Yes - I can join in the way you describe. I was just nervous about the efficiency - probably my lack of experience with the defined tables. My first impressions where that the defined table would be created like a temporary table, then joined - so I wanted the smallest result set possible.
It appears that SQL is better than that so I will run some tests and go from there.
Thanks again
Guarddata-
-
Guard I think I did you a disservice by misunderstanding your initial question, and going off post with my answers. I apologize. As to your question I have seen a couple threads on these forums about UDF's and efficiency, and I will try to find them for you. Having never done a study on them myself I am going to bump this post up to the experts otherwise.
Tim C.
//Will write code for food
Tim C //Will code for food
I see said the blind man. Interesting issue. How do you store the permissions? Can you post the DDL? I have never tried to do row permissions before... I'm actually at a loss to think of a clean / fast way to do this. Hopefully the gurus on this site can help. Sorry guard.
)Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply