Solved

Firebird SYSDBA security issues.

Posted on 2006-10-18
16
1,893 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 10

Expert Comment

by:kacor
ID: 17763933
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
ID: 17764023
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
ID: 17764172
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:NickUpson
ID: 17766739
- 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
ID: 17767369
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
ID: 17768372
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
ID: 17768901
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
ID: 17768978
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
 
LVL 5

Accepted Solution

by:
rowdy_h earned 250 total points
ID: 17779356
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
ID: 17783169
split points might have been more fair
0
 

Author Comment

by:henryreynolds
ID: 17783279
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
ID: 17783339
No problem, easy mistake to make as Nick ws giving most of the answers :)
0
 

Author Comment

by:henryreynolds
ID: 17783470
Hi rowdy_h

Thank you for understanding, and I am sorry again.

Keep well

Henry
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 17801860
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
ID: 17802100
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
ID: 17810241
Hi NickUpson

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

Henry
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Read about achieving the basic levels of HRIS security in the workplace.
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…

739 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