Access SQL Server ODBC MDB File Security

[adserte@gmail.com]

I have a security related question.

I was wondering how i can set up security so that for a table:
a user can read all data in the table but only update and delete their
own data (there is a username field in the table where the user enters
their username)

I am using access 2003 and linking tables odbc from sql server with
windows security.

Any help would be much appreciated.

[Tom van Stiphout]

Re: Access SQL Server ODBC MDB File Security

On 20 Apr 2006 05:06:08 -0700, adserte@gmail.c om wrote:

There is no built-in way to do that, so you'll have to write some
code.
If this was the Northwind Orders form, in Form_Current I would write:
Me.AllowEdits = (Me.txtUserName = g_strLoggedInUs erName)
(I'm comparing the username in the field with a global variable set
when the user logs in)

At the form level we also have AllowAdditions and AllowDeletes you may
want to take advantage of.

-Tom.

[color=blue]
>I have a security related question.
>
>I was wondering how i can set up security so that for a table:
>a user can read all data in the table but only update and delete their
>own data (there is a username field in the table where the user enters
>their username)
>
>I am using access 2003 and linking tables odbc from sql server with
>windows security.
>
>Any help would be much appreciated.[/color]

[adserte@gmail.com]

Re: Access SQL Server ODBC MDB File Security

another question regarding linked tables in access 2003 from sql
server:

i have linked sql server tables to access (odbc) and was wondering if I
can automatically update the linked tables when starting access?

[adserte@gmail.com]

Re: Access SQL Server ODBC MDB File Security

Thanks Tom for your reply.
Ok, tried that and it locks the form correctly but users can open the
table and edit any data. i would prefer to do the same thing, use
AllowEdits and AllowDeletes not only for the form but also for the
whole data table.

[Tom van Stiphout]

Re: Access SQL Server ODBC MDB File Security

On 20 Apr 2006 07:02:04 -0700, adserte@gmail.c om wrote:

Check out the Connect property and RefreshLink method in the help
file.
-Tom.

[color=blue]
>another question regarding linked tables in access 2003 from sql
>server:
>
>i have linked sql server tables to access (odbc) and was wondering if I
>can automatically update the linked tables when starting access?[/color]

[Tom van Stiphout]

Re: Access SQL Server ODBC MDB File Security

On 20 Apr 2006 07:22:14 -0700, adserte@gmail.c om wrote:

Users have no business with the raw tables, so don't give them access
to the Database window.
Tools > Startup > Display Database Window
Tools > Startup > Use Special Keys (prevents F11)
These options can also be set in code; see the SetOption method in the
Help file.

-Tom.

[color=blue]
>Thanks Tom for your reply.
>Ok, tried that and it locks the form correctly but users can open the
>table and edit any data. i would prefer to do the same thing, use
>AllowEdits and AllowDeletes not only for the form but also for the
>whole data table.[/color]

[Br@dley]

Re: Access SQL Server ODBC MDB File Security

adserte@gmail.c om wrote:[color=blue]
> I have a security related question.
>
> I was wondering how i can set up security so that for a table:
> a user can read all data in the table but only update and delete their
> own data (there is a username field in the table where the user enters
> their username)
>
> I am using access 2003 and linking tables odbc from sql server with
> windows security.
>
> Any help would be much appreciated.[/color]


Row level security.

I use a UDF that I insert into a where clause in my views. It returns true
if the record is valid for the current user. By using a UDF the view is
still updatable. It handles returning only the employees own record, or all
records of people the person manages (as defined by an organisation tree
table), or all records if an admin user.

Users have no permissions to tables at all.

I use a User table in my database that matches up an employee record with
the username (I use my own add user security routines in a stored procedure
so that this record is automatically created when new users are added).

Works with Windows and SQL logins.

Hope that makes some sense. I can probably show some examples if needed.
--
regards,

Br@dley

[adserte@gmail.com]

Re: Access SQL Server ODBC MDB File Security

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.

[Br@dley]

Re: Access SQL Server ODBC MDB File Security (long)

adserte@gmail.c om wrote:[color=blue]
> 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.[/color]


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