Skip to Main Content
  • Questions
  • Synonyms vs Alter session current_schema

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, David.

Asked: November 03, 2002 - 12:50 pm UTC

Last updated: January 15, 2008 - 1:05 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

I've been reading a lot of articles that talk about private or public synonyms (including your site and ixora site). I use private synonyms, and all users connect to database with their accounts. I've experimented that volume of SYN$ table is very big. Ok, slow queries of dictionary, more space, internal overhead ...

I've tested with "alter session set current_schema .." and my results are very good versus synonyms. I'd like to know if there would any problem if I do "Alter session set current_schema = <owner>" versus use of owner.object for all users in a transaccional system that today there are 500 users. (addicional internal overhead, lost performance, ..). This application is developed with Oracle Forms 6i and is executing with Forms Server.

For example, using private synonyms I'm getting:

INSERT into sinonim_privat
values
(:b1,:b1, 'prova inserciĆ³ tkprof 1')


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 299997 47.27 57.16 0 1469 312826 299997
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 300000 47.27 57.16 0 1469 312826 299997

I imagine that parse = 3 because I've used a anonymous block for this test. This test has been executed 3 times.

and I'm getting this results using alter session:

INSERT into sinonim
values
(:b1,:b1, 'prova inserciĆ³ tkprof 1')


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 299997 43.04 49.49 0 1440 312848 299997
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 300000 43.04 49.49 0 1440 312848 299997

Do you think that is a good idea use alter session for a big transaccional system? (addicional internal overhead, lost performance, ..).

Atentament
David Pujol
Thanxs.

and Tom said...

I've been recommending the alter over synonyms for a while.

I don't like synonyms (not even a big fan of the alter -- believe the schema should be qualified in the app but anyway...)

Alter is the lesser of two evils.



Rating

  (19 ratings)

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

Comments

Synonyms vs Alter session

David Pujol, November 04, 2002 - 3:43 am UTC

Ok Tom, thanxs, I understand that I can use "alter session" for transactional systems and don't lost more performance than synonyms. I've seen that alter session changes the current schema, but users are same so, I don't think that there are any problem if I use auditTrail.

Tom Kyte
November 04, 2002 - 8:26 am UTC

correct, the only thing the current_schema changes is the default schema (owner) name that is pre-pended to unqualified object references.

it does not change your identity
it does not change your privileges
just the default owner added to unqualified references.

Oracle Metalink note: 1036894.6

sudhakar nagisetty, January 31, 2003 - 1:08 pm UTC

Use SET CURRENT_SCHEMA
Note that CURRENT_SCHEMA is not a documented parameter, and therefore its functionality could be changed or the parameter could be made unavailable at any time.

Tom Kyte
January 31, 2003 - 1:32 pm UTC

Not to worry:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem18.htm#2053087 <code>

tis fully documented and supported these days. (gotta look at the versions and age of the documents sometimes in metalink -- even here. I answer with regards to the version in the title of the question -- the answer maybe different for your release)

Oracle Metalink note: 1036894.6

Sudhakar Nagisetty, January 31, 2003 - 1:48 pm UTC

Thanks, your input is very helpful

what happens when owner changes

eric givler, January 31, 2003 - 11:31 pm UTC

you said:

---
I don't like synonyms (not even a big fan of the alter -- believe the schema should be qualified in the app but anyway...)
---

The DBAs, in there infinite wisdom, decide that our ownername, RECORDS, should conform to the standard of RECORDS_DBA. All data is exported and brought back in with a fromuser touser import. Now we have to change *all* frontend source code - go into EVERY record group, every block property, search and replace all code, revise all scripts that had the hard-coded owner, etc.

And we'd better go in and fix all backend source code, including view definitions and stored source. What's worse is that triggers didn't load if the owner was there, and our DB jobs were submitted on import as a person that doesn't even exist anymore as that schema was dropped. (they are seen in dba_jobs, but not in user_jobs in the new schema)

In a case like this, the public synonyms would have avoided all the frontend mess at least. The backend stuff really didn't need explicit usernames.

Is this too contrived to be appropriate? (It has happened to me MORE THAN ONCE).

Again, apologies for another question, I just thought I'd share an issue I've had to deal with.

Tom Kyte
February 01, 2003 - 9:33 am UTC

so parameterize your installation scripts?  when I goto install code -- I generally have to run:

SQL> @install A B C D E F G 

where a b c d e f g are the parameters to my install scripts.  I set the define variable to something like ^ (instead of & which happens in real code too often with the web stuff).  then my code has:


   select x,y,z from ^schema..table_name


Also, if the DBA's say "gotta use a standard name cause we said so", just give your manager the estimate of how many $$$'s this will cost and ask him to ask them to justify such an expenditure.  Seems like a really bad post-decision by the DBA's eh?

