CODEWITHSUNDEEP
ms-accesspermissions
Dan Soap
Dan Soap

Reputation: 10248

How can I make a remote table in MS Access "write only"?

I have a little MS Access application (I know, I know), which accesses a table on a remote MS SQL Server.

I also have a form, which allows the users to enter data into the table. The problem is, that I want the users not to be able to read or modify existing data, but I only want them to enter data and store it (the data is a bit sensitive).

I tried to grant only INSERT privileges to the user connecting to the database, resulting in the error, that the table is not accessible at all.

After googling, I couldn't find anything which would solve this issue.

So my question: How can I ensure, that the users only enter data, but do not modify or read existing data in MS Access (2003)?

Upvotes: 1

Views: 659

Answers (4)

Dan Soap
Dan Soap

Reputation: 10248

Finally here's what I've done:

First, I created two tables:

CREATE TABLE mydata (...)
CREATE TABLE mydata2 (...)

Then I created an INSTEAD OF trigger:

CREATE TRIGGER mytrigger ON mydata 
INSTEAD OF INSERT 
AS
   INSERT INTO mydata2 SELECT * FROM INSERTED
END

This moved every single entry from mydata to mydata2 on insert. The form in Access remained on mydata though, which made the entries invisible to the user.

Thanks to CodeSlave, who also suggested this solution

Upvotes: 0

JeffO
JeffO

Reputation: 8043

I prefer a stored proc, but thought this was an alternate to give access to a view of the table with a check option

create table testview (somevalue varchar(25), entereddate datetime)
go
insert into testview values( 'First Value', getdate() )

go
create view testview_currentonly
as 
SELECT 
    somevalue
   , entereddate
FROM testview
WHERE entereddate >= getdate()

with check option
-- end view create
go
insert into testview_currentonly values( 'Second Value', getdate() )

select * from testview_currentonly

select * from testview

You can't select anything from this view because all entries (assuming the user could not manipulate the value going into the 'entereddate' field (probably should have a default?).

Upvotes: 2

Kevin Ross
Kevin Ross

Reputation: 7215

I would remove select permissions from the table (as you already have done) and do all the IO through a stored procedure. That way you can control exactly what is inserted into the system

Let me know if you need help running a stored procedure in ADO and I will post something up

Upvotes: 3

Mitch Wheat
Mitch Wheat

Reputation: 300489

For the identity principal you use to access the remote SQL server table (this will be defined in the link), remove all permissions except db_datareader.

You can do this with MS Access permissions (but be warned: it's quite a difficult area...):

Microsoft Access Database Security - Security Permissions

Types of permissions (MDB)

Upvotes: 1

Related Questions