SQL Server Object Security

[Noloader]

Hello,

We are using Access Front End (ADP Project) and SQL Server 2000
backend. SQL Server is using NT Security.

We do not want the users to access the the underlying tables. So, we
went to each table and unchecked 'Select'. We also placed an 'X' in
all columns for guest and public account.

Next, we created a view. The view has 'Select' permission given to the
users (it is also enforcing some business rules on whether a record
should be returned).

Access XP is behaving as expected - the tables are not showing up, and
a report can be created using the view.

Here is the test situation we have set up:
User 'test_a' has 'select' allowed on the view.
Built in user 'public' and 'guest' were not allowed on the view.

When we configured the view as stated above, 'test_a' did not have
access to the view (even though we specifically granted). It appears
the 'public' must also have at least 'Select'.

It is not the case for a member of BUILTIN\Adminis trators.

Is this expected behavior? Does SQL server not follow the NT Security
model? Or am I simply missing something with the 'public' role?

Should I create roles, then add users to the roles (so I can remove
public). This seems a little different than the NT model of
users/groups.

Also, thanks to Simon and Pete for their previous replies.

Jeff
no******@yahoo. com

[Dan Guzman]

> We also placed an 'X' in

all columns for guest and public account.
The red 'X' denotes an explicit DENY. Because DENY takes precedence over a
GRANT, the effective result is that no users can access the view because all
users are members of the public role. It is generally best to avoid using
DENY unless you have a specific reason to use it.
Should I create roles, then add users to the roles (so I can remove
public). This seems a little different than the NT model of
users/groups.
Yes, it's a good practice to grant permissions to roles rather than
individual users. This allows you to more easily control data access via
role membership. You can ignore the public role unless you want to grant
permissions to all database users.

I suggest you clear (REVOKE) all permissions on the view and then grant
permissions only to those roles as desired.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Noloader" <no******@yahoo .com> wrote in message
news:6b******** *************** ***@posting.goo gle.com... Hello,

We are using Access Front End (ADP Project) and SQL Server 2000
backend. SQL Server is using NT Security.

We do not want the users to access the the underlying tables. So, we
went to each table and unchecked 'Select'. We also placed an 'X' in
all columns for guest and public account.

Next, we created a view. The view has 'Select' permission given to the
users (it is also enforcing some business rules on whether a record
should be returned).

Access XP is behaving as expected - the tables are not showing up, and
a report can be created using the view.

Here is the test situation we have set up:
User 'test_a' has 'select' allowed on the view.
Built in user 'public' and 'guest' were not allowed on the view.

When we configured the view as stated above, 'test_a' did not have
access to the view (even though we specifically granted). It appears
the 'public' must also have at least 'Select'.

It is not the case for a member of BUILTIN\Adminis trators.

Is this expected behavior? Does SQL server not follow the NT Security
model? Or am I simply missing something with the 'public' role?

Should I create roles, then add users to the roles (so I can remove
public). This seems a little different than the NT model of
users/groups.

Also, thanks to Simon and Pete for their previous replies.

Jeff
no******@yahoo. com

[Noloader]

"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message news:<MU******* **********@news read2.news.pas. earthlink.net>. ..

We also placed an 'X' in
all columns for guest and public account.
The red 'X' denotes an explicit DENY. Because DENY takes precedence over a
GRANT, the effective result is that no users can access the view because all
users are members of the public role. It is generally best to avoid using
DENY unless you have a specific reason to use it.

Should I create roles, then add users to the roles (so I can remove
public). This seems a little different than the NT model of
users/groups.

Yes, it's a good practice to grant permissions to roles rather than
individual users. This allows you to more easily control data access via
role membership. You can ignore the public role unless you want to grant
permissions to all database users.

I suggest you clear (REVOKE) all permissions on the view and then grant
permissions only to those roles as desired.

--
Hope this helps.

Dan Guzman
SQL Server MVP

...

Hi Dan,

Thanks for the reply.
You can ignore the public role unless you want to grant
permissions to all database users.

