473,378 Members | 1,138 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 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 2468
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Fran Tirimo | last post by:
I am developing a small website using ASP scripts to format data retrieved from an Access database. It will run on a Windows 2003 server supporting FrontPage extensions 2002 hosted by the company...
5
by: great_googley_moogley | last post by:
Greetings, I am in the process of installing a SQL database at a customer location. I have determined that there are 3 ways to do this, and I wanted to know which is the best of the 3. 1...
2
by: J.Beaulieu | last post by:
Hi I'll have probably to use sql server soon but prior to that I have a question concerning priviledges and security. Is it possible for someone to do like in access, ie creating a db/table...
1
by: nzanella | last post by:
Hello, I am currently upgrading a database of products to include pictures to be displayed online. I would like to know whether it would be best to store the pictures themselves in a database as...
12
by: Wadim Grasza | last post by:
I want to store and display (on a form or a report) multiple pictures per record in an access database. The pictures are not stored within the database. They are stored as files and the database...
3
by: Hutty | last post by:
I'm trying create a calendar control that updates an access database with events. I have some code I managed to piece together. First error I'm running into is the Mycommand.ExecuteNonQuery(). I...
5
by: Seok Bee | last post by:
Dear Experts, I currently trying to use the FileUpload control from asp.net 2.0 to upload files. The uploading of the file I would like to store it in the Access Database. Unfortunately, I've no...
3
by: John Taylor | last post by:
Tried to find any reference to this on the Microsoft help pages but can't find any reference - maybe I'm just not smart enough to find it. However; I have been working on a membership database...
9
by: flymo | last post by:
Hello All, I have been asked to build a simple database that will operate and track a liquid dispensing device. The machine is run in a multi-bus drop (coin operated basis) but I need to make...
6
by: =?Utf-8?B?UGFycm90?= | last post by:
I cannot access my SQL database in a browser control created in my C# Windows application program I can, however, access the database from an IE browser. I get the ubiquitous "Login failed for NT...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.