Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    14

    Unhappy Unanswered: how to grant permission on some columns only?

    how to grant permission to a user update,select only some rows of a table?

    e.g.: a table named emp with column:
    empno cname city sal discnt

    the question is what's sql be to:
    Grant permission to user eoneil on the customers table to update only the cname and city columns, and select all columns other than the discnt column.

    great thanks for your help!!!

    yanz

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Not 100% sure how to implement. However, you might want to look in the direction of creating a view.

    For example,
    Code:
    CREATE VIEW my_view (SELECT * FROM emp WHERE city = 'SOME CITY');
    
    GRANT update ON my_view TO some_user;
    I know this likely won't address your question completely, but might give you some ideas...
    JoeB
    save disk space, use smaller fonts

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    You could create specific roles where you grant permission(s) at the column level.

    For example: To grant user/role oe the REFERENCES privilege on the employee_id column and the UPDATE privilege on the employee_id, salary, and commission_pct columns of the employees table in the schema hr, issue the following statement:
    Code:
    GRANT REFERENCES (employee_id), 
              UPDATE (employee_id, salary, commission_pct) 
       ON hr.employees
       TO oe;


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Even better! Learned something new -- Thanks LK...
    JoeB
    save disk space, use smaller fonts

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    That's why this is the BEST Oracle Forum -- I also learn something new every day!


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Mar 2004
    Posts
    14
    yes, I also learn a lot from this forum. thanks for all of your help!
    sorry, here I have another question for you. I tried

    GRANT UPDATE (employee_id, salary, commission_pct)
    ON hr.employees
    TO oe;
    It works! but when I tried:

    GRANT select (employee_id, salary, commission_pct)
    ON hr.employees
    TO oe;

    It won't work. why? thanks!

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is because you should check syntax and spelling and use the keyword ON where required.

  8. #8
    Join Date
    Mar 2004
    Posts
    14
    If I say:
    grant select on hr.employees to oe;
    it works. but my question is how to grant select on some columns only. not all of the columns.

    I still couldn't figure out what's wrong with this syntax:
    GRANT select (employee_id, salary, commission_pct)
    ON hr.employees
    TO oe;

    great thanks!

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First statement works because it is correctly written. Oracle complains about the second because you used incorrect syntax.

    You'll have to create a view on that table and include only those columns that you'd like to "show" to the other user, and grant SELECT on view.

    Another option would be use of vertical partitioning (but don't ask me about that; I never used it).

  10. #10
    Join Date
    Jul 2012
    Posts
    1

    According to Oracle documentation.

    I know that this post is very old, but i found it when i's finding this error.
    According to Oracle11g:
    Only "UPDATE, REFERENCES and INSERT can be restricted by specifying a subset of updatable columns".
    "A select privilege can be restricted by creating a view with a subset of columns and granting the select privilege only on the view".
    Last edited by EdgarSan17; 07-26-12 at 17:12. Reason: Incomplete

  11. #11
    Join Date
    Oct 2004
    Posts
    60
    Oracle VPD will help you in securing row / column security.
    Last edited by edwin_fredrick; 07-27-12 at 01:52.

Posting Permissions

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