Home » RDBMS Server » Security » Error in pl/sql and encryption
Error in pl/sql and encryption [message #3140] Fri, 06 September 2002 14:37 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Have problems in encryption[Re: Error in pl/sql and encryption] [message #3143 is a reply to message #3142] Fri, 06 September 2002 17:03 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I would really recommend you use the DBMS_OBFUSCATION_TOOLKIT than your own custom encryption/decryption routines.

Here is a link with examples:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:791026226790

The error is caused by the fact that your username variable is just containing a string 'select ...', not the actual results of the decryption.

Just change that variable declaration to:

username varchar2(200) := decrypt1(v_username);


But I would still incorporate the built-in encryption/decryption into your custom functions.
Previous Topic: How to grant execute privileges for some procedures and not to grant to some other procedures of the
Next Topic: Fine Grain Access and Materialised views
Goto Forum:
  


Current Time: Mon Oct 18 06:08:43 CDT 2021