0

I have the following scenario in my Oracle Database (11g) and I am trying to work around grants and access privileges.

Note that the following is just a Minimum Viable Example and it does not cover the complete logic of what my program will do.

What I have

  • MyMainSchema: this schema has several tables in it
  • MyProcs: this schema has only several procedures in it. Every time I create a table in MyMainSchema I (manually) give S I D U in MyProcs for that table.
  • MyPlaceHolder: this schema is empty

I have a package and a procedure in MyProcs, which all it does is to

  • read data from MyMainSchema
  • create a new table in MyPlaceHolder
  • insert data in this newly created table
  • drops this newly created table

To achieve what I want, my solution until now is

  • run as MyProcs user
  • read data from MyMainSchema
  • alter session and switch to MyPlaceHolder user
  • create a new table in MyPlaceHolder
  • issue a grant to give S I D U permissions to MyProcs on the newly created table
  • alter session and switch to MyPlaceHolder user
  • insert data in this newly created table
  • do some processing
  • (at some point) drops this newly created table

I am trying to avoid the user / schema switch and the grant commands.

My question is: Is there a command (or more) that it will run only once and it will result the following:

  • MyProcs will have the ability to create and drop any table new or old in MyPlaceHolder.
  • MyProcs will 'automatically' have S I D U permissions on any table that is created in MyPlaceHolder
2
  • Why not create the tables in MyProc? This can be done by setting the PACKAGE in MyPlaceHolderas AUTHID CURRENT_USER. Commented Jun 12, 2018 at 12:19
  • @MichaelKutz is a requirement of my project to keep this setup and Procedures and Tables in separate schemas. Your proposal will work, however I can not apply it in this specific case.
    – Athafoud
    Commented Jun 12, 2018 at 12:27

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.