CODEWITHSUNDEEP
sqloracle-databaseplsql
karuna nidhan
karuna nidhan

Reputation: 41

switching user session in pl/sql script

    set serveroutput on;
   declare
    username1 varchar2(40);
   cnt number;
    hello varchar2(20);
    c sys_refcursor;
    begin
    select sys_context('userenv','session_user') into username1 from dual;
     select lower(username1) into username1 from dual;
     select count(lower(username)) into cnt from karuna.tableusers where        lower(username)=username1;
     if cnt=1 then
     dbms_output.put_line('username found');
      execute immediate 'connect karuna/password ';
     open c for 'select item_name  from sells12 where item_id=12';
      fetch c into hello;
      dbms_output.put_line(hello);
      close c;
     else
     dbms_output.put_line('u dont have previllege to access database');
      ----raise_application_error(-20001,'error out');
       end if;
       end;

/

I want to switch session user but I am getting error at connect karuna/karuna line

Upvotes: 1

Views: 7614

Answers (2)

rakan
rakan

Reputation: 1

you have to give dba privilege to connect from your user to KARUMA user or CONNECT privilege.

please follow this link http://docs.oracle.com/cd/B28359_01/java.111/b31224/proxya.htm

Upvotes: 0

ibre5041
ibre5041

Reputation: 5288

Look at Oracle's feature n-tier (proxy) authentication. Maybe it's not what you need. Oracle offers you:

  • n-tier auth
  • set role statement (to get more privs. from password "protected" roles)
  • alter session set current_schema=KARUMA. This will not switch your username/privs but the default schema.

The right choice really depends on your needs.

Upvotes: 1

Related Questions