I recently had an inquiry from one of our Database Services clients. They have an Oracle database, the entire data model is in a single schema with a single username. They have recently started adding additional reporting users and roles to their business, but that has not been reflected in the database yet. The developer asked me whether it was better to have a single read-only user or a set of different users.
The answer, as with most IT-related queries, is: “it depends”. And while I generally favor the blanket “one read-only user” approach, both have their pros and cons.
User-by-User Setup
Pro: You know what each user can get to. When set up properly, not everyone can get to everything; this could be important from a security perspective – how critical/vital/sensitive is your overall data? What are the other layers of protection before getting to it?
Con: More administrative work, specifying who can get to what, and maintaining it.
One Read-Only User Setup
Pro: Easy to set up and administer
Con: Everyone who has the username/password can “see” everything.
Generally speaking, when deciding which approach to take, there are several issues to consider:
- What are the most sensitive tables? Would everyone essentially need to have read-only access to these tables anyway? This gets into your data model design and how much the data has to be shared between different types of users. If so, it would tend to favor the one read-only user setup.
- If the data is more dispersed, and certain types of users need access to only certain data, then consider the user-by-user setup.
- If you want to hide your sensitive data but everyone needs access to certain critical tables, could you create stored procedures and have the users access the stored procedures instead of the database directly? (This is more of an advanced topic, but it is sometimes relevant.)
- Do you want to possibly track user access at some level, so you want to know when user X accesses the database? You’ll need to have user-by-user then.
With Oracle, it’s not really “either-or” but rather “both-and”. Following are some key DBA concepts that allow you the flexibility to do any or all of the items above without a lot of administrative overhead. (For more detailed information around any of these key concepts, do some Google searches and check out the Oracle Documentation – and especially the Oracle Database Concepts Manual 11gR2.)
Key DBA Concepts
- Each user by default can only see their stuff
- To let user X see user Y’s data, you need two things: permissions and path.
- Permissions
- granted by user Y –or-
- granted by an admin –or-
- granted through a role –or-
- granted through a system-level privilege, such as read-only to everything in the database
- Path – how do I tell him as user X that I want to get user Y’s data:
- Select * from userY.table1 – manually specify the table each time you want to access it
- Select through a synonym – generally a ‘public’ synonym
- Permissions
- Roles and synonyms ease the admin’s burden
Now, let’s say that you have a REALLY complex environment. You want to track the individual users, and some of them can write to some of the tables, and some of them can read some of the tables – but not all.
- Create public synonyms for every table in the schema. This only needs to be done once. NOTE: If you create new tables, you also need new synonyms.
- You can have dozens (or hundreds)of users, and handfuls (or dozens) of roles. Each user would be a member of one or more roles.
- You grant appropriate table-level access (insert, update, delete, select) to the role.
If you set up the roles correctly, you can have any number of users. Once the roles are set up, the admin only has to create the new user and assign the correct role. If you want specific user access – let’s say that you want Group A of users to be able to update table2 – you can grant that at the user level or at the role level.
Again, the setup you choose depends on your specific environment.