Privileges Management & Administration

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.
js4oracleAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
js4oracleAuthor Commented:
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
The Firewall Audit Checklist

Preparing for a firewall audit today is almost impossible.
AlgoSec, together with some of the largest global organizations and auditors, has created a checklist to follow when preparing for your firewall audit. Simplify risk mitigation while staying compliant all of the time!

 
sdstuberCommented:
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
 
js4oracleAuthor Commented:
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
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
js4oracleAuthor Commented:
great ... thanks folks, now confused, whom to assign points ... or 50/50 ;-)
0
 
sdstuberCommented:
your choice

I think both of us gave useful info so a split seems appropriate
but you can weight them however you want.
0
 
slightwv (䄆 Netminder) Commented:
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
 
js4oracleAuthor Commented:
good advice ... let's leave it open for others to compete, will close it in a later while then.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.