Show the $$$ effect and you might well find you are not in such a situation.

but -- you brought up a good point, VIEWS.  I like views alot.  they can achieve the same thing as synonyms, don't pollute the namespace, import the metadata into my schema (so user_tab_columns has data for example), let me "rename a column" in a flash, let me "reorder the columns" in a table instantly..... 

ALTER SESSION SET CURRENT SCHEMA

A reader, October 15, 2005 - 8:18 pm UTC

Tom:

I have two users appusr (application db user), appown (application db schema owner).

SELECT, INSERT, UPDATE, DELETE GRANTS on application TABLES are granted to 'appusr' using ROLES (from schema owner). Private synonyms are maintained for each database objects under 'appusr'. APPUSR schema does not have any create table or drop table provileges.

Now i need to create "psoadmin" (prod support user) user who needs create table/drop table privilege along with SELECT/INSERT/UPDATE/DELETE privilege on application tables to run sql scripts for data fixes.

I have setup ALTER SESSION CURRENT_SCHEMA=appusr (for psoadmin) instead of creating private synonyms in their schema.

Steps are outlined below :

psoadm@TESTRECO> alter session set current_schema=APPUSR ;

Session altered.

psoadm@TESTRECO> select sys_context('userenv','current_schema') x from dual ;

X
--------------------------------------------------------------------------------
APPUSR


psoadm@TESTRECO> desc dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(4)
DNAME VARCHAR2(10)
LOC VARCHAR2(13)

psoadm@TESTRECO> create table dept_bkup as select * from dept where deptno=10 ;

ERROR at line 1:
ORA-01031: insufficient privileges

psoadm@TESTRECO> update dept set dname='ACCOUNTING' where DEPTNO=60
/
1 row updated.

psoadm@TESTRECO> select sys_context('userenv','current_schema') x from dual ;

X
--------------------------------------------------------------------------------
APPUSR

Now, i prefixed psoadm.dept_bkup and it works.

psoadm@TESTRECO> create table psoadm.dept_bkup as select * from dept
/

Table created.


Question:
=========

I prefixed the schema owner (psoadm.) to backup the table changed records before issuing any DML on the tables, it works.

Do you know of any other way to make it work without prefixing (.psoadm) using "CURRENT_SCHEMA" method ?


Thanks alot.

Tom Kyte
October 16, 2005 - 8:05 am UTC

umm, when you used current schema, you said in effect "unless I say otherwise, assume the schema associated with all un-schema'ed objects is 'APPUSR'"

you sort of "did this", you asked it to assum APPUSR whenever the schema name isn't there.

Thanks

A reader, October 16, 2005 - 11:54 am UTC


Set CURRENT_SCHEMA cannot handle Database Links

Hemant K Chital, October 17, 2005 - 1:25 am UTC

I have been considering using the SET CURRENT_SCHEMA
so that my application does not have to connect as
the Schema owner but as an account with the necessary
privileges. However, many portions of my (Distributed)
application require Database Links -- particularly
one single Database with links to many other databases.
Unfortunately, the ALTER SESSION SET CURRENT_SCHEMA
cannot handle DB Links (I've logged a TAR and read
Bugs 2373249, 2554178 and 1290378). The Support Analyst
does state that 2554178 is fixed in 9.2.0.5 though what
I see of the Bug text is that it is fixed in 10.1.
I haven't upgraded this particular database from 9.2.0.1
to 9.2.0.5 but will do so.
My question : What is your opinion about using
ALTER SESSION against a schema that owns DB Link
so as to be able to make use of those DB Links as well,
without creating private/public synonyms ?

Tom Kyte
October 17, 2005 - 7:31 am UTC

I don't believe the PRIVATE dblinks should become available - the current_schema doesn't change permissions at all here, and you never had permission to use the PRIVATE dblinks.


I personally prefer to "hide" dblinks behind views. The views can be granted on AND the view imports the metadata from the remote site - meaning you can "describe" them without hitting the dblink, you can build procedures against them that, when they compile don't need to hit the dblink and so on.

creating views with current_schema set fails

Aaron Valdes, October 20, 2005 - 2:23 pm UTC

Hi Tom,

Can you help me figure this out?

oracle@testdb1 $ sqlplus /

SQL> show user
USER is "OPS$ORACLE"

SQL> alter session set current_schema = AVALDES;

Session altered.

SQL> create table test (id int);

Table created.

--Create table "AS" works:
SQL> create table test2 as select * from test;

Table created.

--but creating a view fails
SQL> create view test_v as select * from test;
create view test_v as select * from test
                                    *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select * from dba_sys_privs where grantee in (USER);

GRANTEE     PRIVILEGE             ADM
--------    ----------            ---
OPS$ORACLE  UNLIMITED TABLESPACE  NO


