Board index » delphi » Restricting access to db based on user id

Restricting access to db based on user id

Hi,
I was wondering if anyone had any suggestions for the following
scenario:
We have front end (in Delphi 2.0) that accesses a MS SQL Server 6.0 db.

What we would like to do is have only 1 version of the front end and
distribute it to a number of different groups of users which have
different levels of access, not only in the create/edit/delete
functions, but also in which rows they are able to see from the base
table.

We would like all these restrictions to be controlled by SQL Server as
opposed to the front end (as mentioned above, so that we only have to
have 1 version of the front end, and so all security checks are at the
server level).  Has anyone else done anything similar to this, and if
so, would you mind sharing your experience? :)  Right now, we are not
using any views - just base tables.

TIA,
Susan Mc

--
-----------------------------------------------------
Susan G. McManus            ... a founding member of
Systems Developer               Ocicats Anonymous...
PPI Financial Group Ltd.
-----------------------------------------------------

 

Re:Restricting access to db based on user id


Quote
Susan G. McManus wrote:

> What we would like to do is have only 1 version of the front end and
> distribute it to a number of different groups of users which have
> different levels of access, not only in the create/edit/delete
> functions, but also in which rows they are able to see from the base
> table.

> We would like all these restrictions to be controlled by SQL Server as
> opposed to the front end (as mentioned above, so that we only have to
> have 1 version of the front end, and so all security checks are at the
> server level).  Has anyone else done anything similar to this, and if
> so, would you mind sharing your experience? :)  Right now, we are not
> using any views - just base tables.

We implemented something similar but we ended up doing everything
through views and stored procedures. There was no access to the back-edn
tables.
The server has a role-function cross-reference table and a
networkid-role cross reference table.
Inside each 'function' we said 'Can we run?'.
It also got a bit slow when we tried to disable menus and buttons to
block of security dead-ends because there were lots of 'can we run ?'
messages going mack to the server.
If I did it again, I would get a list of available runs at application
log-in and use that to affect the menus and buttons at the front-end.
Each function (1-4 forms) would still do the 'can we run? ' call to the
server as it starts.

I hope this helps.

Other Threads