CODEWITHSUNDEEP
javasqloracle-databasejdbc
Java Drinker
Java Drinker

Reputation: 3167

In ORACLE DB, determine if another user can run a select query

Is it possible to check whether another ORACLE user will be able to run a particular query? This check would need to be performed by an ADMIN account.

The reason for this is that we have processes that run using the ADMIN account. It's possible for non-admins to submit queries to these processes, so we want to be able to confirm during the execution of the process whether the user would be able to run the query themselves, and if not, end execution.

The other piece to this is that the process are controlled by a Java program, so if there is no way to do this in Oracle, perhaps there is a solution in Java/JDBC?

Java:1.5 Oracle: 10.2+ All the tables involved are part of the ADMIN schema, and access is controlled via SELECT/INSERT/UPDATE/DELETE table GRANTS.

Hope I've been clear enough

Upvotes: 0

Views: 672

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

In SQL Server, the solution is something called execute as login. Given this information, I found that Oracle has the equivalent (here is a StackOverflow reference):

ALTER SESSION SET CURRENT_SCHEMA = schema;

My suggestion is that before running the query, set the schema to the actual user. Then, just let the query fail and return that error back to the calling program.

The Oracle documentation for this is here.

EDIT:

The documentation for Oracle explicitly gives this example:

CONNECT scott ALTER SESSION SET CURRENT_SCHEMA = joe; SELECT * FROM emp;

Because emp is not schema-qualified, the table name is resolved under schema joe. But if scott does not have select privilege on table joe.emp, then scott cannot execute the SELECT statement.

That gives me the impression that it is changing privileges when changing the schema.

Upvotes: 0

GriffeyDog
GriffeyDog

Reputation: 8376

You could try the undocumented Oracle package, DBMS_SYS_SQL, which has a parse() method that takes a user ID as a parameter. Here is a link with some info: http://www.pythian.com/blog/using-dbms_sys_sql-to-execute-statements-as-another-user/

Upvotes: 3

Related Questions