Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Levels of Security in Database Tool. ASP Report Maker
I am working on security for a database, and I can only list IDs in a field
named €˜ReportsTo in one column. This is kind of a stretch, but I thought someone here may have dealt with something similar, or may be thinking-out-of-the-box more than I am right now. The scenario is kind of simple, lets say a SalesRep logs into the database, and wants to see all his/her records. Ok, fine, I have the ID for the SalesRep and I know which Director the SalesRep reports to. Same for the Director, I have this ID and I know which VP the Director reports to. However, at the VP level, I have a problem. The VP wants to login and wants to see all his/her records as well as all the records of the Director under him/her, as well as all the records of the SalesRep under the Director. So, anyway, I can go one up level, but not two levels. I am using ASP Report Maker to create a web-based interface. http://www.hkvstore.com/aspreportmaker/ I dont think this is possible to do what I want to do with that tool. I have one level of security working fine, but not two levels. It would require some kind of if€¦then statement, which I am pretty sure that asp tool cant handle. Has anyone encountered this issue before? Anyone at all? I know you are not supposed to multi-post, and I NEVER do it, but I feel I need to do it this one time because I may find just one person here, or perhaps just one person at the Excel Programming group, which may be able to help me out of this dilemma. This is time-sensitive and Im really looking for a needle in a haystack here... I try to contribute to these DGs as much as I can!! Thanks for this, and thanks for all the help in the past. Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Levels of Security in Database Tool. ASP Report Maker
Not completely sure I understand the issue. You want to be sure that the VP
can see data for all the people in his reporting chain, right? And one VP can't see another's reporting chain's data. Can you use a SQL UDF to recursively query a given User ID's reports to come up with a list of permitted IDs? here's a sample that works on a GL account structure where there's both a company # and a 'parent' account. It returns a table of all the descendant accounts Create FUNCTION [dbo].[fn_SubordinateAccounts](@Parent AS INT) RETURNS @Accounts Table ( Parent INT NOT NULL, lvl int NOT NULL, Account INT NOT NULL, UNIQUE CLUSTERED(lvl, Account) -- Index will be used to filter level ) AS BEGIN DECLARE @lvl AS INT; SET @lvl = 0; -- Initialize level counter with 0 -- Insert root node to @Subs INSERT INTO @Accounts(parent, lvl, Account) SELECT distinct Parent, @lvl, Child FROM dbo.Accounts WHERE Parent = @Parent AND Child IS NOT NULL WHILE @@rowcount 0 -- while previous level had rows BEGIN SET @lvl = @lvl + 1; -- Increment level counter -- Insert next level of subordinates to @Subs INSERT INTO @Accounts(parent, lvl, Account) SELECT DISTINCT p.Account, @lvl, c.Child FROM @Accounts AS P -- P = Parent INNER JOIN dbo.Account AS C -- C = Child ON P.lvl = @lvl - 1 -- Filter parents from previous level AND C.Parent = P.Account WHERE C.Child IS NOT NULL; END RETURN; END "ryguy7272" wrote: I am working on security for a database, and I can only list IDs in a field named €˜ReportsTo in one column. This is kind of a stretch, but I thought someone here may have dealt with something similar, or may be thinking-out-of-the-box more than I am right now. The scenario is kind of simple, lets say a SalesRep logs into the database, and wants to see all his/her records. Ok, fine, I have the ID for the SalesRep and I know which Director the SalesRep reports to. Same for the Director, I have this ID and I know which VP the Director reports to. However, at the VP level, I have a problem. The VP wants to login and wants to see all his/her records as well as all the records of the Director under him/her, as well as all the records of the SalesRep under the Director. So, anyway, I can go one up level, but not two levels. I am using ASP Report Maker to create a web-based interface. http://www.hkvstore.com/aspreportmaker/ I dont think this is possible to do what I want to do with that tool. I have one level of security working fine, but not two levels. It would require some kind of if€¦then statement, which I am pretty sure that asp tool cant handle. Has anyone encountered this issue before? Anyone at all? I know you are not supposed to multi-post, and I NEVER do it, but I feel I need to do it this one time because I may find just one person here, or perhaps just one person at the Excel Programming group, which may be able to help me out of this dilemma. This is time-sensitive and Im really looking for a needle in a haystack here... I try to contribute to these DGs as much as I can!! Thanks for this, and thanks for all the help in the past. Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Levels of Security in Database Tool. ASP Report Maker
Thanks for trying, but that's not it. I think I will eventually, hopefully
soon, stumble upon the solution. Thanks again, Ryan-- -- RyGuy "Duke Carey" wrote: Not completely sure I understand the issue. You want to be sure that the VP can see data for all the people in his reporting chain, right? And one VP can't see another's reporting chain's data. Can you use a SQL UDF to recursively query a given User ID's reports to come up with a list of permitted IDs? here's a sample that works on a GL account structure where there's both a company # and a 'parent' account. It returns a table of all the descendant accounts Create FUNCTION [dbo].[fn_SubordinateAccounts](@Parent AS INT) RETURNS @Accounts Table ( Parent INT NOT NULL, lvl int NOT NULL, Account INT NOT NULL, UNIQUE CLUSTERED(lvl, Account) -- Index will be used to filter level ) AS BEGIN DECLARE @lvl AS INT; SET @lvl = 0; -- Initialize level counter with 0 -- Insert root node to @Subs INSERT INTO @Accounts(parent, lvl, Account) SELECT distinct Parent, @lvl, Child FROM dbo.Accounts WHERE Parent = @Parent AND Child IS NOT NULL WHILE @@rowcount 0 -- while previous level had rows BEGIN SET @lvl = @lvl + 1; -- Increment level counter -- Insert next level of subordinates to @Subs INSERT INTO @Accounts(parent, lvl, Account) SELECT DISTINCT p.Account, @lvl, c.Child FROM @Accounts AS P -- P = Parent INNER JOIN dbo.Account AS C -- C = Child ON P.lvl = @lvl - 1 -- Filter parents from previous level AND C.Parent = P.Account WHERE C.Child IS NOT NULL; END RETURN; END "ryguy7272" wrote: I am working on security for a database, and I can only list IDs in a field named €˜ReportsTo in one column. This is kind of a stretch, but I thought someone here may have dealt with something similar, or may be thinking-out-of-the-box more than I am right now. The scenario is kind of simple, lets say a SalesRep logs into the database, and wants to see all his/her records. Ok, fine, I have the ID for the SalesRep and I know which Director the SalesRep reports to. Same for the Director, I have this ID and I know which VP the Director reports to. However, at the VP level, I have a problem. The VP wants to login and wants to see all his/her records as well as all the records of the Director under him/her, as well as all the records of the SalesRep under the Director. So, anyway, I can go one up level, but not two levels. I am using ASP Report Maker to create a web-based interface. http://www.hkvstore.com/aspreportmaker/ I dont think this is possible to do what I want to do with that tool. I have one level of security working fine, but not two levels. It would require some kind of if€¦then statement, which I am pretty sure that asp tool cant handle. Has anyone encountered this issue before? Anyone at all? I know you are not supposed to multi-post, and I NEVER do it, but I feel I need to do it this one time because I may find just one person here, or perhaps just one person at the Excel Programming group, which may be able to help me out of this dilemma. This is time-sensitive and Im really looking for a needle in a haystack here... I try to contribute to these DGs as much as I can!! Thanks for this, and thanks for all the help in the past. Ryan--- -- RyGuy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Levels of Security in Database Tool. ASP Report Maker
I got it. Problem solved.
Thanks to all who looked. Ryan--- -- RyGuy "ryguy7272" wrote: Thanks for trying, but that's not it. I think I will eventually, hopefully soon, stumble upon the solution. Thanks again, Ryan-- -- RyGuy "Duke Carey" wrote: Not completely sure I understand the issue. You want to be sure that the VP can see data for all the people in his reporting chain, right? And one VP can't see another's reporting chain's data. Can you use a SQL UDF to recursively query a given User ID's reports to come up with a list of permitted IDs? here's a sample that works on a GL account structure where there's both a company # and a 'parent' account. It returns a table of all the descendant accounts Create FUNCTION [dbo].[fn_SubordinateAccounts](@Parent AS INT) RETURNS @Accounts Table ( Parent INT NOT NULL, lvl int NOT NULL, Account INT NOT NULL, UNIQUE CLUSTERED(lvl, Account) -- Index will be used to filter level ) AS BEGIN DECLARE @lvl AS INT; SET @lvl = 0; -- Initialize level counter with 0 -- Insert root node to @Subs INSERT INTO @Accounts(parent, lvl, Account) SELECT distinct Parent, @lvl, Child FROM dbo.Accounts WHERE Parent = @Parent AND Child IS NOT NULL WHILE @@rowcount 0 -- while previous level had rows BEGIN SET @lvl = @lvl + 1; -- Increment level counter -- Insert next level of subordinates to @Subs INSERT INTO @Accounts(parent, lvl, Account) SELECT DISTINCT p.Account, @lvl, c.Child FROM @Accounts AS P -- P = Parent INNER JOIN dbo.Account AS C -- C = Child ON P.lvl = @lvl - 1 -- Filter parents from previous level AND C.Parent = P.Account WHERE C.Child IS NOT NULL; END RETURN; END "ryguy7272" wrote: I am working on security for a database, and I can only list IDs in a field named €˜ReportsTo in one column. This is kind of a stretch, but I thought someone here may have dealt with something similar, or may be thinking-out-of-the-box more than I am right now. The scenario is kind of simple, lets say a SalesRep logs into the database, and wants to see all his/her records. Ok, fine, I have the ID for the SalesRep and I know which Director the SalesRep reports to. Same for the Director, I have this ID and I know which VP the Director reports to. However, at the VP level, I have a problem. The VP wants to login and wants to see all his/her records as well as all the records of the Director under him/her, as well as all the records of the SalesRep under the Director. So, anyway, I can go one up level, but not two levels. I am using ASP Report Maker to create a web-based interface. http://www.hkvstore.com/aspreportmaker/ I dont think this is possible to do what I want to do with that tool. I have one level of security working fine, but not two levels. It would require some kind of if€¦then statement, which I am pretty sure that asp tool cant handle. Has anyone encountered this issue before? Anyone at all? I know you are not supposed to multi-post, and I NEVER do it, but I feel I need to do it this one time because I may find just one person here, or perhaps just one person at the Excel Programming group, which may be able to help me out of this dilemma. This is time-sensitive and Im really looking for a needle in a haystack here... I try to contribute to these DGs as much as I can!! Thanks for this, and thanks for all the help in the past. Ryan--- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Security Levels and Macros | Excel Discussion (Misc queries) | |||
Macro - security levels | Excel Discussion (Misc queries) | |||
how do i access template database tool as in excel 2000 in 2003 | Excel Discussion (Misc queries) | |||
The most powerful computer security tool | Excel Worksheet Functions | |||
The most powerful computer security tool | Excel Discussion (Misc queries) |