October 19, 2009 at 3:34 am
Hi,
I have a database with following tables:
1. Offices
2. OfficeGroups
3. Stations
4. StationGroups
5. Users
6. UserGroups
7. Accounts
Account will contains information about which office, station and user it belongs to, account number and creation date. Office, station and users have groups.
I want to apply security permission for each Office, officeGroup, Station, StationGroup, User and UserGroup to access accounts available.
Could anyone please provide the best way to handle the data partitioning or applying security permission for this scenario.
Thanks in advance
October 19, 2009 at 7:43 am
It Depends...
Let me give a explanation.
You have said you need to maintain security also across, then it would be good to implement schemas separately for group of tables..
It does not end there, if you later need to have SProcs on top of these tables which would join multiple tables, then the user executing need to have permissions on the other schemas also and it needs assigning permissions to the users individually.
Regarding partitioning, I dont think you are saying about Database partitioning, it is applicable only in case of very Large Tables, see the # of records in those tables, upon which database partitioning can be implemented.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 19, 2009 at 8:09 am
Thanks for your response.
My issue with the existing data partitioning is # of records. I have 2+ million records in Accounts table. Right now I have stored procedures to get
1. the allowed offices for the logged in user
2. the allowed stations for the logged in user
3. the allowed users for the logged in user and
4. combining above i will retrieve the accounts.
This takes lot of time just to query the account ids.
Could you suggest me best way to accommodate this?
Thanks in advance
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply