• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 52
  • Last Modified:

Oracle equivalent of Microsoft SQL Server "EXECUTE AS"

In Microsoft SQL Server I can execute any query as any other user as long as impersonation rights have previously been granted. To clarify by example.

Consider I have the following connection string
Provider=MSOLEDBSQL;Data Source=SQLSERVER;Initial Catalog=SANDBOX;User ID=TestUser;Password=BADPASS;

Open in new window

Now consider I'm executing the following code
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
	conn.Open();
	using (OleDbCommand cmd = new OleDbCommand(string.Empty, conn))
	{
		cmd.CommandText = "INSERT INTO [USERTEST] ([USERNAME], [TIMESTAMP]) VALUES (CURRENT_USER, GETDATE())";
		cmd.ExecuteNonQuery();
	}
	conn.Close();
}

Open in new window

Simply enough, this will insert a row into the table named USERTEST in the default schema of user TestUser. The values will be ("TestUser", GETDATE()).
Now, if I modify the code like this:
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
	conn.Open();
	using (OleDbCommand cmd = new OleDbCommand(string.Empty, conn))
	{
		cmd.CommandText = $"EXECUTE AS USER = 'Fred'";
		cmd.ExecuteNonQuery();
		cmd.CommandText = "INSERT INTO [USERTEST] ([USERNAME], [TIMESTAMP]) VALUES (CURRENT_USER, GETDATE())";
		cmd.ExecuteNonQuery();
		cmd.CommandText = $"REVERT";
		cmd.ExecuteNonQuery();
	}
	conn.Close();
}

Open in new window

It will insert a row into the table named USERTEST in the default schema of user Fred. The values will be ("Fred", GETDATE()).
Essentially, this behaves exactly the way it would if I had replaced the User ID in the connection string with "Fred" (assuming I had the proper login credentials).

I know Oracle has the following command:
alter session set current_schema = <username>;

Open in new window

However, this only alters the target schema. Executing "SELECT USER FROM DUAL" still returns "TestUser". Is there a way in Oracle to duplicate the behavior that SQL Server has here?
0
Russ Suter
Asked:
Russ Suter
  • 12
  • 6
  • 4
  • +2
1 Solution
 
Eugene ZCommented:
try

 AUTHID CURRENT_USER
0
 
SujithData ArchitectCommented:
If you would like to insert into another schema you can do the following. Provided the schema has granted insert privilege for the source schema.

as Target schema grant the privilege -
grant insert on table to source_schema.

As source schema do the insert
insert into target_schema.table values(....);
0
 
Russ SuterAuthor Commented:
@Eugene Z
That would not work. I can't type AUTHID FRED and have it execute as FRED. In fact, that's not even a valid SQL statement in Oracle.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Russ SuterAuthor Commented:
@Sujith
That doesn't accomplish the goal. I need something that will return "FRED" when I execute the statement "SELECT USER FROM DUAL"
0
 
SujithData ArchitectCommented:
If you are looking for inserting data into another schema; my suggestion will work.

what is the purpose of selecting user from dual? Are you trying to do something else?
0
 
Russ SuterAuthor Commented:
If I'm looking for inserting data into another schema then my solution will work with even less effort. I need to be able to fully impersonate another user in the same way that the Microsoft SQL Server EXECUTE AS statement allows.
0
 
slightwv (䄆 Netminder) Commented:
Check out CURRENT_SCHEMA.

This is an 11g doc but it hasn't changed
https://docs.oracle.com/cd/B28359_01/server.111/b28310/general009.htm#ADMIN02101

Note:  It will not return FRED with select user from dual but think about the security implications if it did?

From a quick Google, I think it is similar to EXECUTE AS.
0
 
Russ SuterAuthor Commented:
@slightwv

I've already been down that road as explained in my original post.

The fundamental difference here is that SQL Server's user model is quite different from Oracle's.

In SQL Server, USER, SCHEMA, and LOGIN are 3 completely independent entities. A SCHEMA can be created without an associated user. A USER can be created without an associated LOGIN. It's the LOGIN that actually authenticates. This allows an application to use a single LOGIN to authenticate but identify as any valid USER when executing code. In Oracle, USER, SCHEMA, and LOGIN are all the same thing.

I'm getting the impression that what I want to do simply isn't possible.
0
 
slightwv (䄆 Netminder) Commented:
Sorry.  Missed that in all the text.
0
 
slightwv (䄆 Netminder) Commented:
See if sys_context('USERENV','CURRENT_USER') is better suited:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SYS_CONTEXT.html#GUID-B9934A5D-D97B-4E51-B01B-80C76A5BD086


CURRENT_USER
 
The name of the database user whose privileges are currently active. This may change during the duration of a database session as Real Application Security sessions are attached or detached, or to reflect the owner of any active definer's rights object. When no definer's rights object is active, CURRENT_USER returns the same value as SESSION_USER. When used directly in the body of a view definition, this returns the user that is executing the cursor that is using the view; it does not respect views used in the cursor as being definer's rights. For enterprise users, returns schema. If a Real Application Security user is currently active, returns user XS$NULL.
 
0
 
Russ SuterAuthor Commented:
Unfortunately I've looked at that also. That only returns a value. It doesn't allow me to alter the context, only see it.
0
 
awking00Commented:
I am always a little leary when asked to "convert" a query from one rdbms to another. The reality is that they are often not "convertible" but most of the time the one rdbms can replicate the process of the other. I'm not sure how you are using the database connectivity but, once the connection is made to the Oracle database, the following [PL/SQL] procedure might accomplish what you intend.
CREATE OR REPLACE PROCEDURE load_test(uname in varchar2) IS
v_sql varchar2(255);
BEGIN
v_sql := 'insert into '||uname||'.usertest (username, timestamp) values('||uname||', sysdate)';
EXECUTE IMMEDIATE v_sql;
COMMIT;
END;
/

Then issue the command
 exec load_test('Fred');
0
 
Russ SuterAuthor Commented:
I've looked at proxy authentication which is too slow and doesn't work in OleDb.
I've looked at the undocumented package dbms_sys_sql.parse_as_user but that has too much overhead and doesn't work in OleDb.
I've looked at CURRENT_SCHEMA but that only gets me halfway there.

I think the final conclusion is that this is just one of those things that's possible on one platform and impossible in the other. I'm going to have to refactor the code on a broader scale now to simulate the behavior.
0
 
Russ SuterAuthor Commented:
@awking00

That's an interesting approach but won't work for me here. There are no stored procedures in the database, nor are they allowed due to the fact that it would require maintaining separate code across multiple RDBMS systems.
0
 
slightwv (䄆 Netminder) Commented:
I'm betting there is something under SYS_CONTEXT that does it.


alter session set current_schema=Fred;

then:
select sys_context('USERENV','CURRENT_SCHEMA') from dual;
0
 
Russ SuterAuthor Commented:
I just tested that. It does not reflect "FRED" at all. Instead, it still returns the username identified in the connection string.
0
 
slightwv (䄆 Netminder) Commented:
It works for me in sqlplus:
SQL> create user fred identified externally;

User created.

SQL> select sys_context('USERENV','CURRENT_SCHEMA') from dual;
SYSTEM

SQL> alter session set current_schema=Fred;

Session altered.

SQL> select sys_context('USERENV','CURRENT_SCHEMA') from dual;
FRED

Open in new window

0
 
Russ SuterAuthor Commented:
Hmmm, I tried it again and got the expected result. I must have done something wrong the first time. This isn't what I want but it seems to be the closest possible solution in Oracle.
0
 
slightwv (䄆 Netminder) Commented:
To be honest, I'm still not sure what problem you are trying to solve.  If you know something is in the Fred schema, why would you need to insert that value into some table?
0
 
Russ SuterAuthor Commented:
It's a long and complicated story. Suffice it to say that I'm trying to retrofit a very old program to do things in a more acceptable manner. The current code is just bad but there's so much of it that a rewrite would be too time consuming so I've been tasked with trying to find a way to improve the process but maintain backward compatibility.
0
 
Eugene ZCommented:
it looks like you have an opportunity to learn some new fpr you Oracle coding  ways

Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)
https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#LNPLS00809

Example 8-13 Specifying Invoker's Rights With a Procedure

CREATE OR REPLACE PROCEDURE create_dept (
   v_deptno NUMBER,
   v_dname  VARCHAR2,
   v_mgr    NUMBER,
   v_loc    NUMBER) 
AUTHID CURRENT_USER AS
BEGIN
   INSERT INTO departments VALUES (v_deptno, v_dname, v_mgr, v_loc);
END;
/
CALL create_dept(44, 'Information Technology', 200, 1700);

Open in new window

0
 
Russ SuterAuthor Commented:
There's nothing there that I don't already know. This question was more advanced than that. Anyway, this question has already been answered.
0
 
Eugene ZCommented:
you cannot  tell that PL/SQL Subprograms are "basic" :).

check this one  dbms_sys_sql

with the example
Using DBMS_SYS_SQL to Execute Statements as Another User
https://blog.pythian.com/using-dbms_sys_sql-to-execute-statements-as-another-user/

also you can try proxy..
0
 
Russ SuterAuthor Commented:
Scroll up and read a little. Those avenues have already been explored. And yes, subprograms are pretty basic.
0
 
Eugene ZCommented:
:) all is basic.

Whatever works for you .


And you are welcome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 12
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now