7
I would like to prevent deletions on a table in Sqlite.
CREATE TRIGGER nao_deletar_tabela BEFORE DELETE ON tabela
BEGIN
SELECT RAISE(IGNORE);
END;
It worked! Whenever one runs DELETE, nothing happens.
The problem and my difficulty is: It is still possible to delete the records using DROP TABLE.
How can I stop command DROP TABLE?
5
Basically you can’t. Any attempt will fail.
You have not given context to what you want to do so it may be that your problem is another.
If you want to prevent a user from causing problems with the data. Forget it. He can do whatever he wants one way or another.
The only way not to allow it to do damage is not to let it have access to the file where the database is and only its application access where the file is. This is virtually impossible in environments where you don’t have full control of what you’re unlikely to have.
If your application is giving SQL access to the user and you’re afraid they’ll do something wrong, take the access. It does not matter whether it is useful to give this access, if it has to limit it in this way, it cannot give full access. You would have to make an interpreter that checks whether you can do the command or not. It’s complicated.
When I speak of user, it is any user, even some programmer.
Note that it does not prevent direct access to the file. Remember that you are using a database made for specific circumstances (I even use it for other cases but I know what I’m doing). Give me access to your file and I’ll do whatever I want with the data there, even delete the records individually. I end this TRIGGER that you created in less than a minute.
This recommendation to prevent direct use of SQL is only valid if the user/programmer does not have access to the file.
If you are worried about a security flaw letting you delete the data, do not waste time. Security flaws can bring more problems than this. And the solution is to solve security holes. There is no other way.
If you’re worried about this, you might want to think about another database. Sqlite is made for relatively simple applications (it doesn’t even have to be that simple) and programmers who can handle the entire base without making catastrophic mistakes. It gives you speed, flexibility, freedom and even reliability, but gives you no security through access privileges.
If you have a strong reason that requires privilege customization, then you will need to use another database. One that provides the very granular possibility of what each user can do. But note that even in this case, if someone has access to the process of this database and access to the files he may with some work bypass this type of restriction as well.
You can modify the Sqlite code to prevent the command in this or all conditions. You will need to know a little C, study the code, take care, but it is possible to adapt to what you want.
I’m not advising to do this, just giving an alternative.
Again this does not solve anything if there is direct access to the file.
If you have full control over the database hardly this protection will be necessary.
If the user has access to the file, he can do whatever he wants, however much you protect in this way.
In theory there is a way to protect more that is to encrypt the entire file so that only your application can access it. But even if this is effective falls into the first case, you will protect yourself from yourself. It doesn’t seem necessary since no one makes a mistake and gives a DROP TABLE unintentionally.
-4
Often those who have advanced knowledge think they no longer need to research!
CREATE TRIGGER [Trg_notDropTable] ON DATABASE
FOR DROP_TABLE AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
if(@object = 'tblCliente')
begin
PRINT 'Esta tabela não pode ser removida! Tabela essencial para plano de negócios\n'
rollback
end
SET NOCOUNT OFF;
end
GO
ENABLE TRIGGER [Trg_notDropTable] ON DATABASE
Almost the same, if the person can drop the table they delete Rigger. When it comes to Sqlite, the actual treatment of the problem is being done in the wrong layer. Incidentally, I had already dealt with this issue in comment - If the idea is for the user to work directly on SQL, better use an engine that allows permissions, or abstract the interface. Rigger turns out to be a patch on a bigger problem.
By the way, I would like to see this code of yours working in an Sqlite base, it would be possible to do one in http:/sqlfiddle.com ?
Browser other questions tagged sql database sqlite sqlite3 permissions
You are not signed in. Login or sign up in order to post.
I think the best thing to do is not to give administrator permission to the end user. And protect code against SQL injection into the application layer and not into the database.
– Oralista de Sistemas
It is a specific case, where I am reusing the bank of an existing and third party application, and this, in some tasks, performs a 'cleaning'. In simple and limited tasks it performs a DELETE, which I was able to block. When she performs a complete cleanup she makes a DROP and recreates the bank. Need in the bank to prevent this application, which I have no control, do not clean this data using DROP.
– thiagobarradas
In MS SQL Server I would withdraw permission from
DROPuser that this stubborn application uses to connect to the database. In this case, it would remove the role db_owner. sqlite has this type of feature?– Caffé
Yeah, the sqlite has no Permission feature (GRANT) :(
– thiagobarradas
@thiagobarradas Sqlite is not one of the best banks to use in production because it does not have authentication. To avoid these problems I recommend that you create some routine in your application to synchronize with another database.
– gmsantos
@gmsantos but he mentioned that it is a legacy code. =\
– heat
@Heat not to confuse legacy code with reuse of an existing bank.
– gmsantos
yes yes yes. I was sorry if I didn’t notice that he is creating a new application taking advantage of existing bank.
– heat
There is a lot of "dangerous" concept here in the comments. Just as you can drop a table in the Sqlite, you can delete a table even directly through the filesystem in another database, if the base is local. The problem is using the right architecture in the right place. And Sqlite is successfully used in production in myriad things. Hardly anyone here uses a browser that doesn’t rely on Sqlite, or a mobile phone that doesn’t rely on Sqlite, and various other things. As always, the secret is to use the screwdriver, and hammer to nail (the problem is if the reverse is happening).
– Bacco
@thiagobarradas Take a look at [tour]. You can accept an answer if it solved your problem. You can vote on all posts on the site as well. Did any help you more? You need something to be improved?
– Maniero