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.
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.
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.
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.
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 ?
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
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
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
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?
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?
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;
/