2

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.

3 Answers 3

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
    Commented May 17, 2017 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
    Commented Jun 6, 2016 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
    Commented Jun 6, 2016 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
    Commented Jun 6, 2016 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
    Commented Jun 6, 2016 at 17:31
-1

Had the same problem, here's my answers:
In both cases below, I am executing "CREATE TABLE x..." as the user SCOTT
(Execute vSql as vUser)

Option 1, wrapper procedure:

DECLARE
  vUser varchar2(30):='SCOTT';
  vSql varchar2(400):='CREATE TABLE x(c1 VARCHAR2(1),c2 VARCHAR2(1) DEFAULT ''A'')';

  vWrapper varchar2(400);
BEGIN
  vWrapper:='CREATE PROCEDURE '||vUser||'.ExecAsUserWrapper AS BEGIN execute immediate '''||REPLACE(vSql,'''','''''')||'''; END;';
  execute immediate vWrapper;
  execute immediate 'BEGIN '||vUser||'.ExecAsUserWrapper; END;';
  execute immediate 'DROP PROCEDURE '||vUser||'.execAsUserWrapper';
END;
/

Option 2, uses dbms_sys_sql:

DECLARE
  vUser varchar2(30):='SCOTT';
  vSql varchar2(400):='CREATE TABLE x(c1 VARCHAR2(1),c2 VARCHAR2(1) DEFAULT ''A'')';

  vDBMSCursor integer;
  vResult integer;
  vUserInt integer;
BEGIN
  select user_id into vUserInt from all_users where username = vUser;
  vDBMSCursor:=sys.dbms_sys_sql.open_cursor();
  sys.dbms_sys_sql.parse_as_user(vDBMSCursor,vSql,dbms_sql.native,vUserInt);
  vResult:=sys.dbms_sys_sql.execute(vDBMSCursor);
  sys.dbms_sys_sql.close_cursor(vDBMSCursor);
END;

WARNING: Do not grant dbms_sys_sql to a regular user. Not even your admin user account. That package allows you to run any sql as any user.

Both options require the target schema to have permission to run the provided statement.

Your Answer

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

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