Read Only Database
-
For one of my project I need to create a read only database. I'm authorized to create INSERT and SELECT queries but not UPDATE or DELETE. To do that I suppose I need to add a DATE_STAMP column on each table and adapt all the SELECT to filter only the latest version of my record. Is this realizable in term of performance, size of data, queries? Do you have suggestions, documentations, links about the subject. Thank you,
-
For one of my project I need to create a read only database. I'm authorized to create INSERT and SELECT queries but not UPDATE or DELETE. To do that I suppose I need to add a DATE_STAMP column on each table and adapt all the SELECT to filter only the latest version of my record. Is this realizable in term of performance, size of data, queries? Do you have suggestions, documentations, links about the subject. Thank you,
Of course you do not talk about 'read only' database as SQL means it - 'read only' is a state of the database when no-one can do any write functions, including insert. What you are talking about is that your inserts are fixed and final, can't be removed or updated... for that you have to learn about GRANT[^]/DENY[^]/REVOKE[^] of SQL permissions... For instance, this will prevent from user MyUser to run UPDATE and DELETE queries on table MyTable...
DENY UPDATE, DELETE ON MyTable TO MyUser
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
For one of my project I need to create a read only database. I'm authorized to create INSERT and SELECT queries but not UPDATE or DELETE. To do that I suppose I need to add a DATE_STAMP column on each table and adapt all the SELECT to filter only the latest version of my record. Is this realizable in term of performance, size of data, queries? Do you have suggestions, documentations, links about the subject. Thank you,
B413 wrote:
Is this realizable in term of performance, size of data, queries
With proper indexing and eventually partitioning it won't be a problem. Harddisk space is cheap nowadays.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
For one of my project I need to create a read only database. I'm authorized to create INSERT and SELECT queries but not UPDATE or DELETE. To do that I suppose I need to add a DATE_STAMP column on each table and adapt all the SELECT to filter only the latest version of my record. Is this realizable in term of performance, size of data, queries? Do you have suggestions, documentations, links about the subject. Thank you,
I would call this auditing, unless I have the requirement completely wrong, where you need to track changes but retain only the current information in the table. This is one of the few valid uses for triggers spit. Use a trigger to fire on inset/update/delete, on insert and delete grab the ID, you don't need each field value here, on update grab the before and after values, the ID on the user and shove them into an audit table. If you are doing bulk inserts (etl data in) then turn off the triggers before processing.
Never underestimate the power of human stupidity RAH
-
I would call this auditing, unless I have the requirement completely wrong, where you need to track changes but retain only the current information in the table. This is one of the few valid uses for triggers spit. Use a trigger to fire on inset/update/delete, on insert and delete grab the ID, you don't need each field value here, on update grab the before and after values, the ID on the user and shove them into an audit table. If you are doing bulk inserts (etl data in) then turn off the triggers before processing.
Never underestimate the power of human stupidity RAH
I think hell just froze over. Did you just recommend the use of a trigger? (spit)
Wrong is evil and must be defeated. - Jeff Ello[^]
-
I would call this auditing, unless I have the requirement completely wrong, where you need to track changes but retain only the current information in the table. This is one of the few valid uses for triggers spit. Use a trigger to fire on inset/update/delete, on insert and delete grab the ID, you don't need each field value here, on update grab the before and after values, the ID on the user and shove them into an audit table. If you are doing bulk inserts (etl data in) then turn off the triggers before processing.
Never underestimate the power of human stupidity RAH
What you suggest is one of my possibilities but I would like to investigate on the possibility only to allow INSERT and SELECT. I my case I need to prove that NOBODY can never hack the system and modify a tracking data. A trigger can be deactivated. I would like to GRANT only INSERT and SELECT to all users. I saw system build for FDA rules where developers or DBA can still have all the power on production database and I saw them removing audit data on production. Wrong and testing data in this case but where is the limit between this and falsification.
-
I think hell just froze over. Did you just recommend the use of a trigger? (spit)
Wrong is evil and must be defeated. - Jeff Ello[^]
Jörgen Andersson wrote:
Did you just recommend the use of a trigger
Yeah I feel dirty, had to do it again in the c# forum for the same reason ugh. It must be the change in local, I'm on holidays in Cairns, it must be affecting my vitriol levels.
Never underestimate the power of human stupidity RAH
-
What you suggest is one of my possibilities but I would like to investigate on the possibility only to allow INSERT and SELECT. I my case I need to prove that NOBODY can never hack the system and modify a tracking data. A trigger can be deactivated. I would like to GRANT only INSERT and SELECT to all users. I saw system build for FDA rules where developers or DBA can still have all the power on production database and I saw them removing audit data on production. Wrong and testing data in this case but where is the limit between this and falsification.
Ok so this has nothing to do with read only, it is about locking down your database with enough security to pacify your clients paranoia. Good luck, delving into the high levels of database security is not a trivial excercise. Personally I would try and get hold of a DBA/expert in the area if your client can justify the expense.
Never underestimate the power of human stupidity RAH
-
Jörgen Andersson wrote:
Did you just recommend the use of a trigger
Yeah I feel dirty, had to do it again in the c# forum for the same reason ugh. It must be the change in local, I'm on holidays in Cairns, it must be affecting my vitriol levels.
Never underestimate the power of human stupidity RAH
If it's of any consolation, I'm totally agreeing with you not just in general on this subject but also about this exception.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Ok so this has nothing to do with read only, it is about locking down your database with enough security to pacify your clients paranoia. Good luck, delving into the high levels of database security is not a trivial excercise. Personally I would try and get hold of a DBA/expert in the area if your client can justify the expense.
Never underestimate the power of human stupidity RAH
It's not about paranoia... unit testing, business and database testing has been for client paranoia and audit validation. In my case it's about respecting law and users protection.
