Security ideas for row-level (entity) security

Security ideas for row-level (entity) security

Post by Steve Smit » Wed, 31 May 2000 04:00:00



Hi all.

I'm interested in any ideas anyone has on how to implement a security
structure for the following:

The application is a contact management system which will store various
client accounts, client/vendor contacts, meetings, deals, etc.  This is
written in VB5 using ADO to access SQL 7.0.  All database connectivity will
be handled from within MTS.  We need to be able to restrict users' access to
certain clients, contacts, etc.

Currently I have .dll which handles all the database connectivity for the
client apps, and this dll parses all SQL statements and adds security
clauses ("... AND ACCT.OWNER='SS'...") before submitting them to SQL Server.
This involves searching the query for "hot" tables, adding joins to the
security tables, and inserting one or more WHERE clauses into the query to
limit the rows returned.  This process is complex and error prone, and
difficult to maintain when new types of entities are added to the system.

The two major design goals are:

1) Security should be transparent to the client application(s).  The
application developers should be able to write "SELECT * FROM ACCT" and get
back a restricted list of accounts without worrying about how to tie the
query to the security tables.  (Of course, my contract allows me to severely
punish developers who submit SELECT * from anything...)

2) If at all possible, keep all connections to SQL Server to a single user
account.  This is to allow maximum "poolability" of SQL connections.  If
necessary, some high-level accounts can be created (i.e. Midwest, South,
West, etc), which would allow pooling at least within that particular
account.  However, individual users may belong to one or more groups, so a
regional manager may have rights to, say, both Midwest and Northeast.

I'd like to use views to accomplish at least part of this, but I'm not sure
how to give each user (or user class) it's own view of a given table,
without giving each view a different name (which makes it hard to keep the
security transparent to the client, when the ACCT table will effectively
have a different name depending on the user).  My best bet so far is to
create a seperate view of each "hot" table for each user, appending the
username (ACCT_USER1, ACCT_USER2, etc), and then parse and replace calls to
these tables, so that a submitted query "SELECT * FROM ACCT" is translated
to "SELECT * FROM ACCT_USER1".

Any other ideas on how other people have implemented entity/row-level
security?  Is Requirement 2 accurate, or is there a way to pool database
connections across different logins and "impersonate" another login on a
batch-by-batch basis?

Thanks in advance for any input,

Steve

 
 
 

1. Row level security

I read the last thread in this newsgroup regarding row level security,
nice implementation of views but it will not solve a problem I'm
looking at.

I need row level security to the level that the user who adds the
record can determine user groups that can and cannot view the record.

Is there a way to implement views to implement this level of security
that I am not seeing?

Any other ideas?

Thanks,

mporter

2. SQL Server 2000 and Access 2002 ADP Project in NT 4.0

3. Row level security...

4. SQL Riddle. See if you can solve this one...

5. DW Row Level Security

6. VB4 Certification

7. Method For Row-Level Security?

8. US-SC-Data Modeler-Aide Inc.

9. Row-level security restriction

10. Row-level security

11. Row-Level Security?

12. row level security

13. Security design: Views or row-level permissions?