Can most well known databases be set to read only, or only certain container files set to read only while others are still read write? Whether it's Microsoft SQL, Postgres, Oracle, MySQL, etc.
It's just a very general question and I'm not even asking which database system would be capable of it.
I'm requesting a feature from a software provider to be able to set certain database containers as read only and they're telling me they can't, and I want to make the comment that I find it strange when most database systems have that feature, but I want to double check myself as it's been awhile since I had to do any in depth support of any system with a database.
Edit for clarity 7/2 9:57AM EST: This would be going through the database management interface, not doing something like setting file system permissions to read-only. I apologize for any confusion.
Most databases can have access levels for users or processes set to read only. This vendor however may require full access to the db and certain tables in order to function properly. You are trying to compare apples to oranges here.
Databases also store metrics and configurations for applications. Some store documents and images. It is rare that a database can be set to read only. As stated by Rockn, many users can have read only access so they cannot add any information. But the application usually needs to keep the database fields updated.
The broad answer is "yes," but without sufficient details, it's impossible to give you a useful answer. You don't specify what you asked your vendor to do, how to do it, why, and what the constraints of the application are (because remember, it's not just a database).
In a sense, your question was akin to "can a car be made to go faster? The mechanic tells me 'no.'"
Broadly, yes, a car can theoretically be made to go faster than its rated output, speedometer limit, etc. But is it wise? Is it practical? Is it cost-effective? Is it even within the bounds of physics, physical limitations, or safety for that particular vehicle? What liability would the mechanic incur if he/she did *something* to make your Ford Focus hit 205 mph?
What kind of drivetrain and motor would have to be shoved into that Focus to make that happen? What kind of fuel? Does the engine compartment have enough room for that to even be reality? What about brakes? Do you have enough physical space to install an appropriate braking system that could slow down and stop a Focus that hits 205 mph? How about aerodynamics? What kind of spoiler would you need to keep the thing from flying off into the air and landing on its roof?
Thanks, guys. This does answer my question since it really was in the broadest and most general sense. I understand some fields may still need to writable such as access records and permission modification. I appreciate the time taken for input. To go along with Fessor's comment, this database is storing the equivalent of documents and images- that's all we're asking to be able to set to read only.
Thanks again.
Yeah, it really depends on how the application behaves.
Most databases store metrics and other operational data in system tables. These must be read-write at all times. Most applications store data in special containers, and depending on how the application works it could break if set to read-only.
As an example, our ERP runs over Oracle (the actual database doesn't matter). The data for the ERP lives in a container. The data for job scheduling and reports lives in a different container. I can set the ERP data to read-only and users can run reports but they cannot change data. If, however, the job and report data lived in the same container (or if I made that read-only as well) the system would break because no reports could be added.
It really depends on how the application is separating data, not to mention how it handles database exceptions when a user tries to add or change information that lives in a read-only area.
If your vendor is telling you it can't be done, it likely has to do with how they store metadata or, possibly queries - making the application unusable.
The application always needs to connect to the database with some sort of login. You could always deny write access to those certain tables for that login. Could it break the application? Possibly.
Insofar as SQL Server is concerned, it is a far better practice to control read/write ability using permissions for various Users on the databases (or as part of the security in the software itself), rather than making the database itself read-only. Read-only generally comes into play using certain high availability options. That is more instance level than software level.
I imagine other database engines are similar.
Insofar as SQL Server is concerned, it is a far better practice to control read/write ability using permissions for various Users on the databases (or as part of the security in the software itself), rather than making the database itself read-only. Read-only generally comes into play using certain high availability options. That is more instance level than software level.
I imagine other database engines are similar.
Yes, and I just made an edit to my original post for clarification, and I apologize if I caused any confusion leaving out this info, but this would be using the database management interface, not doing something crazy like setting the file system permission of the database file to read-only, which I know in most cases (if not all) would not work.
Insofar as SQL Server is concerned, it is a far better practice to control read/write ability using permissions for various Users on the databases (or as part of the security in the software itself), rather than making the database itself read-only. Read-only generally comes into play using certain high availability options. That is more instance level than software level.
I imagine other database engines are similar.
This ^^^^ is unique to SQL Server, because its concept of "permissions" mimic Windows filesystem permissions, which allows the administrator to deny writes to the data owner. With most databases, there is no [logical] way to prevent the owner from writing. There are, generally, two ways to "fix" this:
It's also worth noting that you can set a connection to a database server to be read-only such that, even if the database user in question has read-write permissions on a table, they will not be able to write to that table as the connection itself is specifying to only process commands that only require read access (like a SELECT statement).
It's kind of like how you can give someone full permission to a folder on a system locally through the file system permissions but only allow them read permission if accessing that same folder though a network share.
Happy Monday! Welcome to the Monday Spark! Grab a cup of Coffee and read on! To start things off… A Monday Quotes Top 10. 1. Monday should be optional. 2. Good morning. Keep calm and pretend it’s not Monda...
Hi Folks, The Company I work for has just sold off its sales operation. As part of the sale, several laptop & desktop computers that were used in the sales department will be taken to the new companyMy Director has asked me to effectively pre...
Hello,I have:1- 150 HIKVISION IP camera 4MP 2- 5*32-CH HIKVISION NVR I want to display a live view of the 150 camera on a video wall.What is the additional required devices should I have?I think I just need a video wall controller and a high performance p...
In honor of Geek Pride day on May 25th, we want to know about your unique hobbies and interests and all the things you could spend hours talking about.Are you a huge comic book fan? Do you love creating megastructures out of LEGO bricks? Are you int...
Your daily dose of tech news, in brief. Welcome to not only Friday, but according to Lonny6654 who wrote today's community-created Spark article, it is also World Bee Day. To raise awareness of the importance of pollinators, the threats they face, ...
