Solved

Privileges Management & Administration

Posted on 2011-09-16
11
301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

What, When and Where - Security Threats from Q1

Join Corey Nachreiner, CTO, and Marc Laliberte, Information Security Threat Analyst, on July 26th as they explore their key findings from the first quarter of 2017.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Make the most of your online learning experience.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

626 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