Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, andrew.

Asked: August 26, 2002 - 2:46 pm UTC

Answered by: Tom Kyte - Last updated: July 06, 2020 - 5:38 am UTC

Category: Database - Version: 9.0.0

Viewed 100K+ times! This question is

You Asked

I used alter "session set current_schema = xx" to change schema in a block of code. It is not working and I suspect that the schema has not been changed for some reason.

How can I query the system to return the current schema I am in? Is there a parameter or v$sysstat variable or something? I want to display that value after the above statement to confirm that that schema has indeed been changed.

Thanks.

and we said...

It is working, give me an example.

select sys_context( 'userenv', 'current_schema' ) from dual;


ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 execute immediate 'alter session set current_schema=scott';
3 for x in ( select sys_context('userenv','current_schema') x from dual )
4 loop
5 dbms_output.put_line( x.x );
6 end loop;
7
8
9 execute immediate 'alter session set current_schema=ops$tkyte';
10 for x in ( select sys_context('userenv','current_schema') x from dual )
11 loop
12 dbms_output.put_line( x.x );
13 end loop;
14 end;
15 /
SCOTT
OPS$TKYTE

PL/SQL procedure successfully completed.

Now, if your "block of code" is actually a stored procedure and it is further a DEFINERS RIGHTS procedure (the default kind) then expect this (logged in as ops$tkyte by the way):

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p
2 as
3 begin
4 execute immediate 'alter session set current_schema=scott';
5 for x in ( select sys_context('userenv','current_schema') x from dual )
6 loop
7 dbms_output.put_line( x.x );
8 end loop;
9
10
11 execute immediate 'alter session set current_schema=ops$tkyte';
12 for x in ( select sys_context('userenv','current_schema') x from dual )
13 loop
14 dbms_output.put_line( x.x );
15 end loop;
16 end;
17 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec p
OPS$TKYTE
OPS$TKYTE

PL/SQL procedure successfully completed.

you cannot change your identity in a definers rights procedure, it is fixed AT COMPILE time by definition (if you have my book "expert one on one", read the chapter on invoker vs definer rights).

Now, try this:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p
2 AUTHID CURRENT_USER

3 as
4 begin
5 execute immediate 'alter session set current_schema=scott';
6 for x in ( select sys_context('userenv','current_schema') x from dual )
7 loop
8 dbms_output.put_line( x.x );
9 end loop;
10
11
12 execute immediate 'alter session set current_schema=ops$tkyte';
13 for x in ( select sys_context('userenv','current_schema') x from dual )
14 loop
15 dbms_output.put_line( x.x );
16 end loop;
17 end;
18 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec p
SCOTT
OPS$TKYTE

PL/SQL procedure successfully completed.

but make sure you understand the ramifications of doing that -- it makes procedures act more like anonymous blocks (and limits the ability of the shared pool to allow you to scale!)



and you rated our response

  (27 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Another reason

August 26, 2002 - 6:19 pm UTC

Reviewer: Rui Brito from Portugal

Hi
You need to give grants to all objects who want access by the oher user. Set current_schema is only a part of the solution, grants is the other part.

Rui Brito

A related question

March 27, 2003 - 5:27 pm UTC

Reviewer: John from New Jersey, USA

We have 10 database schemas for 10 Weblogic applications (will have more in the future). Each schema has a set of stored procedures and tables. If we don't want to repeat the stored procedures in each schema (for maintenance reasnon), is there a way to use the same set of stored procedures to access tables under different schema?

For instance, I create stored procedures in schema SP, and for user1 to user10, create private synonyms for each of them to access these stored procedures (of course grant execute privilege first), and then add the following code in each stored procedure so that the stored procedures will access data from tables under different schema depending on who execute the stored procedure (e.g. user1 through user10). I got some errors with the following simple code, and haven't got a chance to work on it further. But would like to hear your opinion on this.

CREATE OR REPLACE PROCEDURE MYtest
IS
v_schema SYS.user_users.username%TYPE;
BEGIN
SELECT username
INTO v_schema
FROM user_users;

EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = v_schema';
DBMS_OUTPUT.put_line ('v_schema = ' || v_schema);
END MYtest;
/


Tom Kyte

Followup  

March 27, 2003 - 7:14 pm UTC

wrong approach. use fine grained access control:

</code> http://asktom.oracle.com/~tkyte/article2/index.html <code>

to implement VPD for your database. one schema. one set of tables. one set of procedures. Every group of users thinks they have their own schema, their own database...



Thanks for your prompt response.

March 27, 2003 - 11:10 pm UTC

Reviewer: John from New Jersey, USA

The requirement is that physical data must be separated from each other. So fine grained access control will not allow me to achieve the goal. Is there a way in Oracle to access tables under different schema through one set of stored procedures?

Tom Kyte

Followup  

March 28, 2003 - 7:06 am UTC

the requirement is silly -- revisit it. why or how does "physical" separation

a) help you
b) do anything for you


