Solved

Privileges Management & Administration

Posted on 2011-09-16
11
296 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 73

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 76

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
 
LVL 73

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 73

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 76

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 73

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Big data transfers via information superhighways require special attention and protection. Learn more about the IT-regulations of the country where your server is located. Analyze cloud providers and their encryption systems for safe data transit. S…
Find out what Office 365 Transport Rules are, how they work and their limitations managing Office 365 signatures.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now