Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    8

    Unanswered: ora-01017 after creating new user

    HI,

    I have a working database on a windows machines. The oracle version is 8.1.7.What i tried doinh was create a user on the machine which i can then use to logon and access the database. However, when i logon as sqlplus myusername/passwd@$ORASCLE_SID , i get the error ora-01017.

    The sql i used to create the user is pasted below:

    CREATE USER "backupsvc-oracle" IDENTIFIED BY "letmein"
    DEFAULT TABLESPACE EPPIXDBS
    TEMPORARY TABLESPACE TEMP
    QUOTA UNLIMITED ON EPPIXDBS;

    GRANT UNLIMITED TABLESPACE TO backupsvc-oracle;
    GRANT ALTER ANY CLUSTER TO backupsvc-oracle;
    GRANT ALTER ANY DIMENSION TO backupsvc-oracle;
    GRANT ALTER ANY INDEX TO backupsvc-oracle;
    GRANT ALTER ANY INDEXTYPE TO backupsvc-oracle;
    GRANT ALTER ANY LIBRARY TO backupsvc-oracle;
    GRANT ALTER ANY OUTLINE TO backupsvc-oracle;
    GRANT ALTER ANY PROCEDURE TO backupsvc-oracle;
    GRANT ALTER ANY SEQUENCE TO backupsvc-oracle;
    GRANT ALTER ANY SNAPSHOT TO backupsvc-oracle;
    GRANT ALTER ANY TABLE TO backupsvc-oracle;
    GRANT ALTER ANY TRIGGER TO backupsvc-oracle;
    GRANT ALTER ANY TYPE TO backupsvc-oracle;
    GRANT ALTER SESSION TO backupsvc-oracle;
    GRANT ANALYZE ANY TO backupsvc-oracle;
    GRANT COMMENT ANY TABLE TO backupsvc-oracle;
    GRANT CREATE ANY CLUSTER TO backupsvc-oracle;
    GRANT CREATE ANY CONTEXT TO backupsvc-oracle;
    GRANT CREATE ANY DIMENSION TO backupsvc-oracle;
    GRANT CREATE ANY DIRECTORY TO backupsvc-oracle;
    GRANT CREATE ANY INDEX TO backupsvc-oracle;
    GRANT CREATE ANY INDEXTYPE TO backupsvc-oracle;
    GRANT CREATE ANY LIBRARY TO backupsvc-oracle;
    GRANT CREATE ANY OPERATOR TO backupsvc-oracle;
    GRANT CREATE ANY OUTLINE TO backupsvc-oracle;
    GRANT CREATE ANY PROCEDURE TO backupsvc-oracle;
    GRANT CREATE ANY SEQUENCE TO backupsvc-oracle;
    GRANT CREATE ANY SNAPSHOT TO backupsvc-oracle;
    GRANT CREATE ANY SYNONYM TO backupsvc-oracle;
    GRANT CREATE ANY TABLE TO backupsvc-oracle;
    GRANT CREATE ANY TRIGGER TO backupsvc-oracle;
    GRANT CREATE ANY TYPE TO backupsvc-oracle;
    GRANT CREATE ANY VIEW TO backupsvc-oracle;
    GRANT CREATE CLUSTER TO backupsvc-oracle;
    GRANT CREATE DIMENSION TO backupsvc-oracle;
    GRANT CREATE INDEXTYPE TO backupsvc-oracle;
    GRANT CREATE LIBRARY TO backupsvc-oracle;
    GRANT CREATE OPERATOR TO backupsvc-oracle;
    GRANT CREATE PROCEDURE TO backupsvc-oracle;
    GRANT CREATE PUBLIC SYNONYM TO backupsvc-oracle;
    GRANT CREATE SEQUENCE TO backupsvc-oracle;
    GRANT CREATE SESSION TO backupsvc-oracle;
    GRANT CREATE SNAPSHOT TO backupsvc-oracle;
    GRANT CREATE SYNONYM TO backupsvc-oracle;
    GRANT CREATE TABLE TO backupsvc-oracle;
    GRANT CREATE TRIGGER TO backupsvc-oracle;
    GRANT CREATE TYPE TO backupsvc-oracle;
    GRANT CREATE VIEW TO backupsvc-oracle;
    GRANT DELETE ANY TABLE TO backupsvc-oracle;
    GRANT DROP ANY CLUSTER TO backupsvc-oracle;
    GRANT DROP ANY CONTEXT TO backupsvc-oracle;
    GRANT DROP ANY DIMENSION TO backupsvc-oracle;
    GRANT DROP ANY DIRECTORY TO backupsvc-oracle;
    GRANT DROP ANY INDEX TO backupsvc-oracle;
    GRANT DROP ANY INDEXTYPE TO backupsvc-oracle;
    GRANT DROP ANY LIBRARY TO backupsvc-oracle;
    GRANT DROP ANY OPERATOR TO backupsvc-oracle;
    GRANT DROP ANY OUTLINE TO backupsvc-oracle;
    GRANT DROP ANY PROCEDURE TO backupsvc-oracle;
    GRANT DROP ANY SEQUENCE TO backupsvc-oracle;
    GRANT DROP ANY SNAPSHOT TO backupsvc-oracle;
    GRANT DROP ANY SYNONYM TO backupsvc-oracle;
    GRANT DROP ANY TABLE TO backupsvc-oracle;
    GRANT DROP ANY TRIGGER TO backupsvc-oracle;
    GRANT DROP ANY TYPE TO backupsvc-oracle;
    GRANT DROP ANY VIEW TO backupsvc-oracle;
    GRANT DROP PUBLIC SYNONYM TO backupsvc-oracle;
    GRANT EXECUTE ANY INDEXTYPE TO backupsvc-oracle;
    GRANT EXECUTE ANY LIBRARY TO backupsvc-oracle;
    GRANT EXECUTE ANY OPERATOR TO backupsvc-oracle;
    GRANT EXECUTE ANY PROCEDURE TO backupsvc-oracle;
    GRANT EXECUTE ANY TYPE TO backupsvc-oracle;
    GRANT EXTENDS ANY TYPE TO backupsvc-oracle;
    GRANT FORCE ANY TRANSACTION TO backupsvc-oracle;
    GRANT FORCE TRANSACTION TO backupsvc-oracle;
    GRANT GLOBAL QUERY REWRITE TO backupsvc-oracle;
    GRANT INSERT ANY TABLE TO backupsvc-oracle;
    GRANT LOCK ANY TABLE TO backupsvc-oracle;
    GRANT QUERY REWRITE TO backupsvc-oracle;
    GRANT SELECT ANY SEQUENCE TO backupsvc-oracle;
    GRANT SELECT ANY TABLE TO backupsvc-oracle;
    GRANT UPDATE ANY TABLE TO backupsvc-oracle;
    --- Explicitly grant select on system views
    grant select on sys.v$session to backupsvc-oracle;
    grant select on sys.v$process to backupsvc-oracle;
    grant select on sys.v$parameter to backupsvc-oracle;
    grant execute on sys.dbms_lock to backupsvc-oracle;
    grant execute on sys.dbms_system to backupsvc-oracle;

    Anyone know how to get around the error?

    Thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If I'm not wrong, you could (as still on 8.1.7) shorten this code to something simpler like this:
    Code:
    CREATE USER backupsvc-oracle IDENTIFIED BY letmein;
    
    GRANT CONNECT, RESOURCE TO backupsvc-oracle;
    I believe this should work.

    Basically, you didn't do anything wrong granting all those privileges, but you missed to grant CONNECT to it.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >However, when i logon as sqlplus myusername/passwd@$ORASCLE_SID
    I am not surprised.
    When you issued the CREATE USER command it appears you used double quote mark around username & password; which mean they were stored as lower case values.
    By entering the above line Oracle by default UPPERcases the username & password so they no longer match what is in the DB.
    Or "@$ORASCLE_SID" is going to a DB where that is not a valid un/pw combo.

    01017, 00000, "invalid username/password; logon denied"
    // *Cause:
    // *Action:
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2004
    Posts
    8
    Thanks so are you saying that the username is stored as uppercase i.e. BACKUPSVC-ORACLE ?Sorry wud have checked that myself on the system but i am not on site today.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Thanks so are you saying that the username is stored as uppercase i.e. BACKUPSVC-ORACLE ?
    No, I'm saying the username is stored as entered in the CREATE USER command.

    CREATE USER user_name1 ......
    -- is stored as USER_NAME1

    CREATE USER "user_name2" ......
    -- is stored as user_name2

    USER_NAME1<>user_name1

    You control what goes into the DB.
    You just need to be consistant with what you use.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    May 2006
    Posts
    6
    Akeeln,

    grant the connect. Normally usernames and passwords aren't case sensitive on Oracle...

    Rik

  7. #7
    Join Date
    May 2006
    Posts
    6
    Sorry, it seems that you can create a user with lower case...my mistake.

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    btw is there any system privilege you DIDN'T grant to backupsvc-oracle?

    The combination of CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE alone gives that account the power of God.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •