MS SQL Server security - Roles based security 2005 versus 2000

Author

Roles based security 2005 versus 2000

francisco.lopez@community.nospam

2006-10-25, 6:01 am

Hi,
There is something really easy and simple that I do to control permissions
in Sql Server 2000: this is role based security.

I set up a role, give the role necessary permissions to the appropriate objects,
then add users to the role. Viola! You are done. When you need to add a user,
you just add the user into the role.

It appears, this is not longer the case in Sql Server 2005. Sql Server 2005
has a bigger security model.

Now, my question is: can someone show me some articles or tell me what is
the best practice for handling security the way I like to do it in Sql Server
2000? How can I set up a Database Role, give the role permissions (to tables,
sp, functs, etc); and, add users/login to the role.

It appears this is not as simple as it used to be - more power, more responsability.

Francisco

Peter Yang [MSFT]

2006-10-25, 6:01 am

Hello Francisco,

I understand that you'd like to know how to handle security the same way as
that in SQL 2005. If I'm off-base, please let me know.

Based on my experience, you could still use the same secuirty model as that
in SQL 2000. You could map users in database to SQL logins, add the users
to a database role, and grant the proper permissions to the role. The major
difference is that schema and users are seperated in 2005. You may want to
see major changes in secuirty of SQL 2005 from:

Security in SQL Server 2005
http://www.microsoft.com/sql/techno...features_1.mspx

Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005
http://msdn.microsoft.com/msdnmag/i...ity/default.asp
x

On The Horizon: Improved Data Security In SQL Server 2005
http://www.microsoft.com/technet/te...taSecurity/defa
ult.aspx

Also, sp_addrole and sp_droprole is replaced by CREATE ROLE and DROP ROLE.
You may want to see some other related change in SQL 2005 from the
following link:

Deprecated Database Engine Features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143729.aspx

CREATE ROLE
http://msdn2.microsoft.com/en-us/library/ms187936.aspx

sp_addrolemember
http://msdn2.microsoft.com/en-us/library/ms187750.aspx

SQL 2005 has some new conceptions such as Principals and Securables but
they acutally reference some old entities such as login/user/role table or
extend its scope to new features such as certficate/key/schema etc, please
see the following article for details:

Security Considerations for Databases and Database Applications
http://msdn2.microsoft.com/en-us/library/ms187648.aspx

Generally, on database level you shall still use database role to control
permssions on objects such as table/view etc but you may need to consider
new features when necessary.

If anything is unclear, please feel free to let's know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
====================
====================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
====================
====================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Peter Yang [MSFT]

2006-10-25, 6:01 am

Hello Francisco,

Just want to check in if you have further questions on the issue. Please
feel free to post back if you have any comments. Thanks!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

====================
====================
=============

This posting is provided "AS IS" with no warranties, and confers no rights.
====================
====================
==============

francisco.lopez@community.nospam

2006-10-25, 6:01 am

Hi, Peter Yang [MSFT],
Thanks for the reply.

This is how I understand it - at this moment.

Database Role based security in Sql 2005
1. Add the database role thru Database>>Security>>Database Roles>>New
Database Role... Click [OK] to close database role window.
2. Right Click Database>>Properties... Select Permissions page. Add [new
role] to DB.
3. Give the role permissions in Explicit permissions for [new role].
4. Add new users to [new role].

Now, I think the above works.
However, I am still learning about how to use the Database Role>>Properties>>Securables>>Add...

I think both approaches should work in a similar way; but, when i add tables,
sps, to the securables window and save.
When I return to the Role>>Properties>>Securables pane; the settings are
GONE! Try it. I am sure there is a logical explanation for this but it has
elluded me for a while.

Francisco

> Hello Francisco,
>
> Just want to check in if you have further questions on the issue.
> Please feel free to post back if you have any comments. Thanks!
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> ====================
====================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights. ====================
====================
==============
>

Peter Yang [MSFT]

2006-10-25, 6:01 am

Hello Francisco,

I was able to repro the problem as following:

