1

I'm learning Oracle and did some testing. I used the sys user and created a table t in the scott schema and I found t's owner is scott, and scott has full access to t. I want to confirm, does scott always have full access in the schema scott? No matter who created these objects?

2 Answers 2

1

Yes, there is no permission for own objects DML or DDL enforced and there is no „creator“ concept for objects.

(The only thing which stops you from inserting is therefore tablespace quota, which is always counted towards the owner)

This is BTW the reason why it is frowned upon to connect as schema owner users if you can avoid it - as you cannot limit drop or alter for them.

1

SQL Language Reference

Prerequisites

For you to select data from a table, materialized view, analytic view, or hierarchy, the object must be in your own schema or you must have the READ or SELECT privilege on the table, materialized view, analytic view, or hierarchy.

but

To issue an Oracle Flashback Query using the flashback_query_clause, you must have the READ or SELECT privilege on the objects in the select list. In addition, either you must have FLASHBACK object privilege on the objects in the select list, or you must have FLASHBACK ANY TABLE system privilege.

So for a simple select it is sufficient to own the objects. But if you want to make a Flashback query on one of your tables, you need additional flashback-related privileges.

Also you have UPDATE, INSERT and DELETE privileges on the tables of your schema.

Details you need to execute a statement you can find in the prerequisite sevtion at the beginning of the chapter that describes a statement in this manual.

3
  • Plus: the user does not have the privilege to drop that schema, even if though the schema is owned by the user.
    – user1822
    Aug 1, 2019 at 5:50
  • @a_horse_with_no_name This is described in the manual "You must have the DROP USER system privilege. " to drop a user. But as far as I know you cannot drop a user that is logged in. So I think you never can drop your own user.
    – miracle173
    Aug 1, 2019 at 5:54
  • Yes, I know (and it's caused by the tight coupling between user and schema in Oracle). I just wanted to add that to the list to make it complete
    – user1822
    Aug 1, 2019 at 5:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.