[quoted text, click to view] "Brad H McCollum" <bmccoll1@midsouth.rr.com> wrote in message
news:52031869.0410131030.5961c2f3@posting.google.com...
> I'm writing an application using VB 6.0 as the front-end GUI, and the
> MSDE version of SQL Server as the back-end (it's a program for a
> really small # of users --- less then 3-4).
>
> I'm trying to determine, through the Visual Basic interface, the
> permissions of each user that's using the application on his/her
> machine.
>
> For example, let's say I'm user "Michael" that's sitting down at my
> machine using the app. I've written. The security for logging into
> SQL Server will be setup using Windows Security (Trusted Connection)
> as opposed to Windows & SQL Server security. When Michael accesses a
> particular form in the VB 6.0 GUI, I want to run some code that
> automatically checks Michael's permission levels on the underlying
> table (actually, a stored procedure supplying the data from the table)
> that supplies the data to the form he's looking at and then give him
> some feedback on the form as to what type of permissions he has while
> he's browsing through the data shown in the form.
>
> For example, Michael opens a particular form, code in the background
> is run to identify that this is Michael accessing the form, the code
> returns a value that identifies what type of permissions he has on the
> data in the form, and a text box on the form informs Michael (for
> example) that he only has read-only permissions to the data he is
> viewing and cannot edit any of the data.
>
> As another example, user Karen sits down at her computer, logs into
> the application, opens the same form that Michael just opened, the
> code is run in VB to detect the level of permissions she has on the
> data being displayed in the form, and the text box on the form informs
> her that she has editing permissions on the data in the underlying
> table.
>
> Etc...
>
> If anyone can post an example of the code they use in accomplishing
> this task in an application they've written, I'd really appreciate a
> point in the right direction or a real-world example that's been
> implemented by one of you. I've written several apps. thus far using
> MSDE as the back-end, but the previous apps. I've written were for
> clients that didn't care about restricting access to the
> application... everyone could pretty much use the application as they
> desired and do anything they desired to the data.
>
> The current client I'm writing the app. mentioned here for wants to
> have security in place to where various users access the application
> with various levels of permissions to do stuff (or *not* do stuff) to
> the data in the application.
>
> Thanks very much in advance for any assistance / code provided!
>
> Sincerely,
> Brad McCollum
> bmccoll1@midsouth.rr.com
You're probably looking for the PERMISSIONS() function, which shows the
current user's effective permissions on an object. To identify a user's
login, use SYSTEM_USER or SUSER_SNAME().
You should also check out roles, and instead of granting permissions to each
user, grant them to the roles instead. You can then also use IS_MEMBER() to
see if a user is in a specific role, and allow or deny actions based on
that. It may seem a bit pointless to create a role with only one or two
users, but adding and removing users from roles is much easier than granting
and revoking permissions on multiple objects.
You can also consider using stored procedures as much as possible, instead
of granting any permissions on tables, views etc. This means that users have
no access to base tables, so it's harder for them to do something they
shouldn't, accidentally or otherwise. In addition, it's easier to implement
complex more security logic in a procedure, where you can check functions
like the ones above, or perhaps even use your own permissions lookup table
if you need very specific permissions.
You might find this information useful (the second link shows how to display
only a particular user's data from a table which contains data for all
users):
http://vyaskn.tripod.com/sql_server_security_best_practices.htm http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm And of course the "Managing Security" section in Books Online.
Simon
