Question :
In oracle we have database inside database we have schemes and inside schemas we have tables.
For Example consider the following scenario
I have 2 databases like Database1 and Database2.
In Database1 it have Schema-A and Schema-B and users u1 and u2 these 2 schemas have tables.
In Database2 it has same schema-A and schema-B and users u1 and u2 and these schemas have some tables.
In MySQL both database and Schema are same. So How can do the above scenario in MySQL.
Answer :
You can get extra levels in the hierarchy by:
- running multiple instances of MySQL servers – you can run multiple MySQL servers on the same host using different ports and sockets
- using prefixes (e.g.
a_
,b_
) in the names of tables or databases
The database or table name prefixes can be used mostly for visual purposes, although you can also give grants based on prefixes, e.g.:
GRANT SELECT, INSERT, UPDATE, DELETE ON `db1_%`.* TO 'user1'@'%';
In the above grant, user1
is allowed to run DML queries on all databases with names starting with db1_
.