Solved

Firebird SYSDBA security issues.

Posted on 2006-10-18
16
1,862 Views
Last Modified: 2013-12-09
Good day

I am worried about database security issues.
I have just install my app and database at a client site. I am worried someone can copy my database and install fireibird on his/her machine
and then overite ths SYSDBA password and see all my metadtaa and procedures.

How can I protect my databases SYSDBA username and how can I hide all my procedures in my database, I dont want someone how have copied my database to see the procedures.

I know their are laws that can protect the data and etc..... but I want to protect my databases.

Please if it is possible , can someone show me good examples on how to protect my database and my security2.gdb file.

Thank you
Henry
0
Comment
Question by:henryreynolds
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 10

Expert Comment

by:kacor
Comment Utility
Hi Henry,

1. You have to install your server and the database on a well controlled machine which other people can't access on. By this way you can reserve the whole control over the database so the metadata too.
2. If I know rigth in the newest FireBird versions external person can't change the owner rigths without knowing the login name and password.
3. If you write stored procedures to solve completely the job the whole database will be hidden for the users.

wbr Janos
0
 
LVL 19

Expert Comment

by:NickUpson
Comment Utility
you can delete the SP code from the database by deleting it from the system tables (BACKUP FIRST AS NOT RECOVERABLE), it will continue to work as when you save an SP it is converted to BLR internallly and that is saved as well.
0
 

Author Comment

by:henryreynolds
Comment Utility
Hi thank you for the reply,
NickUpson you also helped me last week with RC5 issues, thank you again.....

Which system tables data must I delete ?
And each time if I recomplie a procedure, must I delete the system table again ?.

What about SYSDBA username and password, I changed the password, but I see if I reinstall firebird then I can get access to the database by using SYSDBA masterkey again. I dont want someone to get access to the database ever, even if he isntall a new copy of firebird.

Thank you
Henry
0
 
LVL 19

Expert Comment

by:NickUpson
Comment Utility
- Which system tables data must I delete ?

It's an update to blank the fields that hold the human-readable version, I'll have to look it out

- And each time if I recomplie a procedure, must I delete the system table again ?.

once it's done you cannot edit the procedure as the human-readble version no longer exists. to make a change you would have to modify the existing SP and then do the update to remove the readable code again

- What about SYSDBA username and password, I changed the password, but I see if I reinstall firebird then I can get access to the database by using SYSDBA masterkey again. I dont want someone to get access to the database ever, even if he isntall a new copy of firebird.

there is currently no way to do this
0
 

Author Comment

by:henryreynolds
Comment Utility
Thank you NickUpson

Will you let me know what field is the update field. And must I just update that field to null.

Thanx
Henry
0
 
LVL 19

Expert Comment

by:NickUpson
Comment Utility
TAKE A BACKUP FIRST, better yet take several

What you can do is delete your procedure and trigger source code.
update rdb$procedures
set rdb$procedure_source = null
where rdb$procedure_name not starting with 'RDB$';

update rdb$triggers
set rdb$triggere_source = null
where rdb$trigger_name not starting with 'RDB$';

but bear in mind that BLR can be reverse engineered it'sjust most people won't bother.
0
 

Author Comment

by:henryreynolds
Comment Utility
Hi NickUpson

Thank you I am increasing the points, because it works 101%.

I just want to know, will this cause any problems for me, can I for instance copy the procedures from my pc and compile the procedure on the client server and then run the update statement again to delete all the source ? I am just sceared it will cause a error if a procedure are called from delphi...

Can you maybe also helped me with this issue. How can in one command give access to all users on all procedures and tables.

It must grant all tables and all procedures to all users in my database.

Thanx

Henry
0
 
LVL 19

Expert Comment

by:NickUpson
Comment Utility
you can extract the SP's, load them into the database, then run the update, the SP's will still work

there is no builtin command to grant access you need to do "grant update on table to username" etc

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 5

Accepted Solution

by:
rowdy_h earned 250 total points
Comment Utility
There are a couple of shortcuts with granting permissions. You can use all in place of the list of permissions to grant for a table. Also you can use public in place of the username to grant them to. However, there's no shortcut that I know of to replace all tables and procedures.
So you can do
grant all on TABLE to public;
grant execute on PROCEDURE to public;

