Two Levels of Security in Database Tool. ASP Report Maker

[ryguy7272]

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, let’s 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 don’t 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
can’t 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 I’m 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---

 

[Duke Carey]

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]

Thanks for trying, but that's not it. I think I will eventually, hopefully
soon, stumble upon the solution.

Thanks again,
Ryan--

--
RyGuy

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

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, let’s 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 don’t 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
can’t 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 I’m 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---

 

[ryguy7272]

I got it. Problem solved.
Thanks to all who looked.
Ryan---

--
RyGuy

Thanks for trying, but that's not it. I think I will eventually, hopefully
soon, stumble upon the solution.

Thanks again,
Ryan--

--
RyGuy

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

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, let’s 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 don’t 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
can’t 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 I’m 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---