Interbase Metadata View User Privileges

[MikeMon 12]

Hi

 

I use Interbase XE3. How can I give access to a user to see ONLY the metadata of the tables and fields he/she has privileges for. Currently, he/she can see the metadata of the whole database.

 

Kindly, advise

[Sriram 4]

One cannot do this in InterBase. The database metadata is kept in system relations, and all the users have access to the system relations. So, they can see all metadata in the database. As the owner of the database, you can make the whole database metadata invisible to all users. Check $INTERBASE/examples/blindmeta.sql for this. There are also complimentary readmeta.sql and writemeta.sql to allow various access to the system metadata for all users.

 

You either grant or revoke a (set of) privileges for users to the whole table (user defined or system tables); thus, you cannot grant a user a privilege for a subset of the records, OOTB. Now, you can modify blindmeta.sql to suit your needs, where you may want to make it all blind to a certain group of users, and give read/write privilege to others.

 

Alternatively, a long winded way (I haven't tried this), would be to make system metadata blind to all users (using blindmeta.sql), and then write a custom stored procedure for users to see metadata for the tables/fields they have access to. You will need to grant "SELECT" privilege on the system relations for this stored procedure, and EXECUTE of this procedure to PUBLIC. Your stored procedure would internally validate the user against privileges they have on the "user tables" by looking up RDB$USER_PRIVILEGES, and only then execute the lookup to RDB$RELATIONS and RDB$RELATION_FIELDS/RDB$FIELDS which have the metadata for tables/fields. Your authentication logic is then centered inside the stored procedure, and achieves the control you want to have.

Perhaps a lot more work than what you wanted, but possible I think. 

 

[Guest]

@Sriram is definately correct. Hiding metadata, IMHO, is like encrypting a chunk of javascript in order to protect your IP. It is not a brilliant idea from start so i think that you need a lot on your feet for starting such a project/endeavour.

You would also need a higher level of maintenance and secure funding because you are the only one who can use tools on that database. I have had a few IB/FB databases sent my way where the problem was the client, not the DB.

[MikeMon 12]

30 minutes ago, Dany Marmur said:

@Sriram is definately correct. Hiding metadata, IMHO, is like encrypting a chunk of javascript in order to protect your IP. It is not a brilliant idea from start so i think that you need a lot on your feet for starting such a project/endeavour.

You would also need a higher level of maintenance and secure funding because you are the only one who can use tools on that database. I have had a few IB/FB databases sent my way where the problem was the client, not the DB.

@Sriram& @Dany Marmur

 

Thank you for your input.

 

Dany, allow me to disagree with you in the sense that you are assuming that firstly, I want to HIDE the metadata, and secondly, that what's important in terms of protection is just the data and NOT the metadata. What I really want is to show the metadata of ONLY the tables or views that I've given a user a view access to; which should be the default behavior in my opinion. Only the database owner and the sysdba should have full access to view all the metadata by default. To give you an example, I've created a view and given view access to a user to just view the data in that view, but that user can connect to the database, let's say with IBConsole, and view the metadata of the whole database. In this way, they can use the metadata for their own use, e.g. set up their own database. For me this is a no-brainer. Why would I give a view access to a user for a single view, but be OK for that user to see my database's whole metadata?

[Jeff Overcash 2]

16 hours ago, MikeMon said:

@Sriram& @Dany Marmur

 

Thank you for your input.

 

Dany, allow me to disagree with you in the sense that you are assuming that firstly, I want to HIDE the metadata, and secondly, that what's important in terms of protection is just the data and NOT the metadata. What I really want is to show the metadata of ONLY the tables or views that I've given a user a view access to; which should be the default behavior in my opinion. Only the database owner and the sysdba should have full access to view all the metadata by default. To give you an example, I've created a view and given view access to a user to just view the data in that view, but that user can connect to the database, let's say with IBConsole, and view the metadata of the whole database. In this way, they can use the metadata for their own use, e.g. set up their own database. For me this is a no-brainer. Why would I give a view access to a user for a single view, but be OK for that user to see my database's whole metadata?

Well, technically you can never hide your table structures cause clients can/have to get at that and have to get at that when SQL is prepared (and plans will tell someone really interested in the relationships).  What you can do though is null out your trigger and SP source in rdb$triggers and rdb$procedures leaving behind the binary version of it as that is what IB uses not your source.  So the real "brains" of your DB can not be extracted.  They can get at input and output variables (once again the server has to tell clients about that information so even hiding the metadata it can be extracted by someone knowing how), but how those variables are used internally can be hidden by removing the source after it is compiled.  BLR is not recompiled on backup/restore so the no source is nor harmful to any operations.

[Guest]

16 hours ago, MikeMon said:

In this way, they can use the metadata for their own use, e.g. set up their own database.

So, it's about your IP. Not security, not performance, not need-driven development. I'm sorry but i cannot spot any other formal need for doing it in your post.

[MikeMon 12]

5 minutes ago, Dany Marmur said:

So, it's about your IP. Not security, not performance, not need-driven development. I'm sorry but i cannot spot any other formal need for doing it in your post.

True!!