Home » RDBMS Server » Server Administration » User Creation
User Creation [message #144506] Wed, 26 October 2005 19:01 Go to next message
hexmanju
Messages: 78
Registered: September 2005
Location: MUMBAI
Member
Hi,

Being a Junior DBA in my firm i also have a job of creating Users in Oracle....

So whenever i create a user i assign a password policy to them..,Basically the verify_function we can say... my verify_function is designed in such a way that the password should have alteast one digit,one special and minimum 8 letters..


So i created a User as


CREATE USER ZZMANJU IDENTIFIED BY HITISI11!
DEFAULT TABLESPACE HSTABLE
TEMPORARY TABLESPACE HSTEMP
PROFILE PASSWORD_POLICY;

When i issue this statement i get an error as
"missing or invalid option at line 1"

So what i did is i issued a statement like

CREATE USER ZZMANJU IDENTIFIED BY "HITISI11!"
DEFAULT TABLESPACE HSTABLE
TEMPORARY TABLESPACE HSTEMP
PROFILE PASSWORD_POLICY;

i.e password in quotes ..

It worked....


Maybe it did not work in the first statement because of the special character !.Also @ may not work...But my password policy allows ! as well as @ as special characters for passwords...

So,please tell me a strong reason behind this..

Thanks
Manju
Re: User Creation [message #144512 is a reply to message #144506] Wed, 26 October 2005 19:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
'!' is a reserved special character. Can be used in not-equal to as shown.
So, when you encapsulate it with quotes, it will work.
But doing so is not a good practise ( in oracle , certain objects created within quotes are DIFFERENT. Like " EMP" and EMP tables are two different tables).
Similarly, @ is used to for TNSALIAS, as in

sqplus scott/tiger@myremotedb
or
connect scott/tiger@remotedb

So when used in oracle password, you may be able to create the user, but cannot use it.
Becuase when authenticated against the dictioanry, @ would be interpreted as tnsalias. You can overcome these kind of 'limitations'. But that would way deviating from the standard oracle conventions.

scott@9i > select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
DEPT                           TABLE
EMP                            TABLE

scott@9i > select * from cat where table_name !='EMP';

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
DEPT                           TABLE

scott@9i > create user test identified by test!;
create user test identified by test!
                                   *
ERROR at line 1:
ORA-00922: missing or invalid option


scott@9i >  create user test identified by "test!";

User created.

scott@9i > create user test2 identified by test@;
create user test2 identified by test@
                                    *
ERROR at line 1:
ORA-00922: missing or invalid option


scott@9i >  create user test2 identified by "test@";

User created.

scott@9i >  grant connect to test;

Grant succeeded.

scott@9i >  !sqlplus -s test/test@
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_string>] | /

ERROR:
ORA-01017: invalid username/password; logon denied


^C

scott@9i > !sqlplus -s test/"test@"
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_string>] | /

ERROR:
ORA-01017: invalid username/password; logon denied


^C^D
Re: User Creation [message #144513 is a reply to message #144512] Wed, 26 October 2005 19:30 Go to previous messageGo to next message
hexmanju
Messages: 78
Registered: September 2005
Location: MUMBAI
Member
prompt>sqlplus -s test/test! will work right according to the example created by you?Anyway Thanks a Lot for a wonderful and quick response...

Thanks
Manju

[Updated on: Wed, 26 October 2005 19:30]

Report message to a moderator

Re: User Creation [message #144516 is a reply to message #144513] Wed, 26 October 2005 19:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
had you craeted the user as test/"test!" , it will.
Re: User Creation [message #144521 is a reply to message #144506] Wed, 26 October 2005 20:45 Go to previous message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
Try creating the user password without !
Previous Topic: connect as sysdba
Next Topic: Setting up a DB link between servers and creating synonyms
Goto Forum:
  


Current Time: Fri Jan 10 12:48:52 CST 2025