it only seems to be impeding you.

There is -- but it is hugely inefficient. Pitty that common sense is prevented in blind pursuit of "some phantom requirement"

I would enjoy hearing the business justification for such a restriction. Many times "requirements" are the result of lack of knowledge and once knowledge is let out of the box -- requirements disappear.

I have a feeling someone did not understand what was possible, came up with a design predicated on physical separation (which could be achieved via partitioning!) and made it a rule. Perhaps once shown what is possible the "rule" disappears.


If you have my book "Expert one on one Oracle" -- read the chapter on Invoker rights routines. But it will be

o a management nightmare
o really ugly
o not very performant
o the wrong way.

Thanks, Tom

March 28, 2003 - 9:11 am UTC

Reviewer: John from New Jersey, USA

Let me try to explain this to you. Our company provides prescription drug related consulting services to pharmaceutical companies. Each of our client firmly requires that their data to be separated from others. Our software was designed based on that requirement. That is when we launch our new product, each of our clients will have their own database instance.

The question I'm asking is for our pilot functional demo. We will allow our clients to login to our system via the web based application, manipulate data, examine the output, etc. Instead of creating a separate database instance for each of them, I decided to create a separate schema for each of them. Since we want each client to have their own play environment, and our software is designed to handle one client at a time, we have to have a separate set of tables for each client. The natural way would be to have a separate set of stored procedures as well. It would be nice if we can use one set of stored procedures to access data from different schema, so that it's easier for us to debugging, update stored procedures.

In your first reply, you said my original approach is wrong. Could you explain why it is so? Is it not doable or not perform well?


Tom Kyte

Followup  

March 28, 2003 - 9:27 am UTC

Ok -- then FGAC is *exactly* what you are looking for i still think... Since the "requirement" isn't a requirement at all.

Charge these guys lots -- running 100 instances costs you many times more then running 1.


Anyway -- I would go with the stored procedures in each schema approach. Reasoning:

a) this is a demo
b) using invoker rights routines, which would permit this, would terribly complicate your life
c) for a demo....

In this case, the simple "just copy it over and over" solution is the best solution. It would add much time to your debug stage to provide Invoker right support for a demo environment.

I would myself find it easier to augment each table with a CUSTOMER_NAME, default the column to the USER, and use FGAC to restrict what each user sees.



Thanks, Tom.

March 28, 2003 - 11:52 am UTC

Reviewer: John from New Jersey, USA

I am on your side. The original idea was not mine any way. My plan was to copy it over and over.

The original design was to run one database server for each client! I have since suggested to them they can run multiple instances on the same server.

I don't even want to get into the debate of either using one database for one client or one database for multiple clients at this point. But thank you again for your help.


Tom, I have to ask you again on this.

April 01, 2003 - 9:52 am UTC

Reviewer: John from New Jersey, USA

