Allowing access on DBC tables/view. How risky it is?

Database
Enthusiast

Allowing access on DBC tables/view. How risky it is?

Hi Friends

I wanted to know ,wheather DBC tables/views should be accessable to all the DB users ?
What are the risks of exposing the DBC objects to ALL users? What can be a alternative if any?
6 REPLIES
Enthusiast

Re: Allowing access on DBC tables/view. How risky it is?

You should create separate user(s) and give access to the necessary tables only.
Enthusiast

Re: Allowing access on DBC tables/view. How risky it is?

Hi,
A couple of other thoughts on this, all random....

Think about locking issues. Users could cause issues in locking, creating views on the tables with the required access semantics and granting access to those views might be a better option.

Who are you giving the access to? Is this to help them understand your data model, ie the table, column and index definitions? Or your performance tables , ResusageSPMA etc.

Will granting access to the tables cause more questions to be asked than you anticipate?
Do your users understand the tables and thier contents?

We seem to create views here and grant access to those. This gets around the locking issues, and allows some of the columns to be nulled out if the DBAs see fit.

Random.
Enthusiast

Re: Allowing access on DBC tables/view. How risky it is?

I disagree with Random_Thought!
Unless there are really good security reasons, give users access to DBC by granting public access for select.
Many enquiry tools need access to map data - they usually use Help Column or select from DBC. Tables/ Columns for this. Resusage tables are different - few users need access and performance is such that you do not want people perusing these tables to see what they can find.

In all databases, try to keep access to the database level - not at table/ view level. If you have lots of users with lots of views, you end up with milions of rows in AccessRights. This can cause delays and deadlocks on table create/ drop , and give you horrendous logon times.
If necessary, you can duplicate views in diferent databases - mapping the combinations rto different user roles.
Enthusiast

Re: Allowing access on DBC tables/view. How risky it is?

I too think that giving select access on DBC should do no harm. If you want to exclude performance monitoring tables (ResUsage, DBQL, AmpUsage) then do so.

Earlier I misread the post and thought that you wanted to hand over DBC user to your DB users.
Enthusiast

Re: Allowing access on DBC tables/view. How risky it is?

I Agree with Jimm,
Re reading my post, it’s confusing. "Granting access to the tables" should have read Granting access to these tables" i.e the performance tables.

Create views of the performance tables and grant access to them, it helps prevent locking issues. Doing this though can garner more questions from users querying the performance of the server than you would want! I find we get questions regarding the server being out of capacity (100% CPU), complaints that their job is being held in a queue, and other users are taking priority etc.

However, l granting access to the whole of DBC would allow the users to see Accessrights, Dbase, profiles, Accounts.

If I was a disgruntled employee, I would be able to find out which databases/users had access to which tables etc, would know the users names and other details etc. It would be a security nightmare.

I follow the maxim “Only grant access to the tables required to allow the user to do their job”. Opening up access to objects because it’s easier is a mistake that is often made, it can comes back to haunt, often with reputational damage to the company that has just lost its credit card details!

But perhaps that is just experience given the places I have worked.

Random.
Enthusiast

Re: Allowing access on DBC tables/view. How risky it is?

thanks all for your reply...