We found if we revoke the deny role, other roles loose access. So
would you suggest we simply revoke (not deny) the public role and add
a role to encompass our users? If we deny the public role, then our
additional role does not have access to the view.

Also, what is the difference between revoke and deny. I've seen the
behavior it exhibits, but I was not able to get a good definition from
the help files.

Thanks,
Jeff
no******@yahoo. com

[Dan Guzman]

> So would you suggest we simply revoke (not deny) the public role and add

a role to encompass our users? If we deny the public role, then our
additional role does not have access to the view.
Yes, I suggest you start by revoking all object permissions. Then create
your own role(s), add role members and grant permissions to the role.
Also, what is the difference between revoke and deny. I've seen the
behavior it exhibits, but I was not able to get a good definition from
the help files.
This is a bit confusing and is why I discourage using DENY in most cases. A
GRANT adds a security entry that gives a user/role the specified permission.
A DENY adds a security entry that prohibits a user/role the specified
permission. The important point is that both GRANT and DENY *add* security
entries and, when conflicting entries exist, DENY takes precedence.

A REVOKE *removes* GRANT and DENY security entries so the result is no
permission entries. With no object permissions, the only users that can
directly access the object are:

1) the dbo user (includes sysadmin role members)

2) db_owner role members

3) db_datareader and db_datawriter role members (tables and views)

Other users can still access the object indirectly if the ownership chain is
unbroken and they have been granted permissions on the directly referenced
object. You can use unbroken ownership chains as a security mechanism by
allowing data access only via views and procs while prohibiting direct
access to the underlying data.

The example below illustrates how to implement this.

USE MyDatabase
GO

EXEC sp_addlogin 'MyUser1'
EXEC sp_addlogin 'MyUser2'

EXEC sp_addrole 'MyUserRole'
EXEC sp_addrole 'MyPowerUserRol e'

EXEC sp_adduser 'MyUser1'
EXEC sp_adduser 'MyUser2'

EXEC sp_addrolemembe r 'MyUserRole', 'MyUser1'
EXEC sp_addrolemembe r 'MyPowerUserRol e', 'MyUser2'
GO

CREATE TABLE MyTable
(
MyNonSensitiveD ata int NOT NULL,
MySensitiveData int NOT NULL
)
GO

CREATE VIEW MyView
AS
SELECT MyNonSensitiveD ata FROM MyTable
GO

GRANT SELECT ON MyView TO MyUserRole
GRANT ALL ON MyView TO MyPowerUserRole
GO

CREATE VIEW MyOtherView
AS
SELECT MyNonSensitiveD ata, MySensitiveData FROM MyTable
GO
GRANT ALL ON MyOtherView TO MyPowerUserRole
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Noloader" <no******@yahoo .com> wrote in message
news:6b******** *************** ***@posting.goo gle.com... "Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message

news:<MU******* **********@news read2.news.pas. earthlink.net>. ..

We also placed an 'X' in
all columns for guest and public account.

The red 'X' denotes an explicit DENY. Because DENY takes precedence over a GRANT, the effective result is that no users can access the view because all users are members of the public role. It is generally best to avoid using DENY unless you have a specific reason to use it.

Should I create roles, then add users to the roles (so I can remove
public). This seems a little different than the NT model of
users/groups.

Yes, it's a good practice to grant permissions to roles rather than
individual users. This allows you to more easily control data access via role membership. You can ignore the public role unless you want to grant permissions to all database users.

I suggest you clear (REVOKE) all permissions on the view and then grant
permissions only to those roles as desired.

--
Hope this helps.

Dan Guzman
SQL Server MVP

...

Hi Dan,

Thanks for the reply.

You can ignore the public role unless you want to grant
permissions to all database users.

We found if we revoke the deny role, other roles loose access. So
would you suggest we simply revoke (not deny) the public role and add
a role to encompass our users? If we deny the public role, then our
additional role does not have access to the view.

Also, what is the difference between revoke and deny. I've seen the
behavior it exhibits, but I was not able to get a good definition from
the help files.

Thanks,
Jeff
no******@yahoo. com