My question was how to use one set of stored procedures to access data from different user schema, and the idea was to use " SESSION SET CURRENT_SCHEMA" command at the beginning of each stored procedure so that the session is dynamically set each time when a stored procedure is called. Something like this:

SELECT username
INTO v_schema
FROM user_users;

EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = v_schema';

And you said this is a wrong approach. Can you explain a little bit why. Is it not going to work (I haven't been able to make it work) or is it not a good practice? I am trying to explain this to my non-Oracle, non-database colleagues. Thanks in advance.


Tom Kyte

Followup  

April 01, 2003 - 4:35 pm UTC

beyond the fact that it doesn't work?

well, it "could" work but it would require authid current_user (invoker rights routines) which requires a totally different development mindset and set of testing.  <b>what I mean is -- it adds a ton of complexity to your code that is not relevant for anything beyond a demo - which would be a bad idea</b>.

I would never change my production code just to make a tiny little throw away demo area "cleaner"


If you have my book "Expert one on one Oracle" -- you find out why this is bad (and when it is good -- it is not 100% evil).  

So, it can be done but 

a) it complicates your development unnecessarily
b) you get very very very little benefit from it in your case
c) it would not be a good practice.




ops$tkyte@ORA920> drop user a cascade;

User dropped.

ops$tkyte@ORA920> drop user b cascade;

User dropped.

ops$tkyte@ORA920> drop table t;

Table dropped.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create user a identified by a;

User created.

ops$tkyte@ORA920> create user b identified by b;

User created.

ops$tkyte@ORA920> grant resource to a;

Grant succeeded.

ops$tkyte@ORA920> grant resource to b;

Grant succeeded.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create table a.t as select 'table owned by a' data from dual;

Table created.

ops$tkyte@ORA920> create table b.t as select 'table owned by b' data from dual;

Table created.

ops$tkyte@ORA920> create table t as select 'table owned by ' || user data from dual;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace procedure definer_rights( p_user in varchar2 )
  2  as
  3  begin
  4          execute immediate 'alter session set current_schema = ' || p_user;
  5
  6          for x in ( select * from t )
  7          loop
  8                  dbms_output.put_line( p_user || ': ' || x.data );
  9          end loop;
 10  end;
 11  /

Procedure created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace procedure invoker_rights( p_user in varchar2 )
  2  AUTHID CURRENT_USER
  3  as
  4  begin
  5          execute immediate 'alter session set current_schema = ' || p_user;
  6
  7          for x in ( select * from t )
  8          loop
  9                  dbms_output.put_line( p_user || ': ' || x.data );
 10          end loop;
 11  end;
 12  /

Procedure created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec ops$tkyte.definer_rights( 'A' );
A: table owned by OPS$TKYTE

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec ops$tkyte.definer_rights( 'B' );
B: table owned by OPS$TKYTE

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec ops$tkyte.definer_rights( user );
OPS$TKYTE: table owned by OPS$TKYTE

PL/SQL procedure successfully completed.

<b>in a definer rights procedure -- the SQL is 100% bound to the underlying objects at compile time -- with no hope of changing what they are bound to, invoker rights are different:</b>


ops$tkyte@ORA920>
ops$tkyte@ORA920> exec ops$tkyte.invoker_rights( 'A' );
A: table owned by a

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec ops$tkyte.invoker_rights( 'B' );
B: table owned by b

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec ops$tkyte.invoker_rights( user );
OPS$TKYTE: table owned by OPS$TKYTE

PL/SQL procedure successfully completed.



<b>but now you have the "grant from heck" problem cropping up</b> -- you need to make sure that the person RUNNING THE PROCEDURE has privs on the underlying objects directly -- no longer is "execute on p" good enough -- they need "execute on p, insert on t1, select on t2, update on t3 and so on" -  UUUUGGGGGLLLLY and unnecessary. 

Thank you for your clear illustration.

April 02, 2003 - 3:50 pm UTC

