Обсуждение: Data access permission?
Dear all Can PostgreSQL, MySQL or other database programs can allow some user to access some specific data while other data is prohibited to them in a same database? If so, how to implement that? For instance, a client can print last month's statement through the Internet by providing a password to him/her; this client can only check the data that belong to him/her, at the meantime, there is other clients' data in the same database. Thanks for your kind help! Pluto
Pluto <pluto@annoyance.net> writes: > Dear all > > Can PostgreSQL, MySQL or other database programs can allow some user to > access some specific data while other data is prohibited to them in a > same database? If so, how to implement that? For instance, a client can > print last month's statement through the Internet by providing a > password to him/her; this client can only check the data that belong to > him/her, at the meantime, there is other clients' data in the same > database. Thanks for your kind help! I don't understand very well the idea. If you want to restrict user access per table you can do it using the commands grant/revoke; however if you want to restrict user access per row; I'm afraid this is not possible, at least not without triggers or rules. Regards, Manuel.
Pluto wrote: >Dear all > >Can PostgreSQL, MySQL or other database programs can allow some user to >access some specific data while other data is prohibited to them in a >same database? If so, how to implement that? For instance, a client can >print last month's statement through the Internet by providing a >password to him/her; this client can only check the data that belong to >him/her, at the meantime, there is other clients' data in the same >database. Thanks for your kind help! What you need to do is to deny access to the underlying table, but create views containing each user's particular data and make those views readable by the appropriate users. Updatable views are not yet implemented, so you will have to do some roundabout work to enable users to update data - perhaps have them store data in intermediate locations from which some other process transfers the data into the underlying table. You probably need to be running 7.1 to do this effectively. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "But the fruit of the Spirit is love, joy, peace, patience, kindness, goodness, faithfulness, gentleness, self control; against such there is no law." Galatians 5:22,23
Pluto wrote: > Can PostgreSQL, MySQL or other database programs can allow some user to > access some specific data while other data is prohibited to them in a > same database? If so, how to implement that? For instance, a client can Check the GRANT and REVOKE commands. This will give you table-level access control. > print last month's statement through the Internet by providing a > password to him/her; this client can only check the data that belong to > him/her, at the meantime, there is other clients' data in the same > database. Thanks for your kind help! If the data is in the same table, create two views (see CREATE VIEW) to control what data can be seen. Grant permission to the view required and revoke permissions on the underlying table. You can of course also apply controls in your application, and it might be an idea to do both (security in depth). There's more in Bruce's book (link to the online version from www.postgresql.org) and my notes at techdocs.postgresql.org - Richard Huxton
If he wants to restrict access to certains rows, he could create views on those tables containing only the desired rows and then grant access in that view to the correspondent user. Of course, he would have to create a view to each user, but that would be no worse than manually grant/revoke access to rows (if that were possible). Carlos Felipe Zirbes DBServer Assessoria em Sistemas de Informação E-mail: carlosz@dbserver.com.br Fone: (51) 342-8055 / (51) 378-9056 Fax: (51) 342-4838 -----Original Message----- From: Manuel Sugawara [mailto:masm@fciencias.unam.mx] Sent: sexta-feira, 30 de março de 2001 15:21 To: Pluto Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Data access permission? Pluto <pluto@annoyance.net> writes: > Dear all > > Can PostgreSQL, MySQL or other database programs can allow some user to > access some specific data while other data is prohibited to them in a > same database? If so, how to implement that? For instance, a client can > print last month's statement through the Internet by providing a > password to him/her; this client can only check the data that belong to > him/her, at the meantime, there is other clients' data in the same > database. Thanks for your kind help! I don't understand very well the idea. If you want to restrict user access per table you can do it using the commands grant/revoke; however if you want to restrict user access per row; I'm afraid this is not possible, at least not without triggers or rules. Regards, Manuel. ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Thanks to all who reply to my question, you do really help! Pluto Oliver Elphick wrote: > Pluto wrote: > >Dear all > > > >Can PostgreSQL, MySQL or other database programs can allow some user to > >access some specific data while other data is prohibited to them in a > >same database? If so, how to implement that? For instance, a client can > >print last month's statement through the Internet by providing a > >password to him/her; this client can only check the data that belong to > >him/her, at the meantime, there is other clients' data in the same > >database. Thanks for your kind help! > > What you need to do is to deny access to the underlying table, but > create views containing each user's particular data and make those views > readable by the appropriate users. Updatable views are not yet > implemented, so you will have to do some roundabout work to enable > users to update data - perhaps have them store data in intermediate locations > from which some other process transfers the data into the underlying table. > > You probably need to be running 7.1 to do this effectively. > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight http://www.lfix.co.uk/oliver > PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "But the fruit of the Spirit is love, joy, peace, > patience, kindness, goodness, faithfulness, > gentleness, self control; against such there is no > law." Galatians 5:22,23 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html