Help with creating database roles and NT Groups

Help with creating database roles and NT Groups

Post by serg » Tue, 16 Jul 2002 07:46:02



I am little bit confused on the method to take for creating
SQL Logins and Database roles.

I am using SQL Server 7 (SP4) and Access 2000 Project
(.adp) as the front-end.

I am using NT Authentication, the SQL Server is part
of an SBS4.5 domain, so all users who will be using the Access
program are already connected to the NT Server. The Access
ADP is setup to use Integrated Security, therefore no user login
prompts for the users.

When creating database roles, what approach should i take?
    1- should i create groups and assign permission to these groups
    one by one; for example:
        Accounting Group
            - able to access Orders tables, Shipping tables, Products table
            (read only, no reason for them to modify anything in these
tables)
            - able to modify Customers table (to put a Customer's Account
            Status field to "Good Credit", "Bad Credit", etc...)
        Sales Group
            - able to enter/modify Orders, Shipping, etc...

    My point being here is: i create a group then assign all the permissions
    to this group.

    2- An opposite method: I create roles such as :
        - OrdersAllAccess
            now i put users or groups in this role. All users and groups in
this
            database role will be able to read, edit, add Orders....
        - OrdersReadAccess
            .... users/groups will have read only...

        - Products All role
            ...

    So this case, i am creating two roles per entity let's say and then put
the
    users/groups in these roles...

I hope i am explaining well... I am still confused and don't know which
method is good and which one is bad.

Should i be creating many NT Groups first, such as :
    - Accounting, then put a few peoples name in here
    - Sales
    - Supervisors
    - Managers
    ...

And if i create these NT groups, do i avoid putting a user name
in more than one group? For example, the president should have
access to everything, do i create an NT group called President,
then create a Database user to this NT login, then create a database
role called President and put the President group in there?

Honestly, i am very confused now, i don't know what to do?
With the possibilities of putting groups inside groups and so on,
i am completely lost on what to do.

The problem is not just dealing with security on the server side,
i have to be able to check the user opening the Access file, who
he/she is, and then check which group she's part of, then show
controls or not, etc... And if users exist in many groups, i am not
sure if this will improve my life or complicate it further.

At this point, i need any advise you can provide me with, since
i am completely lost at what direction to go.

Thank you very much

 
 
 

Help with creating database roles and NT Groups

Post by Mary Chipma » Tue, 16 Jul 2002 22:42:43


You can do it either way. If your Windows groups map to the
functionality you want in your database, you can enable those groups
to use the database and assign permissions directly to the Windows
groups. If not, you can create your own database roles, and assign
either users or Windows groups to those roles, and users automatically
inherit the permissions of the groups. You should read up on the
subject in SQL BooksOnline -- the section on security and configuring
groups and roles is quite good. See "Managing Security" in the
"Administering SQL Server" book.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Sun, 14 Jul 2002 18:46:02 -0400, "serge"


>I am little bit confused on the method to take for creating
>SQL Logins and Database roles.

>I am using SQL Server 7 (SP4) and Access 2000 Project
>(.adp) as the front-end.

>I am using NT Authentication, the SQL Server is part
>of an SBS4.5 domain, so all users who will be using the Access
>program are already connected to the NT Server. The Access
>ADP is setup to use Integrated Security, therefore no user login
>prompts for the users.

>When creating database roles, what approach should i take?
>    1- should i create groups and assign permission to these groups
>    one by one; for example:
>        Accounting Group
>            - able to access Orders tables, Shipping tables, Products table
>            (read only, no reason for them to modify anything in these
>tables)
>            - able to modify Customers table (to put a Customer's Account
>            Status field to "Good Credit", "Bad Credit", etc...)
>        Sales Group
>            - able to enter/modify Orders, Shipping, etc...

>    My point being here is: i create a group then assign all the permissions
>    to this group.

>    2- An opposite method: I create roles such as :
>        - OrdersAllAccess
>            now i put users or groups in this role. All users and groups in
>this
>            database role will be able to read, edit, add Orders....
>        - OrdersReadAccess
>            .... users/groups will have read only...

>        - Products All role
>            ...

>    So this case, i am creating two roles per entity let's say and then put
>the
>    users/groups in these roles...

>I hope i am explaining well... I am still confused and don't know which
>method is good and which one is bad.

>Should i be creating many NT Groups first, such as :
>    - Accounting, then put a few peoples name in here
>    - Sales
>    - Supervisors
>    - Managers
>    ...

