Limit user in sql plus to a single record in a table

I have one employee table...this table has 5 columns (empname, empgsm, empsal, empaddr, empdep) ...it has 10 records. I've created 10 users equivalent to the empnames colu开发者_如何学编程mn in the table. When a user logs in with his empname aka username & password, he will be able to see only his record from the table.

e.g. Smith is an employee, a user called smith was created. when this user is in session, and types "Select * from Employee_table" he only gets the record that belongs to him, where empname is smith.

How do I do this using privileges?

---

My sugestion is to create view on the table where include where condition based on curent user name some thing like this:

select * from employee t
where sys_context('USERENV','SESSION_USER') = t.empname`enter code here

---

From the tag sqlplus I guess you are using Oracle.

In Oracle you can do this using Fine Grained Access Control aka Virtual Private Database. It works by adding where clauses to any sql statement.

If you have an application based on the schema you can also add a where clause (where username = user) yourself. This is probably less secure but sufficient for many use cases

The Oracle user name of the currently logged on user is available in the variable 'user'.

---

If we are talking about Oracle, the GRANT command will allow users to SELECT a table, so can retrieve any row. If you want to restrict the user access to a table a row level, use advanced features as Oracle Label Security 1 2. This is recommended if you have heavy security needs, but I prefer to use another mechanism to validate users, like LDAP.