In order to maintain security in a multi-user environment, you have to define different access types for different users.
Many people wonder what is the difference between a user and schema; the answer is way too easy 🙂 There is none. It’s practically the same and each user has its own schema. Technically, they are the same but there are users/schemas with a specific purpose. By creating multiple systems or purpose-specific schemas/users you can simply differentiate the purpose of data stored there.
Every user creates by default a schema named the same. For instance, when you create a user JOHN he automatically gets his own schema JOHN as well. That means that when he creates an object, let’s say a table called SALARY, the object will be stored in JOHN’s schema -> thus the full path for selection is
SELECT * FROM JOHN.SALARY;
As mentioned above, you can create “system” users to store a specific set of data.
Check on these illustrative examples:
- STG_DATA – data stored in stage
- DWH_DATA – data warehouse (reporting) data
- PROD_DATA – production data
- DM_DATA – data marts
- SALES_DATA – sales department data
- IT_DATA – IT data
- … and so on and so forth
The creativity about the naming convention is up to each database designer and developer 🙂 If you are interested in some best practices and tips please navigate to my article Naming convention.
I also mentioned roles and security. A multi-user environment requires necessitates two main requirements.
- Data sharing
To prevent data leakage and make sure only an authorized user can access some set of data you have to use GRANT command (or REVOKE to remove the access). This is very helpful for data sharing as well, so to prevent data duplicating. You can easily share (GRANT) data with another user by allowing them to access a particular table. However, considering tens, hundreds or even thousands of users in a database, granting directly to each user will cause a big mess sooner than you can imagine. To avoid this, Oracle developers came up with a great idea of roles 🙂 You can assign a role to a set of users and grant all privileges to that role. It’s easier to maintain roles assigned to users. If you want to read more about grants and revokes see my article about Permission.
Quiz: How you can easily manage set of privileges across all users?