>And if i create these NT groups, do i avoid putting a user name
>in more than one group? For example, the president should have
>access to everything, do i create an NT group called President,
>then create a Database user to this NT login, then create a database
>role called President and put the President group in there?

>Honestly, i am very confused now, i don't know what to do?
>With the possibilities of putting groups inside groups and so on,
>i am completely lost on what to do.

>The problem is not just dealing with security on the server side,
>i have to be able to check the user opening the Access file, who
>he/she is, and then check which group she's part of, then show
>controls or not, etc... And if users exist in many groups, i am not
>sure if this will improve my life or complicate it further.

>At this point, i need any advise you can provide me with, since
>i am completely lost at what direction to go.

>Thank you very much


 
 
 

Help with creating database roles and NT Groups

Post by serg » Wed, 17 Jul 2002 04:02:45


Thank you Mary, i will read the section in the Books Online.

It's also clearer for me about NT groups and Database roles.

I will avoid creating NT groups. Instead i will add all users
(about 15-20), in the SQL Logins, then add them all in
the database as users. Then i will create many database roles
and add the users to the roles they will have access to.

My database roles will consift of mainly two database roles
per entity:
    - Orders Full (users in this role are able to add/modify
    Orders data)
    - Orders Read (users... are only able to read...)

I don't think i'll have any nesting scheme that will get too
complex like you point out on page 117 of your book.

Since a user will always be inside one of the two or three
database roles in each entity, the nesting will not get too complex.
The user is either in :
    - entity role 1
    - entity role 2
    - entity role 3
    - or not part of any of this entity's roles.

I will not make a user be part of more than one entity role, always
one or nothing.

Then, in the Access forms Open event, i will check to see the user
is member of what role and enable/disable controls on the form based
on roles.

By the way, i was reading one of your posts in another thread, you
were saying that you had no plans of writing a new book for ADP 2002
since there was no major improvements on ADP 2002, you were also
complaining that the UI was worst and that MS hadn't done anything
about unbound ADO recordsets, do you think it's worth upgrading
from Office 2000 Developer to Office 2002 before the end of MS's
rebate offers ending this July 31st?

I assume Office 2002 uses MDAC 2.7, I know you're not an MS
support person, but are you aware of any issues installing Office XP
on an NT 4.0 Server (SBS 4.5 SP6)?

Thank you very much


> You can do it either way. If your Windows groups map to the
> functionality you want in your database, you can enable those groups
> to use the database and assign permissions directly to the Windows
> groups. If not, you can create your own database roles, and assign
> either users or Windows groups to those roles, and users automatically
> inherit the permissions of the groups. You should read up on the
> subject in SQL BooksOnline -- the section on security and configuring
> groups and roles is quite good. See "Managing Security" in the
> "Administering SQL Server" book.

> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446

> On Sun, 14 Jul 2002 18:46:02 -0400, "serge"

> >I am little bit confused on the method to take for creating
> >SQL Logins and Database roles.

> >I am using SQL Server 7 (SP4) and Access 2000 Project
> >(.adp) as the front-end.

> >I am using NT Authentication, the SQL Server is part
> >of an SBS4.5 domain, so all users who will be using the Access
> >program are already connected to the NT Server. The Access
> >ADP is setup to use Integrated Security, therefore no user login
> >prompts for the users.

> >When creating database roles, what approach should i take?
> >    1- should i create groups and assign permission to these groups
> >    one by one; for example:
> >        Accounting Group
> >            - able to access Orders tables, Shipping tables, Products
table
> >            (read only, no reason for them to modify anything in these
> >tables)
> >            - able to modify Customers table (to put a Customer's Account
> >            Status field to "Good Credit", "Bad Credit", etc...)
> >        Sales Group
> >            - able to enter/modify Orders, Shipping, etc...

> >    My point being here is: i create a group then assign all the
permissions
> >    to this group.

> >    2- An opposite method: I create roles such as :
> >        - OrdersAllAccess
> >            now i put users or groups in this role. All users and groups
in
> >this
> >            database role will be able to read, edit, add Orders....
> >        - OrdersReadAccess
> >            .... users/groups will have read only...

> >        - Products All role
> >            ...

> >    So this case, i am creating two roles per entity let's say and then
put
> >the
> >    users/groups in these roles...

> >I hope i am explaining well... I am still confused and don't know which
> >method is good and which one is bad.

> >Should i be creating many NT Groups first, such as :
> >    - Accounting, then put a few peoples name in here
> >    - Sales
> >    - Supervisors
> >    - Managers
> >    ...

