Thread: Oracle Database Privileges
02-24-09, 12:13 #1Registered User
- 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.
02-24-09, 12:16 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
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.
02-24-09, 12:59 #3Registered User
- 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?Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.