sunny aleti, August 23, 2017 - 5:21 am UTC
Thank you. Can you please elaborate more on this? With data gurd whatever trigger we create in production Db also get replicated into replica (standby DB), so how do we differentiate different login types? We do not want any access to production for a user, while granting read only access to standby DB
Thx
August 23, 2017 - 6:53 am UTC
Yes, the SAME trigger exists in BOTH databases. For example
SQL> create or replace
2 trigger LOGIN_CHECK
3 after logon on database
4 declare
5 v_mode varchar2(30);
6 begin
7 select database_role
8 into v_mode
9 from v$database;
10
11 if v_mode = 'PRIMARY' then
12 raise_application_error(-20000,'NO YOU CANNOT USE THIS ACCOUNT ON PRODUCTION');
13 elsif v_mode = 'PHYSICAL STANDBY' then
14 --
15 -- any particular code I want to run when in standby mode
16 --
17 null;
18 end if;
19 end;
20 /
Trigger created.
SQL> conn scott/tiger
ERROR:
ORA-04088: error during execution of trigger 'MCDONAC.LOGIN_CHECK'
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: NO YOU CANNOT USE THIS ACCOUNT ON PRODUCTION
ORA-06512: at line 9
Warning: You are no longer connected to ORACLE.
sunny aleti, August 23, 2017 - 5:40 pm UTC
Thank you.
sunny aleti, August 23, 2017 - 5:43 pm UTC
Lastly, would this trigger create any performance issues (login latency) for other application logins? Do you foresee any drawbacks to this solution. I just want to make sure I'm prepared for any push packs from application support management
August 24, 2017 - 1:01 am UTC
Obviously depends how much logic you add to this trigger, but if you have an application that logs in so frequently that a login trigger causes problems, then you have a bigger root cause to investigate...ie, the volume of logins
how do we differentiating users in both DB's
sunny aleti, September 06, 2017 - 9:54 pm UTC
Sorry to comeback to this again. Can you please help me understand how are differentiating users in both DB's (Primary and standby)?
With the above logic I see that the trigger would restrict all users not just those on the standby database, since in the replication environment we have to grant access to objects on the primary database
September 07, 2017 - 2:25 am UTC
You have two database security roles:
ROLE1 - basic connect privileges only
create role role1;
grant connect to role1;
ROLE2 - read only on all appropriate tables
create role role2 identified by my_secret_password;
grant select on [tables] to role2;
Each user gets:
grant role1, role2 to U;
alter user U default role role1;
So by default, they have basic connect privs. If they want more, they need to know the password for role2 (which they dont).
You login trigger does:
if [i am the standby] then
execute immediate 'set role role2 identified by my_secret_password';
end if;
sunny aleti, September 07, 2017 - 2:31 am UTC
Thank you.
So how do we deal with power/admin users and process accounts that are OK to have full access to Primary DB?
September 07, 2017 - 2:45 am UTC
You just adjust what is in role1 for them, or have multiple roles.
*You* have total flexibility here.