> >And if i create these NT groups, do i avoid putting a user name
> >in more than one group? For example, the president should have
> >access to everything, do i create an NT group called President,
> >then create a Database user to this NT login, then create a database
> >role called President and put the President group in there?

> >Honestly, i am very confused now, i don't know what to do?
> >With the possibilities of putting groups inside groups and so on,
> >i am completely lost on what to do.

> >The problem is not just dealing with security on the server side,
> >i have to be able to check the user opening the Access file, who
> >he/she is, and then check which group she's part of, then show
> >controls or not, etc... And if users exist in many groups, i am not
> >sure if this will improve my life or complicate it further.

> >At this point, i need any advise you can provide me with, since
> >i am completely lost at what direction to go.

> >Thank you very much

 
 
 

Help with creating database roles and NT Groups

Post by Mary Chipma » Wed, 17 Jul 2002 10:31:19


The features I mentioned that I didnt like were the UI (where
everything is a query) and the unbound recordset issue. However, if
you are planning an adp app, then I'd definitely choose Access 2002
over Access 2000. Remember that 2000 was v1 of a new product, so there
were a lot of bug fixes in 2002 that make the upgrade worth while, and
it works with SQL Server 2000 without complaining. I don't know of any
installation issues with NT, but it never hurts to search the KB and
ask around a bit before leaping in.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Mon, 15 Jul 2002 15:02:45 -0400, "serge"


>Thank you Mary, i will read the section in the Books Online.

>It's also clearer for me about NT groups and Database roles.

>I will avoid creating NT groups. Instead i will add all users
>(about 15-20), in the SQL Logins, then add them all in
>the database as users. Then i will create many database roles
>and add the users to the roles they will have access to.

>My database roles will consift of mainly two database roles
>per entity:
>    - Orders Full (users in this role are able to add/modify
>    Orders data)
>    - Orders Read (users... are only able to read...)

>I don't think i'll have any nesting scheme that will get too
>complex like you point out on page 117 of your book.

>Since a user will always be inside one of the two or three
>database roles in each entity, the nesting will not get too complex.
>The user is either in :
>    - entity role 1
>    - entity role 2
>    - entity role 3
>    - or not part of any of this entity's roles.

>I will not make a user be part of more than one entity role, always
>one or nothing.

>Then, in the Access forms Open event, i will check to see the user
>is member of what role and enable/disable controls on the form based
>on roles.

>By the way, i was reading one of your posts in another thread, you
>were saying that you had no plans of writing a new book for ADP 2002
>since there was no major improvements on ADP 2002, you were also
>complaining that the UI was worst and that MS hadn't done anything
>about unbound ADO recordsets, do you think it's worth upgrading
>from Office 2000 Developer to Office 2002 before the end of MS's
>rebate offers ending this July 31st?

>I assume Office 2002 uses MDAC 2.7, I know you're not an MS
>support person, but are you aware of any issues installing Office XP
>on an NT 4.0 Server (SBS 4.5 SP6)?

>Thank you very much



>> You can do it either way. If your Windows groups map to the
>> functionality you want in your database, you can enable those groups
>> to use the database and assign permissions directly to the Windows
>> groups. If not, you can create your own database roles, and assign
>> either users or Windows groups to those roles, and users automatically
>> inherit the permissions of the groups. You should read up on the
>> subject in SQL BooksOnline -- the section on security and configuring
>> groups and roles is quite good. See "Managing Security" in the
>> "Administering SQL Server" book.

>> -- Mary
>> Microsoft Access Developer's Guide to SQL Server
>> http://www.amazon.com/exec/obidos/ASIN/0672319446

>> On Sun, 14 Jul 2002 18:46:02 -0400, "serge"

>> >I am little bit confused on the method to take for creating
>> >SQL Logins and Database roles.

>> >I am using SQL Server 7 (SP4) and Access 2000 Project
>> >(.adp) as the front-end.

>> >I am using NT Authentication, the SQL Server is part
>> >of an SBS4.5 domain, so all users who will be using the Access
>> >program are already connected to the NT Server. The Access
>> >ADP is setup to use Integrated Security, therefore no user login
>> >prompts for the users.

