Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006

    Unanswered: Oracle Database Privileges


    I have been using Oracle database for few years whereby everything has been done by our vendor initially. So, I have no problem in creating table and granting access for viewing.

    Now, this is the first time I install the Oracle DB (XE) and start everything from 0 and I really found that it is interesting. I have a of doubt after some trying. Hope that I can get the answer:

    1.) I login as sys or dba to create new schema by using the command:

    CREATE USER winnie IDENTIFIED BY winnie (As I understand Oracle Database automatically creates a schema when you create a user)

    2.) I login as winnie and access to the winnie schema and create a table by using TOAD. I key in few records. When use the select * from mytable, there is an error message saying that the table or view is not existed. But if I use select * from winnie.mytable, then the select statement do return result. I really wonder, this is because if is the schema containing the table, If we omit schema, Oracle assumes the table is in your own schema. Then why I still have to reference the table by the schema name??

    3.) When I try to create table by using the : create table employees as select * from otherschema.employee, the employees table is not created under the winnie schema. After checking by using select * from all_objects where object_name like 'EMP%', then only found that the table owner is SYS, WHY???? I have to use create table winnie.employees as select * from otherschema.employee then only the employees table will be successfully created in winnie schema.

    4.) By default what are the privileges will be granted to a new schema???? for my case, I use - > grant dba to winnie, I even can't select the table that I create (login as winnie)

    5.) I try to create another new schema name winwin, and create a table by using TOAD Create table menu, the table is successfully created, but yet it is not found under the table listing.

    pls advise.

    Ying Ying

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    Answers to all your questions are contained in the Fine Manual above.
    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 2004
    Liverpool, NY USA
    When you run the select, are you logged onto the database as the user winnie, or are you connected as the sys user?
    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