-
Expert Member
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?
-
Contributing Member
Re: Grant Privileges
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.
-
Expert Member
Re: Grant Privileges
I tried this
create database link db_link_xxx
connect to cmsjan identified by cmsjan using orion.ssapl01;
It is showing error message
ORA - 02010 Missing host connection string.
Last edited by krishnaindia2007; 12-15-2007 at 03:08 AM.
-
Contributing Member
Re: Grant Privileges
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
-
Expert Member
Re: Grant Privileges
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.
-
Expert Member
Re: Grant Privileges
For more details following this link
-
Contributing Member
Re: Grant Privileges
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.
-
Re: Grant Privileges
You need to make required change to the TNSNAMES.ORA file to connect to the other database.
-
Expert Member
Re: Grant Privileges
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules