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

When you get nervous about access rights

May 31, 2010 12:46 by Herman

Use this view as an example to create your own to see who has access to what:

 

create or replace view table_privs as
SELECT r.GRANTEE, r.owner, r.table_name, rle, granted_role, direct , r.grantor as role_grantor, d.grantor as direct_grantor, r.privilege, r.grantable
FROM

SELECT rp.GRANTEE, 'Y' as RLE, rp.GRANTED_ROLE, tp.OWNER, table_name, grantor, privilege, grantable
FROM DBA_TAB_PRIVS tp, dba_role_privs rp
WHERE
 tp.GRANTEE = rp.GRANTED_ROLE
and tp.OWNER IN ('EDW','EDW_P','SECURE')
AND tp.GRANTEE NOT IN (SELECT USERname FROM DBA_USERS)) r
,
(SELECT GRANTEE, 'Y' as DIRECT, owner, table_name, grantor, privilege, grantable 
FROM DBA_TAB_PRIVS WHERE OWNER IN ('EDW','EDW_P','SECURE')
AND GRANTEE IN (SELECT USERname FROM DBA_USERS)) d
WHERE
r.grantee  = d.grantee (+)
and
r.table_name = d.table_name (+)
and
r.privilege = d.privilege (+)
UNION
SELECT d.GRANTEE, d.owner, d.table_name, rle, granted_role, direct , d.grantor as rg, d.grantor as dg, d.privilege, d.grantable
FROM

SELECT rp.GRANTEE, 'Y' as RLE, rp.GRANTED_ROLE, tp.OWNER, table_name, grantor, privilege, grantable
FROM DBA_TAB_PRIVS tp, dba_role_privs rp
WHERE
 tp.GRANTEE = rp.GRANTED_ROLE
and tp.OWNER IN ('EDW','EDW_P','SECURE')
AND tp.GRANTEE NOT IN (SELECT USERname FROM DBA_USERS)) r
,
(SELECT GRANTEE, 'Y' as DIRECT, owner, table_name, grantor, privilege, grantable 
FROM DBA_TAB_PRIVS WHERE OWNER IN ('EDW','EDW_P','SECURE')
AND GRANTEE IN (SELECT USERname FROM DBA_USERS)) d
WHERE
d.grantee =  r.grantee (+)
and
 d.table_name = r.table_name (+)
and
d.privilege = r.privilege   (+)
ORDER BY GRANTEE, TABLE_NAME, PRIVILEGE;


Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5