2

How can I use grant and creation of a view to allow a user to have read only access to certain columns in a table but also be allowed to update only one of the columns?

Can you specify a specific column only for update on a grant?

Christopher
  • 671
  • 3
  • 10
  • 18
  • 1
    Please provide more information... is the view based on 1 table or > 1 tables ? is the column he shall be allowed to update somthing calculated/formatted... ? – Yahia Jul 28 '11 at 03:08
  • Only 1 table, and the columns only contain data, no calculations or functions. So basically, the user should be able to see 4 of the 5 columns in the table, but only able to update 1 of them. – Christopher Jul 28 '11 at 03:09
  • see my answer... this can be done without a view... but if you need the view let me know - can add that if necessary – Yahia Jul 28 '11 at 03:16

1 Answers1

2

So let's say we have a table T with col1...col5 and the user U, he should not see col5 () view needed) and should update col3 (no view needed):

CREATE VIEW V AS SELECT col1, col2, col3, col4 FROM T;

GRANT SELECT, UPDATE (col3) ON V TO U;

see http://www.oracle-dba-online.com/sql/grant_and_revoke_privileges.htm

EDIT: corrected a mistake...

Yahia
  • 69,653
  • 9
  • 115
  • 144
  • Great, I wasn't sure if you could specify a specific column in the grant. thx! – Christopher Jul 28 '11 at 03:18
  • Thanks. One other quick question if you have a second. To take this one step further, on the single column that I am granting the user update privileges, how can I put a where clause in there so that they can't update that column unless the value of the column is greater than a specific number? – Christopher Jul 28 '11 at 03:36
  • can they see a row when the value smaller than a specific number ? IF yes, then you need to use a "BEFORE UPDATE" trigger on the view... IF no, then just put the where clause into the view definition – Yahia Jul 28 '11 at 03:38
  • Yep they need to be able to see the column when the value is smaller. I'll see if I can figure out that trigger. Thanks again – Christopher Jul 28 '11 at 03:41