1

I would like to be able to run DML queries (INSERT,UPDATE,DELETE) by executing a procedure on a given schema. Those queries need to change data on different schemas on the same instance. So, basically, I need to run this procedure with all the privileges of a user that it needs to be ran on. I put the

AUTHID CURRENT_USER

clause in the package header as well as

EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA =B')

clause in the body of procedure. This works perfectly fine for non-DML/DDL queries like SELECT, unfortunetely when I run the DML procedure there's an error about insufficient privileges. When I grant privileges to user A (the one which the package is stored on) the queries run with no problems. So clearly the procedure is ran as user A (package user) and not user B (user I want to run DML queries on).

Is there anything I can do to make this happen? I want to keep all the code on user A without manually granting any privileges on all the users.

1

A bit old question but if anybody is interested, there is a way to do it. Take a look at dbms_sys_sql package. It allows you to execute any SQL as any user on the database. It is how APEX does it. But be careful granting execute privileges to this package since it can be used to impersonate anybody on the database and it is very powerful.

1
  • Yes, I found this and proposed as part of our solution, but the DBA's wouldn't go with it, as this package is not supported by Oracle :) – PRHMN May 17 '17 at 6:42
0

This:

EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA =B')

Does not do what you think it does. ;)

That statement does not change who YOU are. It changes your default Schema for purposes of searching/finding an object.

For example, try this:

1) Create a table in User TESTUSERB. Let's call it TEST

2) Login as User TESTUSERA.

3) desc TEST (this should fail, because "TESTUSERA" does not have an object "TEST", and there is no synonym pointing to the table in "TESTUSERB"

4) desc testuserb.test this should work fine.

5) alter session set current_schema=TESTUSERB;

6) desc testuserb.test this should also work.

So that AUTHID CURRENT_USER is telling the package to run using the privs of USERA ... the "Current user" ... the user who called the package.

If you need the DML to run as the OWNER of the package, you need AUTHID DEFINER (the default) ...

So to recap:

There is the OWNER of the Package. There is the UserID that CALLED the package.

When you create a package, by DEFAULT, it will use the privileges of the OWNER of the package. You can change this by using AUTHID CURRENT_USER, in which case, the package now uses the privileges of the user who calls it.

By this quote:

"So clearly the procedure is ran as user A (package user) and not user B (user I want to run DML queries on)."

It sounds like you have the Package compiled in "User B" .. and want the DML to use the privileges of "User B" ... in which case, remove the AUTHID line as well as the ALTER SESSION SET CURRENT_SCHEMA .. and it should run fine.

Note, however, that compiled objects do not see privileges in a ROLE, you need to grant access to underlying objects Directly. (so if you need INSERT on table UserC.XYZ, you need to GRANT INSERT ON UserC.XYZ TO UserB;, and not GRANT myrole TO UserB; .. even if Myrole has that same insert.

4
  • Package is compiled on User A and I want it to run DML queries on User B objects without granting privileges. That requires me to "borrow" User's B privileges for execution of User A package. That didn't work as you said. I thought that by using the AUTHID and ALTER SESSION commands I will call the package with the User B privileges and Inser/Update data on User B objects. If I remove those commands and keep running the package there still will be problems with privleges. So, anyway, is it possible to run User A package so it inserts data on User B table without granting additional privs? – PRHMN Jun 6 '16 at 17:12
  • No: If the package is in User A, and accessing User B's tables, User A needs access to those tables, OR whoever "CALLs" the package in User A initially ... needs access to User B's tables (and then you could use AUTHID). But what you describe would be much much easier if you put the package in User B's schema. Then GRANT EXECUTE ON UserB.my_package TO UserA; This is the way it's "normally" done. The EXECUTE is the ONLY privilege you would need, with no additional AUTHID confusion ;) – Ditto Jun 6 '16 at 17:24
  • Well, I would like to apply this to many users. User A is a "master user" which calls the package as many different users. According to what you say I would have to create the package on all those users and grant the execution privilege to User A which would just call them without messing around with privileges. Weel, that's an option, although I was hoping for much smoother solution. :) Anyway - thank you! – PRHMN Jun 6 '16 at 17:27
  • From what you describe, the most secure option would likely be to have UserA as the Main "gatekeeper" that everyone calls. It then has simple EXEC privs on various other Packages in other schemas - as needed (similar to what I described above). The only way to make it "simpler", would be to give UserA additional grants, however, that may not be seen to be as secure ... ;) But those are pretty much your two options if I understand you correctly ... good luck! – Ditto Jun 6 '16 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.