problem to write connect user command inside the procedure any other option is there

[fbk2k5]

hi


i have a problem to connect the user inside the stored procedure in oracle
i tried option

CREATE OR REPLACE PROCEDURE Chk_Permission AS
UserID VARCHAR2(10):='1234';
fname VARCHAR2(20):='franklin';
lname VARCHAR2(20):='baskar';
BEGIN
EXECUTE IMMEDIATE 'CONNECT SYSTEM/manager@almdevp AS sysdba';
EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO dataentry';
EXECUTE IMMEDIATE 'CONNECT jcowner/jcowner';
EXECUTE IMMEDIATE 'GRANT INSERT,SELECT ON PERSON TO dataentry';
EXECUTE IMMEDIATE 'CONNECT SYSTEM/manager@almdevp AS sysdba';
EXECUTE IMMEDIATE 'GRANT dataentry TO appuser';
EXECUTE IMMEDIATE 'CONNECT appuser/appuser';
EXECUTE IMMEDIATE 'INSERT INTO jcowner.PERSON VALUES('||UserID||','||fname||','||lname||')';
EXECUTE IMMEDIATE 'CONNECT SYSTEM/manager@almdevp AS sysdba';
EXECUTE IMMEDIATE 'REVOKE dataentry FROM appuser';
--dbms_output.put_line('welcome');
END;

the above procedure
dataentry is a role it have the collection of privileges( create session, insert, delete etc)

jcowner is a actual schema that contains the person table

appuser is a schema to get the previleges and do the insert operation &

revoke the privileges from the appuser .




is it possible or not. if possible pls help me &


if it is not possible any other option there pls reply this forum.


thanks

[masdi2t]

hi


i have a problem to connect the user inside the stored procedure in oracle
i tried option

CREATE OR REPLACE PROCEDURE Chk_Permission AS
UserID VARCHAR2(10):='1234';
fname VARCHAR2(20):='franklin';
lname VARCHAR2(20):='baskar';
BEGIN
EXECUTE IMMEDIATE 'CONNECT SYSTEM/manager@almdevp AS sysdba';
EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO dataentry';
EXECUTE IMMEDIATE 'CONNECT jcowner/jcowner';
EXECUTE IMMEDIATE 'GRANT INSERT,SELECT ON PERSON TO dataentry';
EXECUTE IMMEDIATE 'CONNECT SYSTEM/manager@almdevp AS sysdba';
EXECUTE IMMEDIATE 'GRANT dataentry TO appuser';
EXECUTE IMMEDIATE 'CONNECT appuser/appuser';
EXECUTE IMMEDIATE 'INSERT INTO jcowner.PERSON VALUES('||UserID||','||fname||','||lname||')';
EXECUTE IMMEDIATE 'CONNECT SYSTEM/manager@almdevp AS sysdba';
EXECUTE IMMEDIATE 'REVOKE dataentry FROM appuser';
--dbms_output.put_line('welcome');
END;

the above procedure
dataentry is a role it have the collection of privileges( create session, insert, delete etc)

jcowner is a actual schema that contains the person table

appuser is a schema to get the previleges and do the insert operation &

revoke the privileges from the appuser .




is it possible or not. if possible pls help me &


if it is not possible any other option there pls reply this forum.


thanks

CONNECT is SQLPLUS comment, not SQL command. i think u can execute SQLPLUS command with EXECUTE IMMEDIATE (this statement just for SQL command only)

cmiiw