1. In SQL Mgmt Studio expand SECURITY under a specific Database and then
Expand ROLES
2. RIght-Click on "DATABASE ROLES" and select "Create Database Role"
3. Specify the Name of the Role and click OK
4. Right-Click on the Database Role and select Properties
5. In the Properties Dialog select the SECURABLES page
6. Add a Table Object as a securable and specify some "Specific Permissions"
7. Click on OK to save and close
8. Look at the Properties of the Database Role again and select the
SECURABLES page
9. Note that the Securable Object added does not show up in here
10. If you add the same object back in again it retains the previous
settings

This is a known issue and has been reported to the product group. From the
feedback of product team, this behavior is by design due to the following
reason:
there could conceivably be hundreds of thousands of objects in a database.
Attempting to display all the objects and permissiosn would be time
consuming. Product team remove this feature to optimize the performance of
Management studio. By searching for a particular tables/SPS again, you
will see the active permission settings. The permissions you added are
active and if you try to grant the permissions again by select the
tables/sps, you shall see the permissions you added.

Also if you want to display the all the permssions of a database role from
your own application, you may consider use databaserole object in SMO to
do this job. You may use EnumObjectPermission
s method to get all the
permissions.

http://msdn2.microsoft.com/en-us/li...management.smo.
databaserole. enumobjectpermission
s.aspx

Please rest assured that your feedback on this is routed to the product
team. There might be a feature to allow users to enable/disable this
feature if they want to see the list and having no concerns about
performance. In the meantime, I also encourage you submit via the link below

http://lab.msdn.microsoft.com/produ...ck/default.aspx

Please let's know if you have any further concerns on this issue. Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

====================
====================
=============

This posting is provided "AS IS" with no warranties, and confers no rights.
====================
====================
==============

francisco.lopez@community.nospam

2006-10-25, 6:01 am

Hi Peter,
Thank you.
In my opinion, the best way to handle data overload could be to add a filter
by... ability as standard in the Oracle SQL Developer here http://www.oracle.com/technology/pr...oper/index.html
..

Cheers,
Francisco

> Hello Francisco,
>
> I was able to repro the problem as following:
>
> 1. In SQL Mgmt Studio expand SECURITY under a specific Database and
> then
> Expand ROLES
> 2. RIght-Click on "DATABASE ROLES" and select "Create Database Role"
> 3. Specify the Name of the Role and click OK
> 4. Right-Click on the Database Role and select Properties
> 5. In the Properties Dialog select the SECURABLES page
> 6. Add a Table Object as a securable and specify some "Specific
> Permissions"
> 7. Click on OK to save and close
> 8. Look at the Properties of the Database Role again and select the
> SECURABLES page
> 9. Note that the Securable Object added does not show up in here
> 10. If you add the same object back in again it retains the previous
> settings
> This is a known issue and has been reported to the product group. From
> the
> feedback of product team, this behavior is by design due to the
> following
> reason:
> there could conceivably be hundreds of thousands of objects in a
> database.
> Attempting to display all the objects and permissiosn would be time
> consuming. Product team remove this feature to optimize the
> performance of
> Management studio. By searching for a particular tables/SPS again,
> you
> will see the active permission settings. The permissions you added are
> active and if you try to grant the permissions again by select the
> tables/sps, you shall see the permissions you added.
> Also if you want to display the all the permssions of a database role
> from your own application, you may consider use databaserole object
> in SMO to do this job. You may use EnumObjectPermission
s method to get
> all the permissions.
>
> http://msdn2.microsoft.com/en-us/li...erver.managemen
> t.smo. databaserole. enumobjectpermission
s.aspx
>
> Please rest assured that your feedback on this is routed to the
> product team. There might be a feature to allow users to
> enable/disable this feature if they want to see the list and having no
> concerns about performance. In the meantime, I also encourage you
> submit via the link below
>
> http://lab.msdn.microsoft.com/produ...ck/default.aspx
>
> Please let's know if you have any further concerns on this issue.
> Thank you!
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> ====================
====================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights. ====================
====================
==============
>

Peter Yang [MSFT]

2006-10-25, 6:01 am

Hello Francisco,

Thank you for your feedback! I think this is a great idea for a future
product enhancement and I will certainly forward your this to proper
channel. Also, you may want to submit via the link I mentioned so that
product team could hear your voice directly. Thanks!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

====================
====================
=============

This posting is provided "AS IS" with no warranties, and confers no rights.
====================
====================
==============

Sponsored Links