Reviewer: John from New Jersey, USA

I'm sorry I used the phrase you hate most - it doesn't work. It didn't work for some reasons, either I didn't do it properly, or it's a dead end. I posted my problem here anyway, just want to hear from you if I'm heading the right direction.

I have twisted your code a bit.

Login as JW

evig01:jw> create or replace procedure invoker_rights( p_user in varchar2
2 )
3 AUTHID CURRENT_USER
4 as
5 begin
6 for x in ( select * from t )
7 loop
8 dbms_output.put_line( p_user || ': ' || x.data );
9 end loop;
10 end;
11 /

Procedure created.

evig01:jw> grant execute on jw.invoker_rights to public;

Grant succeeded.

evig01:jw> grant connect to a;

Grant succeeded.

evig01:jw> grant connect to b;

Grant succeeded.

Login as A:

evig01:a> exec jw.invoker_rights( user );
A: table owned by a

PL/SQL procedure successfully completed.

Login as B:

evig01:b> exec jw.invoker_rights( user );
B: table owned by b

PL/SQL procedure successfully completed.

This is exactly what I need. The procedure are created under "JW". When user A execute the procedure, it access data from schema A, whereas when user B execute the procedure, it access data from schema B.

I have two more questions:

1) What's the difference of "AUTHID CURRENT_USER" and "execute immediate 'alter session set current_schema = ' || p_user;"? You've used both of them in your procedure.

2) When you call your procedure, you pass "user" as the parameter? It looks like a built in variable, but when I do "print user", I got:

evig01:jw> print user
SP2-0552: Bind variable "USER" not declared.

But I can do:

evig01:jw> select user from dual;

USER
------------------------------
JW

1 row selected.

Could you explain what is "user" here?


Tom Kyte

Followup  

April 02, 2003 - 4:41 pm UTC

1) well, they are apples and oranges...

authid current_user -- that changes the way stored procedures work all together.  The procedure runs with the privileges of the current invoker (hence "invoker rights").  

set current_schema -- that simply changes the default schema name used to resolve objects when they need to be resolved.  eg: 

SQL> select * from emp;

will use the current_schema to figure out "schema.emp".  In a definer rights procedure, the current schema is MEANINGLESS as the objects are all permanently bound to a single object at compile time.  In an invoker rights routine -- they are used (as shown above).

2) user is a function.  it returns a string, it is the name of the user logged into the database. 

Thanks Tom. They were well explained.

April 03, 2003 - 10:15 am UTC

Reviewer: John from New Jersey, USA


What is the way out to avoid creating synonym

May 07, 2003 - 11:26 am UTC

Reviewer: Riyaz H from Chennai, India

Hi Tom, I have a question for you.

MY INTENTION: Avoid Creating SYNONYMS (because it leads to lot of confusion & difficult to maintain as you also stressed in many places)

I tried out the following
--------------------------
1. User 'a' has table t1
2. User 'b' has table t2
3. t2 rights granted to user a. (from user b)
4. Currently connect in user a
5. I am creating a procedure
create or replace procedure p_1 authid current_user
as
n1,n2 number;
begin
alter session set current_schema=a;
select count(1) into n1 from t1;
alter session set current_schema=b;
select count(1) into n1 from t2;
end;

It is giving error, not able to get the table t2.

6. So I tried different method. Written 2 different procedure
alter session set current_schema=a;

create or replace procedure p_1a authid current_user
as
n1,n2 number;
begin
select count(1) into n1 from t1;
end;

alter session set current_schema=b;

create or replace procedure p_1a authid current_user
as
n1,n2 number;
begin
select count(1) into n1 from t2;
end;

-- Got created
7. Now when tried to execute
alter session set current_schema=a;
execute p_1;
alter session set current_schema=b;
--execute permission should be there
execute p_2;

8. But when try to call create a procedure again gave error, not able to get p_2. What is the reason. (I have given execute permission already, and also changing the schema)

