Home » RDBMS Server » Security » connect/password question
connect/password question [message #9736] Tue, 02 December 2003 14:16 Go to next message
Jae Bosou
Messages: 7
Registered: December 2003
Junior Member
I have a SQL script that contains a hard coded user/password connection. example:
------------------------------------
connect user/password;
drop view myview;
commit;
create view myview
blah.. blah.. blah...
commit;
spool myview.lis
select .... blah..blah..
spool off;
exit;
--------------------------------------------
The problem with this SQL, it requires hard coded user and password every time I run (not secure).

How do I avoid this and create a simple PL/SQL function or procedure? I am a novice PL/SQL. If you can give a step-by-step example or an example link on the net, I really appreciate it.
Re: connect/password question [message #9738 is a reply to message #9736] Tue, 02 December 2003 14:44 Go to previous messageGo to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
I don't know why you use a script to connect, but
when you drop something including view you do not have to commit because Oracle commits on all DDL commands and Drop is one of them.
Insted of CREATE VIEW use CREATE OR REPLACE VIEW - this way you do not have to DROP your view.
Again, you do not have to COMMIT after CREATE.
And the main thing is - why use a script to create view?
You can CREATE OR REPLACE VIEW once and this view will be automatically refreshed every time when source data changes and you SELECT from the view.
Re: connect/password question [message #9745 is a reply to message #9738] Tue, 02 December 2003 15:29 Go to previous messageGo to next message
Jae Bosou
Messages: 7
Registered: December 2003
Junior Member
....forget about this creating views script and commit stuff... it's only an example.
What I want to know is how do I create a function/procedure without entering HARD-CODED USERNAME/PASSWORD. In another word ... changing from plain SQLplus script to PL/SQL written in function/prodecure.
Re: connect/password question [message #9748 is a reply to message #9736] Wed, 03 December 2003 00:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
In fact, you don't need to hard code the password:
Possible workarounds are:
1. Get rid of the connect statement in the script. Connect to SQL*Plus (or Toad or SQL*Navigator or any other tool you might be using) and run the script from there.

2. Replace the hardcoded password with &password. The script will prompt you for a password. Look at this script:
Accept password prompt 'Enter password: '
conn mhe/&password@some_database
Set serverout on
Set Verify off
Accept some_variable prompt 'Enter a variable: '
Declare
  function get_length(p_text in varchar2)  return number 
  is
  begin
    return length(p_text);
  end;
Begin
  dbms_output.put_line('The variable entered was &some_variable with length '||
                        get_length('&some_variable'));
end;
/
Set verify on
The accept statement allows you to specify a prompt (if you ommit it, the prompt would be :'Enter value for password'. When the script is run, it looks like this:
SQL> @c:t1test
Enter password: mhe
Connected.
Enter a variable: some stupid text
The variable entered was some stupid text with length 16

PL/SQL procedure successfully completed.
A similar mechanism (&user instead of user) can make the script prompt for a username.

HTh,
MHE
Re: connect/password question [message #9749 is a reply to message #9745] Wed, 03 December 2003 00:14 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You cannot connect from within a pl/sql procedure, since you have to be connected to execute the procedure and connect is a SQL*Plus command, so it cannot be used within a procedure. I have listed some alternatives below. I can't tell you which is best without more specifics about what you want to do.

If you are already connected, but want to select from another schema, you can accomplish this by granting the necessary privileges and using schema.tablename.

If you want to prompt for a password without displaying the password, you can use ACCEPT and PROMPT and HIDE to prompt for and accept a hidden password without showing it anywhere.

If you want to use your script as is, but you just need to prevent anyone from reading the password within it, you can use WRAP to encrypt the SQL script to prevent anyone from reading the entire script, including username and password.
Previous Topic: Grant the rights to other database user
Next Topic: auditing of the db
Goto Forum:
  


Current Time: Sat Aug 15 15:37:21 CDT 2020