grant privilege to execute a procedure for user in another database

sam_2012
sam_2012 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

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;

Author

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.
Most Valuable Expert 2012
Distinguished Expert 2018

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.
Ensure you’re charging the right price for your IT

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

Author

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"
Most Valuable Expert 2012
Distinguished Expert 2018

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;

Author

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
Most Valuable Expert 2012
Distinguished Expert 2018
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;

Author

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.

Author

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.
Most Valuable Expert 2012
Distinguished Expert 2018

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.

Author

Commented:
the user sysadmin exists in pdborcl1 database. able to connect

Author

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
Most Valuable Expert 2012
Distinguished Expert 2018

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.
Most Valuable Expert 2012
Distinguished Expert 2018

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';

Author

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;

Author

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.
Most Valuable Expert 2012
Distinguished Expert 2018

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.

Author

Commented:
I just run the below comamnd

exec P_GETEMPS_PRG@mylink , it executed the stored procedure.

Author

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.
Most Valuable Expert 2012
Distinguished Expert 2018

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial