question

Abhishek_Jain31 avatar image
Abhishek_Jain31 asked

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) Regards, Abhishek
sqloracledbaexport-datamaintenance
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi, may i know what kind of restrictions you want for database?? Thanks Sumit Rana
0 Likes 0 ·
SumitRana avatar image
SumitRana answered
You can give DB role to user: Public, db_denydatareader and db_denydatawriter.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
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][1] @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. [1]: http://ask.sqlservercentral.com/questions/95308/how-to-restrict-dba-from-readwrite-but-still-can-p.html
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.