But if the select any table priv is given to OPS$ORACLE then
creating the view works.

oracle@testdb1 $ sqlplus '/as sysdba'

SQL> grant select any table to OPS$ORACLE;

Grant succeeded.

SQL> exit;

oracle@testdb1 $ sqlplus /

SQL> alter session set current_schema = AVALDES;

Session altered.

--creating the view works now
SQL> create view test_v as select * from test;

View created.

OPS$ORACLE has the DBA role and can select any table. Why does it need that priv to create the view but not the table?

Thanks 




 

Tom Kyte
October 20, 2005 - 4:57 pm UTC

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

nothing to do with current schema, everything to do with roles and compiled stored objects (triggers, procedures, views...)

RE: Synonyms vs Alter session current_schema

Aaron Valdes, October 20, 2005 - 9:08 pm UTC

Thats what I thought but what I am able to create procedures and triggers. The only error is on creating a view. I just want to understand why the view is erroring.

oracle@testdb1 $ sqlplus /

SQL*Plus: Release 9.2.0.5.0 - Production on Thu Oct 20 17:46:39 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> alter session set current_schema = AVALDES;

Session altered.

SQL> create table testtab (str varchar2(20));

Table created.

SQL> insert into testtab values ('Hello');

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace procedure test_p
  2  as
  3  begin
  4   for i in (select * from testtab) loop
  5     dbms_output.put_line(i.str);
  6   end loop;
  7  end;
  8  /

Procedure created.

SQL> show errors
No errors.

SQL> exec test_p;
Hello

PL/SQL procedure successfully completed.

SQL> create or replace trigger test_trg
  2  after insert on testtab
  3  begin
  4    dbms_output.put_line('Fired');
  5  end;
  6  /

Trigger created.

SQL> show errors
No errors.

SQL> insert into testtab values ('hello again');
Fired

1 row created.

SQL> commit;

Commit complete.

SQL> exec test_p;
Hello
hello again

PL/SQL procedure successfully completed.

SQL> create or replace view test_v as select * from testtab;
create or replace view test_v as select * from testtab
                                               *
ERROR at line 1:
ORA-01031: insufficient privileges



SQL> desc avaldes.testtab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STR                                                VARCHAR2(20)

SQL> desc testtab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STR                                                VARCHAR2(20)

SQL> desc avaldes.test_p;
PROCEDURE avaldes.test_p

SQL> desc test_p;
PROCEDURE test_p

thanks
 

Tom Kyte
October 21, 2005 - 8:05 am UTC

current_schema DOES NOT CHANGE your privs at all. It does not affect privileges at all. Just some default schema name resolution.


I don't have a complete test case, not sure where to start (eg: create users, grant them whatever you are currently granting, etc)



Having the same issue...

Jon, January 31, 2006 - 1:52 pm UTC

Hi Tom,

Bought your new book and I'm currently in the first chapter setting up environments.

Basically, I'm unable to create a view for STATS

I've logged in under my personal account, which has been given the dba and connect roles.

I can select the views involved seperately and when they are unioned. Actually, when I remove the 'CREATE OR REPLACE VIEW stats AS' part of the statement, it excutes correctly.

CREATE OR REPLACE VIEW stats
AS
SELECT 'STAT...' || a.NAME NAME, b.VALUE
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
UNION ALL
SELECT 'LATCH.' || NAME, GETS
FROM v$latch;


FROM v$statname a, v$mystat b
*
ERROR at line 3:
ORA-01031: insufficient privileges



column NAME format A60;
SELECT 'STAT...' || a.NAME NAME, b.VALUE
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
UNION ALL
SELECT 'LATCH.' || NAME, gets
FROM v$latch;

NAME VALUE
------------------------------------------------------------ ----------
STAT...logons cumulative 1
STAT...logons current 1
STAT...opened cursors cumulative 283
STAT...opened cursors current 2
STAT...user commits 0
STAT...user rollbacks 0
STAT...user calls 1091
STAT...recursive calls 454
STAT...recursive cpu usage 1
STAT...session logical reads 257
.
.
.
.

select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
JON CONNECT NO YES NO
JON DBA NO YES NO
PUBLIC PLUSTRACE NO YES NO

3 rows selected.

I've also written it as jon.stats and sys.v_$mystat. And, I'm running 9iR2. Nothing seems to work.

Any ideas?

Thanks,
Jon

Tom Kyte
January 31, 2006 - 3:41 pm UTC

The errata sheet on apress has this one, I lost some underscores there:

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

has the needed grants

"You must be granted select DIRECTLY on SYS.V_$STATNAME, SYS.V_$MYSTAT, and SYS.V_$LATCH. It will not work to have select on these via a ROLE"

Have the same issue...

Jon, January 31, 2006 - 4:51 pm UTC

