February 26, 2008 at 2:00 am
I am designing a database that will have N security levels.
Level 1 will be for Anonymous Web users (read-only access to a limited set of tables and views.)
Levels 2 to N-1 will have increasing access to the system, via the web.
Level N will be for System Administrators. (Access to everything via an MS Access client, or SQL Server Management Studio.)
I am planning to design the database with N Schemas (SchemaLevel1 to SchemaLevelN),
and N roles (RoleLevel1 to RoleLevelN).
RoleLevel1 will have access to SchemaLevel1 only
RoleLevel2 will have access to SchemaLevel2 + SchemaLeval1.
RoleLevel3 will have access to SchemaLevel3 + SchemaLevel2 + SchemaLevel1.
etc.
....
RoleLevelN will have access to all schemas.
I am unclear what rights schema ownership gives to the owner. Hence, I am unclear how to grant ownership to the various scemas. I could do it one of two ways:
A) SchemaLevel1 is owned by RoleLevel1, SchemaLevel2 is owned by RoleLevel2.
or
B) all Schemas are owned by RoleLevelN (the System Administrator and Database Owner.)
I'd be grateful to receive advice on this matter.
Thanks,
Mark Thornton
February 26, 2008 at 2:37 am
I think you might be making life difficult for yourself. What if you need to do row-level security in the future?
Since all non-administrators will have access only via the web, why not use stored procedures or views?
February 27, 2008 at 3:19 am
I am planning to use views and stored procedures. But they will be part of a schema structure, as I described in my original posting.
Best wishes,
Mark
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply