No question at this time
DBA Top 10
1 B. Vroman 15300
2 M. Cadot 12400
3 J. Schnackenberg 7400
4 T. Boles 6950
5 A. Kavsek 6900
6 M. Hidayathullah ... 3300
7 T. P 1300
8 G. Lambregts 1100
8 P. Wisse 1100
10 B. Derous 500
10 R. Wauben 500
10 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48245
Total active users1631
Act. users last 24h3
Act. users last hour0
Registered user hits last week190
Registered user hits last month742
Go up

grant privileges
Next thread: can i Delete archives generated from snapshot standby database
Prev thread: ORA-07445: exception encountered: core dump [audplsfailure()+54]

Message Score Author Date
Hi Guys I want to dynamically grant select on a...... Tso P May 15, 2018, 17:23
Without testing this and without more research I t...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Tim Boles May 16, 2018, 15:25
Hello Tso, I think that Tim has a point here......... Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts Bruno Vroman May 16, 2018, 19:02
Brilliant Guys!!! Thanks a lot for your replies...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Tso P May 21, 2018, 10:15

Follow up by mail Click here


Subject: grant privileges
Author: Tso P, South Africa
Date: May 15, 2018, 17:23, 96 days ago
Os info: solaris 10
Oracle info: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Message: Hi Guys

I want to dynamically grant select on a user2 whenever user1 create table(s).

I have trigger below:

create or replace TRIGGER grant_select_to_ohiuser
after CREATE ON user1.schema
declare
instr varchar2(255);
exec_instr number;
begin
IF ( ora_dict_obj_type in ('TABLE','VIEW') )
then
instr := 'execute immediate "grant select on ' ||ora_dict_obj_name ||' to user2";';
dbms_job.submit( exec_instr, replace(instr,'"','''') );
end IF;
end;

Now, whenever the table gets created by third user, i.e. user3, the trigger don't grant privileges.

Like if the table is created by third user, i.e. user3, for example when user user3 run create table user1.test (id number);

The trigger don't grant privileges to user2, its only when the table is created by user1 then trigger works perfect.

Please help !!!

Thanks in advance...

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

Subject: Re: grant privileges
Author: Tim Boles, United States
Date: May 16, 2018, 15:25, 95 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Without testing this and without more research I think....again "THINK"....the problem is going to be who actually executes the line within the trigger. And that might depend on who owns the trigger.


'execute immediate "grant select on ' ||ora_dict_obj_name ||' to user2";';


If it is user1 (because they own the table) I would think it would work. But if it is user3 (because they are creating the table) then probably need to do a little adjustment to your syntax and include the ora_dict_obj_owner...something like

'execute immediate "grant select on ' ||ora_dict_obj_owner||'.'||ora_dict_obj_name ||' to user2";';


I will have to check this out sometime when I have time.

Let us know if you figure it out.

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

Subject: Re: grant privileges
Author: Bruno Vroman, Belgium
Date: May 16, 2018, 19:02, 95 days ago
Score:   Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts
Message: Hello Tso,

I think that Tim has a point here...

Did you look at my old script (posted in 2010) Automatic near-real time maintenance of privileges :
  https://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=5888
You will see that indeed I grant to "SCOTT.something"...

Best regards,

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

Subject: Re: grant privileges
Author: Tso P, South Africa
Date: May 21, 2018, 10:15, 90 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Brilliant Guys!!!

Thanks a lot for your replies...

I used a database level trigger and used a "privileged account" to hold this database trigger.

As suggested here:

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:646423863863

@Bruno, i would still test your script as it looks interesting.

Much appreciated!!!

Thanks

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