0

I have two databases, employeedb and contractordb. The employeedb has a user called employeeuser and contractordb has a user called contractoruser. (These are the users I log in with in Oracle SQL Developer). When I am logged into contractordb as contractoruser and I issue the command grant select on table_name to employeeuser; it fails saying 'ORA-01917: user or role 'employeeuser' does not exist'. I get the same thing if I log in as SYS. If I use the GUI and go to "Privileges->Grant" I also don't see the user there. How can I allow employeeuser to select from one of contractoruser's tables?

  • 1
    Are they actually two different instances on the same machine? Or two schemas on the same machine, or two instances on different machines? – kevinsky Oct 17 '14 at 17:26
1

If you actually have different databases (and it sounds like you do), you can't grant permissions for users in one database to access data in another database. You'd need to create a database link in the EmployeeDB database that connects to the ContractorDB database. That database link can either use a fixed username and password in the ContractorDB database (i.e. create an EmployeeUserRemote user in ContractorDB with a password that doesn't change that can be hard-coded into the database link definition) or that database link can be a CURRENT_USER database link in which case the username and password would need to match in the two databases for every user that wants to use the database link. Queries that use the database link log in to the remote database (ContractorDB) as the specified user and have the privileges of that user in the remote database.

  • Thanks Justin. I will create a database link and use a CURRENT_USER link. I will use "CREATE DATABASE LINK remote.us.oracle.com CONNECT TO CURRENT_USER USING 'remote';" In the USING remote part, in my example above, would 'remote' be 'contractordb'? – KyleM Oct 17 '14 at 17:49
  • @KyleM - It would be whatever the TNS alias on the EmployeeDB database for the ContractorDB database is. That's generally going to be the same as the name of the database but that's just a naming convention-- your TNS alias might be different. – Justin Cave Oct 17 '14 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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