?
Solved

How to access other  user tables in oracle rather than prefix user name and synonyms

Posted on 2003-02-18
16
Medium Priority
?
5,353 Views
Last Modified: 2007-12-19
how can we use tables of other users  rather than using
a prefix name of the user eg prod.emp or using public synonymn

is there any other way either than above two to use objects of other users



0
Comment
Question by:vasusms
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 7980058
set up user synonym.


logged in as the app_user with privs to access the prod tables.

create synonym accounts from prod.accounts.

something like that will work, just check the syntax and it should do the trick.


good luck,
daniels@asix.com
0
 
LVL 2

Expert Comment

by:tschofiled
ID: 7980939
You could alternatively set up views

as table owner :-

create view <vname>
as select * from <owner>.<table_name>
;

create public synonym <sname> for <vname>
;

Tony
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7981090
You could also use this:

ALTER SESSION SET CURRENT_SCHEMA=target_username;


You must have the BECOME USER privilege to do this, and also any other object privileges to access and manipulate that user's objects.



Andrew
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 7983550
whoops. correct syntax is "for" not "from"

create synonym accounts for prod.accounts.


good luck,
daniels@asix.com
0
 

Author Comment

by:vasusms
ID: 7984899
ALTER SESSION SET CURRENT_SCHEMA=target_username

i have 300 more user if every one using alter session and set current_schema=targer_username  does it effect the performance
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 7988786
no, it should not. but you could always set up a login trigger to fire a stored procedure that does all this for you so your users don't have to issue the command. then pin both the trigger and the procedure in memory.


good luck,
daniels@asix.com


0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7990861
The login trigger will affect performance during connection times, especially if you have a high rate of connect/disconnect per second.  You would probably be better of with public/private synonyms.



Andrew
0
 

Author Comment

by:vasusms
ID: 7991246
hi Mr daniels,

thnks for ur update

but the problem is
  ALTER SESSION SET CURRENT_SCHEMA=target_username

  we canot use this command in procedure or in trigger
we get a error ,can u pls.. mail me how to use this command in procedure  or in trigger

since in procedure or trigger we cannot include the command alter session




0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7995081
CREATE OR REPLACE TRIGGER login_trg
ON DATABASE AFTER LOGON...

BEGIN

execute immediate 'alter session set current_schema=target_username';

END;
/





Andrew
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7995086
Just to add one more thing, you should have an exception handler. If you hit an exception, a user might not be able to log in at all:

CREATE OR REPLACE TRIGGER login_trg
ON DATABASE AFTER LOGON...

BEGIN

execute immediate 'alter session set current_schema=target_username';

EXCEPTION WHEN OTHERS THEN null;

END;
0
 
LVL 7

Accepted Solution

by:
Daniel Stanley earned 80 total points
ID: 7996023
here is another way.

good luck,
daniels@asix.com



create a new SYS table that holds the schema names along with the users that are allowed to connect to them.
then setup a logon trigger to lookup the values and alter the current session at the time of logon. this will all be transparent to your users but you will need to maintain the user_connections table with appropriate entries.

see example:

as SYS user
--create new table.
$sys$ORCL@scatcat>create table user_connections(
  2  schema_name varchar2(30),
  3  user_name varchar2(30),
  4  active char(1));

Table created.

$sys$ORCL@scatcat>desc user_connections;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------
 SCHEMA_NAME                                                    VARCHAR2(30)
 USER_NAME                                                      VARCHAR2(30)
 ACTIVE                                                         CHAR(1)

--add your user and schema name for which you want to allow your user to access.
$sys$ORCL@scatcat>insert into user_connections values(
  2  'MASTER_WA', 'TEST_USR', 'Y');

1 row created.

note: for this example you may only have one entry for each user in this table, if you want a particular username to have access to more than one schema you will need to alter the trigger pl/sql to allow for it.

$sys$ORCL@scatcat>select * from user_connections;

SCHEMA_NAME                    USER_NAME                      A
------------------------------ ------------------------------ -
MASTER_WA                      TEST_USR                       Y

1 row selected.

note: make sure your user has select privs on the schema objects you wish to access.


as SYS user
--create logon trigger.
create or replace trigger tmp_trig
after logon on database
declare
v_usr varchar2(30);
v_str varchar2(100);
v_schema varchar2(30);
begin
select TRIM(schema_name)
into v_schema
from user_connections
where TRIM(user_name) = USER
and upper(active) = 'Y';
v_str := 'alter session set current_schema=' || v_schema;
execute immediate v_str;
end;
/

trigger created.

now..  when you logon to the database with the test_user you will be able to access the MASTER_WA objects witout having to prefix them with the schema name.

this should be a good road map for you.

0
 

Author Comment

by:vasusms
ID: 8118127
hi daniels@asix.com


  i try to create the above trigger at system user
i cannot logon to system or any other users
only i can logon thu internal, so i tried to drop the trigger, trigger sucessfuly droped. but still i cannot logon because i think the trigger is firing



0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 8122243
You should ALWAYS put in an exception handler for logon triggers, even if just this:

EXCEPTION WHEN OTHERS THEN null;


If there is an error in the code, the trigger will hang, and all users will continue to have problems logging in.  All users will be essentially locked out, as you have experienced.  


Andrew
0
 

Author Comment

by:vasusms
ID: 8149009
hi Mr Daniels,

  how r you,thnks for your response for my query,

i tried to create the user_connections tables
and triggers at sys user it is working fine when i am trying on 9i,8.1.7

when i am trying on 8.1.5 the trigger is working fine but the problem is it is not setting the
alter session current_schema =PRODUCTION

when i try on the sqlplus the above command is working fine
when i am using the above command inside a trigger after logon database it is not setting the current_schema

when i created the above  trigger after insert at table level the trigger is working and setting the current_schema to production

can u pls .. guide me the reason why in 8.1.5 the it is not setting the current_schema in after logon database

regds
Srinivasan



0
 

Author Comment

by:vasusms
ID: 8372056


many tables are sitting in wrong tablespace
if i want to move the table into new tablespace in oracle 7.3.4 any alter query to move the change the table tablespace


currently

eg

table emp sitting on index_ts tablespace

i want to move the table to

table emp on User_ts (tablespace)







 

0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 8373227
Open a new question. This one is closed.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question