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: multiple cascade paths
    ... You should not have them if your schema is designed properly. ... make you understand that I tried to show that a proper design can have ... am a SQL Server MVP, for crying out loud - you can safely assume that I ...
    (comp.databases.ms-sqlserver)
  • 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: same application on multiple schemas
    ... As for the "comparison" with Sql Server, it was not intended to ask ... why Oracle does "not" support sql server behaviour:) I know oracle (I ... For every customer that will use our application, ... will use schema "SchemaA" ...
    (comp.databases.oracle.server)
  • 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)