Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Privileges Management & Administration

Posted on 2011-09-16
11
Medium Priority
?
304 Views
Last Modified: 2012-05-12
Need some guidelines on privileges management & administration, having tough times dealing with developers, they are creating Public synonyms & granting all privileges (select, insert, update, delete) to public.

Highly appreciate if any one can share some solid & experienced thoughts about, how to effectively manage rights/privileges, without jeopardizing the security of data and also not on the expense of work bottleneck.

Thanks in advance.
0
Comment
Question by:js4oracle
  • 4
  • 4
  • 3
11 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 36548609
public isn't bad if the objects being created are truly public.

however if they aren't,  it really should be a simple list of who needs what.

If you have a application that logins as a single user then create 2 users,  one to hold the objects and one to use them.
Simply change all public synonyms to private for that "using" user and revoke all grants.

Then apply grants individually as they are needed.  - this will take some more thought, but it's essential.
If your developers don't appreciate the value of security then escalate to their management.


if you have an application where every user (or many of them) gets to log in as themselves
then things get a little trickier.  You "might" want to use public synonyms rather than create private synonyms for every user
For the privileges though, simply use a role (or set of roles) and do your grants to the roles rather than public.

As mentioned above, security does take some thought and hence extra time.
But, for simple stuff like synonyms and grants, it should be fairly quick.

The easiest way is to simply revoke all privileges. Then add grants as they are needed.   - the white list approach
Don't grant all then revoke each one individually as you find problems - the black list approach

white listing means each "oops" is an inconvenience while the user waits for the privilege and tries again
black listing means each "oops" resulted in data corruption/exposure/theft

which would you rather have? if your developers don't appreciate that, then again, raise it to management.



 




0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 36548655
I agree with everything above.  There really isn't much more to add.

I just wanted to reiterate the use of roles.  In our shop I even take things a step further in security.  I moved as much of the logic as possible into stored procedures.  This further masks my actual table layout and design.

It also helps with security since all the user can do is execute the procedure.  They cannot even select from the base tables.

Ask the developers this:  Do you mind if any database user can see your salary, address, EAP concerns?

If the data is important enough, you need to protect it.

Have management scan:
http://flowingdata.com/2011/06/13/largest-data-breaches-of-all-time/
0
 

Author Comment

by:js4oracle
ID: 36549134
thanks, but if using role, how about procedures, they cannot then execute via procs, righ.

And slightwv, how you did it using procedures, can you explain a little more.

thanks agian both of you.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 74

Expert Comment

by:sdstuber
ID: 36549184
roles can have execute on procedures, packages and functions.

privileges granted through roles don't apply "inside" those objects though.

however,  if you separate the owner schema from the user accounts, then it shouldn't matter, because the procedures will be owned by the same schema that owns the tables and other objects.
0
 

Author Comment

by:js4oracle
ID: 36549384
how about test (real owner), test_ro (read only role) & test_rw (read/write role) either roles or even schema, what you'll prefer role is better or schema is good.

if possible to explain a little in detail, that'll be very helpful too, as need to present this today in a meeting.
thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36549735
you need both

you need a schema to own the objects.

your users should not log in as that schema they will either have a shared oracle account (common with web apps) or they will have individual accounts.

in either case,  privileges will be granted to roles, those roles will then be granted to the user account(s)
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36549975
>>how you did it using procedures, can you explain a little more.

Say you have a table like: create table tab1(col1 char(1), col2 char(1));

instead of granting insert,delete on the table to some user, create a procedure and grant execute on it:

--just typed in, untested.
create or replace procedure myProc(inAction in varchar2, inCol1 in char, inCol2 in char)
is
begin
     case inAction
        when 'Insert' then
             insert into tab1 values(inCol1, inCol2);
        when 'Delete' then
             delete from tab1 where col=inCol1 and col2=inCol2;
      end case;
end;
/
0
 

Author Comment

by:js4oracle
ID: 36549985
great ... thanks folks, now confused, whom to assign points ... or 50/50 ;-)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36550001
your choice

I think both of us gave useful info so a split seems appropriate
but you can weight them however you want.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36550032
I'm fine with however you want to split.

You might also think about leaving it open for other Experts in different time zones.
0
 

Author Comment

by:js4oracle
ID: 36550129
good advice ... let's leave it open for others to compete, will close it in a later while then.
0

Featured Post

WatchGuard Case Study: NCR

With business operations for thousands of customers largely depending on the internal systems they support, NCR can’t afford to waste time or money on security products that are anything less than exceptional. That’s why they chose WatchGuard.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ransomware - Defeated! Client opened the wrong email and was attacked by Ransomware. I was able to use file recovery utilities to find shadow copies of the encrypted files and make a complete recovery.
This article is about my experience upgrading my consulting machine to Windows 10 Version 1709 (The Fall 2017 Creator Update)
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question