Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5635
  • Last Modified:

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

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
vasusms
Asked:
vasusms
  • 6
  • 5
  • 4
  • +1
1 Solution
 
Daniel StanleyDatabase engineerCommented:
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
 
tschofiledCommented:
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
 
asimkovskyCommented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Daniel StanleyDatabase engineerCommented:
whoops. correct syntax is "for" not "from"

create synonym accounts for prod.accounts.


good luck,
daniels@asix.com
0
 
vasusmsAuthor Commented:
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
 
Daniel StanleyDatabase engineerCommented:
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
 
asimkovskyCommented:
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
 
vasusmsAuthor Commented:
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
 
asimkovskyCommented:
CREATE OR REPLACE TRIGGER login_trg
ON DATABASE AFTER LOGON...

BEGIN

execute immediate 'alter session set current_schema=target_username';

END;
/





Andrew
0
 
asimkovskyCommented:
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
 
Daniel StanleyDatabase engineerCommented:
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
 
vasusmsAuthor Commented:
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
 
asimkovskyCommented:
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
 
vasusmsAuthor Commented:
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
 
vasusmsAuthor Commented:


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
 
asimkovskyCommented:
Open a new question. This one is closed.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now