But you have to do this for each table or procedure you want to grant permissions to. Of course you don't have to use both all and public, so if you just want to grant select permissions on a table to anyone you can use
grant select on TABLE to public;

Or if you want to grant all permissions to a certain use you can use
grant all on TABLE to USERNAME;
0
 
LVL 19

Expert Comment

by:NickUpson
Comment Utility
split points might have been more fair
0
 

Author Comment

by:henryreynolds
Comment Utility
Hi NickUpson , rowdy_h

Sorry I only realize now the comment from rowdy_h, I did not see the comment was done by rowdy_h, I will rectify the problem with expert-exchange.

I am truly sorry rowdy_h, but thank you for your reply, I am so use to getting comment from NickUpson always regarding interbase.

Thank you all

Henry
0
 
LVL 5

Expert Comment

by:rowdy_h
Comment Utility
No problem, easy mistake to make as Nick ws giving most of the answers :)
0
 

Author Comment

by:henryreynolds
Comment Utility
Hi rowdy_h

Thank you for understanding, and I am sorry again.

Keep well

Henry
0
 
LVL 19

Expert Comment

by:NickUpson
Comment Utility
CREATE OR ALTER PROCEDURE PR_SYS_GRANTALL
AS
  declare variable result Varchar(64);
  declare variable stmt Varchar(100);
begin
/*
Author   : Nick Upson
Date     : 25/10/2006
Purpose  : grant correct permissions to all tables and procedures
*/
FOR SELECT R.RDB$RELATION_NAME
FROM RDB$RELATIONS R
WHERE R.RDB$SYSTEM_FLAG = 0
AND NOT EXISTS ( SELECT * FROM RDB$USER_PRIVILEGES P
                  WHERE R.RDB$RELATION_NAME = P.RDB$RELATION_NAME
                    AND P.RDB$USER = 'PUBLIC' )
INTO :result
DO
BEGIN
    STMT = 'GRANT ALL ON ' || result || ' TO PUBLIC';
    EXECUTE STATEMENT :STMT;
END

FOR SELECT R.RDB$PROCEDURE_NAME
    FROM RDB$PROCEDURES R
    WHERE NOT EXISTS ( SELECT * FROM RDB$USER_PRIVILEGES P
                        WHERE R.RDB$PROCEDURE_NAME = P.RDB$RELATION_NAME
                          AND P.RDB$USER = 'PUBLIC' )
INTO :result
DO
BEGIN
    STMT = 'GRANT EXECUTE ON ' || result || ' TO PUBLIC';
    EXECUTE STATEMENT :STMT;
END
end
0
 
LVL 19

Expert Comment

by:NickUpson
Comment Utility
oops typo in first one

CREATE OR ALTER PROCEDURE PR_SYS_GRANTALL
AS
  declare variable result Varchar(64);
  declare variable stmt Varchar(100);
begin
/*
Author   : Nick Upson
Date     : 25/10/2006
Purpose  : grant correct permissions to all tables and procedures
*/
FOR SELECT R.RDB$RELATION_NAME
FROM RDB$RELATIONS R
WHERE R.RDB$SYSTEM_FLAG = 0
AND NOT EXISTS ( SELECT * FROM RDB$USER_PRIVILEGES P
                  WHERE R.RDB$RELATION_NAME = P.RDB$RELATION_NAME
                    AND P.RDB$USER = 'PUBLIC' )
INTO :result
DO
BEGIN
    STMT = 'GRANT ALL ON ' || result || ' TO PUBLIC';
    EXECUTE STATEMENT :STMT;
END

FOR SELECT R.RDB$PROCEDURE_NAME
    FROM RDB$PROCEDURES R
    WHERE NOT EXISTS ( SELECT * FROM RDB$USER_PRIVILEGES P
                        WHERE R.RDB$PROCEDURE_NAME = P.RDB$RELATION_NAME
                          AND P.RDB$USER = 'PUBLIC' )
INTO :result
DO
BEGIN
    STMT = 'GRANT EXECUTE ON PROCEDURE ' || result || ' TO PUBLIC';
    EXECUTE STATEMENT :STMT;
END
end
0
 

Author Comment

by:henryreynolds
Comment Utility
Hi NickUpson

Thank you, How can I open this question again and split the points ?
hank you

Henry
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now