Map user in oracle 10g

oracle-10g

here is the scenario:

There are two schema created in oracle 10g, say db 1 and db 2. db1 can be accessed by username : sys with password and db2 can be accessed by username : test with password.

How is it possible to access db1 using username : test with password?
In MSSQL there is an option for user mapping to different database. is there any such options in oracle 10g to do the same?

Best Answer

There is no similar option assuming that "db1" and "db2" are actually Oracle databases.

What SQL Server calls a "database" is roughly equivalent to what Oracle calls a "schema". In Oracle, you would generally only run one database on a server though that database may have many different schemas to support multiple applications. Each Oracle database has a completely separate set of users.

You could create a database link in db2 that connects to db1 and allow the test user to use that database link to access objects in schemas on the db1 database. Going further, you could use that database link to replicate data to db2 from db1 so that you could query it locally. That's as close as you'll get to exposing data in one database to a user in another database.

Of course, from your description, it is entirely plausible that you merely have two schemas in the same database in which case you can freely grant access on objects in any schema to any user using the appropriate GRANT statement.

Related Question