New post
Question
· Nov 1, 2017

Property Deleted, %OnDelete

#Caché

Greetings to all! Faced with such a problem, I ask for help, someone with great practical skills I think has already implemented this. The task was not to delete any objects from the database, but somehow mark this data deleted by client, because of this, I added Property Deleted As% Boolean [InitialExpression = 0]; in the class. And in the redefined method %OnDelete:

ClassMethod %OnDelete(oid As %ObjectIdentity) As %Status [ Private, ServerOnly = 1 ]
{
        s person = ..%Open(oid)
        s person.Deleted = 1
        d person.%Save()
        Quit $$$ERROR(2112, "DeleteOperationNotAllowed")
}

The documentation says if %OnDelete returns $$$OK, then the object will be deleted, but the contrary is necessary. Everything was fine, but it was discovered that, Deleted did not change. After I call "do ##class(Person).%DeleteId(29564)", the object is not deleted and the property also does not change. I looked for a solution, but it was unsuccessful, I think it's because of the roll back, if an error occurs, then CACHE' restores object to its previous state. Now to the question: is it possible to somehow correct this solution, so that the ultimate goal has been achieved or would someone mind giving more professional solution to this problem?

Discussion (3)0
Log in or sign up to continue
Dmitry Maslennikov · Nov 1, 2017

I would recommend instead of mark such object as deleted, with such flag like this. Just "move" it to another table some kind of Trash, when you can store this object as a serialized string for example. In this case object will really disappear from his table and will not be availble with via SQL or any other accesses. But in this Trash, you can have information about deletion data, who deleted and information to restore it.

0 0
Amir Samary · Nov 1, 2017

Hi!

Yes. There is definitely a rollback if you return an error. That won't ever work. If you want to prevent people from deleting records on this table, simply don't give them permission to do it.

If you configure your security right, you can have a role for the user that is being used to access the application (by JDBC, ODBC or dynamic result sets such as %SQL.Statement or %Library.ResultSet) and give that role the right permissions. That would be permissions on the database (say %DB_MYDATABASE), and specific GRANTs for SELECT, INSERT, UPDATE and DELETE on specific tables.

Then, you can let this specific table without the GRANT for DELETE and obligate everyone to UPDATE the Deleted property instead.

You can even use ROW Level Security to hide that record from "normal" users while letting it appear to "report" or "analytics" users.

Beware though that the enforcement of SQL privileges will only work through JDBC, ODBC or dynamic result set objects such as %SQL.Statement. If you issue a %DeleteId() on an object, it will be deleted. That is by design because if you are running object script code and have RW privileges on the database, you can do almost anything. So, it makes no sense to check for SQL privileges on every method call. The system would spend most of its time checking privileges instead of working.  So, what is normally done is that you set your security roles and users right and try to protect the entry points of your application. For instance, you can use custom resources to label and protect:

  • CSP Pages using the SECURITYRESOURCE parameter
  • General Methods - by checking only once if a user has a specific resource with $System.Security.Check() method
  • SOAP and REST services - by protecting the CSP application that exposes them or by using the SECURITYRESOURCE parameter on your %CSP.REST class or even by using $System.Security.Check() on each individual method.

The idea is to protect entire areas of the application instead of checking privileges on every single database access.

HTH!

Kind regards,

AS

1 0