how to grant select, different servers, same username/password different schema

Question

• I'm not a DB expert, my boss has retired and it's up to me now. He created a production database, and a test database on another server with same username/password and schema name test_SameAsOtherDB. I'm trying to pull the live data from the production table, into the test DB table, but there are no privileges for me to do so.

I tried by logging into my production DB and "grant select on mytable_name to testDB.mytable_name;" but it gives me the error "ORA-00933: SQL command not properly ended". I've tried all the combinations of user/password/schema that I could think of, but none work.

Everything I've researched says use "GRANT select on tablename to USER" but the user is the same on each DB. Hope I made sense, any help would be appreciated. Thanks, John

Answer 1

You access a database on another server through a DATABASE LINK, not with the syntax to access an object in another schema on the same server. You must create the DBLINK using the "CONNECT TO user IDENTIFIED BY password" syntax. (And the user used to execute "CREATE DATABASE LINK" must have the privilege to do...) Then you will be able to "SELECT ... FROM mytable_name@DBLINK_TESTDB" assuming you named the DBLINK "DBLINK_TESTDB" with the same rights the user used in the "CONNECT TO" statement has in the target DB. (And change that policy having the same passwords in all environment...)