Create or replace procedure p_3 is
begin
execute immediate 'alter session set current_schema=a';
p_1;
execute immediate 'alter session set current_schema=b';
p_2;
end;

MY OBJECTIVE IN NUTSHELL:
------------------------
1. I need to write a SINGLE PROCEDURE
2. Inside that, tables of both the users ( a & b) will be accessed. (sepeartely)
3. Grant permission will be given
4. I need to switch the user using 'alter sesion' command
5. I don't want to use synonyms.

I want to write a procedure similar to below: (a & b are schemas). This is basically I am doing TO AVOID CREATING SYNONYMS

create or replace procedure p_1 authid current_user
as
n1,n2 number;
begin
alter session set current_schema=a;
select count(1) into n1 from t1;
alter session set current_schema=b;
select count(1) into n1 from t2;
end;

WHAT IS THE WAY OUT?

Thanks in advance.

Tom Kyte

Followup  

May 07, 2003 - 1:42 pm UTC

current schema is ALWAYS the owner (definer) of the procedure in a definers rights routine (as you have coded)

i do not recommend or suggest invokers rights routines.

here I would either

a) create a private synonym
b) create a view <<<=== my personal choice

from A to B.


A view is what you are looking for.

August 28, 2003 - 3:39 pm UTC

Reviewer: A reader


Execute immediate for ddl

August 29, 2003 - 3:00 pm UTC

Reviewer: A.Titievsky from Louisville,KY

Drop works,create - does not ?
----------------
(HUMTEST) /home/axt0830> sqlplus medrec                                                         
                                                                                                
SQL*Plus: Release 9.2.0.3.0 - Production on Tue Aug 26 08:56:27 2003                            
                                                                                                
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.                             
                                                                                                
Enter password:                                                                                 
                                                                                                
Connected to:                                                                                   
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production                                
With the Partitioning, OLAP and Oracle Data Mining options                                      
JServer Release 9.2.0.3.0 - Production                                                          
                                                                                                
SQL> begin                                                                                      
  2  execute immediate 'create table t2(n char)';                                               
  3  end;                                                                                       
  4  /                                                                                          
                                                                                                
PL/SQL procedure successfully completed.                                                        
                                                                                                
SQL> drop table t2;                                                                             
                                                                                                
Table dropped.                                                                                  
                                                                                                
SQL>  create or replace procedure prc                                                            
  2  as                                                                                         
  3 begin                                                                                      
 4  execute immediate 'create table t2(n char)';                                               
  5  end;                                                                                       
   6  /                                                                                          
                                                                                                
Procedure created.                                                                              
                                                                                                
SQL> set serverout on               
SQL> exec prc                       
BEGIN prc; END;                     
                                    
*                                   
ERROR at line 1:                    
ORA-01031: insufficient privileges  
ORA-06512: at "MEDREC.PRC", line 4  
ORA-06512: at line 1

SQL> create or replace procedure prc as    
  2  begin                              
  3  execute immediate 'drop table t2'; 
  4  end;                               
5 /

Procedure created.

SQL> create table t2(note char);        
                                        
Table created.                          
                                        
SQL> exec prc;                          
                                        
PL/SQL procedure successfully completed.
                                        
SQL> select * from t2;                  
select * from t2                        
              *                         
ERROR at line 1:                        
ORA-00942: table or view does not exist
 

Tom Kyte

Followup  

August 29, 2003 - 3:05 pm UTC

you need a priv to create
you need only to own to drop

you need privs granted to you in order to do it in a procedure, see
</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

FGAC

August 30, 2003 - 3:48 am UTC

Reviewer: Matt from Australia

I was looking at your comments above regardng placing multiple sets of application data into one instance and using FGAC to ensure that each set of application users only get to see data their own application data. That is, there are two copies of the same application that rely on the same data structures.

As usual I am just checking my understanding. To implement this scheme I understand that:

