Single database many schemas????

[shailesh]

Hello,

We are using Oracle 8.1.6 on NT 4.0.To access single database by many users, I have created main schema where all database objects will get stored. I have created public database link in main schema. Then rest of the users e.g. 'User1','User2' schema has simple updatable view with same name of base table and will have unique user id.
They 'User1 or User2' will 'Create or Replace View as select * from Table@dblink where Userid=1'. So when 'User1' select data from view for him it's table he can see only his data. So this way Altough data is stored at one place, but more than one user can access same table and can see only their data not others.
Since different users are going to run same application of same database structure, I have created this. Is it the correct way or any secured method I can use to get the same result. Please guide me in this.

Thanks & regards
Shailesh

[bensr]

Can't you make just 1 view
Create or Replace View as
select *
from Table@dblink
where Userid = user

USER should filter at record-level

[balaap]

Hi shailesh,
To do this, you need not to create a database link at all.

'User1 or User2' will 'Create or Replace View as select * from Table where Userid=1' will do your requirements. Moreover dblink should be used only when you are accessing a remote database objects. If you access same database objects using link, each access will create a session and one day you will run out of database sessions. Instead of using links, use synonyms. I hope I had given you a correct solution. If you want further details, please let me know.

Regards,
A.P.BALASUBRAMANIAN
balaap@yahoo.com, balaap@uk2.net.