I would like to prevent deletions in a table in SQLite.
CREATE TRIGGER nao_deletar_tabela BEFORE DELETE ON tabela
BEGIN
SELECT RAISE(IGNORE);
END;
It worked! Whenever a DELETE is executed, nothing happens.
The problem and my difficulty is: It is still possible to delete the records using DROP TABLE .
How can I prevent the command DROP TABLE ?
Basically it does not. Any attempt will fail.
You have not given context to what you want to do so your problem may be different.
If you want to prevent a user from causing problems with the data. Forgets. He can do whatever he wants in one way or another.
The only way to not allow it to do damage is to not let it access the file where the database is and only your application accesses where the file is. This is virtually impossible for ambitious people who do not have full control of what they are unlikely to have.
If your application is giving SQL access to the user and you are afraid of doing something wrong, take the access. It does not matter if it is of any use to give this access, if it has to limit in this way, it can not give full access. You would have to make an interpreter to see if you can do the command or not. It's complicated.
When I speak of user, it is any user, even some programmer.
Note that this 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). My access to your file and I do what I want with the data there, even erase the records individually. I end up with this TRIGGER 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 concerned about a security breach letting the data be deleted, do not waste time. Security flaws can bring more problems than this. And the solution is to solve the security flaws. There's no other way.
If you are worried about this, it would be good to think about another database. SQLite is made for relatively simple applications (and it does not have to be that simple) and programmers who can handle the whole database without committing catastrophic errors. It gives you speed, flexibility, freedom and even reliability, but it does not give you security through access privileges.
If you have a strong reason that requires the same privilege customization, then you will have to use another database. One that provides the very granular chance 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 it may with some work go over this kind of restriction as well.
You can modify the SQLite code to prevent the command in this or all conditions. You'll need to know a bit of C, study the code, be careful, but you can adapt to what you want.
I'm not advising you to do this, just by 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, this protection is rarely needed.
If the user has access to the file, he can do what he wants no matter how much you protect it.
In theory there is a way to protect more than it is to encrypt the whole file so that only your application can access it. But even if this is effective it falls in the first case, you will protect yourself. It does not seem to be necessary since nobody makes a mistake and gives DROP TABLE unintentionally.
Many times those with advanced knowledge think they do not need to search anymore!
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