Different privileges in SQL SERVER & VB.NET

[angelica]

Hi,

I need some help pls. Ive been using MS Access as a database and when I needed different right I used to create functions that allows different users to do different things in an application.

For example I need 3 different user types or profiles whatever they're called

1. user to input in certain data.

2. User to do some searches with a read only on data

3. Administrator who inputs in data different from user 1. and does some searches also.


What I need to know is how to handle this is SQL SERVER. Do I set the provileges from within the database or do I use functions and CASE to identify the user.

this is my first attempt on the SQL SERVER. I appreciate all advice on this.

And many thanks

[si_the_geek]

You set it up in the database. If you go into the "Security" section of your SQL Server tools, you can create Users and Groups on a server/database.

You should create three Groups, which each have the permissions you mentioned. You can specify which databases/tables/columns they have permissions for, and what permissions they have in each case (such as read/edit/delete).

Then create new Users (or add Windows users), and assign them to the Groups.


If you want, you can also make some changes to your program (like hiding certain controls), but that should only be as an addition to the database changes.

[angelica]

Heya,


Need some more help pls. it's the first time me using the SQL SERVER and some things are nto so clear to me yet. 1.

1..If I have a menu in Windows Application and some of the Menu items I need to make them access according to the user eg. only to user 1, can I still do that in SQL SERVER.

2.. If I have a control eg. a drop down menu can I make it accessible also to whoever user I want, like showing it up at runtime according to whoever signed in through SQL SERVER?

Thanks for you time.

[si_the_geek]

Those questions are not related to SQL Server at all - they are to do with your application.

What you want is to show/enable certain features (which you do in the normal manner) based on a certain value (the username or Group, or even better the specific rights that they have).

How you get that value depends on how you have set up the users/groups.

[angelica]

heya si the geek,

First of all many thanks for your prompt support. Lasst question about the users. Im still to see how the secuirty works at the moment Im getting used to the queries and adding tables etc.

I actually need 3 types of users.

Do I create a Table storing the usernames and passwords and maybe USERID? Do I use the USERID of the table or is there a way to use the USER or group from the SQL SERVER. ?

Is it possible to give me an example or link pls?

Cheers and thanks

[si_the_geek]

You don't need to create a table for the users - just set up the Users and Groups in SQL Server as I explained above (more details are in the help for SQL Server).

Once you have done that, it will not be possible (via your program or any other route) to view/edit data unless they are a member of the appropriate security group.


In terms of your application, you might want to create a table to say what permissions/features are available to each Group/User (which means changes in permissions/features available to them only needs database editing, rather than modifying the program), or perhaps detect permissions for each item instead (which means no modifications at all, only setting the security).

It is much easier to use a table, and that is the option I would take.

To detect which user is logged in, you can execute either of these queries:

SELECT CURRENT_USER

SELECT USER_NAME

To find out what Group a user is in, you can execute the system stored procedure sp_helpuser, which returns various items including Group_Name, eg:

sp_helpuser CURRENT_USER

[angelica]

Need some hands on.

One little more thngy. in the first line yu said I dont need a table and then it tells me to create one and store the permissions and features.

Feeling rather stupid or missing something. Will the table in the have somefield like this?


FIELDS: UserID username Password AccessLevel

[si_the_geek]

The table is not for security (the security is handled by the database), it is for your program to know what features to enable/disable.

Exactly what fields you have in it depends on your program, but it could be something like this:

GroupName (or UserName if you prefer)
CanInputData_Table1
CanInputData_Table2
...

Of course you could just hard-code that into your program instead (hence the "might want to"), but that can cause problems later.

[mendhak]

Create a set of roles (under security). I'll call the roles AppNameWrite, AppNameRead and AppNameAdmins.

Go to your database objects (stored procs, tables, views) and grant the relevant permissions to the various roles. So you can grant SELECT to all of them, but grant INSERT and UPDATE only to AppNameWrite and AppNameAdmins. So on and so forth.

Then create a few users and add those users to the roles you want them to map to.

Alternatively, you can simply create the three users and grant the permissions directly on your database objects.

[mendhak]

Merge?
http://www.vbforums.com/showthread.php?t=515400

[si_the_geek]

Duplicate threads merged - please do not create more than one thread for the same question.

[angelica]

Si the Geek and Mendak,

MANY Many THANKS.


Will try out your suggestions.