| Error in pl/sql and encryption [message #3140] |
Fri, 06 September 2002 14:37  |
pras
Messages: 57
Registered: June 2002
|
Member |
|
|
Hi,
1)I am trying to execute the following pl/sql procedure.
I wonder why it fails to create a user in the database?
2)Can I pass the username in encrypted form so that the procedure will accept that and decrypt it before it creates the user in the database?If so what should I do?
3)If 'prasanna' is the owner of this procedure and he gives 'execute' permission on this to
another user for eg:'james'.
Can james execute this procedure from his login to execute this procedure from prasanna's schema to create a user?(Here 'james' should not have create user privilege)
Prasanna has DBA privilege:James has only connect and resource.
(The procedure was written in SQLPLUS for Oracle 8i(8.1.7))
Prasanna has DBA privilege:James has only connect and resource.
SQL>prasanna
password:******
SQL> CREATE OR REPLACE procedure create_user(username varchar2, password
2 varchar2)
3 IS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 EXECUTE IMMEDIATE 'create user '||username||' identified by
7 '||password||' default tablespace users temporary tablespace temp';
8 execute immediate 'grant connect,resource to '||username||'';
9 END;
10 /
Procedure created.
SQL> grant create user to prasanna;
Grant succeeded.
SQL> run create_user('jkb','jkb');
1* grant create user to prasanna
Grant succeeded.
SQL> connect jkb;
Enter password: ***
ERROR:
ORA-01017: invalid username/password; logon denied
I appreciate your help!
Prasanna
|
|
|
|
| Re: Error in pl/sql and encryption [message #3141 is a reply to message #3140] |
Fri, 06 September 2002 15:19  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
system> create user prasanna identified by pw;
User created.
system> grant connect, resource to prasanna with admin option;
Grant succeeded.
system> grant create user to prasanna;
Grant succeeded.
system> @connect
Enter user@dbname: prasanna
Enter password: **
Connected.
prasanna> create or replace procedure create_user
2 (username varchar2, password varchar2)
3 is
4 begin
5 execute immediate 'create user ' || username || ' identified by ' || password ||
6 ' default tablespace users temporary tablespace temp';
7 execute immediate 'grant connect, resource to ' || username;
8 end;
9 /
Procedure created.
prasanna> exec create_user('jkb', 'jkb')
PL/SQL procedure successfully completed.
prasanna> @connect
Enter user@dbname: jkb
Enter password: ***
Connected.
jkb>
If PRASANNA grants another user the EXECUTE privilege on this privilege, that user can create users because the procedure will, by default, be executed with the privileges of the owner (PRASANNA).
|
|
|
|
| Have problems in encryption[Re: Error in pl/sql and encryption] [message #3142 is a reply to message #3140] |
Fri, 06 September 2002 16:26 |
pras
Messages: 57
Registered: June 2002
|
Member |
|
|
Hi
Thank you so much.It worked out successfully!
I am trying to pass an encrypted username to the
above procedure which will in turn decrypted by the
procedure to create the user.
{I created 2 functions in prasanna's schema;
encrypt1 and decrypt1)}
/*This uses user defined algorithm to encrypt
the input*/
create or replace function encrypt1
(convstr IN varchar2) return varchar2 is
retstr varchar2(32000):='A';
tempno NUMBER;
begin
for i in 1..length(convstr) loop
tempno:=ascii(substr(convstr,i,1));
retstr:=retstr||to_char(tempno+60);
END LOOP;
return retstr;
/*insert into members values(retstr);*/
END;
/
**********************************************
/*for decryption*/
create or replace function decrypt1
(convstr IN varchar2) return varchar2 is
retstr varchar2(32000):='A';
tempch varchar2(2);
loopno NUMBER;
stepno NUMBER;
Begin
loopno:=length(convstr)/3;
for i in 1..loopno LOOP
stepno:=i*3-1;
tempch:=chr(to_number(substr(convstr,stepno,3))-60);
retstr:=retstr||tempch;
END LOOP;
RETURN retstr;
END;
/
************************************************
[[If you want to see the code for these functions let me
know please!]]
eg:
SQL> select encrypt1('egg') from dual;
ENCRYPT1('EGG')
--------------------------------------------
A161163163
SQL> select decrypt1('A161163163') from dual;
DECRYPT1('A161163163')
---------------------------------
Aegg
***************************************
My procedure is as below:I would like to know the reason for the error
SQL> CREATE OR REPLACE procedure create_user(v_username varchar2, password varchar2)
2 IS
3 username varchar2(200):='select decrypt1(v_username) from dual';
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 EXECUTE IMMEDIATE 'create user '||username||' identified by '||password||'
7 default tablespace users temporary tablespace temp';
8 execute immediate 'grant connect,resource to '||username||'';
9 END;
10 /
Procedure created.
SQL> grant connect,resource to prasanna with admin option;
Grant succeeded.
SQL> exec create_user('A161163163','xxx');
BEGIN create_user('A161163163','xxx'); END;
*
ERROR at line 1:
ORA-01935: missing user or role name
ORA-06512: at "PRASANNA.CREATE_USER", line 6
ORA-06512: at line 1
I would like to know why this causes error?
Any help is appreciated!
Prasanna
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
