471,616 Members | 1,908 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,616 software developers and data experts.

Database Access Control

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


Nov 21 '05 #1
4 2405
Check out Application Roles
(http://msdn.microsoft.com/library/de...rity_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

--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mi**********@mallesons.nospam.com |* W* http://www.mallesons.com

MS User wrote:
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



Nov 21 '05 #2
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.

--
David Portas
SQL Server MVP
--

Nov 21 '05 #3
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.

"MS User" wrote:
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


Nov 21 '05 #4
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
--

"MS User" <sq****@sql.com> wrote in message
news:Ox**************@TK2MSFTNGP14.phx.gbl...
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


Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by great_googley_moogley | last post: by
3 posts views Thread by Hutty | last post: by
5 posts views Thread by Seok Bee | last post: by
3 posts views Thread by John Taylor | last post: by
6 posts views Thread by =?Utf-8?B?UGFycm90?= | last post: by
1 post views Thread by XIAOLAOHU | last post: by
1 post views Thread by ZEDKYRIE | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.