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?
Grant permissions in oracle from one database to another
oracle
Related Question
- Grant permissions to a user to grant select to specific tables in several schemas in Oracle
- Granting password protected role to another role problem in Oracle
- Grant user error
- Amazon-rds – Oracle AWS RDS supermaster user cant grant select on some tables
- What privileges do I need to change the password of sys in an Oracle Database
- ORA 01031 Insufficient privileges on GRANT SELECT on ALL_CATALOG to a user as system user
Best Answer
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_USERdatabase 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.