roles / users and application design question 
Author Message
 roles / users and application design question

Hi all,

I need to define roles in my application. Certain groups are restricted to
access some part of my application.

I want to know how to deal with users, and usergroups (roles in sqlserver).
Where should i store the user data ? should i create a table with user
information ?

And when i add a user, how do i add that user to my sqlserver login and add
that user to my Users of my sqlserver database with a certain role ?

thanks in advance,
Patrick



Tue, 09 Nov 2004 03:59:48 GMT  
 roles / users and application design question

well here is the bit for adding the user to SQL

sp_adduser
Adds a security account for a new user in the current database. This procedure is included for backward compatibility. Use sp_grantdbaccess.

Syntax




Is the name of the user's login. login is sysname, with no default. login must be an existing Microsoft? SQL ServerT login or Microsoft Windows NT? user.

Is the name for the new user. user is sysname, with a default of NULL. If user is not specified, the name of the user defaults to the login name. Specifying user gives the new user a name in the database different from the login ID on SQL Server.

Is the group or role that the new user automatically becomes a member of. group is sysname, with a default of NULL. group must be a valid group or role in the current database. Microsoft SQL Server version 7.0 uses roles instead of groups.

Return Code Values
0 (success) or 1 (failure)

Remarks
SQL Server usernames can contain from 1 to 128 characters, including letters, symbols, and numbers. However, usernames cannot:

  a.. Contain a backslash character (\).

  b.. Be NULL, or an empty string ('').
After a user has been added, use the GRANT, DENY, and REVOKE statements to define the permissions controlling the activities performed by the user.

Use sp_helplogin to display a list of valid login names.

Use sp_helprole to display a list of the valid role names. When specifying a role, the user automatically gains the permissions that are defined for the role. If a role is not specified, the user gains the permissions granted to the default public role. To add a user to a role, a value for username must be supplied (username can be the same as login_id.)

To access a database, a login must be granted access by using sp_adduser or sp_grantdbaccess, or the guest security account must exist in the database.

sp_adduser cannot be executed inside a user-defined transaction.

Permissions
Only the dbo and members of the sysadmin fixed server role can execute sp_adduser.

Examples
A. Add a user
This example adds the user Victoria to the existing fort_mudge role in the current database, using the existing login Victoria.

EXEC sp_adduser 'Victoria', 'Victoria', 'fort_mudge'

B. Add a username with the same login ID
This example adds the default username Margaret to the current database for the login Margaret, which belongs to the default public role.

EXEC sp_adduser 'Margaret'

C. Add a user who uses a different username
This example adds the Haroldq login to the current database with a username of Harold, which belongs to the fort_mudge role.

EXEC sp_adduser 'Haroldq', 'Harold', 'fort_mudge'

See Also

sp_addrole

sp_dropuser

sp_grantdbaccess

sp_grantlogin

sp_helpuser

System Stored Procedures

The user role bit I have not figured out yet, but look in SQL books online for something like sp_sqlroles or sp_userrole.

Cheers,
Maartin.

Quote:

> Hi all,

> I need to define roles in my application. Certain groups are restricted to
> access some part of my application.

> I want to know how to deal with users, and usergroups (roles in sqlserver).
> Where should i store the user data ? should i create a table with user
> information ?

> And when i add a user, how do i add that user to my sqlserver login and add
> that user to my Users of my sqlserver database with a certain role ?

> thanks in advance,
> Patrick



Thu, 11 Nov 2004 03:16:05 GMT  
 roles / users and application design question

Maartin,
Thanks for your explanations! Great.

My concern is more how to deal with those users & groups in my application. (VB & ADO)

So i defined a user in a certain group. How should i determine its rights.
Should i get the group information (or user rights) out of sqlserver. Or should i create a user table with group (= rights) information.
For example: For a certain group, menu items are disabled.

And how should i add users ? The database is secured by NT Authenication.
Just on the database by the database administrator or from my application ?
Should i use the login name from Windows or create a table with user information ?

I hope this will make it more clear (?),

