Menu
I'm having an issue regarding the security of an application that I'm building... It's a wrapper for a crystal report viewer that provides users with some additional functionality.
There are many internal users with the ability to create/modify Crystal Reports. I've done some tests, and for an application that deals so intimately with connecting to various data sources, it doesn't seem to care the least bit about doing so safely. There is nothing stopping me from modifying an existing crystal report that everyone trusts to make it into something malicious and harmful. All it takes is an added command table with the following sql:
DELETE FROM tbl_Employees; SELECT FROM tbl_Employees;
In fact, you can do anything in a crystal report that the user has permissions to do... so long as it ends with a select. Which leads me to my question: Is there any way for me to ensure that my application limits any connections to our sql server to just selects? I can't temporarily modify user credentials, and I can't use a single read only account because I still need to limit the user to their normal permissions (i.e. which databases they can query).
I'm not very hopeful, as nothing that I've read has led me to believe that I can restrict connections in such a manner.
On the other hand, most of the people making the reports could take a much more direct approach to destroying our data, if they were so inclined... but I hardly think that that is a good excuse not to do my best to ensure that my application is as safe as I can make it. I just can't seem to find any viable answers.
532
In the Login-New dialog box, in the Select a page pane, click User Mapping. In the right pane, under Users mapped to this login, make sure that you have selected the database to read. Under Database role membership for the database, click db_datareader. This role gives the user read-only data access to the database.
You can restrict access to data at the following levels: You can use the GRANT and REVOKE statements to give or deny access to the database or to specific tables, and you can control the kinds of uses that people can make of the database.
Navigate to Security, right-click Logins and select New Login. On the General screen, select a user or users group. On the User Mappings screen, assign all tables related to NetWrix software the db_datareader role (for example NetWrix_FS_Change_Reporter, or NetWrix_Event_Log_Manager etc).
1) Login to SQL Management studio and connect to your SQL instance. 2) Expand Servers and select your SQL instance. Then tick the box Deny for "View any database" Please note that there are other ways of doing this, or by just setting a deny view permission on specific databases.
You should use a read-only account for reporting purposes--no exceptions! Give the account access to SELECT rights to tables and views and EXEC rights functions (exposed via synonyms). Avoid procedures, if possible--they are usually unnecessary and you may inadvertently give users access to procedures that modify the database (an experience a client of mine encountered).
** edit **
I guess it depends also on how the sensitive data is represented.
You would add a row-level filter to the record-selection formula when the report is run.
If the sensitive data is contained in a small number of tables, you could use role-based security (user added to group; roles assigned to group).
If you are using BusinessObjects Enterprise, you could use a Universe to control data security. BusinessViews are also an option; they are the original (before BusinessObjects and SAP) semantic layer that supports dynamic/cascading parameters, but they have been slated for obsolescence.
66
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
