How a DBA can perform Database maintainance activities with restricted access to DATA?

Hi All,

Could you please help me to find a possible solution for - "How we can perform the below DBA Related activities without having an access to user data in database",

  1. User Management.

  2. Priviliges Management

  3. Tablespace Management (Oracle)

  4. Export Operation (Expdp/Impdp)

  5. Manage Roles

P.S. Client do not want DBA to View Data (not at all)



more ▼

asked Nov 07, 2012 at 07:14 AM in Default

avatar image

10 1 1 2

Hi, may i know what kind of restrictions you want for database??

Thanks Sumit Rana

Nov 21, 2012 at 12:48 PM SumitRana
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Are you asking how to restrict the DBA or how a properly restricted DBA can do his job?

How to restrict the DBA

If you are asking how to restrict the DBA then I join the Thomas and Kevin in suggesting you look at http://ask.sqlservercentral.com/questions/95308/how-to-restrict-dba-from-readwrite-but-still-can-p.html @KevinFeasel 's answer there is really good and I added my two cents below his. My answer in particular was focused on SQL Server rather than Oracle, but the concepts at least are similar.

How a restricted DBA can work

Now, if you are asking how a DBA who has been successfully restricted can do his job, then the answer is dependent on which part of a traditional DBA job we are talking about, but certain parts can be done without knowledge of the structure much less the actual data, other parts require knowledge of the structure but can be done without peeking at the data, and other parts of the job become harder or impossible.

On your specific list #1, 2, and 5 (user management) can all be done without looking at the data. The problem is that giving someone the ability to do #1, 2, and 5 also gives them the ability to get around most restrictions you could put on them...(Not all restrictions. As in my other answer, there are still some ways to limit a DBA, but someone with user management priveleges can get around anything based on top of permissions)

No. 3, managing data by its nature requires knowledge of the structure of the data, but it does not strictly require knowledge of the data itself. Of course, knowledge of the data itself can help at times, especially if you ask the DBA to predict what indexes will be needed before profiler data is available or ask him to help troubleshoot a failing operation.

In general, No. 4, exporting data requires the DBA to have access to it (even if he doesn't actually look). It would in principle be possible to set up a process that uses end-to-end encryption to create an encrypted exported file without the person making the file ever having access, but by that point you've reduced it to a Push-Button process and there's no need for a DBA in the loop.

more ▼

answered Nov 21, 2012 at 06:54 PM

avatar image

15.6k 22 57 38

(comments are locked)
10|1200 characters needed characters left

You can give DB role to user: Public, db_denydatareader and db_denydatawriter.

more ▼

answered Nov 21, 2012 at 12:51 PM

avatar image

180 8 9 14

Check out the answers to the page that @ThomasRushton linked to. @TimothyAWiseman and I put on an insider threat clinic there... Denying datareader and datawriter might be enough to click the compliance checkbox, but they won't do a thing against a willfully malicious DBA. It goes back to Timothy's point: if you can't trust your DBA (or if regulations make it impossible), get a new DBA.

Nov 21, 2012 at 01:13 PM Kevin Feasel
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 07, 2012 at 07:14 AM

Seen: 1819 times

Last Updated: Nov 26, 2012 at 01:54 AM

Copyright 2018 Redgate Software. Privacy Policy