1) I would need to store a mapping of sets of users to applications
2) I would need to add a new column to tables to record the source application for the data
3) I would need to create a security policy on each table to check the value if this new column and generate the appropriate predicate
4) To manage the data (and performance) I could list partition each table on the "application id" so that queries from a particular application prune the unwanted data, so that this data is not considered in the query. I would also need local indexes.

Is this correct?

Thanks in advance.

Tom Kyte

Followup  

August 30, 2003 - 10:50 am UTC

yup, that is a valid implementation at a high level.

one schema for multiple users

October 20, 2003 - 11:27 pm UTC

Reviewer: wasey from USA

I would like to implement a a single schema in which different users can create the objects . The owner of the objects should grant the select privileges to other users to see its objects otherwise the owners of the objects should see there own objects.

How do I impelemnt it? any ideas are really appreciated

Tom Kyte

Followup  

October 21, 2003 - 7:11 am UTC

you don't. it defeats the purpose of a "single schema". a schema is the way to control these things.

by your very very definition (problem definition) you in fact do NOT want a single schema.

one schema for multiple users

October 22, 2003 - 11:25 pm UTC

Reviewer: wasey from USA

What I really meant to do is to let all the users create
there tables through an application in a schema, and the owners should have access to there own tables only through an aplication no other tools. The owner can grant select priveleges to other others to have them view the tables.
This way I can have a single place holder for all the tables
belonging to different users.

I really appreciate the response

Tom Kyte

Followup  

October 23, 2003 - 8:05 am UTC



you are doing it "wrong". i hear you saying what you "want", but it "doesn't work that way".

once you put a table into a schema -- it belongs to that schema, not to the guy who created it. the "owner" is the schema, the "owner" is NOT the guy who created it. the guy who created it would not be able to grant on it, it would have no connection back to them.


rethink this approach. see how the software actually works and use it that way.

how to find out the databse the user belongs

December 26, 2003 - 5:28 am UTC

Reviewer: p.s.vinodh from India, chennai

hi tom,
i would like to know, how to find out the database name for particular user as i logged in . and i dont' have permission to see the v$database table.

can you tell me how to find out how to trace that.. if my system has more than one database.

thanks in advance.



Tom Kyte

Followup  

December 26, 2003 - 10:10 am UTC

ops$tkyte@ORA9IR2> show parameter db_name
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      ora9ir2
ops$tkyte@ORA9IR2> select name from v$database;
 
NAME
------------------------------
ORA9IR2
<b>
ops$tkyte@ORA9IR2> variable x number
ops$tkyte@ORA9IR2> variable y varchar2(25)
ops$tkyte@ORA9IR2> exec :x := dbms_utility.get_parameter_value( 'db_name', :x, :y );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> print y
 
Y
-----------------------------------------------------------------------------------------------------------------------------------
ora9ir2
</b>

that last method should work for all since dbms_utility has execute to public by default. 

thanks

December 29, 2003 - 2:10 am UTC

Reviewer: P.S.Vinodh from India, chennai


Invokers right

June 03, 2004 - 10:34 am UTC

Reviewer: sam from IL usa

Tom,

Just want to double check my approch.
WE have 50 companies data. The approch before I came here was to create 50 diff schema for each company. Each schema has stored procedures some are common to all & some are specific to each company.

The question is can I combine all common proceduers in one schema with invoker's right ??
That will make maintenance much simpler since if we need to change one of the common procedures we can change it in only one place & compile it in one place.
BTW The application users are logged in as each company schema owner so that have access to their tables any way.

IS this good approch?

Sam



Tom Kyte

Followup  

June 03, 2004 - 1:48 pm UTC

you combine into a single schema with either

a) views that let a user see what they are supposed to see
b) FGAC that does the same without introducing views

</code> http://asktom.oracle.com/~tkyte/article2/index.html <code>

that will let us be infinitely more efficient -- rather than having 50+ copies of each sql statment in the shared pool -- just one.

