grant privilege to execute a procedure for user in another database

Hi Team,

Iam using oracle 12c database. I have two PDB database pdborcl1 and pdborcl. I want to grant execute privilege for an user sysadmin in pdborcl1 database to execute a procedure p_ins_customerdetails in hr schema in pdborcl database.  I have created DB links, but need a way to grant privileges to execute the procedure.

Any help in this regard is really appreciated.
sam_2012Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I believe you need to grant execute to the user provided in the DB link.

In pdborcl:
grant execute on hr.p_ins_customerdetails  to db_link_username;
0
sam_2012Author Commented:
Error starting at line 1 in command:
grant execute on hr.P_GETEMPS_PRG to database_link1
Error report:
SQL Error: ORA-01917: user or role 'DATABASE_LINK1' does not exist
01917. 00000 -  "user or role '%s' does not exist"
*Cause:    There is not a user or role by that name.
*Action:   Re-specify the name.

But I have user sysadmin in pdborcl1 database.
0
slightwv (䄆 Netminder) Commented:
Not the database link name.  The username you provided when you created the database link.

when you created the link you used the syntax:  connect to some_username.

You need to grant execute to some_username.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sam_2012Author Commented:
Error starting at line 1 in command:
grant execute on hr.P_GETEMPS_PRG  to sysadmin@database_link1
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
0
slightwv (䄆 Netminder) Commented:
Just the username, forget about the link name.

If sysadmin is the user you provided in the create database link syntax then:
grant execute on hr.P_GETEMPS_PRG  to sysadmin;
0
sam_2012Author Commented:
grant execute on hr.P_GETEMPS_PRG  to database_link1@sysadmin;

Error starting at line 1 in command:
grant execute on hr.P_GETEMPS_PRG  to database_link1@sysadmin
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:



I have logged in as system in PDBORCL database , i Have created an db link database_link1(sysadmin user in pdborcl1 db) in PDBORCL system schema.
then tried the above command. Not sure where iam going wrong.
then
0
slightwv (䄆 Netminder) Commented:
Again, no @ sign, no link name.  Just the username.  Nothing more.

Execute this and only this, do not change it:
grant execute on hr.P_GETEMPS_PRG  to sysadmin;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam_2012Author Commented:
CREATE DATABASE LINK "DATABASE_LINK1"
   CONNECT TO "SYSADMIN" IDENTIFIED BY VALUES ':1'
   USING 'pdborcl1';


Error report:
SQL Error: ORA-01917: user or role 'SYSADMIN' does not exist
01917. 00000 -  "user or role '%s' does not exist"
*Cause:    There is not a user or role by that name.
*Action:   Re-specify the name.

getting the below error.
0
sam_2012Author Commented:
grant execute on hr.P_GETEMPS_PRG  to sysadmin;


Error report:
SQL Error: ORA-01917: user or role 'SYSADMIN' does not exist
01917. 00000 -  "user or role '%s' does not exist"
*Cause:    There is not a user or role by that name.
*Action:   Re-specify the name.

getting the below error.
0
slightwv (䄆 Netminder) Commented:
Well, if you can't create the database link, that is a different issue.  You said you already had a database link.

>>SQL Error: ORA-01917: user or role 'SYSADMIN' does not exist

Error seems self explanatory.  That user doesn't exist in the database you are trying to connect to.
0
sam_2012Author Commented:
the user sysadmin exists in pdborcl1 database. able to connect
0
sam_2012Author Commented:
C:\Windows\system32>sqlplus sysadmin/orcl@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 16 21:00:22 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
4
0
slightwv (䄆 Netminder) Commented:
When running sqlplus, are you on the same database server that is running both PDBs?

Connect to pdborcl and execute (change nothing):
CREATE DATABASE LINK DATABASE_LINK1 CONNECT TO SYSADMIN IDENTIFIED BY VALUES orcl USING 'pdborcl1';


I just tried to create a database link using a user that doesn't exist and I didn't get that error.  The link created.  I received a user error when I tried to use it.

Verify the create database link is really generating that error.
0
slightwv (䄆 Netminder) Commented:
Sorry, I had a copy/paste typo.

Connect to pdborcl and execute (change nothing):
CREATE DATABASE LINK DATABASE_LINK1 CONNECT TO SYSADMIN IDENTIFIED BY orcl USING 'pdborcl1';
0
sam_2012Author Commented:
create public database link
  mylink
connect to
  sysadmin
identified by
  orcl
using 'localhost:1521/pdborcl1';


the dblink is created. I have created a table called as emp in the pdborcl1 sysadmin schema.

then I fire an query below query from hr schema.
select * from emp@mylink;
0
sam_2012Author Commented:
it works .
but when i try to give execute privilege it fails


grant execute on P_GETEMPS_PRG to sysadmin;
Error report:
SQL Error: ORA-01917: user or role 'SYSADMIN' does not exist
01917. 00000 -  "user or role '%s' does not exist"
*Cause:    There is not a user or role by that name.
*Action:   Re-specify the name.
0
slightwv (䄆 Netminder) Commented:
mylink is in pdborcl and connecting to pdborcl1 as sysadmin.

What database were you in when you issued that grant?  That grant needs to be executed in pdborcl1.
0
sam_2012Author Commented:
I just run the below comamnd

exec P_GETEMPS_PRG@mylink , it executed the stored procedure.
0
sam_2012Author Commented:
I did not give any execute privilege to sysadmin in pdborcl1 to execute the procedure in Hr schema in pdborcl. No idea how it worked.
0
slightwv (䄆 Netminder) Commented:
First you wanted to:
execute p_ins_customerdetails in hr schema in pdborcl database.

Now you've created a database link from pdborcl to pdborcl1 and executing P_GETEMPS_PRG.

But as long as you have your answer, great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.