I'm fairly new to Oracle, but what I've understood so far is that a user has it's own schema in Oracle.
I have an existing Oracle db with many tables.
I want to create another user and then grant to this user the drop schema right.
Could this user drop other users? for me what is strange is that if my assumption is correct and a user has its own schema, than the drop schema has sense for an DBA account or for dropping your own schema, but not some other schemas.
To drop another schema lets's say the second schema is named "Y" then with the user Y I should also grant the right to the user "X" to drop the "Y" schema right?
Many thanks for your patience as well for helping me better understand these Oracle concepts.
I've just read that the DROP USER is a SYS privilege, therefore the user that has this privilege can drop any user in the same database.
Could you please help me better understand how an user is linked with a schema? for me there is a relationship of one to one, meaning a user has it's own schema, and cannot exist without a schema. The schema can be empty (e.g. no tables) that is true.
a user has it's own schema, and cannot exist without a schema. The schema can be empty (e.g. no tables) that is true.
Actually a user and schema are synonymous.
The CREATE USER command creates a user. And, it also automatically creates a schema for that user. Unlike, the CREATE SCHEMA command, which just allows you to create multiple tables and views and perform multiple grants in your own schema in a single transaction. A user is the owner of the schema. You can consider a user to be a schema and a schema to be a user. So, let's say you create 10 users via create user statement, then you have 10 schemas.