Patrick

  well here is the bit for adding the user to SQL

  sp_adduser
  Adds a security account for a new user in the current database. This procedure is included for backward compatibility. Use sp_grantdbaccess.

  Syntax



  Arguments

  Is the name of the user's login. login is sysname, with no default. login must be an existing Microsoft? SQL ServerT login or Microsoft Windows NT? user.


  Is the name for the new user. user is sysname, with a default of NULL. If user is not specified, the name of the user defaults to the login name. Specifying user gives the new user a name in the database different from the login ID on SQL Server.


  Is the group or role that the new user automatically becomes a member of. group is sysname, with a default of NULL. group must be a valid group or role in the current database. Microsoft SQL Server version 7.0 uses roles instead of groups.

  Return Code Values
  0 (success) or 1 (failure)

  Remarks
  SQL Server usernames can contain from 1 to 128 characters, including letters, symbols, and numbers. However, usernames cannot:

    a.. Contain a backslash character (\).

    b.. Be NULL, or an empty string ('').
  After a user has been added, use the GRANT, DENY, and REVOKE statements to define the permissions controlling the activities performed by the user.

  Use sp_helplogin to display a list of valid login names.

  Use sp_helprole to display a list of the valid role names. When specifying a role, the user automatically gains the permissions that are defined for the role. If a role is not specified, the user gains the permissions granted to the default public role. To add a user to a role, a value for username must be supplied (username can be the same as login_id.)

  To access a database, a login must be granted access by using sp_adduser or sp_grantdbaccess, or the guest security account must exist in the database.

  sp_adduser cannot be executed inside a user-defined transaction.

  Permissions
  Only the dbo and members of the sysadmin fixed server role can execute sp_adduser.

  Examples
  A. Add a user
  This example adds the user Victoria to the existing fort_mudge role in the current database, using the existing login Victoria.

EXEC sp_adduser 'Victoria', 'Victoria', 'fort_mudge'

  B. Add a username with the same login ID
  This example adds the default username Margaret to the current database for the login Margaret, which belongs to the default public role.

EXEC sp_adduser 'Margaret'

  C. Add a user who uses a different username
  This example adds the Haroldq login to the current database with a username of Harold, which belongs to the fort_mudge role.

EXEC sp_adduser 'Haroldq', 'Harold', 'fort_mudge'

  See Also

  sp_addrole

  sp_dropuser

  sp_grantdbaccess

  sp_grantlogin

  sp_helpuser

  System Stored Procedures

  The user role bit I have not figured out yet, but look in SQL books online for something like sp_sqlroles or sp_userrole.

  Cheers,
  Maartin.


  > Hi all,
  >
  > I need to define roles in my application. Certain groups are restricted to
  > access some part of my application.
  >
  > I want to know how to deal with users, and usergroups (roles in sqlserver).
  > Where should i store the user data ? should i create a table with user
  > information ?
  >
  > And when i add a user, how do i add that user to my sqlserver login and add
  > that user to my Users of my sqlserver database with a certain role ?
  >
  > thanks in advance,
  > Patrick
  >
  >



Fri, 12 Nov 2004 03:24:17 GMT  
 roles / users and application design question

Personally I would create a table that will define the users, then another that defines the groups.
The groups table will have access to certain functions etc, then you can specify extra acces to
users.

This big thing to do before hand is to figure out what groups you might need and what access they
will need, see you might run into a situasion where you have to many groups with more or less the
same acess rights. Then another thing to decide before hand is might one user belong to more than
one group ?

I would od the above and let a user belong to a certain group and then give 'extra' access to the user.
This will also speedup permissions checking etc. So as you dev the app just must break it down into
'user' functions that will be used in the permissions etc.

I hope this has given you some insight, if you want more info or suggestions shout again !!!

cheers,
Maartin.

