Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Benjamin.

Asked: September 26, 2016 - 5:53 pm UTC

Last updated: October 16, 2016 - 6:33 am UTC

Version: >11g

Viewed 1000+ times

You Asked

Hello

I wrote a PL/SQL package which allows me to control RMAN out of SQL*Plus using the RMAN syntax.

To implement it I used the dbms_pipe/dbms_scheduler functionality which starts the external rman process on OS level and call the rman binary with the pipe option. In this way I'm able to perform all rman operation out of sql. With this I reached the goal to a have a platform independent backup procedure. I need no shell/powershell scripts anymore.

To use the rman with the pipe function it will create a private pipe. To be able to open the rman process out of the database, on part of the packaged is owned by the sys schema, because only sys can open a private pipe for inter process communication. I would like to switch the package ownership to non sys user. Can you please provide me the grants which are need for the user, that I can create a private pipe?

Thank you very much
Benjamin

and Connor said...

You should only need execute privs, eg

SQL> conn / as sysdba
Connected.

SQL> grant execute on dbms_pipe to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.

SQL> declare
  2    x int;
  3  begin
  4    x := DBMS_PIPE.CREATE_PIPE('mypipe',private=>true);
  5  end;
  6  /

PL/SQL procedure successfully completed.


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Benjamin Buechele, October 14, 2016 - 6:28 pm UTC

Hello Connor

sorry for my delay. This is not exactly what I was meaning.

for example you do the following:

./rman PIPE abc TARGET SYS/oracle@trgt

this will create a private IN/OUT pipe abc.

What privileges are needed to be able to send command and receive messaged as user scott?

Thanks
Ben
Connor McDonald
October 15, 2016 - 1:47 am UTC

I think you'll need to create a privileged proc in SYS, and allow SCOTT to use it, eg

SQL> conn sys/admin as sysdba
Connected.

SQL> create or replace
  2  procedure sys.privileged_pipe( msg varchar2 ) as
  3    status number;
  4  begin
  5    dbms_pipe.pack_message( msg );
  6    status := dbms_pipe.send_message( 'MY_PIPE' );
  7    if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' ); end if;
  8  end;
  9  /

Procedure created.

SQL> grant execute on sys.privileged_pipe to scott;


Creating your own objects in Sys...????

A reader, October 15, 2016 - 2:22 am UTC

Its not a good practice to create objects in sys user.

Tom will give you time out for doing this.

What are other options ?

Benjamin Buechele, October 15, 2016 - 6:49 am UTC

Hello

thats how I do it exactly at the moment. I created a sys package (sys.privileged_pipe), to be able to send and receive messages as user scott for example. But I would like to know what privileges I need to create working package scott.privileged_pipe

Thanks
Benjamin

To Ben

Gh, October 15, 2016 - 10:26 am UTC

You did give the question and the answer
" ...because only sys can open a private pipe for inter process communication."

Also from documentation :

Access to a private pipe is restricted to:

Sessions running under the same userid as the creator of the pipe

Stored subprograms executing in the same userid privilege domain as the pipe creator

Users connected as SYSDBA

Benjamin Buechele, October 15, 2016 - 11:06 am UTC

Hello

thanks for the feedback, so this simple solution would be grant backup database privs to scott start the rman process instead of sys with scott, then it should be possible to have the package in the scott schema? Ok I will try this :) - I was not thinking about this :)

Thanks
Ben


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.