Avatar of Swaminathan K
Swaminathan K
Flag for India asked on

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.
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
slightwv (䄆 Netminder)

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;
Swaminathan K

ASKER
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.
slightwv (䄆 Netminder)

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Swaminathan K

ASKER
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"
slightwv (䄆 Netminder)

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;
Swaminathan K

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Swaminathan K

ASKER
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.
Swaminathan K

ASKER
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.
slightwv (䄆 Netminder)

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Swaminathan K

ASKER
the user sysadmin exists in pdborcl1 database. able to connect
Swaminathan K

ASKER
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
slightwv (䄆 Netminder)

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

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

ASKER
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;
Swaminathan K

ASKER
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
slightwv (䄆 Netminder)

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.
Swaminathan K

ASKER
I just run the below comamnd

exec P_GETEMPS_PRG@mylink , it executed the stored procedure.
Swaminathan K

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

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.