---------------------------

  Maartin,
  Thanks for your explanations! Great.

  My concern is more how to deal with those users & groups in my application. (VB & ADO)

  So i defined a user in a certain group. How should i determine its rights.
  Should i get the group information (or user rights) out of sqlserver. Or should i create a user table with group (= rights) information.
  For example: For a certain group, menu items are disabled.

  And how should i add users ? The database is secured by NT Authenication.
  Just on the database by the database administrator or from my application ?
  Should i use the login name from Windows or create a table with user information ?

  I hope this will make it more clear (?),

  Patrick


    well here is the bit for adding the user to SQL

    sp_adduser
    Adds a security account for a new user in the current database. This procedure is included for backward compatibility. Use sp_grantdbaccess.

    Syntax



    Arguments

    Is the name of the user's login. login is sysname, with no default. login must be an existing Microsoft? SQL ServerT login or Microsoft Windows NT? user.


    Is the name for the new user. user is sysname, with a default of NULL. If user is not specified, the name of the user defaults to the login name. Specifying user gives the new user a name in the database different from the login ID on SQL Server.


    Is the group or role that the new user automatically becomes a member of. group is sysname, with a default of NULL. group must be a valid group or role in the current database. Microsoft SQL Server version 7.0 uses roles instead of groups.

    Return Code Values
    0 (success) or 1 (failure)

    Remarks
    SQL Server usernames can contain from 1 to 128 characters, including letters, symbols, and numbers. However, usernames cannot:

      a.. Contain a backslash character (\).

      b.. Be NULL, or an empty string ('').
    After a user has been added, use the GRANT, DENY, and REVOKE statements to define the permissions controlling the activities performed by the user.

    Use sp_helplogin to display a list of valid login names.

    Use sp_helprole to display a list of the valid role names. When specifying a role, the user automatically gains the permissions that are defined for the role. If a role is not specified, the user gains the permissions granted to the default public role. To add a user to a role, a value for username must be supplied (username can be the same as login_id.)

    To access a database, a login must be granted access by using sp_adduser or sp_grantdbaccess, or the guest security account must exist in the database.

    sp_adduser cannot be executed inside a user-defined transaction.

    Permissions
    Only the dbo and members of the sysadmin fixed server role can execute sp_adduser.

    Examples
    A. Add a user
    This example adds the user Victoria to the existing fort_mudge role in the current database, using the existing login Victoria.

EXEC sp_adduser 'Victoria', 'Victoria', 'fort_mudge'

    B. Add a username with the same login ID
    This example adds the default username Margaret to the current database for the login Margaret, which belongs to the default public role.

EXEC sp_adduser 'Margaret'

    C. Add a user who uses a different username
    This example adds the Haroldq login to the current database with a username of Harold, which belongs to the fort_mudge role.

EXEC sp_adduser 'Haroldq', 'Harold', 'fort_mudge'

    See Also

    sp_addrole

    sp_dropuser

    sp_grantdbaccess

    sp_grantlogin

    sp_helpuser

    System Stored Procedures

    The user role bit I have not figured out yet, but look in SQL books online for something like sp_sqlroles or sp_userrole.

    Cheers,
    Maartin.


    > Hi all,
    >
    > I need to define roles in my application. Certain groups are restricted to
    > access some part of my application.
    >
    > I want to know how to deal with users, and usergroups (roles in sqlserver).
    > Where should i store the user data ? should i create a table with user
    > information ?
    >
    > And when i add a user, how do i add that user to my sqlserver login and add
    > that user to my Users of my sqlserver database with a certain role ?
    >
    > thanks in advance,
    > Patrick
    >
    >



Fri, 12 Nov 2004 03:43:27 GMT  
 roles / users and application design question

Thanks,
I also thought about the extra table for the 'roles' in my application.
At this point we have only defined groups. But maybe it's a consideration worth moving to user functions.

So you would create a table with "functions" and a table with users?

