Re: Secure schema or view?

zeon (zeon@xxxxxxxxxxxxxxxxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Need help on how to organize users and objects
    ... Yes Oracle is a bit different than Sql Server, but I think you will get the ... It doesnt become part of any schema until you assign it. ... a new developer started to work. ... need the COMMON database which stores tables with generic data and generic ...
    (microsoft.public.sqlserver.security)
  • Re: 3 Simple Security SQL Statements
    ... In SQL 2005, when you create an object, you specify the schema that the ... Kalen Delaney, SQL Server MVP ... Schema for a database role, that seems like the best setup. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)
  • Re: Model Driven Architecture, bussiness rules in DB?
    ... authorization schema. ... neither does SQL Server. ... For instance when you grant access to a table you should be able ... accessing applications? ...
    (comp.databases.ingres)
  • Re: CREATE Table permission
    ... Property Default Schema is not available for database. ... Sounds like you are using some graphical tool in SSMS. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Server to .xsd
    ... > output an XSD from an entire database schema, ... > "Val Mazur" wrote: ... >> Do you need to get schema of specific table in a SQL Server? ...
    (microsoft.public.data.ado)