Re: sql2k5 security

I guess I am confused. Why do I need to make dbo the owner of all objects
inside myschema? The user webuser is a member of the role db_web and not a
member of the role dbo. So it would still fail right? I would appreciate you
shedding some light on this.

Thanks

"David Browne" <davidbaxterbrowne no potted meat@xxxxxxxxxxx> wrote in
message news:eyjZqeYkGHA.456@xxxxxxxxxxxxxxxxxxxxxxx

<param@xxxxxxxxxxxxxxxx> wrote in message
news:uU1mUYYkGHA.1320@xxxxxxxxxxxxxxxxxxxxxxx
Hi all, I am having an issue with SQL2K5 security. Here is my setup.

Tables

dbo.Table1
dbo.Table2
dbo.Table3

Roles

db_web

Users

webuser - member of db_web

Schemas

myschema - Execute/Select/Insert/Delete permissions given to db_web role.

Stored Procedures

myschema.dosomething

When I login as webuser and execute myschema.dosomething I get a
permissions error saying I do not have select and insert permissions on
dbo.Table1

Essentially, I would like to shield all access by webuser to executing
stored procedures and views only and let the stored procs fetch/insert
the data from the base tables. This is considered good security for web
apps.

How do I accomplish this?


You need to make dbo the owner of myschema and all the objects in it.
Then ownership chaining will supress permission checks on the other
objects owned by dbo.

To examine schema ownership:

select name, user_name(principal_id) from sys.schemas

To change it:

ALTER AUTHORIZATION ON SCHEMA::myschema TO dbo;

Remember you must not allow a low-privilege user to create views, synonyms
or procedures in a schema owned by dbo. If you do then this same
mechanism would allow them to access any dbo-owned object.

David


.

Relevant Pages

  • Re: sql2k5 security
    ... When I login as webuser and execute myschema.dosomething I get a permissions error saying I do not have select and insert permissions on dbo.Table1 ... I would like to shield all access by webuser to executing stored procedures and views only and let the stored procs fetch/insert the data from the base tables. ... Then ownership chaining will supress permission checks on the other objects owned by dbo. ...
    (microsoft.public.sqlserver.security)
  • Re: When creating a new table, owner is DBO I need another user
    ... database is mapped to the special user inside each database called dbo. ... any object created by any member of the sysadmin fixed server role ...
    (microsoft.public.sqlserver.security)
  • Re: Default User
    ... database is mapped to the special user inside each database called dbo. ... any object created by any member of the sysadmin fixed server role ...
    (microsoft.public.sqlserver.security)
  • Re: Default User
    ... a member of the db_owner Role. ... The dbo is a user that has implied permissions to perform all activities ... database is mapped to the special user inside each database called dbo. ... any object created by any member of the sysadmin fixed server role ...
    (microsoft.public.sqlserver.security)
  • Re: Changing Table Owner and user Owner
    ... Database Owner (dbo) ... any object created by any member of the sysadmin fixed server role ...
    (microsoft.public.sqlserver.security)