#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    2
    Rep Power
    0

    Restrict Access to certain fields


    What is the best way to hide/remove fields from certain users on postgres. For example having a customer table which has bank_account info
    ____________________
    id | name | bank_account
    1 | bob | 123456
    2 | sam | 987654

    Only certain users should be able to see bank_account, everyone else should see id and name.

    So far I have looked at changing permissions on the fields in the database, this works but has the problem that users can't SELECT * customer as they are restricted from viewing one or more fields in the table.

    Whats the best way to restrict fields access but still allow select * to work?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    The only solution I can think of is to give the users only access to the data through a view.

    Revoke SELECT privilege on the table for that specific user (role)
    Create a view selecting only the columns the user should be able to see
    Grant SELECT privilege on that view to the user (role)
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    2
    Rep Power
    0
    Thanks,

    Can views be set up to auto change if the underline table structure changes?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by jonathan2012
    Can views be set up to auto change if the underline table structure changes?
    No, not that I know of.

IMN logo majestic logo threadwatch logo seochat tools logo