DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: insert/update priv without direct access

  1. #1
    Join Date
    Nov 2001
    Posts
    110

    insert/update priv without direct access

    A user needs insert, update to a table column in my schema. However I donot want to give him the privelages directly. Someone mentioned to use profile to achieve DB Security. Can some give me a relevant link.
    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    looked up virtual private database or fgac.

    but whats wrong with granting insert on the table to him or to a role?

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: insert/update priv without direct access

    Originally posted by Natik
    A user needs insert, update to a table column in my schema.
    There is no such thing as "having INSERT privilege on a COLUMN". It's the whole row or nothing, you can't have insert privilege only on a specific columns (as you can have with UPDATE). If you need to limit someone to write only into certan columns of a table you need to create a view on that table with only those specific columns and grant the insert privilege on that view.

    And forget about using database profiles for what you are asking here - profiles have nothing to do with this whatsoever.

    Also, VPD (aka FGAC) as suggested by davey23uk can not help you here. VPD limits the access to a table/view "horizontaly", while you are trying to limit acces "verticaly".

    But as davey23uk said: what's the reason you don't want to grant him/here the privileges on that table?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    You can create a procedure that insert/update the table in your schema and grant execute to the user that you want to have an indirect access.

  5. #5
    Join Date
    May 2001
    Posts
    736
    Rather than complicating the things with procedures it is better to create a simple view with only those columns and grant the required privileges as suggested by jurig

  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by akhadar
    Rather than complicating the things with procedures it is better to create a simple view with only those columns and grant the required privileges as suggested by jurig
    I didn't negate what JModic says, the alternative that I've made will works fine with the same intention as for the VIEW was intended. The only "complicating" that you mentioned is for somebody who is not used to do with such approach.

    It works fine for me 'cause I have scripts that generates scripts for TABLES/VIEWS privs grants to users/roles. And that if this became a VIEW then this will "complicate" my way of doing things too.

    I have the same scenario with this before, and it works fine for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width