Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Jul 2 '13 at 19:18

This question came from our site for system and network administrators.

    
@ justin : i am totally a newbie in oracle. I am much used with MSSQL, thats why i used the term database for db1 and db2. sorry for that. –  user223 Jul 2 '13 at 8:55

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
you are right Justin, but i have tried grant user before i put this query in serverfault. even after providing grant permission test user was not ale to access db1 schema. can you tell me how i can successfully grant permission to user test to access db1 schema. –  user223 Jul 2 '13 at 8:53
    
@user223 - Well, if you're actually talking about schemas and not databases grant select on schema1.table_name to test and then as test select * from schema1.table_name. –  Justin Cave Jul 2 '13 at 8:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.