Re: Re: Creating a role with read only privileges but user is allowed to change password

Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx> · Mon, 12 May 2014 09:56:34 +1200



    On 12/05/14 08:33, David G Johnston
      wrote:

    
                I
               suggest that you move the password to a separate table
              (my_role_password) with 2 columns:

              
                my_role_id
                password.
              
              This way you can make the my_role table totally
              unalterable by the user, yet they can change their own
              password.

              
              Actually, you should NOT be storing passwords in plain
              text, they should be stored as a secure hash (better than
              MD5).

            
            I have no
              clue what you are trying to get at here...the core problem
              is with database defined roles - which are maintained in
              the system catalog - and the fact that marking a session
              read-only disallows updates to the system catalog...
            

            I do not
              see how adding a user table with role and password
              overcomes that problem since the user table would be
              read-only too - so how would they still be able to change
              their password if the cannot alter the table (data alter,
              not structure).
            

            David J.
            

    I was thinking of using privileges to control access, for user
    'fred':

    
    REVOKE ALL ON my_role FROM fred;

    GRANT SELECT ON my_role TO fred;

    
    That's the guts of it, you will obviously need to check other tables
    and their appropriate privileges.

    
    Note that both commands have the option 'ALL TABLES IN SCHEMA schema_name'.

    
    Cheers,

    Gavin

    
    P.S. You forgot to include my name (and date/time) from the bit you
    quoted from me!