Database Access Control

[MS User]

VB.Net / SQL 2K

We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.

Here is our requirement.

1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'

The whole point is to restrict users not to directly modify data outside
application.

Thanks
John B

 

[Mike Hodgson]

Check out Application Roles
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp).

Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).

So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.

HTH

 

[David Portas]

You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.

 

[Guest]

Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...

ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.

 

[Anthony Thomas]

Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.

Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.

Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.

In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.

The above is for ad-hoc user access only.

For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.

Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.

Sincerely,


Anthony Thomas


--

VB.Net / SQL 2K

We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.

Here is our requirement.

1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'

The whole point is to restrict users not to directly modify data outside
application.

Thanks
John B