As I understand in Oracle, one schema is only for one user and if you (granter) grant privileges to another user (grantee) to access that schema, that schema is copied to the grantee's schema while MySQL just provides access to access the database without copying.
E.g. If user1 is schema1 and if you grant privileges to user2 to access schema1. Will those tables in schema1 be copied to the schema (could be schema2) of user2. How does that work behind the scene?
And,
If I grant only select privileges to user2 to access user1.table1, Will table1 be copied to the schema of user2? Or does user2 only get access to table1 while table1 will still be in schema1?
GRANT SELECT ON user1.table1 TO user2;
Please help me understand how does oracle grant privileges works. Thank you.
that schema is copied to the grantee's schema
That's completely wrong.
The grantee only gets the privilege to access the tables in the other schema. To access the table the grantee needs to prefix the table reference with the grantor's schema:
e.g. user2 needs to run:
select *
from user1.table1;
Nothing is being copied, the query directly access the table in the other schema.