Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Posts
    9

    Unanswered: Difference between normal and 'AS SYSDBA'

    I created a user myuser
    default tablespace 'SYSTEM' <== (this might be my problem)
    said 'grant SYSDBA to myuser'

    when I say sqplus myuser/mypass
    sqlplus> select username from dba_users;
    I get '00942: table of view does not exist'
    but when I say sqplus myuser/mypass as sysdba
    sqlplus> select username from dba_users;
    <it works>

    This problem extends to tables like DBA_TABLESPACES, blah blah blah, but
    I'm sure its all the same problem.

    What do I have to do to get sqlplus to see the right stuff without having to connect 'as sysdba'?

    - John

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What do I have to do to get sqlplus to see the right stuff without having to connect 'as sysdba'?
    You'd benefit from reading the fine Concepts Manual found at http://tahiti.oracle.com & learning about Oracle security rather than just poking blindly at the database.

    >said 'grant SYSDBA to myuser'
    What did Oracle say back to you when you did this?
    AFAIK, by default no "SYSDBA" role exists in V7-V10 Oracle.
    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.

  3. #3
    Join Date
    Jun 2006
    Posts
    9
    > grant SYSDBA to myuser
    it said 'grant Succeeded'

    Maybe it didn't?

    Is there a different way to grant dba privilieges to a user,
    'grant all privileges to myuser' seems a bit excessive.

    - John

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    revoke SYSDBA and grant DBA.

    DBA is a role that Oracle designed for most DBA activities, and remains in the product for backwards compatibility. Many people will suggest that you clone the DBA role and stop relying on the assumption that Oracle will include it in future releases.

    SYSDBA is a system privilege (not a role), and due to the additional privileges it gives you in the database (ie it gives you the most powerful access shutdown/startup), it must be invoked specifically upon login.

    -Chuck

  5. #5
    Join Date
    Jun 2006
    Posts
    9

    Thanks

    Ok using DBA instead of SYSDBA does it for me.
    Thanks for your help.

    - John

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also change your default tablespace to something besides SYSTEM. Never ever use the SYSTEM tablespace for any user that doesn't come with the default load!!
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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