Re: stored procedures and permissions

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 07/01/04 

Date: Wed, 30 Jun 2004 19:13:23 -0600

If Todd is new and can't alter any objects what are the
solutions he is providing for the end users? Essentially
report writing or something along those lines?
I'd probably look at something where he could read all data.
Not sure about the rest. If he's new and you don't want him
modifying objects but need him to create objects then have
Biff or someone else with higher access execute his scripts
for creating objects - if you aren't comfortable with his
altering objects you'd probably want to review what objects
he is creating anyway.
Just a guess as it is kind of broad. Sometimes it's easier
to think of it in terms of "what permissions does x need"
and "what permissions does y need" rather then thinking of x
and y together and separating their permissions. You'd still
want to work towards having all objects owned by the same
user (dbo). It's more a matter of how to configure the
permissions within that structure rather than who should own
what.
I can't think of anything in particular documented in books
online or elsewhere. Different companies as well as
different groups in companies have different methods. You
could set up roles for the different types of developers.
Ideally you could have the Windows groups reflect the same -
then you'd only have to add the Windows groups to the role
for that group. But in real life, it doesn't always work
that way.
For some more security overview information, the following
are good links:
Microsoft SQL Server 2000 SP3 Security Features and Best
Practices
http://www.microsoft.com/technet/prodtechnol/sql/maintain/security/sp3sec/default.asp

SQL Server Security Resources Site:
http://www.microsoft.com/sql/techinfo/administration/2000/security/default.asp

-Sue

 On Wed, 30 Jun 2004 09:48:27 -0700, Damon Torgerson
<damon@ranksix.com> wrote:

>Thanks Sue,
>
>After more searching I found the bol info.
>
>I have a security question:
>
>Suppose I have two developers one with high security clearance (Biff)
>and one with low security clearance (Todd). Todd is new so I want to
>limit the data that he has access to and I don't want him to be able to
>alter the schema. Biff has full access to all the data and schema
>because of his security clearance. Both developers provide solutions to
>the same group of end users. End users access the system via stored
>procedures only.
>
>How might I partition Biff and Todd's access?
>
>This is a pretty open ended question so if there's a section in bol or
>some other resource you can recommend I certainly would appreciate it.
>
>Damon
>
>Sue Hoegemeier wrote:
>
>> The first line is not exactly correct. Understanding
>> ownership chains and how those work is the part that's
>> missing. All the objects need to be owned by the same user.
>> In that case, SQL Server checks permissions on the source
>> object only. For example, if the tables and stored
>> procedures have the same owner, you can grant execute on a
>> stored procedure to a user and the user does not need
>> permissions to the underlying tables.
>> If you have different owners for objects in the chain, SQL
>> Server checks permissions on each object where the owner is
>> different. If the user hasn't been granted permissions on
>> the underlying objects with different owners, you get the
>> permission denied error.
>> You can find some good information on ownership chains and
>> how they work in books online.
>>
>> -Sue
>>
>> On Tue, 29 Jun 2004 11:59:35 -0700, Damon Torgerson
>> <damon@ranksix.com> wrote:
>>
>>
>>>It is my understanding that when a user executes a stored procedure they
>>>are executing the stored procedure with the permissions of the stored
>>>procedure owner.
>>>
>>>However I am running into a problem that I cannot explain and would love
>>>it if someone might be able to shed some light on my problem.
>>>
>>>I have three users: myproject, myproject_app, and myproject_web
>>>
>>>- myproject owns all tables and views
>>>
>>>- myproject_app owns all stored procedures
>>>
>>>- myproject_web can execute some stored procedures
>>>
>>>When myproject_web tries to execute a stored procedure I receive errors
>>>that are similar to:
>>>
>>>Server: Msg 229, Level 14, State 5, Procedure getAllEmployees, Line 3
>>>SELECT permission denied on object 'employees', database
>>>'some_database', owner 'myproject'.
>>>
>>>Below is an example that produces the error...
>>>
>>>/* create a table */
>>>
>>>create table myproject.employees (
>>> id int,
>>> first_name varchar(100)
>>>)
>>>go
>>>
>>>/* grant select on table to user myproject_app */
>>>
>>>grant select on myproject.employees to myproject_app
>>>go
>>>
>>>/* insert some data */
>>>
>>>insert into myproject.employees (id, first_name) values (1, 'Fred')
>>>go
>>>insert into myproject.employees (id, first_name) values (2, 'Sally')
>>>go
>>>insert into myproject.employees (id, first_name) values (3, 'Dwayne')
>>>go
>>>
>>>/* create stored procedure */
>>>
>>>create stored procedure myproject_app.getAllEmployees
>>>as
>>>select id,
>>> first_name
>>>from myproject.employees
>>>go
>>>
>>>/* grant execute to user myproject_web */
>>>
>>>grant execute on myproject_app.getAllEmployees to myproject_web
>>>go
>>>
>>>/* as user myproject_web execute the stored procedure */
>>>
>>>exec myproject_app.getAllEmployees
>>>
>>>/* returns the following error
>>>
>>>Server: Msg 229, Level 14, State 5, Procedure getAllEmployees, Line 3
>>>SELECT permission denied on object 'employees', database
>>>'some_database', owner 'myproject'.
>>>
>>>*/
>>
>>

Relevant Pages

  • RE: What server hardening are you doing these days?
    ... permissions on their data, and Microsoft encourages ISVs to minimize ... I've been able to discuss ACLs and other security issues in Windows with ... Control or DAC (which is what you're referring to by the "stupid ...
    (Focus-Microsoft)
  • Re: permissions to import after a new workgroup file?
    ... To import as a new owner from a previous owner, ... for the objects in the original database. ... Normally permissions are assigned via Groups. ... You shouldn't be altering security measures with the "default workgroup". ...
    (microsoft.public.access.security)
  • Re: get rid of security center?
    ... I have come up with a solution that does not disable Security Center, ... By changing the Permissions of that key, ... settings from being changed again. ... the firewall alert settings in Security Center get ...
    (microsoft.public.windowsxp.help_and_support)
  • Re: Password Protect IExplore
    ... You can protect the files and folders you store on your computer to make ... To set, view, change, or remove special permissions for files and folders ... clear the Inherit from parent the permission entries that apply ... To configure security so that the subfolders and files will not ...
    (microsoft.public.internet.explorer.ieak)
  • Re: Removing the Internet Security in SP2
    ... I have come up with a solution that does not disable Security Center, ... By changing the Permissions of that key, ... settings from being changed again. ... the firewall alert settings in Security Center get ...
    (microsoft.public.windowsxp.security_admin)