1/50th the ram.
1/50th the work.

Release control

July 25, 2004 - 10:45 am UTC

Reviewer: A reader

Our security/audit folks want all Oracle changes (DDL and adhoc or bulk DML that the apps are not designed to do) to go thru a central 'release control' procedure via SQL scripts.

We have dozens of application schemas. Instead of managing all these passwords I was thinking that each SQL script could have as its first line a /* SCHEMA_NAME */

The 'release' shell script would run from a SYSDBA account, extract out this schema name, do a 'alter session set current_schema=...' and run the SQL script

Questions
1. What do you think of this approach?

2. If a developer inadvertently or maliciously prefixes a schema.object in the script, it will operate on that schema and not on my session's 'current_schema'. How can I prevent this?

Thanks

Followup?

July 30, 2004 - 7:59 pm UTC

Reviewer: A reader

Could you please followup on my query above?

Another thought is to create a flat file like the /etc/passwd file in Unix containing username/encryped passwords from dba_users and temporarily change the password to a known password, connect as that user, run the script, and change it back using the identified by values 'encrypted'

Your comments? Thanks

Tom Kyte

Followup  

July 31, 2004 - 10:50 am UTC

I've said before -- i don't see all of these, i see most of them.

but anyway. alter session set current_schema=foo does nothing more than change the default schema name used in SQL (when none is supplied)

therefore, this would be a security hole the size of a space shuttle.

Why? this super user would have to have the sum of all privs of all accounts PLUS (it would need create any table for example, whereas the individual schemas would need just create table).

So, schema1 -- knowing it'll have the power of schema2, schema3 and so on -- can do things it would otherwise NOT be permitted to do.

I would be 100% against such an approach.


Why would you store the hashed passwords form dba_users? You can just have your super user read that out (save it), alter the user to a known password, connect as them and put it right back -- in fact:

</code> http://asktom.oracle.com/Misc/su.html <code>

demonstrates how to do that. no flat files.

Thanks!

August 02, 2004 - 11:19 pm UTC

Reviewer: A reader


A thought

September 22, 2006 - 4:57 pm UTC

Reviewer: Dave from Calgary, AB

Tom,
Is it possible to have user 'A' find the identity of user 'B' if 'B' owns a package or procedure that 'A' is using?

Tom Kyte

Followup  

September 24, 2006 - 1:08 pm UTC

give me a bit more context here. define things like "identity" for example, not really sure what you mean.

if you mean "can A fully qualify the schema object X they run, getting back the actual schema of the owner of X"

if so, see
</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9452 <code>

Late reply

July 21, 2011 - 2:11 am UTC

Reviewer: Alan Baldo from Sao Paulo, Brazil

Part I - Stored Procedures Schema

1) Create a stored procedure schema owner. Compile all the procedures on it.
2) Create a role and grant execute on all procedures to it.
3) Create a public synonym for every procedure.

Part II - App Data Schemas

1) Create one schema for every app.
2) Create the tables on each one.

Part III - Users

1) Define how you are going to map the users to the correct App Data Schema. A "public" table would do it.
2) Create an after logon trigger: do the " session current_schema" on it.
3) Grant the Stored Procedures Role to the real users.


Now you are good to go. You will have:

1) A common stored procedure set for all applications.
2) A relational table mapping users to applications.
3) One separate schema for each application.

My 2 cents anyway...
Tom Kyte

Followup  

July 22, 2011 - 1:45 pm UTC

part I:

1) disagree, you might want to have MULTIPLE schema's for code.

2) create ROLES (plural), not a single role, many roles - to represent the different levels of access your application has.

3) never create public synonyms. They cause a performance hit during parse time. The are a security issue (I wish we didn't create public synonyms - it would be MUCH better if people referenced SYS.dbms_sql, not just dbms_sql. Far too easy for someone to drop in a trojan bit of code and replace in your scope the meaning of dbms_sql and intercept your inputs and modify them). It makes maintaining the code harder (implicit things always do, BE EXPLICIT). It prevents consolidation of multiple applications into a single database.

do NOT use public synonyms!


part ii:

1) create AT LEAST one schema for every app, maybe more, depending on security requirements

