js4oracle
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
if possible to explain a little in detail, that'll be very helpful too, as need to present this today in a meeting.
thanks
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)
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)
>>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;
/
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;
/
ASKER
great ... thanks folks, now confused, whom to assign points ... or 50/50 ;-)
your choice
I think both of us gave useful info so a split seems appropriate
but you can weight them however you want.
I think both of us gave useful info so a split seems appropriate
but you can weight them however you want.
I'm fine with however you want to split.
You might also think about leaving it open for other Experts in different time zones.
You might also think about leaving it open for other Experts in different time zones.
ASKER
good advice ... let's leave it open for others to compete, will close it in a later while then.
ASKER
And slightwv, how you did it using procedures, can you explain a little more.
thanks agian both of you.