0

I created a table called products from a user called john. I want a user called doe to have permission to view that table and another user called lorem to be able to delete, insert, select, and update in this table. How can I do that? What roles and privileges do I need to set?

PD: someone told me that I have to do this with a:

CREATE VIEW view_products
as
SELECT * FROM products;

Is this right?

Glenn
  • 8,932
  • 2
  • 41
  • 54
luisurrutia
  • 586
  • 6
  • 21
  • Possible duplicate: [ORA-01031: insufficient privileges when selecting view] (http://stackoverflow.com/a/140665/1563878). Check this [link](http://stackoverflow.com/a/189496/1563878) too. If you can, post the oracle error code. – Gaston Flores Jun 24 '13 at 00:07
  • 1
    You do not need a view. Read up on the GRANT statement. – OldProgrammer Jun 24 '13 at 00:23
  • I do this 'GRANT SELECT ON COCO.PRODUCTOS TO "ZETA" WITH GRANT OPTION' but with _zeta_ i can view the table – luisurrutia Jun 24 '13 at 00:27

1 Answers1

1

You can use the GRANT statement:

grant select, insert, update, delete on products to lorem;

grant select on products  to doe;

If you plan on allowing multiple, different users access, then you may want to create a ROLE, assign users to the specific roles, then grant access to the roles instead of a individual user. That way, if you add more users in the future, you just have to assign them the proper role, and not mess with the table rights again. Easier to manage.

To use a view:

CREATE VIEW view_products
as
SELECT * FROM products;


grant select, insert, update, delete on view_products to lorem;
grant select on view_products  to doe;
OldProgrammer
  • 12,050
  • 4
  • 24
  • 45