No question at this time
DBA Top 10
1 B. Vroman 15600
2 M. Cadot 14000
3 A. Kavsek 9900
4 T. Boles 6800
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 4400
7 P. Wisse 2100
8 T. P 700
9 B. Derous 500
9 R. Wauben 500
9 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48195
Total active users1655
Act. users last 24h3
Act. users last hour0
Registered user hits last week271
Registered user hits last month1083
Go up

Database Vault implementation with SaaS application using trigger
Next thread: Is Toad free on Mac OSX ?
Prev thread: free tool to make database audit

Message Score Author Date
I am trying to implement database vault to secure ...... nilesh modi Feb 06, 2018, 16:30
Database Vault is made to prevent DBA or other p...... Michel Cadot Feb 06, 2018, 18:05
Thank you for responding Micheal, The objective...... nilesh modi Feb 06, 2018, 18:55
As I said, the best way is to implement through ...... Michel Cadot Feb 06, 2018, 19:02
By the way: "INSERT INTO sys.ddl_log" Neve...... Michel Cadot Feb 06, 2018, 19:10
Any insight on flow ? rough pseudo code to get an...... nilesh modi Feb 06, 2018, 19:11
app_user does NOT create a user, it calls a proc...... Michel Cadot Feb 06, 2018, 19:22
Any feedback? ... Michel Cadot Feb 08, 2018, 09:49

Follow up by mail Click here


Subject: Database Vault implementation with SaaS application using trigger
Author: nilesh modi, Canada
Date: Feb 06, 2018, 16:30, 108 days ago
Os info: OEL 6.0
Oracle info: 11.2.0.4
Message: I am trying to implement database vault to secure certain object as soon as user gets created. User(schema) for each client, is getting created using app_user. I am trying to capture user/schema name in trigger (trigger after create on database) using dictionary object and then execute dbms_macadm package to add some objects into Realm (which is already existing).
My question is :

IS there a limitation on creating trigger under dv_owner ?

The trigger should be owned by sys or dv_owner ? what privileges need to grant vice versa ?

Here is my sample code:

create or replace trigger dvcapture_user1
after create on database
DECLARE
oper ddl_log.operation%TYPE;
realm_name varchar2(30) := NULL;
user_name varchar2(30) := NULL;
BEGIN
IF dbms_standard.dictionary_obj_type = 'USER' then
INSERT INTO sys.ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,ora_dict_obj_type,ora_dict_obj_name, NULL, USER, SYSDATE FROM DUAL;
END IF;
SELECT NAME INTO realm_name FROM dvsys.dba_dv_realm where NAME LIKE 'HIP%';
select object_name into user_name from ddl_log;
IF realm_name = 'XXX' then
NULL;
ELSE
exec DVSYS.DBMS_MACADM.CREATE_REALM(realm_name => 'XXX', description => 'XXX',enabled => 'Y',audit_options => 3);
END IF;

FOR table_name in ('tbl1','tbl2','tbl3')
LOOP
EXEC DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(realm_name => 'XXX',object_owner => 'user_name',object_name => 'table_name',object_type => 'TABLE');
END LOOP;

END dvcapture_user1;
/
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Database Vault implementation with SaaS application using trigger
Author: Michel Cadot, France
Date: Feb 06, 2018, 18:05, 108 days ago
Message:
Database Vault is made to prevent DBA or other privileged users from accessing application data, is this really what you want to do?

Anyway, the best way to achieve things like that is to revoke anyone the privilege CREATE USER and create a procedure to do this and everything you want to do at the same time like checks or create associated objects or privileges and grant the EXECUTE privilege on this procedure to the allowed user.

Note that:
ELSE
exec DVSYS.DBMS_MACADM.CREATE_REALM(realm_name => 'XXX', description => 'XXX',enabled => 'Y',audit_options => 3);
END IF;
is incorrect, EXEC(UTE) is a SQL*Plus command NOT a PL/SQL statement; remove it.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Database Vault implementation with SaaS application using trigger
Author: nilesh modi, Canada
Date: Feb 06, 2018, 18:55, 108 days ago
Message: Thank you for responding Micheal,

The objective is to protect App_data from DBAs.
Now here is application flow:
App connects using app_user> create schema(with 50 objects in it) for each client using UI >

Now only 5 of those 50 objects have sensitive data and we want to protect it as soon as UI creates those schema. Also rest 45 objects need access to DBAs for troubleshooting customer/client issues.

The best way to achieve above to me, is by creating trigger under dvowner , which adds those 5 objects under already created REALM, as soon as schema created. Hence i took that approach

I am struggling to implement it with restricted privileges to dvowner and sys (mutually exclusive privileges)

if you can give your thoughts?

any other out of box approach also ?

I got your exec(ute) thing removed.. it was just quick sample code i was writing.

Nilesh
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Database Vault implementation with SaaS application using trigger
Author: Michel Cadot, France
Date: Feb 06, 2018, 19:02, 108 days ago
Message:
As I said, the best way is to implement through procedure NOT trigger.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Database Vault implementation with SaaS application using trigger
Author: Michel Cadot, France
Date: Feb 06, 2018, 19:10, 108 days ago
Message:
By the way:

"INSERT INTO sys.ddl_log"

Never do this, never creates any object in SYS schema, SYS is Oracle property and neither SYS nor SYS objects follow the common rules (like read consistency).

Read post http://www.orafaq.com/forum/mv/msg/164126/486117/#msg_486117 and subsequent.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Database Vault implementation with SaaS application using trigger
Author: nilesh modi, Canada
Date: Feb 06, 2018, 19:11, 108 days ago
Message: Any insight on flow ?
rough pseudo code to get an idea?

Trigger will be required in that case too , to capture schema_name being created by app_user?
This thing needs to remain out of application code as well.

management want it tobe a infrastructure piece to facilitate releases.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Database Vault implementation with SaaS application using trigger
Author: Michel Cadot, France
Date: Feb 06, 2018, 19:22, 108 days ago
Message:
app_user does NOT create a user, it calls a procedure that will create the user and other stuff you need.
So all code is outside the application.

In short, instead of doing
CREATE USER xxx...
the application does
EXEC CREATE_USER ('xxx',...);

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Database Vault implementation with SaaS application using trigger
Author: Michel Cadot, France
Date: Feb 08, 2018, 09:49, 106 days ago
Message:
Any feedback?

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here