>> >When creating database roles, what approach should i take?
>> >    1- should i create groups and assign permission to these groups
>> >    one by one; for example:
>> >        Accounting Group
>> >            - able to access Orders tables, Shipping tables, Products
>table
>> >            (read only, no reason for them to modify anything in these
>> >tables)
>> >            - able to modify Customers table (to put a Customer's Account
>> >            Status field to "Good Credit", "Bad Credit", etc...)
>> >        Sales Group
>> >            - able to enter/modify Orders, Shipping, etc...

>> >    My point being here is: i create a group then assign all the
>permissions
>> >    to this group.

>> >    2- An opposite method: I create roles such as :
>> >        - OrdersAllAccess
>> >            now i put users or groups in this role. All users and groups
>in
>> >this
>> >            database role will be able to read, edit, add Orders....
>> >        - OrdersReadAccess
>> >            .... users/groups will have read only...

>> >        - Products All role
>> >            ...

>> >    So this case, i am creating two roles per entity let's say and then
>put
>> >the
>> >    users/groups in these roles...

>> >I hope i am explaining well... I am still confused and don't know which
>> >method is good and which one is bad.

>> >Should i be creating many NT Groups first, such as :
>> >    - Accounting, then put a few peoples name in here
>> >    - Sales
>> >    - Supervisors
>> >    - Managers
>> >    ...

>> >And if i create these NT groups, do i avoid putting a user name
>> >in more than one group? For example, the president should have
>> >access to everything, do i create an NT group called President,
>> >then create a Database user to this NT login, then create a database
>> >role called President and put the President group in there?

>> >Honestly, i am very confused now, i don't know what to do?
>> >With the possibilities of putting groups inside groups and so on,
>> >i am completely lost on what to do.

>> >The problem is not just dealing with security on the server side,
>> >i have to be able to check the user opening the Access file, who
>> >he/she is, and then check which group she's part of, then show
>> >controls or not, etc... And if users exist in many groups, i am not
>> >sure if this will improve my life or complicate it further.

>> >At this point, i need any advise you can provide me with, since
>> >i am completely lost at what direction to go.

>> >Thank you very much

 
 
 

Help with creating database roles and NT Groups

Post by serg » Wed, 17 Jul 2002 10:36:43


Thanks Mary, i will search a bit.


> The features I mentioned that I didnt like were the UI (where
> everything is a query) and the unbound recordset issue. However, if
> you are planning an adp app, then I'd definitely choose Access 2002
> over Access 2000. Remember that 2000 was v1 of a new product, so there
> were a lot of bug fixes in 2002 that make the upgrade worth while, and
> it works with SQL Server 2000 without complaining. I don't know of any
> installation issues with NT, but it never hurts to search the KB and
> ask around a bit before leaping in.

> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446

> On Mon, 15 Jul 2002 15:02:45 -0400, "serge"

> >Thank you Mary, i will read the section in the Books Online.

> >It's also clearer for me about NT groups and Database roles.

> >I will avoid creating NT groups. Instead i will add all users
> >(about 15-20), in the SQL Logins, then add them all in
> >the database as users. Then i will create many database roles
> >and add the users to the roles they will have access to.

> >My database roles will consift of mainly two database roles
> >per entity:
> >    - Orders Full (users in this role are able to add/modify
> >    Orders data)
> >    - Orders Read (users... are only able to read...)

> >I don't think i'll have any nesting scheme that will get too
> >complex like you point out on page 117 of your book.

> >Since a user will always be inside one of the two or three
> >database roles in each entity, the nesting will not get too complex.
> >The user is either in :
> >    - entity role 1
> >    - entity role 2
> >    - entity role 3
> >    - or not part of any of this entity's roles.

> >I will not make a user be part of more than one entity role, always
> >one or nothing.

> >Then, in the Access forms Open event, i will check to see the user
> >is member of what role and enable/disable controls on the form based
> >on roles.

> >By the way, i was reading one of your posts in another thread, you
> >were saying that you had no plans of writing a new book for ADP 2002
> >since there was no major improvements on ADP 2002, you were also
> >complaining that the UI was worst and that MS hadn't done anything
> >about unbound ADO recordsets, do you think it's worth upgrading
> >from Office 2000 Developer to Office 2002 before the end of MS's
> >rebate offers ending this July 31st?

> >I assume Office 2002 uses MDAC 2.7, I know you're not an MS
> >support person, but are you aware of any issues installing Office XP
> >on an NT 4.0 Server (SBS 4.5 SP6)?

> >Thank you very much