btw, i think this is a big issue for most developers....
I hoped more people joined this discussion!

  Personally I would create a table that will define the users, then another that defines the groups.
  The groups table will have access to certain functions etc, then you can specify extra acces to
  users.

  This big thing to do before hand is to figure out what groups you might need and what access they
  will need, see you might run into a situasion where you have to many groups with more or less the
  same acess rights. Then another thing to decide before hand is might one user belong to more than
  one group ?

  I would od the above and let a user belong to a certain group and then give 'extra' access to the user.
  This will also speedup permissions checking etc. So as you dev the app just must break it down into
  'user' functions that will be used in the permissions etc.

  I hope this has given you some insight, if you want more info or suggestions shout again !!!

  cheers,
  Maartin.

  ---------------------------


    Maartin,
    Thanks for your explanations! Great.

    My concern is more how to deal with those users & groups in my application. (VB & ADO)

    So i defined a user in a certain group. How should i determine its rights.
    Should i get the group information (or user rights) out of sqlserver. Or should i create a user table with group (= rights) information.
    For example: For a certain group, menu items are disabled.

    And how should i add users ? The database is secured by NT Authenication.
    Just on the database by the database administrator or from my application ?
    Should i use the login name from Windows or create a table with user information ?

    I hope this will make it more clear (?),

    Patrick


      well here is the bit for adding the user to SQL

      sp_adduser
      Adds a security account for a new user in the current database. This procedure is included for backward compatibility. Use sp_grantdbaccess.

      Syntax



      Arguments

      Is the name of the user's login. login is sysname, with no default. login must be an existing Microsoft? SQL ServerT login or Microsoft Windows NT? user.


      Is the name for the new user. user is sysname, with a default of NULL. If user is not specified, the name of the user defaults to the login name. Specifying user gives the new user a name in the database different from the login ID on SQL Server.


      Is the group or role that the new user automatically becomes a member of. group is sysname, with a default of NULL. group must be a valid group or role in the current database. Microsoft SQL Server version 7.0 uses roles instead of groups.

      Return Code Values
      0 (success) or 1 (failure)

      Remarks
      SQL Server usernames can contain from 1 to 128 characters, including letters, symbols, and numbers. However, usernames cannot:

        a.. Contain a backslash character (\).

        b.. Be NULL, or an empty string ('').
      After a user has been added, use the GRANT, DENY, and REVOKE statements to define the permissions controlling the activities performed by the user.

      Use sp_helplogin to display a list of valid login names.

      Use sp_helprole to display a list of the valid role names. When specifying a role, the user automatically gains the permissions that are defined for the role. If a role is not specified, the user gains the permissions granted to the default public role. To add a user to a role, a value for username must be supplied (username can be the same as login_id.)

      To access a database, a login must be granted access by using sp_adduser or sp_grantdbaccess, or the guest security account must exist in the database.

      sp_adduser cannot be executed inside a user-defined transaction.

      Permissions
      Only the dbo and members of the sysadmin fixed server role can execute sp_adduser.

      Examples
      A. Add a user
      This example adds the user Victoria to the existing fort_mudge role in the current database, using the existing login Victoria.

EXEC sp_adduser 'Victoria', 'Victoria', 'fort_mudge'

      B. Add a username with the same login ID
      This example adds the default username Margaret to the current database for the login Margaret, which belongs to the default public role.

EXEC sp_adduser 'Margaret'

      C. Add a user who uses a different username
      This example adds the Haroldq login to the current database with a username of Harold, which belongs to the fort_mudge role.

EXEC sp_adduser 'Haroldq', 'Harold', 'fort_mudge'

      See Also

      sp_addrole

      sp_dropuser

      sp_grantdbaccess

      sp_grantlogin

      sp_helpuser

      System Stored Procedures

      The user role bit I have not figured out yet, but look in SQL books online for something like sp_sqlroles or sp_userrole.

      Cheers,
      Maartin.


      > Hi all,
      >
      > I need to define roles in my application. Certain groups are restricted to
      > access some part of my application.
      >
      > I want to know how to deal with users, and usergroups (roles in sqlserver).
      > Where should i store the user data ? should i create a table with user
      > information ?
      >
      > And when i add a user, how do i add that user to my sqlserver login and add
      > that user to my Users of my sqlserver database with a certain role ?
      >
      > thanks in advance,
      > Patrick
      >
      >



Sat, 13 Nov 2004 01:33:33 GMT  
 roles / users and application design question

Well I would have 3 tables
1.    Groups,
2.    Users,
3.    Functions or Items that will need security.

And then work it from there. You can also skip the 'functions' table and keep it all in the app, but it will give head aches later !
Well, everybody has their own idea on how to do it. I have seen this topic covered many times on various VB groups with more or less the same out come. So don't feel bad about only me joining.

I hope this discussion helped you.