Thanks!

AQ Limitation?

A reader, February 09, 2006 - 5:26 pm UTC

Hi Tom,
A customer of ours had this requirement of appuser being different from appowner. We couldn't go the synonynm route because according to a metalink document, it is not possible to create synonyms for AQ's (and we use a lot of these in our schema). Does this AQ limitation apply to 'alter session set current_schema' as well?
Thanks in advance.

Synonyms vs Alter session current_schema

David Wieland, October 18, 2006 - 12:07 pm UTC

You answered my question about views vs. synonyms fairly well and gave me a valuable heads up regarding the need to assign the view creation privileges directly, not via roles.

I'm always impressed with your combination of knowledge and patience. Thanks.

What about for sharing code

Tom, January 18, 2007 - 12:18 pm UTC

We are in a position where we are consolidating N applications into 1 database. Each of these applications has a significant amount of framework code which is identical.

From a maintenance point of view moving this code into one shared schema is a good idea, but the only way I can see of doing this "non-intrusively" [i.e. without searching out every use of the shared packages and fully qualifying them in each application] is to use private synonyms.

What would you think of the performance tradeoffs of

1. Keeping N copies of an identical package in the SGA

vs

2. Keeping N private synonyms but only one physical package

Obviously, I will test this before making a decision, so is there anything I should look out for in the private synonym case [increased latch contention in the statspack report?]

ALTER SCHEMA and AQ

Luigi D. Sandon, July 17, 2007 - 6:47 am UTC

I've started to use SET CURRENT_SCHEMA to avoid to hardcode the owner within queries (the schema owner can be selected when installing, we cannot ensure our customer does not have already a schema with that name), and everythings works ok but AQ.
I have a package (definer rights, of course) that exposes functions to enqueue/dequeue data. When the functions are called by another user (after the alter session call), the queue name is still looked for in the original user schema, not in the altered one.
If I prepend the queue owner in the package code (something I'd like to avoid, although a script parameter may work, I guess) it works.
Is it by design? Or is it a bug? Are there other restrictions in the namespace change?
Tom Kyte
July 17, 2007 - 11:48 am UTC

the queue name is not an "object identifier" in the true sense - it is doing this 'right'


current_schema in RAC environment

Alexei, July 17, 2007 - 2:37 pm UTC

Sorry, can't test this myself at the moment.

When using CURRENT_SCHEMA approach in RAC environment, and session fails over to another node - is the setting preserved? If I recall documentation correctly, all session setting customizations are lost.

Regards, Alexei

Re: Alter schema and AQ

David Penington, July 17, 2007 - 9:11 pm UTC

G'day Luigi, I had the same problem of AQ using the logged in user's name space. To work around it, my AQ procedures get the username of the procedure's definer (for the default definer's rights schema) and prefix the queue name with this. That way the owner name is not hard coded in the package. It's just:
SELECT uu.username
INTO g_owner
FROM user_users uu;


set current_schema in after logon trigger - many userids

Brian Burton, January 15, 2008 - 8:56 am UTC

Tom
Regarding 'alter session set current_schema', I'm using this code from one of your books to try to set current schema to help us implement individual userids to replace use of generic userids.

CREATE OR REPLACE TRIGGER afterlogon_set_current_schema
AFTER LOGON on DATABASE
BEGIN
IF ( user = 'BBURTON' ) THEN
execute immediate
'alter session set current_schema=csnoladm';
END IF;
END;
/

This works fine, but with many userids (dozens) it will get messy. Is there a way to query for 'role' or 'profile' instead of userid in this code? If so, the code could query for a single role/profile to determine whether to set current schema, instead of dozens of individual userids. Alternately the code could query for "not in 'SYS','SYSTEM',etc...", but that would also present maintenance issues.

I believe 'after logon' mean that the entire logon sequence is completed (roles, privs, profiles) before the after logon trigger fires?

Or, even simpler maybe, is there a way to assign a default_schema at userid creation?

Tom Kyte
January 15, 2008 - 1:05 pm UTC

two words for you:

lookup table


design a table, put data into it, query it in this procedure to determine what schema you want to set for this user. If you can glean what you need from the data dictionary - sure, go for that as well - it is all there. You can do whatever sort of processing you need to figure out how to turn a userid into the schema you would like to use.

there is but one default schema for a user, theirs.

DBMS_SESSION.IS_ROLE_ENABLED

Mohamed Houri, January 16, 2008 - 3:02 am UTC

You may use this

CREATE OR REPLACE TRIGGER afterlogon_set_current_schema
AFTER LOGON on DATABASE
BEGIN
IF ( DBMS_SESSION.IS_ROLE_ENABLED('YOUR_ROLE_NAME') ) THEN
execute immediate
'alter session set current_schema=csnoladm';
END IF;
END;
/