SMC Enterprise Experts
SMC ENTERPRISE Is a consulting firm that implements and develops Information Technology and ERP solutions. This is what our experts has to say.

Simulating schema level privileges on Oracle

June 1, 2010 17:04 by Herman

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
Tags: , ,
Categories:
Actions: E-mail | Permalink | Comments (0) | Comment RSSRSS comment feed

Related posts

Comments

Add comment


(Will show your Gravatar icon)  

  Country flag





Live preview

September 5. 2010 17:18