Solved

Privileges Management & Administration

Posted on 2011-09-16
11
299 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 250 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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 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
Register Today - IoT Current and Future Threats

Are you prepared to protect your organization from current and future IoT Threats?  Join our Wi-Fi expert in episode three of our webinar series for a look at the current state of Wi-Fi IoT and what may lie ahead. Register for our live webinar on April 20th at 9 am PDT!

 
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 77

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 77

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Ransomware continues to grow in reach and sophistication, putting data everywhere at risk. Learn how to avoid being caught in its sinister clutches with these 11 key tips.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

685 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