Cheers,
Maartin.

  Thanks,
  I also thought about the extra table for the 'roles' in my application.
  At this point we have only defined groups. But maybe it's a consideration worth moving to user functions.

  So you would create a table with "functions" and a table with users?

  btw, i think this is a big issue for most developers....
  I hoped more people joined this discussion!


    Personally I would create a table that will define the users, then another that defines the groups.
    The groups table will have access to certain functions etc, then you can specify extra acces to
    users.

    This big thing to do before hand is to figure out what groups you might need and what access they
    will need, see you might run into a situasion where you have to many groups with more or less the
    same acess rights. Then another thing to decide before hand is might one user belong to more than
    one group ?

    I would od the above and let a user belong to a certain group and then give 'extra' access to the user.
    This will also speedup permissions checking etc. So as you dev the app just must break it down into
    'user' functions that will be used in the permissions etc.

    I hope this has given you some insight, if you want more info or suggestions shout again !!!

    cheers,
    Maartin.

    ---------------------------


      Maartin,
      Thanks for your explanations! Great.

      My concern is more how to deal with those users & groups in my application. (VB & ADO)

      So i defined a user in a certain group. How should i determine its rights.
      Should i get the group information (or user rights) out of sqlserver. Or should i create a user table with group (= rights) information.
      For example: For a certain group, menu items are disabled.

      And how should i add users ? The database is secured by NT Authenication.
      Just on the database by the database administrator or from my application ?
      Should i use the login name from Windows or create a table with user information ?

      I hope this will make it more clear (?),

      Patrick


        well here is the bit for adding the user to SQL

        sp_adduser
        Adds a security account for a new user in the current database. This procedure is included for backward compatibility. Use sp_grantdbaccess.

        Syntax



        Arguments

        Is the name of the user's login. login is sysname, with no default. login must be an existing Microsoft? SQL ServerT login or Microsoft Windows NT? user.


        Is the name for the new user. user is sysname, with a default of NULL. If user is not specified, the name of the user defaults to the login name. Specifying user gives the new user a name in the database different from the login ID on SQL Server.


        Is the group or role that the new user automatically becomes a member of. group is sysname, with a default of NULL. group must be a valid group or role in the current database. Microsoft SQL Server version 7.0 uses roles instead of groups.

        Return Code Values
        0 (success) or 1 (failure)

        Remarks
        SQL Server usernames can contain from 1 to 128 characters, including letters, symbols, and numbers. However, usernames cannot:

          a.. Contain a backslash character (\).

          b.. Be NULL, or an empty string ('').
        After a user has been added, use the GRANT, DENY, and REVOKE statements to define the permissions controlling the activities performed by the user.

        Use sp_helplogin to display a list of valid login names.

        Use sp_helprole to display a list of the valid role names. When specifying a role, the user automatically gains the permissions that are defined for the role. If a role is not specified, the user gains the permissions granted to the default public role. To add a user to a role, a value for username must be supplied (username can be the same as login_id.)

        To access a database, a login must be granted access by using sp_adduser or sp_grantdbaccess, or the guest security account must exist in the database.

        sp_adduser cannot be executed inside a user-defined transaction.

        Permissions
        Only the dbo and members of the sysadmin fixed server role can execute sp_adduser.

        Examples
        A. Add a user
        This example adds the user Victoria to the existing fort_mudge role in the current database, using the existing login Victoria.

EXEC sp_adduser 'Victoria', 'Victoria', 'fort_mudge'

        B. Add a username with the same login ID
        This example adds the default username Margaret to the current database for the login Margaret, which belongs to the default public role.

EXEC sp_adduser 'Margaret'

        C. Add a user who uses a different username
        This example adds the Haroldq login to the current database with a username of Harold, which belongs to the fort_mudge role.

EXEC sp_adduser 'Haroldq', 'Harold', 'fort_mudge'

        See Also

        sp_addrole

        sp_dropuser

        sp_grantdbaccess

        sp_grantlogin

        sp_helpuser

        System Stored Procedures

        The user role bit I have not figured out yet, but look in SQL books online for something like sp_sqlroles or sp_userrole.

        Cheers,
        Maartin.


        > Hi all,
        >
        > I need to define roles in my application. Certain groups are restricted to
        > access some part of my application.
        >
        > I want to know how to deal with users, and usergroups (roles in sqlserver).
        > Where should i store the user data ? should i create a table with user
        > information ?
        >
        > And when i add a user, how do i add that user to my sqlserver login and add
        > that user to my Users of my sqlserver database with a certain role ?
        >
        > thanks in advance,
        > Patrick
        >
        >



Sat, 13 Nov 2004 05:49:21 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. SQL Application Role Question

2. Using roles in COM+ : design problem

3. another design-time user control question

4. design-time user control question

5. A Multi-User Design Question

6. VBE in User Designed Control Design Mode

7. A basic user interface design question.

8. Application design question

9. Application Design / Schema Question

10. Application Design Question

11. Using SQL Server Application Roles in a VB.NET web app

12. Role-Based Security Using User's Windows Account Info Not Working Under Win9x

 

 
Powered by phpBB® Forum Software