ad*****@gmail.c om wrote:
Thanks for the reply Br@dley. I would really appreciate if you could
show me an example of the UDF that you mentioned. I am quite new at
security handling in access and sql server databases and espcially new
to using UDF.
This is based on an organisation structure held in a table. The function
used to follow the structure for each record to see if it belonged to the
parent (and thus could be viewed by the user). I found a much easier way
(hash table). When adding a new organisation record, the stored procedure
that does that automatically builds a "parent string". So the search up the
tree structure is done once when the record is created/modified. Then it's
very simple to do a text search for your parent.
eg.
OrgID = 200, ParentOrgID=100 , ParentString = ';200;100;50;'
OrgID = 100, ParentOrgID = 50, Parentstring = ';100;50;'
OrdIG = 50, ParentOrgID = NULL, Parentstring = ';50;'
So, to return all records who are below OrgID=50, no matter how far down the
tree structure, I simply search for ';50;'
(Note: I wanted the top unit to be included which is why each parentstring
contains the current record's orgid)
This is the procedure that creates the string:
CREATE Procedure dbo.SetOrgStrin g @OrgID int
AS
DECLARE @PID int
DECLARE @OrgString varchar(400)
DECLARE @Delim as char, @New as varchar(11)
Set @Delim = ';'
Set @OrgString = @Delim + Cast(@OrgID as varchar(10))
SELECT @PID = ParID FROM tblOrgStructure WHERE OrgID = @OrgID
While @PID is not null
BEGIN
SET @New = @Delim + CAST(@PID as varchar(10))
IF len(@OrgString) > 0
BEGIN
SET @OrgString = @OrgString + @New
END
ELSE
BEGIN
SET @OrgString = @New
END
SELECT @PID = ParID FROM tblOrgStructure WHERE OrgID = @PID
END
SET @OrgString = @OrgString + @Delim
UPDATE tblOrgStructure
SET ParentString = @OrgString
WHERE OrgID = @OrgID
RETURN
I use my own security screens and stored procedures to manage users. The
screen prompts for the usual security info but also prompts for an employee
from tblEmployees. The stored procedures create the necessary SQL logins etc
but also adds an entry to a user table. This table tells me if the user is
Windows/SQL security and links them to an Employee record so we know who
they are.
eg. The procedure for Windows security (I have seperate SP for Win and SQL
security as they are different to set up.
CREATE Procedure dbo.spSecurityA ddUserWin @Personnel_no AS NVARCHAR(10),
@UserLevel AS SMALLINT, @UserName AS NVARCHAR(20), @Domain as NVARCHAR(20),
@ReadOnly AS BIT,
@HRAccess AS BIT, @TRNAccess AS BIT, @COVAccess as BIT
As
DECLARE @return_status as INT
DECLARE @DomainUser AS NVARCHAR(40)
IF IS_MEMBER('db_o wner') = 1 /* only database owners can change
security */
BEGIN
EXEC('USE core')
SET @DomainUser = @Domain + '\' + @UserName
EXEC @return_status = sp_grantlogin @DomainUser
if @return_status = 0
BEGIN
EXEC @return_status = sp_defaultdb @DomainUser, 'core'
IF @return_status = 0
BEGIN
EXEC @return_status = sp_grantdbacces s @DomainUser, @UserName
IF @UserLevel = 1 /* admin level */
BEGIN
IF @HRAccess =0 AND @TRNAccess = 0 AND @COVAccess = 0 /* if no
access set default HR access */
BEGIN
SET @HRAccess = 1
END
IF @HRAccess = 1
BEGIN
EXEC @return_status = sp_addrolemembe r 'coreadmin', @UserName
END
IF @TRNAccess = 1
BEGIN
EXEC @return_status = sp_addrolemembe r 'coreadmintrn', @UserName
END
IF @COVAccess = 1
BEGIN
EXEC @return_status = sp_addrolemembe r 'coreadmincov', @UserName
END
END
ELSE IF @UserLevel = 2 /* manager level */
BEGIN
SET @HRAccess = 1
SET @TRNAccess = 0
SET @COVAccess = 0
IF @ReadOnly = 1
BEGIN
EXEC @return_status = sp_addrolemembe r 'coremanagerro' ,
@UserName
END
ELSE
BEGIN
EXEC @return_status = sp_addrolemembe r 'coremanager', @UserName
END
END
ELSE IF @UserLevel = 3 /* employee level */
BEGIN
SET @HRAccess = 1
SET @TRNAccess = 0
SET @COVAccess = 0
IF @ReadOnly = 1
BEGIN
EXEC @return_status = sp_addrolemembe r 'coreemployeero ',
@UserName
END
ELSE
BEGIN
EXEC @return_status = sp_addrolemembe r 'coreemployee', @UserName
END
END
END
END
IF @return_status = 0
BEGIN /* make entry in user table */
INSERT INTO tblUsers
(Personnel_no, CoreUser, CoreLevel, WinNTUser, DBName, ReadOnlyAccess,
HRAccess, TRNAccess, COVAccess)
SELECT @Personnel_no, @UserName, @UserLevel, 1, @DomainUser,
@ReadOnly, @HRAccess, @TRNAccess, @COVAccess
END
END
RETURN
I have several database roles. Two versions of each (eg. ManagerRole can see
all the people they manage as defined by the org structure, and
ManagerRoleRO, a read-only version for manager that can only view data)
(I also have a version of this called IsValidOrg() )
CREATE FUNCTION IsValidEmp (@PID int)
RETURNS bit AS
BEGIN
DECLARE @found bit, @ValidPID int
if IS_MEMBER('db_s ecurityadmin') =1 or IS_MEMBER('db_o wner') = 1
BEGIN
set @found = 1
END
ELSE if IS_MEMBER('Admi nRole') = 1
BEGIN
set @found = 1
END
ELSE IF IS_MEMBER('Mana gerRole') = 1 OR IS_MEMBER('Mana gerRoleRO') = 1
BEGIN
DECLARE @SecurityOrgID int
SET @SecurityOrgID = [core].[dbo].[SecurityGetOrgI D]()
SELECT @ValidPID = Personnel_no From tblEmployee
INNER JOIN tblOrgStructure ON tblEmployee.Org ID =
tblOrgStructure .OrgID
Where tblOrgStructure .ParentString like '%;' + cast(@SecurityO rgID as
varchar(10)) + ';%'
AND Personnel_no = @PID
if @ValidPID <> Null
begin
set @found = 1
end
else
begin
set @found = 0
end
END
ELSE IF IS_MEMBER('Empl oyeeRole') = 1 OR IS_MEMBER('Empl oyeeRoleRO') = 1
BEGIN
DECLARE @SecurityEmpID int
SET @SecurityEmpID = [core].[dbo].[SecurityGetEmpI D]()
if @SecurityEmpID = @PID
begin
set @found = 1
end
else
begin
set @found = 0
end
END
return @found
END
Then in all my views I simple add a WHERE clause..
eg. SELECT * FROM dbo.tblEmployee WHERE IsValidEmp(pers onnel_no)
Hope that all makes sense:) It took me quite a while to come up with
something that worked the way I wanted. It's not he most elegant code but it
works.
--
regards,
Br@dley
