anyone tell me the best way for data partitioning and applying security permissions?

  • 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

  • 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.

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • 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