2) I don't know what that meant...

part iii:

1) users should not map to a data schema, they should maybe - if anything - map to a stored procedure schema.

2) maybe...

3) yes, the right role, at the right level only. Maybe using secure application roles if you are using a middle tier with a common username/password...


July 24, 2011 - 12:15 am UTC

Reviewer: Alan Baldo from Sao Paulo, Brazil

First of all, in my "defense" I would like to point the original needs of this thread:

"We have 10 database schemas for 10 Weblogic applications (will have more in the future). Each
schema has a set of stored procedures and tables. If we don't want to repeat the stored procedures
in each schema (for maintenance reasnon), is there a way to use the same set of stored procedures
to access tables under different schema?"

That is why I've suggested the approach.

Sometimes making things explict will cause you to hard code the schema name in you application code.

The number of roles, schemas, and how to grant the rigths is of course a task to the app designer.
I do not expect to solve every aspect with 9 points on a list, just to point a way to achieve his needs.

Anyway, I appreciate the follow up.

Multi Schema Access

November 26, 2013 - 2:07 pm UTC

Reviewer: A reader from Delhi India

Hi tom,

I have multiple schemas (10). All has same tables. I would like to create a common package for all to access their table. how is approach of using invoker right. by doing Alter Session....and accessing the related tables mainly using Ref cursors.

create or replace package my_pkg as
function get_dealer_data(p_id number,p_schema varchar2) return ref cursor;
end;
create body my_pkg as
function (p_id number,p_schema varchar2) as
AUTH_ID current_user;

v_cur refcursor
begin
execute immidiate 'alter session set current_user='||p_schema;

open v_cur for select * From mytable where id=p_id;
return v_cur
end;

end;

Will it limit the use of SGA. If yes then what is the best approach.

regards,

vikas sharma

Fetch the data from different schema

June 30, 2020 - 9:58 am UTC

Reviewer: Mohit Tayal from India

Hi Team,

I have one db and having around 150 schema users for each application. Each application schema has one table name is common eg: abc. I need to fetch the data from each schema table by one go.

We need to minimize the load on db as well.

Is any best way for this.
Connor McDonald

Followup  

July 03, 2020 - 12:45 am UTC

The only way to fetch from 100 tables is to list 100 tables, ie

select * from schema1.table
union all
select * from schema2.table
union all
select * from schema3.table
...


Make sure you use "union all" not "union" otherwise we'll remove any duplicate data.

V$SESSION to find the current schema

July 04, 2020 - 2:46 pm UTC

Reviewer: David D.

Hello,
I answer the question posted 18 years ago because I did not found my answer here :-)

"I used alter "session set current_schema = xx" to change schema in a block of code. It is not working and I suspect that the schema has not been changed for some reason.

How can I query the system to return the current schema I am in? "

You can query SCHEMANAME from V$SESSION.

I use two users : HR and HR02.
[oracle@vbgeneric ~]$ sqlplus HR
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show user
USER is "HR"

SQL> select schemaname from v$session where sid = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
SCHEMANAME
------------
HR

SQL> alter session set current_schema=HR02;
Session altered.

SQL> select schemaname from v$session where sid = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
SCHEMANAME
------------
HR02

SQL> show user
USER is "HR"


Connor McDonald

Followup  

July 06, 2020 - 5:38 am UTC

Nice stuff.

Correction

July 04, 2020 - 2:50 pm UTC

Reviewer: David D.


In addition of my previous post, you can query USERNAME in addition of SCHEMANAME to see if there is a difference :
SQL> select username, schemaname from v$session where sid = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
USERNAME SCHEMANAME
---------- ----------
HR HR02

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here