> >> You can do it either way. If your Windows groups map to the
> >> functionality you want in your database, you can enable those groups
> >> to use the database and assign permissions directly to the Windows
> >> groups. If not, you can create your own database roles, and assign
> >> either users or Windows groups to those roles, and users automatically
> >> inherit the permissions of the groups. You should read up on the
> >> subject in SQL BooksOnline -- the section on security and configuring
> >> groups and roles is quite good. See "Managing Security" in the
> >> "Administering SQL Server" book.

> >> -- Mary
> >> Microsoft Access Developer's Guide to SQL Server
> >> http://www.amazon.com/exec/obidos/ASIN/0672319446

> >> On Sun, 14 Jul 2002 18:46:02 -0400, "serge"

> >> >I am little bit confused on the method to take for creating
> >> >SQL Logins and Database roles.

> >> >I am using SQL Server 7 (SP4) and Access 2000 Project
> >> >(.adp) as the front-end.

> >> >I am using NT Authentication, the SQL Server is part
> >> >of an SBS4.5 domain, so all users who will be using the Access
> >> >program are already connected to the NT Server. The Access
> >> >ADP is setup to use Integrated Security, therefore no user login
> >> >prompts for the users.

> >> >When creating database roles, what approach should i take?
> >> >    1- should i create groups and assign permission to these groups
> >> >    one by one; for example:
> >> >        Accounting Group
> >> >            - able to access Orders tables, Shipping tables, Products
> >table
> >> >            (read only, no reason for them to modify anything in these
> >> >tables)
> >> >            - able to modify Customers table (to put a Customer's
Account
> >> >            Status field to "Good Credit", "Bad Credit", etc...)
> >> >        Sales Group
> >> >            - able to enter/modify Orders, Shipping, etc...

> >> >    My point being here is: i create a group then assign all the
> >permissions
> >> >    to this group.

> >> >    2- An opposite method: I create roles such as :
> >> >        - OrdersAllAccess
> >> >            now i put users or groups in this role. All users and
groups
> >in
> >> >this
> >> >            database role will be able to read, edit, add Orders....
> >> >        - OrdersReadAccess
> >> >            .... users/groups will have read only...

> >> >        - Products All role
> >> >            ...

> >> >    So this case, i am creating two roles per entity let's say and
then
> >put
> >> >the
> >> >    users/groups in these roles...

> >> >I hope i am explaining well... I am still confused and don't know
which
> >> >method is good and which one is bad.

> >> >Should i be creating many NT Groups first, such as :
> >> >    - Accounting, then put a few peoples name in here
> >> >    - Sales
> >> >    - Supervisors
> >> >    - Managers
> >> >    ...

> >> >And if i create these NT groups, do i avoid putting a user name
> >> >in more than one group? For example, the president should have
> >> >access to everything, do i create an NT group called President,
> >> >then create a Database user to this NT login, then create a database
> >> >role called President and put the President group in there?

> >> >Honestly, i am very confused now, i don't know what to do?
> >> >With the possibilities of putting groups inside groups and so on,
> >> >i am completely lost on what to do.

> >> >The problem is not just dealing with security on the server side,
> >> >i have to be able to check the user opening the Access file, who
> >> >he/she is, and then check which group she's part of, then show
> >> >controls or not, etc... And if users exist in many groups, i am not
> >> >sure if this will improve my life or complicate it further.

> >> >At this point, i need any advise you can provide me with, since
> >> >i am completely lost at what direction to go.

> >> >Thank you very much

 
 
 

1. query:HELP! HELP HELP HELP HELP

ok here goes...
i have a database set up with many records that are listed like this...
there is an "agency name" then a "contact name" for each agency.
BUT, there can be more than one "site" for each agency, with a related
"contact name" for each "site".
SO...
i know that when i run a query, if i use a single check mark next to a
column name, it will sort out all the dupicates.  which is exactly what i
want.
i need to print a list of all my agencies and the address etc, sorting out
all the duplicated "agency names" that come up due to the multiple
"sites".

as u can see, it is kinda complicated, but if any 1 can provide me wih
help, i would appreciate it so much...


thanks.

2. External Application Calls

3. SQL To ORACLE Help Help Help Help Help

4. SQL Trace is the GREATEST

5. Help Help How can I get NT Group Name

6. Mouse moveover toolbar popout buttons

7. Help Help Register NT by Group

8. Another (ADO?) discrepancy between NT4 and Win2k

9. Help Help How Can I get NT Group Name

10. HELP HELP HELP HELP!

11. Local NT group perms - help!

12. SQL7, raid NT server , can't make copy of database for testdb - HELP

13. HELP - Database Setup App Help NEEDED ???