Re: Public Group Security Issue
From: Jasper Smith (jasper_smith9@hotmail.com)
Date: 11/09/02
- Next message: Mary Chipman: "Re: SQL And Access Project Security Question"
- Previous message: Javier Rosas: "Reset proxy password failed"
- In reply to: Robert Keck: "Public Group Security Issue"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Jasper Smith" <jasper_smith9@hotmail.com> Date: Sat, 9 Nov 2002 14:48:36 -0000
SQL uses the most restrictive security setting to determine the
effective security of a user. All databases users are members of
the public fixed database role and cannot be removed from it.
So if I run through a quick example for a new blank database
(where I am the database owner and a sysadmin)
I add a server login "test" with access to the database via the public role
The database contains a table dbo.tableb which has had no permissions
applied to it.
user "test" tries to run a select * from tableb and gets permission denied
I grant select on tableb to "test" and user "test" can now select from table
I now deny select on tableb to public and user "test" gets permission denied
Owner Object Grantee Grantor ProtectType Action Column
------ ------------ ------------ ------- ----------- ------ ----------------
-- dbo tableb public dbo Deny Select (All+New) dbo tableb test dbo Grant Select (All+New) The effective permission of user "test" is the most restrictive of the permissions, thus even though user test is explicitly granted select on tableb, due to their membership of the public role their effective permission is deny select I hope this is clear :-) -- HTH Jasper Smith (SQL Server MVP) Check out the PASS Community Summit - Seattle, the largest and only user event entirely dedicated to SQL Server, November 19-22. http://www.sqlpass.org/events/seattle/index.cfm "Robert Keck" <robertk@fprodsa.com> wrote in message news:S%Xy9.1467$Vh3.979891368@newssvr11.news.prodigy.com... > What am I missing??? > > I'm new to SQL server and am in the process of doing some compatibility > testing > > I've a table on a SQL2000 server that is joined to an AD domain (using > Windows Only security) > > I've added domain users and domain groups to the SQL database and assigned > appopriate permissions to the table. > > However, the only security changes that matter are table permission change s > to the Public group i.e. if I deny ALL permissions to the Public group (at > the table level), and give ANY/ALL permissions to one of the other groups > and/or users, an appropriate user (on another machine), via an MS Access adp > file, can not access or see the table. Basically, the SQL server seems to > only be concerned with permissions of the Public group and totally > disregards everything else. > > Thanks in advance > >
- Next message: Mary Chipman: "Re: SQL And Access Project Security Question"
- Previous message: Javier Rosas: "Reset proxy password failed"
- In reply to: Robert Keck: "Public Group Security Issue"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
