Thread: Grant Privileges

I am using username cmsjan and its connection string is ssapl.
I have to give all privilege on a table to a user whose username is paylive and connection string is orion.ssapl01.
Both users are in different databases. How to grant privilege?

Dear krishnaindia2007, you cannot grant privileges dierctly from one database to the other by simply using username and connection string. If you want to give all privileges on a table, to different database, you can try creating a database link as follows: create database link db_link_name connect to user_name identified by "password" using 'connection_string' you can view the database link using : select * from user_db_links; to drop the database link use: drop database link db_link_name after creating a database link you can access the data from other database using : select * from user_name.table_name try this. If you face any problem, kindly revert it back. Have a pleasant time.

Dear krishnaindia2007,

Try this

CREATE DATABASE LINK db_link_krk
CONNECT TO cmsjan IDENTIFIED BY "cmsjan"
USING 'orion.ssapl01'

Provide Password in Double Quotes and Connection String in Single Quotes.

Check your Connection String properly if problem occurs.

Have a pleasant time

Thank You sreekumar_nair_it.

Now the external datalink was created. I tried to access a table using the following

select * from cmsweighment@db_link_xxx

It is showing error ORA - 12154 TNS COULD NOT RESOLVE SERVICE NAME.

For more details following this link

You need to make required change to the TNSNAMES.ORA file to connect to the other database.

Dear Krishna,

Check your TNSNAME file in C:\ORANT\NET80\ADMIN\TNSNAMES.ORA

If you sre unable to find the ORANT folder in C:\, Search for TNSNAMES file.

Now once you get the file, check whether the Connection details that you are specifying in Database Link exist in the file:

The file might contain many connections like this:

testing =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.1.121)(PORT = 1521))
(CONNECT_DATA =
(SID = testing)
)
)

Check whether your cconnection is there in the file or not.

If any query, kindly rever it back.

create database link CMSTEST_DB_LINK connect to PAYLIVE identified by "PAYLIVE" using 'ORION.SSAPL01'

Connection details were already there in tnsnames.ora.

ORION.SSAPL01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 10.15.1.111)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)


Still it is showing error message ORA - 12154

Originally Posted by krishnaindia2007

create database link CMSTEST_DB_LINK connect to PAYLIVE identified by "PAYLIVE" using 'ORION.SSAPL01'

Connection details were already there in tnsnames.ora.

ORION.SSAPL01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 10.15.1.111)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)


Still it is showing error message ORA - 12154

Dear Krishna2007,

Try creating the Database Link from any of your team members System
If its working there then copy the TNSNAME to your System.

Is this the same Database Link or you are creating any other Database Link?

Do let me know whether you are performing it in your system or any other system.

Have a pleasant time

I am performing it in my system only.
ORION.SSAPL01 is our live server connection string.
I am trying to access live server tables from our test server using this database link . Regularly we access both test server and live server data. So both connection string details were already there in TNSNAMES.ORA file. All our team members are using the same copy of TNSNAMES.ORA file.