user security

[el472]

if there is two user A & B have all grant on table
emp.is there any way,so that user A can't select & update any data in emp insert by user B

[tamilselvan]

Store the USERID (B) in one column. And then use this column for the update statement whenever USER A updates the table.

[el472]

i didn't get it.can u explain...

[akkerend]

Add a new column in which you store the user who inserted the column, A or B. Create a view dependent on the user column and grant select on the view to A and B.

something like:

CREATE TABLE scott.emp
( empno number
, ename varchar2(30)
, username varchar2(30)
);

CREATE VIEW scott.emp_view
AS
SELECT empno, ename
FROM scott.emp
WHERE username = USER;

CREATE PUBLIC SYNONYM emp FOR scott.emp_view;

GRANT SELECT ON emp_view to A,B;