Re: sql2k5 security


<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: Help pls: Not allow users access to tables, stored procedures
    ... tables are owned by dbo. ... SQL Server MVP ... etc) is performed via stored procedures. ... correct permission for select permission on the tables. ...
    (microsoft.public.sqlserver.security)
  • Re: MS Access cant find Recordsource object
    ... object names and stored procedures that reference objects. ... I guess I'll get busy prepending dbo. ... > since later versions are better able to resolve queries where objects ... If I give those users sysadmin privileges, ...
    (microsoft.public.sqlserver.server)
  • Re: dbo. in vs generated code for stored procedures.
    ... all of your stored procedures be owned by dbo so that you don't have ... stored procedures to be owned by dbo, ... If she is not a sysadmin, ... A developer who is a db_owner can ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Preventing Injection - Client Side
    ... > In addition the program needs to read from ... Are they owned by "dbo"? ... To perform DML operations on tables, I would use stored procedures. ...
    (microsoft.public.sqlserver.security)
  • Re: sql2k5 security
    ... Why do I need to make dbo the owner of all objects ... The user webuser is a member of the role db_web and not a ... member of the role dbo. ... webuser - member of db_web ...
    (microsoft.public.sqlserver.security)