Secure schema or view?

We have one database with multiple schemas.
Different departments have their own schema, which only they should be able
to read/write.
We also have 1 reference data schema that all users of the database should
have read access
What is the best way to secure the data?
Proposed Method 1 (which I've been trying to do):
1. Define groups to the database with datareader and datawriter permissions
to the database
2. Create views on the tables.
3. Grant the appropriate AD groups read/write to the views
4. Deny public access to the underlying tables
Issue with this method is what is the best way to keep departments from
reading another department's schema without having
to specify them by name in a DENY SELECT statement - this would have to be
done every time we add a new group to the database.

Proposed Method 2: (based on postings in this forum)
1. Define groups to the database with datareader and datawriter
2. Change the owner of the departmental schemas to an AD group that contains
only the users that should have access to the data in that schema
For the reference schema, grant select to public
Remove all grants and deny's that were previously done on the views and tables
Will this method provide adequate protection?

zeon (zeon@discussions.microsoft.com) writes:
> We have one database with multiple schemas.
> Different departments have their own schema, which only they should be
> able to read/write.
> We also have 1 reference data schema that all users of the database should
> have read access
> What is the best way to secure the data?

I would create one role per deparment schema, and then grant
INSERT, DELETE, UPDATE and SELECT one that schema to the respective role.
I would also make sure that no one is a member of db_datareader/writer.

If the departments are permitted to create objects in their respective
schema, the roles needs permission for that too. It would also be a good
idea to make the role the owner of the schema, as by default the schema
owner, owns the obejcts created in the schema.

Grant access to all roles (or public) on the reference schema.


--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx