Oracle does
not come with the concept of schema level security. That is, one cannot create
user A and user B where user B is granted the privilege to do anything in user
A’s schema.
There is a
workaround though. One can create a database DDL trigger as follows:
CREATE OR
REPLACE
TRIGGER
prevent_alter_any
BEFORE ALTER ON
database
BEGIN
dbms_output.put_line('Checking..');
IF SYS_CONTEXT('USERENV','SESSION_USER') !=
'EDW' and ora_dict_obj_owner = 'EDW'
THEN
raise_application_error(-20901,'You do
not have the privileges to perform the action');
end if;
END
prevent_alter_any;
/
This will
prevent a user with the ALTER ANY table system privilege from being able to
alter objects in the EDW schema.
By juggling
the logic one can effectively allow ANY privileges on only a set of schemas for
a particular user.
Remember that this is still a workaround, and
preferably typical users should, in general, not have powerful system
privileges
Be the first to rate this post
- Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5