Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Unanswered: access to public synonyms(SYSDBA & SYSOPER)

    Hi All,
    I understand that when we connect as sysdba, we land into SYS schema and if we connect as sysoper, the schema is PUBLIC.

    When I connect as sysdba, I am able to query all public synonyms (like v$datafile etc.,). However I am not able to query v$datafile if I connect as sysoper. It says table/view does not exist.

    Can anyone help in understanding this?

    Thanks in advance
    qA

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Check the administrators guide (you know, the documentation).

    Sysoper was always meant as "dba-lite" - someone who could start/stop the database, but couldn't see business data for example.

  3. #3
    Join Date
    Jul 2003
    Posts
    70

    Question

    Hi,
    I agree to you. But i fail to understand how sysoper is restricted to access PUBLIC SYNONYMS though sysoper lands in PUBLIC schema after connecting.


    Regards,
    qA

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    There is no such thing as a PUBLIC schema. Sysoper is a priviledge and you can connect as any user that has that priv. It is a low priviledge user that can do things like start/stop the database, but can't access any database data. It is meant for a system operator.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2003
    Posts
    70

    Thumbs up

    Hi,
    I would like to quote the following lines from Oracle 9i Concepts.

    "When you connect with SYSDBA privileges, you are in the schema owned by SYS.
    When you connect as SYSOPER, you are in the public schema. SYSOPER privileges
    are a subset of SYSDBA privileges."

    After you connect as sysoper, if you do select user from dual; or show user in your SQL prompt it shows as PUBLIC.

    So I do think there is a schema called PUBLIC.

    Regards
    qA

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It's not a schema, it is a defined as a role. sysoper connects as a user and then adjusts it self to look like a user named public, however public is defined as a role and is not found in sys.user$. See an interesting discussion at the following link.

    http://www.petefinnigan.com/weblog/a...s/00000060.htm
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jul 2003
    Posts
    70
    Hi,
    Thanks for the URL. It was really informative.
    Can you tell me when we create a PUBLIC synonym (like v$datafile), what privilege a user should need to query such public synonyms?


    Thank you once again
    QA

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A synonym is simply another pointer to an existing object. If you have rights to that specific object (v$datafile), then you will access it via the synonym. If you do not, then the synonym will not work.
    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
  •