March 12, 2009 at 8:20 am
Hi,
Im try to bring back all column specified permissions with 1 select statment. From what i understand the columns field contains a binary value which denotes if a value of 1 all fields and from then on the ordinal position of the field.
I wrote the below function to bring back the column links. But im wondering if there is simpler way to do this.
Please see the below function.
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='fn_FINDCOLUMNIDS')
BEGIN
DROP FUNCTION fn_FINDCOLUMNIDS
END
GO
CREATE FUNCTION fn_FINDCOLUMNIDS (@VARBIN VARBINARY)
RETURNS @retCOLIDS TABLE (ID BIGINT)
AS
BEGIN
IF @VARBIN<=1
BEGIN
GOTO THEEND
END
DECLARE @BIN INT
DECLARE @BINPOS BIGINT
DECLARE @COMPARE INT
DECLARE @FOUND INT
DECLARE @COUNT INT
SELECT @BIN=CONVERT(INT,@VARBIN),@BINPOS=1,@FOUND=0,@COUNT=1
SELECT @COMPARE=@BINPOS
WHILE @FOUND=0
BEGIN
IF @BIN<@BINPOS*2
BEGIN
INSERT @retCOLIDS VALUES (@COUNT-1)
SELECT @COMPARE=@BINPOS,@BINPOS=@BINPOS/2
WHILE @BINPOS<>0
BEGIN
SET @COUNT=@COUNT-1
IF @BIN>=(@COMPARE+@BINPOS)
BEGIN
INSERT @retCOLIDS VALUES (@COUNT-1)
SET @COMPARE=@COMPARE+@BINPOS
SET @BINPOS=@BINPOS/2
END
ELSE
BEGIN
SET @BINPOS=@BINPOS/2
END
IF @BINPOS=1
BEGIN
SET @FOUND=1
END
END
END
ELSE
BEGIN
SET @BINPOS=@BINPOS*2
END
SET @COUNT=@COUNT+1
END
THEEND:
RETURN
END
GO
Any help on this would be greatly appriciated.
Thanks
PostIT
March 12, 2009 at 8:52 am
Yes you could make this much faster (and simpler, IMHO) by replacing the outer loop with a join to Tally table to index the bytes and replacing the inner loop with a join to the numbers 0 to 7 to index the bits.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2009 at 9:01 am
How would i join this to the varbinary value and also offset it as the first binary bit is the all permission setting?
March 12, 2009 at 11:54 am
Actually, I came up with an even better way to do it. Hang on while I write it up and test it...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2009 at 12:21 pm
Cool thanks.
March 12, 2009 at 12:55 pm
Here you go, I think that you'll find that this works much better:
CREATE FUNCTION fnFINDCOLUMNS (@VARBIN VARBINARY(4000), @TableID int)
RETURNS TABLE AS RETURN
Select id, colid, [name]
From syscolumns
Where colid > 0
And id = @TableID
And Substring(@VARBIN, ((colid-1)/8)+1, 1) & power(2,(colid-1)%8) > 0
GO
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 13, 2009 at 4:15 am
I tried you code but i didnt get the result i expected. Though thanks to the ideas put forward in your script i have managed to get what i think works. Please see below.
select * from sysprotects sp, syscolumns sc
where sp.id=sc.id
and (power(2,colid) & sp.columns)>1
March 13, 2009 at 4:23 am
Or the entire script to get all object based permissions is...
SELECT
su2.name as 'Owner',
so.name AS 'Object',
su.name AS 'Grantee',
su3.name AS 'Grantor',
'ProtectType'=
CASE sp.protecttype
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
END,
'Action'=
CASE sp.action
WHEN 26 THEN 'REFERENCES'
WHEN 178 THEN 'CREATE FUNCTION'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
WHEN 236 THEN 'CREATE RULE'
END,
'Column'=
CASE ISNULL(CAST(COLUMNS AS INT),0)
WHEN 0 THEN 'None'
WHEN 1 THEN 'All'
ELSE sc.name
END
FROM sysprotects sp
INNER JOIN sysusers su on su.uid=sp.uid
INNER JOIN sysobjects so on so.id=sp.id
INNER JOIN sysusers su2 on so.uid=su2.uid
INNER JOIN sysusers su3 on sp.grantor=su3.uid
LEFT JOIN syscolumns sc on sp.id=sc.id and power(2,sc.colid)&sp.columns > 1
WHERE so.xtype <> 'S'
Thanks for pointing me in the right direction.
:D:D:D
March 13, 2009 at 5:56 am
EvilPostIT (3/13/2009)
I tried you code but i didnt get the result i expected. Though thanks to the ideas put forward in your script i have managed to get what i think works. Please see below.
select * from sysprotects sp, syscolumns sc
where sp.id=sc.id
and (power(2,colid) & sp.columns)>1
Believe it or not, I woke up early this morning with the realization that I had given you the wrong answer. :w00t: Unfortunately, I reversed the byte-ordering (big-endian vs. little-endian thing, at the byte level).
Anyway, your solution is an ingenious and simpler imrpovement, however, I beleive that it will fall off either at the 33rd or the 64th column (not sure). I have a meeting to run to, but I believe that this is the corrected version of mine:
CREATE FUNCTION fnFINDCOLUMNS (@VARBIN VARBINARY(4000), @TableID int)
RETURNS TABLE AS RETURN
Select id, colid, [name]
From syscolumns
Where colid > 0
And id = @TableID
And Substring(@VARBIN, (LEN(@VARBIN)-((colid-1)/8+1), 1) & power(2,(colid-1)%8) > 0
GO
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 13, 2009 at 5:57 am
EvilPostIT (3/13/2009)
Or the entire script to get all object based permissions is...
An even better approach. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 13, 2009 at 7:14 am
Yep your right it falls over on the 32nd. Have been working on this since about 5 mins after posting the entire script. Will try and intgrated the new code you have posted into the join clause. Just 1 question though. For what purpose are you using the substring function?
March 13, 2009 at 7:54 am
By the looks of things to fix this issue i cannot use the power function as that specifically returns an int value based upon the data type of the input expression. Any ideas how to effectivly do a "to the power of" statement without using a function (ie in native sql)...
March 13, 2009 at 1:39 pm
EvilPostIT (3/13/2009)
Yep your right it falls over on the 32nd. Have been working on this since about 5 mins after posting the entire script. Will try and intgrated the new code you have posted into the join clause. Just 1 question though. For what purpose are you using the substring function?
A VARBINARY is a string data type, just like VARCHAR is, however it is a string of TINYINT's instead of a string of CHAR(1)'s.
As such, many of the string functions will also work on VARBINARY's. In this case I am using Substring(..) to extract the Byte (TINYINT) that contains the (colID) bit. Then I use Power(..) to mask to the actual bit in that byte. This works for any number of columns because the Power function never has to go over 28 (256).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 13, 2009 at 1:45 pm
EvilPostIT (3/13/2009)
By the looks of things to fix this issue i cannot use the power function as that specifically returns an int value based upon the data type of the input expression. Any ideas how to effectivly do a "to the power of" statement without using a function (ie in native sql)...
Well you could cast your base as a BIGINT:
power(Cast(2 as BIGINT),sc.colid)&sp.columns
That will get you to 64 columns.
Although you could cast it to a much larger DECIMAL datatype, that won't help because the bitwise AND operator ("&") only works upto BIGINT anyway.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 16, 2009 at 7:23 am
Success. See what you think of this.
SELECT
su2.name as 'Owner',
so.name AS 'Object',
su.name AS 'Grantee',
su3.name AS 'Grantor',
'ProtectType'=
CASE sp.protecttype
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
END,
'Action'=
CASE sp.action
WHEN 26 THEN 'REFERENCES'
WHEN 178 THEN 'CREATE FUNCTION'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
WHEN 236 THEN 'CREATE RULE'
END,
'Column'=
CASE ISNULL(CAST(COLUMNS AS INT),0)
WHEN 0 THEN 'None'
WHEN 1 THEN 'All'
ELSE CONVERT(VARCHAR(255),sc.name)
END
FROM sysprotects sp
INNER JOIN sysusers su on su.uid=sp.uid
INNER JOIN sysobjects so on so.id=sp.id and so.xtype <> 'S'
INNER JOIN sysusers su2 on so.uid=su2.uid
INNER JOIN sysusers su3 on sp.grantor=su3.uid
LEFT JOIN syscolumns sc on sp.id=sc.id and
(
(substring(sp.columns,((sc.colid)/8)+1,1) & power(2,(sc.colid-(8*((sc.colid-1)/8)))))>0 and sc.colid<8
OR
(substring(sp.columns,((sc.colid)/8)+1,1) & power(2,(sc.colid+1-(8*((sc.colid)/8))))/2)>0 and sc.colid>=8
)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply