Re: Schema as a security mechanism?

From what I read, I found that schema is used as a container to group
database objects, rather than a security mechanism on its own. Role is a
grouping of db users for a purpose.

Than is correct. A schema is basically a namespace to facilitate grouping related database objects. A role is a group of users with similar security requirements.


So how can schema helps in security? I read something about defense in
depth, something like multiple level of security. Does schema by itself helps
security?

Schema can be used to implement custom security. For example, you could grant execute permissions on all stored procedures in the Sales schema with:

GRANT EXECUTE ON SCHEMA::Sales TO SalespersonRole


Can it replace role?

Schema are not intended to replace roles.


Lastly, with the question posed, 'why should be change from using role to
schema?', how should I answer this?

For the same sort of reason you should switch from using a hammer to a screwdriver. If you have a hammer in your hand and your target is a screw, it's time to switch.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Eugene" <Eugene@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:87513959-0819-4E1B-9A2B-129D997DEEBF@xxxxxxxxxxxxxxxx
Hi, I have a question regarding the new schema feature, and the existing
database roles. I have found quite some discussion on them in both msdn
newsgroups as well as articles for other websites.

Briefly, I got asked this question, why should we choose to use schema
instead of roles that we have been using all these years/

From what I read, I found that schema is used as a container to group
database objects, rather than a security mechanism on its own. Role is a
grouping of db users for a purpose.

So how can schema helps in security? I read something about defense in
depth, something like multiple level of security. Does schema by itself helps
security? Can it replace role?

Lastly, with the question posed, 'why should be change from using role to
schema?', how should I answer this?

thanks
Eugene

.

Relevant Pages

  • Re: ASP.NET web app, Win2003, & Active Directory
    ... This error generally results from the ADSI schema cache not getting read ... The primary cause of this is a security issue. ... are binding to AD to (which is generally the null or anonymous account) ... you end up binding to the directory because your current security ...
    (microsoft.public.dotnet.security)
  • Re: INFOSEC/NACOSA sec. templates and EXPANDING the AD SChema?
    ... Microsoft MVP (Windows Security) ... > INFOSEC/NACOSA sec. templates and EXPANDING the AD SChema? ... > Controllers" ver 1.1, 15.Aug.2002), when I try to expand the AD schema ...
    (microsoft.public.windows.server.security)
  • Re: INFOSEC/NACOSA sec. templates and EXPANDING the AD SChema?
    ... Microsoft MVP (Windows Security) ... > INFOSEC/NACOSA sec. templates and EXPANDING the AD SChema? ... > Controllers" ver 1.1, 15.Aug.2002), when I try to expand the AD schema ...
    (microsoft.public.win2000.security)
  • Re: Export schema
    ... schema extensions seems to be pretty much completely undocumented to ... with the attribute I want to set default security on, ... I'm adding new ACEs - but instantiating a new object does get the new ... >partition head and the defaultSecurityDescriptor of the attributeSchema ...
    (microsoft.public.windows.server.active_directory)
  • Re: Creating schema in oracle10g express edition
    ... In oracle if i've 50 schemas have 50 users.. ... oracle have the concept of one database, where i can create a user (and ... than a schema) as container of my database objects (tables, ...
    (comp.databases.oracle.server)