and what is the sys privs that bypass trigger?
lsc, March 30, 2002 - 3:17 pm UTC
hi tom
since dba is a role and role is a set of system privileges, may I ask which is the privilege that makes DBA Role bypass logon trigger errors?
March 30, 2002 - 4:29 pm UTC
it is not a privilege, it is "burned into" the server. It is the fact that the name of a role the current user has is "DBA" that bypasses the ability of the trigger to prevent the login.
Just like when you grant DBA or RESOURCE to a user, they automagically get granted UNLIMITED TABLESPACE -- it's just because of the name of the role.
User rerstricted to logon from a particular terminal
mohammad toaha, March 30, 2002 - 10:22 pm UTC
I wanted to restrict a user say, 'SCOTT' to logon from a particular terminal say,'TDE23' and the trigger as follow but it did not work.
Tom,please tell me the mistake I have made here to solve my problem.
CREATE OR REPLACE TRIGGER afterlogon
AFTER LOGON on DATABASE
BEGIN
IF USERNAME='SCOTT' AND TERMINAL = 'TDE23' THEN
RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon from this
terminal');
END IF;
END;
/
Warning: Trigger created with compilation errors.
March 31, 2002 - 9:01 am UTC
username and terminal are neither declared nor set to a value.
see the example above for how to get "terminal".
then read up on sys_context to see what else you can get there (session user, current schema, ip address, etc).
USER is something else you might be interested in.
Parag
Parag, March 31, 2002 - 5:22 am UTC
Hi Tom :
Even i am also facing same kind of Problem. I wanted that , If specific user login to Oracle through " SQL prompt" & If User name = 'Parag' ( Database User) & if IP Address is '10.16.1.90' and if Terminal Name is 'Acc_Ter_16' then I wanted SQL to throw out tht User.
I can findout Terminal & IP Address through SYS_CONTEXT.USERENV Function.
Please helpme out .
Regards
- Parag
March 31, 2002 - 9:05 am UTC
Well, you cannot do it cause you cannot tell that the user is using sqlplus (and what the heck is so special about sqlplus? if you lock me out of that, I'll just compile cdemo6.c and be right back in).
you might think you can tell its sqlplus, but I'll just
c:> copy sqlplus.exe notsqlplus.exe
and you'll see that as notsqlplus.exe.
Anyway, if parag is a DBA, you cannot do this at all (as demonstrated above). If parag is not a DBA, you can do this to a degree but ONLY based on the username and ip_address-- both of which are available via sys_context.
restricted time
A reader, March 31, 2002 - 5:54 am UTC
is there any way to make log on to time for example from 7 am to 6 pm
March 31, 2002 - 9:09 am UTC
umm, sure.
if ( to_char( sysdate, 'hh24' ) not between 7 and 18 )
then
raise........
as I said tho, will not work for DBA, works for all others.
error
abeginner, March 31, 2002 - 6:13 am UTC
dear tom,
u remove the errors not by adding up more, rather subtracting from queries mostly.
March 31, 2002 - 9:11 am UTC
does not compute. doesn't even seem to make sense.
Your keyboard is broken too I see.
Parag
Parag, March 31, 2002 - 10:17 am UTC
Tom :
I think that u took me in wrong way. I was not mean to say that " Check that User has login through Oracle SQl* OR What" & u have put stress / focus on that aspect. My Dearest Tom ... I just wanted to check that , If anybody logon to Oracle with defined IP_Address & with particular TERMINAL_NAME , just thorwn them out. Don't allowed them to connect to Oracle.
Hope this time u will Interpret me in a right way.
But anyway Thanks a lot for a time.
" Tom is a Last Hope for any Oracle Porbs "
March 31, 2002 - 1:30 pm UTC
Well, if you cannot derive the answer to your question from the material above -- I don't know what else I can do for you.
I don't see how I could have interpreted:
f specific user login to Oracle
a) through " SQL prompt" &
b) If User name = 'Parag' ( Database User)
c) & if IP Address is '10.16.1.90' and
d) if Terminal Name is 'Acc_Ter_16'
then I wanted SQL to throw out tht User.
any differently then I did. However, if you only care about the ip_address and terminal_name (a big difference from those two PLUS sql*plus PLUS username) -- the answer is right above you. Use sys_context, use raise_application_error, expect it to work ONLY for non-dba people
don't do that please
abeginner, March 31, 2002 - 11:01 am UTC
dear Tom ,
what I meant was you provide even shorter solutions instead of adding up more and more to the problem queries you have been asked to.
March 31, 2002 - 1:39 pm UTC
Again -- I don't know what you are trying to say here.
You seem to be asking for something -- one star.
The entire solution to this question is here. The original question contains the entire answer. Their trigger is syntactically correct, it works. It cannot be used to prevent a DBA logon.
What more do you want?
DETAILED EXAMPLE
mohammad toaha, March 31, 2002 - 10:01 pm UTC
IF sys_context('userenv','Terminal') = 'HALOGENTEACH' THEN
Could you please explain the above line.What is 'HALOGENTEACH' here.
CREATE OR REPLACE TRIGGER afterlogon
AFTER LOGON on DATABASE
BEGIN
IF sys_context('SCOTT','DPDDIS3') = 'HELOGENTEACH' THEN
RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon from this
terminal');
END IF;
END;
/
Trigger created.
SQL> CONNECT SCOTT/TIGER
Connected.
So,it did not work as you see above.
My user is 'SCOTT' and Terminal is 'DPDDIS3'.Wouold you please write the trigger correctly for me.Please include ip_address also.
Thanks in advance.
April 01, 2002 - 7:44 am UTC
'HALOGENTEACH' is the terminal name the person who asked the question in the first place was looking for.
I do not know where you made sys_context('SCOTT','DPDDIS3') up from.
Ok, here we go. Say you have a user whose name will be in the variable l_username. You have a terminal whose designation will be in l_termainal. If the user islogging in from the terminal you do NOT want them to be using -- this is what you are looking for:
ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE TRIGGER afterlogon
2 AFTER LOGON on DATABASE
3 DECLARE
4 l_username varchar2(30) := 'SCOTT';
5 l_terminal varchar2(30) := 'pts/2';
6 BEGIN
7 IF (USER = l_username AND sys_context('userenv','Terminal') = l_terminal )
8 then
9 RAISE_APPLICATION_ERROR
10 (-20001, 'You are not allowed to logon from this terminal!!!');
11 END IF;
12 END;
13 /
Trigger created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> connect scott/tiger
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to logon from this terminal!!!
ORA-06512: at line 7
Warning: You are no longer connected to ORACLE.
ops$tkyte@ORA817DEV.US.ORACLE.COM> connect /
Connected.
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop trigger afterlogon;
Trigger dropped.
Now, as to why this is either hard or confusing -- I'm 100% at a loss since this is sooooo similar to the very very first example here.
Things for everyone to consider:
o look up the sys_context function, understand how to use it, when to use it and then use it
o understand that in 816 and up -- a logon trigger is NOT going to prevent a DBA from logging in and there is no way to do so. You need to NOT use DBA which is *just a role after all -- one that you can create*
o If you do not have a good mastery of PL/SQL -- playing with an on-logon trigger is a really horrible idea.
For Toaha
Ali, April 01, 2002 - 12:54 am UTC
Try this:
CREATE OR REPLACE TRIGGER afterlogon
AFTER LOGON on DATABASE
BEGIN
IF SYS_CONTEXT('USERENV','SESSION_USER') = 'SCOTT' AND
SYS_CONTEXT('USERENV','TERMINAL') = 'DPDDIS3' AND
SYS_CONTEXT('USERENV','IP_ADDRESS') = '123.123.123.123' THEN
RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon from this terminal');
END IF;
END;
/
Raise_application_error procedure in AFTER LOGON trigger
Paul Brewer, June 26, 2002 - 8:33 am UTC
Excellent advice, as ever. I notice though (8.1.7.0.0) that logon triggers also don't appear to fail if the IMP_FULL_DATABASE role is enabled.
Are there any other 'exemptions', besides the ones already mentioned?
Thanks,
Paul
June 26, 2002 - 9:26 am UTC
Interesting, just going to prove we all learn something new everyday ;)
So, I setup a test:
------------------- test.sql --------------------------------
set echo on
drop user a cascade;
create user a identified by a;
grant create session, &1 to a;
create trigger logon_trigger
after logon on database
begin
raise_application_error( -20001, 'No, you cannot log on' );
end;
/
connect a/a
insert into ops$tkyte.test values ( userenv('sessionid'), '&1', 'GRANTED' );
insert into ops$tkyte.test select userenv('sessionid'), role, 'IN EFFECT' from session_roles;
connect /
drop trigger logon_trigger;
exit
------------------------------------------------------------------------
and then issued:
select 'sqlplus / @test ' || role from dba_roles;
and made that be test.sh. I ran test.sh and found results like this:
ops$tkyte@ORA817DEV.US.ORACLE.COM> select sessionid, roles, msg
2 from test
3 order by sessionid, msg;
SESSIONID ROLES MSG
---------- ------------------------------ --------------------
15718 DBA GRANTED
15718 PLUSTRACE IN EFFECT
15718 DBA IN EFFECT
15718 SELECT_CATALOG_ROLE IN EFFECT
15718 EXECUTE_CATALOG_ROLE IN EFFECT
15718 DELETE_CATALOG_ROLE IN EFFECT
15718 EXP_FULL_DATABASE IN EFFECT
15718 IMP_FULL_DATABASE IN EFFECT
15718 JAVA_ADMIN IN EFFECT
15718 JAVA_DEPLOY IN EFFECT
15733 IMP_FULL_DATABASE GRANTED
15733 PLUSTRACE IN EFFECT
15733 IMP_FULL_DATABASE IN EFFECT
15733 SELECT_CATALOG_ROLE IN EFFECT
15733 EXECUTE_CATALOG_ROLE IN EFFECT
15 rows selected.
that shows that the imp_full_database and DBA roles seem to be the only "magic" ones.
Further suggestion
Jim Kennedy, June 26, 2002 - 11:23 am UTC
If you are going to do this in ohter than an example I would put the information in a table. That is NOT hard code the username etc. in the logon trigger but create a package witha function that accesses a table with the username and IP address for valid combinations. The function would return an idnication of okay to log on or not and then raise the error. That would make maintenence much simpler. That is if a user moved machines or you added more users then it would be a simple table insert or update as the case may be and not a rewrite of the trigger.
(Just have the trigger call the function in the package)
Security
Paul, June 28, 2002 - 10:45 am UTC
Tom,
What if you wanted (for security reasons) to limit access to DBAs to certain terminals (in case somebody get hold of a DBA password for example). If AFTER LOGON triggers don't work on DBAs, how could you achieve this?
Paul
June 28, 2002 - 12:44 pm UTC
Don't use the DBA role.
Create your own role "MY_DBA".
Grant whatever privs (i've ALWAYS thought that DBA and especially resource and connect have TOO MANY privs by default) you feel necessary to MY_DBA.
Grant MY_DBA to these DBA's.
Done....
logon trigger has to be created with SYS
A reader, August 15, 2002 - 3:28 pm UTC
Hi Tom
I have been doing some small tests and if I create a logon trigger as any user which is not SYS I get
create or replace trigger trace
after logon on database
begin
if user = 'SCOTT'
then
execute immediate 'alter session set sql_trace=true';
end if;
end;
/
*** SESSION ID:(11.173) 2002-08-15 21:27:30.752
Skipped error 604 during the execution of SYSTEM.TRACE
*** 2002-08-15 21:27:30.752
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 4
SCOTT has DBA role
If I create the trigger as SYS then everything is fine.
Also logon triggers when they are triggered under what usrname they are executed? The owner of trigger or the user that is login in?
August 15, 2002 - 7:25 pm UTC
read
</code>
http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>
you have alter ssession via a role, roles are not enabled in a trigger (or proc/func generally and never in a view) hence the alter session fails.
sys has alter session directly, not via a role
NEVER NEVER NEVER do anything as sys -- sys is special. Don't use SYS.
ahh same mistake again!
A reader, August 16, 2002 - 2:52 am UTC
Howly this is the role stuff again, I always get into same trap!
I did a small test to see under what user is the trigger executed when they are fired
I created a table called logon under scott´s schema, created a trigger to insert username and sysdate in logon everytime someone logins, I used
create or replace trigger logon
after logon on database
begin
insert into logon (user, sysdate);
end;
/
I have not created any synonyms for logon but logon table still gets inserted. So I guess the trigger is fired by SCOTT? Otherwise how can users who has not got insert privilege insert in scott´s table....
Ambiguously if that was the case under what user is alter session set sql_trace executed?
Or triggers works like procedures, packages and functions, the executer runs the trigger as trigger´s owner?
August 16, 2002 - 7:37 am UTC
The trigger runs with DEFINERS RIGHTS (just like a procedure). It runs with a schemaid = SCOTT's. Just like a procedure. (if you have my book "expert one on one", read the invokers/definers rights chapter)
The trigger is not FIRED by scott, the trigger is fired by the act of logging onto the database. The trigger runs in scotts privilege domain since scott wrote it -- just like a procedure would.
The system privilege that bypasses the trigger
Dave, October 30, 2002 - 10:43 am UTC
I tried creating a new role called my_dba by granting it all the same system priviliges and roles as dba, and I granted my_dba to a test user. The test user was still able to bypass the trigger. However, revoking the 'administer database trigger' system privilege worked.
October 31, 2002 - 8:09 pm UTC
Excellent -- thanks for the followup, I never dug down that deep on it to see exactly what priv it was.
what can go wrong if trigger is created under SYS?
A reader, November 29, 2002 - 8:22 am UTC
Hi
What can go wrong if stuffs are created under SYS?
We have 3 Oracle Premium Support Service On-Site DBA in our company and they create all logon triggers and procedures called under SYS!
November 29, 2002 - 10:28 am UTC
Things work differently for sys then for other users.
I do not personally approve of their technique.
There is never any reason to have to use sys. For the logon triggers - they should have created an account, granted just the bare privs needed to that account and created the objects there.
Same for the procedures.
It is less a matter of things going wrong -- and they can -- then it is "just the wrong way to do it".
SYS is special.
SYS is magic.
SYS should not be touched by us -- only by the database developers in Redwood Shores.
works on sysdba and sysoper accounts too?
A reader, November 29, 2002 - 8:24 am UTC
November 29, 2002 - 10:32 am UTC
two different "works" semantics here.
"works" in this context (this question) is that even if the trigger fails -- sysdba "works" (so it can log in, remove trigger, fix problem)
"works" in the context of the other question is that the trigger WON'T prevent sysdba from logging in, won't "work" to stop them. (if it did prevent them, then you could never fix a database with bad triggers - -triggers that are failing)
Same problem
raju, January 09, 2003 - 11:26 am UTC
Hi Tom,
I have a user for which I have created a logon trigger and it doesn't seem to fire. I understand that the if the users are granted the DBA role then the trigger doesn't fire, but in my case when I queried the dba_role_privs tables I find that the user has only CONNECT,RESOURCE granted. But still the trigger doesn't fire.
Any ideas where I should be looking at ? Also, which data dictionary view tells what are the privileges(not roles) granted to the user for eg : I might grant CREATE TABLE to the user, so there should be some view where I can query this info.
Thanks in advance.
January 09, 2003 - 1:31 pm UTC
the trigger fires, it'll just NOT FAIL.
What is the trigger?
How do you know it is not firing?
Like I give examples to show howto -- you need to do the same....
Tigger code
raju, January 09, 2003 - 3:45 pm UTC
OK .. here is the trigger which doesn't seem to work ...
SYSTEM@ORA92:MYCOMP> conn sys/sys as sysdba
Connected.
SYSTEM@ORA92:MYCOMP> drop trigger scott_on_logon;
Trigger dropped.
SYSTEM@ORA92:MYCOMP> create or replace trigger scott.scott_on_logon
2 after logon on scott.schema
3 begin
4 if sys_context('userenv','session_user') = 'SCOTT' then
5 RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon');
6 else
7 RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon from this terminal');
8 end if;
9 end;
10 /
Trigger created.
SYSTEM@ORA92:MYCOMP> conn scott/tiger@ora92
Connected.
Interestingly, the following works, the only difference being that I have not specified the schema name infront of the trigger name i,e the trigger is owned by SYS.
SYSTEM@ORA92:MYCOMP> conn sys/sys as sysdba
Connected.
SYSTEM@ORA92:MYCOMP> create or replace trigger scott_on_logon
2 after logon on scott.schema
3 begin
4 if sys_context('userenv','session_user') = 'SCOTT' then
5 RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon');
6 else
7 RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon from this terminal');
8 end if;
9 end;
10 /
Trigger created.
SYSTEM@ORA92:MYCOMP> conn scott/tiger@ora92
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to logon
ORA-06512: at line 3
Warning: You are no longer connected to ORACLE.
Please could you explain this behaviour.
Thanks
January 09, 2003 - 6:56 pm UTC
Looks like a safety precaution.
You cannot write a trigger that prevents YOU from logging on. The first trigger is owned by scott and scott cannot have a trigger that fails scott from logging in.
the second trigger is owned by SYS (bad idea, don't use sys for anthing - especially "testing"). have some other dba own it.
More on the topic
raju, January 09, 2003 - 11:32 pm UTC
Hi Tom,
Many thanks for the prompt response.
The problem is that in my actual database the trigger is trying to execute a PL/SQL block which is required for some context setting. The trigger is owned by the SCHEMA user for which we need the trigger to fire on LOGON. The trigger is not intended to prevent anyone from logging on.
OK.. the point I am trying to get here is ...are we saying that we should never create a LOGON trigger for a user which is owned by the same user ?
Please explain.
Thanks
January 10, 2003 - 7:17 am UTC
The trigger is in fact firing though. It is just that the raise application error is not preventing you from logging in - the server catches the error and deals with it.
If you look at your alert log, you'll see lots of error stacks in there -- for each time you tested this. The trigger fires, it just doesn't stop them from logging in.
You can most certainly create the trigger as the same person, it'll just never prevent you from logging in.
That works but ...
raju, January 10, 2003 - 8:59 am UTC
Hi Tom,
Thanks ! I checked the alert log file and I could find that the trigger did fire.
But, I am still struggling with the problem I have on the test database. The LOGON trigger simply calls a block of PL/SQL code. It is something like this :
create or replace trigger test.on_logon
after logon on test.schema
declare
l int;
begin
l = <schema_name>.<package_name>.proc_name
end;
/
Now, this ON LOGON trigger doesn't seem to fire. When I execute the above PL/SQL block, the PL/SQL block works OK as expected(i,e it sets the session context as expected), but this block doesn't seem to be executed when I logon.
The strange thing is that the same trigger is working on some database and not on the one where I am investigating. Is there any init.ora parameter etc which might cause this behaviour.
There is no ALERT or TRC file generated and hence don't understand how to investigate this further.
Can you throw some light on how I should investigate this further ?
Thanks again.
January 10, 2003 - 9:28 am UTC
is _system_trig_enabled set in the init.ora?
Spot on
raju, January 10, 2003 - 9:50 am UTC
Tom,
Thanks very very much. You are great.
Yes, you are right. The parameter is set to FALSE in the database where I was investigating.
How can I set this parameter to TRUE ?
Thanks again.
January 10, 2003 - 9:53 am UTC
just remove it from the init.ora and restart.
Oracle Version 8174
A reader, July 08, 2003 - 6:11 pm UTC
Hi Tom,
CREATE OR REPLACE TRIGGER TRIG_TEST
AFTER LOGON ON database
DECLARE
app_name VARCHAR2(48);
BEGIN
SELECT module INTO app_name FROM v$session
WHERE audsid = USERENV('sessionid');
insert into t1 (c1) values (app_name);
commit;
END;
select * from t1;
no rows selected
How can I get module info using logon trigger only for sqlplus.
July 09, 2003 - 7:34 am UTC
very doubtful the MODULE info will be set right after a logon, given that this field is populated BY the application *after* logging in.
you should at least see a row in there however:
ops$tkyte@ORA817DEV> drop table t1;
Table dropped.
ops$tkyte@ORA817DEV> create table t1( c1 varchar2(80) );
Table created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace trigger trig_test
2 after logon on database
3 declare
4 app_name varchar2(255);
5 begin
6 begin
7 select nvl( module, 'nothing up my sleeve' )
8 into app_name
9 from v$session where audsid = userenv('sessionid');
10 exception
11 when no_data_found then
12 app_name := 'no data found, sessionid = ' || userenv('sessionid');
13 end;
14 insert into t1 (c1) values (app_name);
15 end;
16 /
Trigger created.
ops$tkyte@ORA817DEV> show errors
No errors.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> @connect /
ops$tkyte@ORA817DEV> set termout off
ops$tkyte@ORA817DEV> REM GET afiedt.buf NOLIST
ops$tkyte@ORA817DEV> set termout on
ops$tkyte@ORA817DEV> select * from t1;
C1
--------------------------------------------------------------------------------
nothing up my sleeve
ops$tkyte@ORA817DEV>
check your alert log on the server to see if the trigger is failing for some reason
Commit in trigger
A reader, July 09, 2003 - 8:52 am UTC
Hi Tom,
The reader above is using a commit in the trigger. Is that allowed in an AFTER LOGON TRIGGER ???
Thanks
July 09, 2003 - 11:24 am UTC
yes.
ops$tkyte@ORA817DEV> create or replace trigger trig_test
2 after logon on database
3 declare
4 app_name varchar2(255);
5 begin
6 begin
7 select nvl( module, 'nothing up my sleeve' )
8 into app_name
9 from v$session where audsid = userenv('sessionid');
10 exception
11 when no_data_found then
12 app_name := 'no data found, sessionid = ' || userenv('sessionid');
13 end;
14 insert into t1 (c1) values (app_name);<b>
15 commit;</b>
16 end;
17 /
Trigger created.
ops$tkyte@ORA817DEV> show errors
No errors.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> @connect /
ops$tkyte@ORA817DEV> set termout off
ops$tkyte@ORA817DEV> REM GET afiedt.buf NOLIST
ops$tkyte@ORA817DEV> set termout on
ops$tkyte@ORA817DEV> select * from t1;
C1
--------------------------------------------------------------------------------
nothing up my sleeve
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> drop trigger trig_test;
Trigger dropped.
A reader, July 09, 2003 - 9:56 am UTC
Tom,
Is there a way to find out if the users are logging in thru sqlplus, as i noticed in the program column of v$session it stores the link wich doesnot display the full link as it is only 48.
Thanks.
July 09, 2003 - 11:36 am UTC
not reliably -- for the very reason you have said.
You can definitely DISABLE or reduce what they can do. see the sqlplus guide -- product user profile table.
A reader, July 09, 2003 - 4:19 pm UTC
Thanks!!
Thanks
A reader, August 25, 2003 - 12:36 am UTC
Wealth of information.. I have a question
Raj, December 05, 2003 - 4:40 pm UTC
Hi Tom,
Thanks for answering all the questions.
Database version 9iR2
Our application users are our oracle users and we had to give some of the rights to user to do some admin work like dropping user, creating user, changing password etc. which we are controlling through roles and all this is done using a procedure which checks that the user can delete only certain users having certain roles.
Now if that user connects through Sqlplus or some thirdparty tool then we have no control of stopping that user from deleting some of the important user.
Is there any way we can stop them from doing it.
December 06, 2003 - 8:53 am UTC
if you have all of the logic to ensure that the user cannot add/drop users in stored procedures (as you seem to be saying that is what you did) what does it matter of they run this procedure via your application at the push of a button or via sqlplus at the hit of an enter key?
they would be able to drop these "important users" in your application just as easily no?
or am i misunderstanding you here?
Sorry for not being clear
Raj, December 08, 2003 - 5:55 pm UTC
Hi Tom,
Thank you very much for your response.
Sorry for not being clear about the logic we use to decide whether to drop user or not :
In the proc parameter( oldUser, newUser, password)
1. First we check if the user who is trying to use this admin command has admin_role ( a role which has certain rights like drop, create user ).
2. Then we check if the user being dropped (oldUser) has a client_role ( a role given to application user. )
3. Then we check if the user being created (newUser) exists or not (should not exist)
if this is true then only we drop the user from procedure. so by using procedure one cannot delete any user.
But if the admin_role user logs on directly to SQLPLUS and try to drop any important user then he will be so I was wondering how to control that.
Thanks
Raj
December 09, 2003 - 6:02 am UTC
well, if a user with admin role logs into the application they can drop the user right?
so what if they do it
a) via the app
b) via sqlplus
???????
privileges should be to package owner not admin_role
Gary, December 09, 2003 - 5:13 pm UTC
"1. First we check if the user who is trying to use this admin command has
admin_role ( a role which has certain rights like drop, create user )."
The answer seems to be don't actually give admin_role CREATE/DROP USER privileges. You only need that privilege for the owner of the package (and it should be granted directly, not through a role, and the package should not be INVOKER rights).
December 10, 2003 - 2:46 am UTC
they are doing "do it yourself security". the owner of the package does have the right to drop anyone, they are doing a check that says "hey, if the person running this package has been granted this special role, we'll let them drop users too -- else not"
the role itself is not what lets them drop users or not.
Can you check the current user's roles in a trigger?
Nick, March 31, 2004 - 11:08 am UTC
I have a requirement to check a user's role before allowing update of
a column. Briefly, some roles are always permitted to update, others
are permitted only if the "OLD" value is NULL.
I first tried a trigger [Oracle 8.1.7, Windows]
(here is the interesting snip):
-------------------
-- some roles have priv to enter a new HC_NUMBER
if( :OLD.HC_NUMBER is NULL ) then
begin
select NULL into cDummy from DBA_ROLE_PRIVS p
where p.GRANTEE = USER
and p.GRANTED_ROLE in
('ENROLLSUPER','FINANCE','ENROLL_R','ENROLL_RW') and rownum=1 ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20100,'PERMISSION DENIED ON ENTRY OF HC
NUMBER ');
end;
else -- user is updating an existing hc number
.
.
-----------
but this won't compile:
PLS-00201: identifier 'SYS.DBA_ROLE_PRIVS' must be declared
I really wanted to use USER_ROL_PRIVS, but of course the trigger is
owned by someone other that the current user so that won't work.
Any ideas on a fix or a better way?
Thanks
March 31, 2004 - 11:27 am UTC
Interesting anomaly...
Connor McDonald, April 22, 2004 - 3:27 am UTC
You end up not being logged on, but having a session...
sys@db92> create or replace
2 trigger BLAH
3 after logon on database
4 begin
5 if user = 'SCOTT' then
6 raise_application_error(-20000,'NO NO NO');
7 end if;
8 end;
9 /
Trigger created.
(another session)
SQL> conn scott/tiger
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: NO NO NO
ORA-06512: at line 3
SQL> select * from tab;
SP2-0640: Not connected
(back to original session)
sys@db92> @sess
Enter value for sid:
SID SERIAL# USERNAME
---------- ---------- ------------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 48 SCOTT (18)
9 1
10 1
12 5 SYS (0)
11 rows selected.
so SCOTT is indeed "there"...
Cheers
Connor
What about system.
Bill, June 10, 2004 - 12:21 pm UTC
You have stated several times in this thread to never create anything in SYS, and I agree with you. However we create all our users, roles, triggers...etc in SYSTEM. Do you agree?
June 10, 2004 - 5:19 pm UTC
NOT IN THE LEAST.
system is Oracle's
system is not your account.
there is zero percent chance an established site would allow you to mess up system so thoroughly.
you want an account? create one. grant it ONLY what it needs (system is "fairly powerful")
Is it Correct?
Naveen, December 17, 2004 - 1:46 am UTC
Is it Correct if i say
RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon');
wont work for a user who has
ADMINISTER DATABASE TRIGGER privilege.
I think so.
Its not only 'DBA' that matters ..
December 17, 2004 - 7:57 am UTC
did you try it out in the way I would have? create a user, grant them create session and administer database whatever, test it?
Yes I did
Naveen, December 20, 2004 - 1:28 am UTC
Yes I did the same way..
Tested and got the result..
Its not raising the application error for the users with administer database privilege
when I revokes it, it works..
December 20, 2004 - 8:14 am UTC
there you go then
How to restrict logon from another session??
Neeraj, February 11, 2005 - 4:38 am UTC
Hi Tom,
I want to restrict the users from one session to log in to other session(by using connect). Is there any way I can know whether the user has logged from new session of SQL*Plus or through connect statement. which trigger should I use? Pls Help.
February 11, 2005 - 8:11 pm UTC
there is quite simply "no difference between the two"
connect is in fact:
a) logoff
b) logon
really -- you cannot discern between using connect in sqlplus and starting sqlplus fresh.
_system_trig_enabled
A reader, September 10, 2005 - 10:26 am UTC
Hi
I have a question about this parameter: _system_trig_enabled
When we run catalog and catproc do we need to set this to false? There is a note on Metalink which says so however if that´s the case then everytime we want to create a new database then we should set this to false as well but nowhere mentions it.
I have set this parameter only when I am patching or upgrading.
May you give some lights?
September 10, 2005 - 11:42 am UTC
note # please, so I can see it in context.
nore 149948.1
A reader, September 10, 2005 - 12:10 pm UTC
Hi
it's note 149948.1:
---------
When performing any of the following actions on an Oracle8i (or 9i) database:
o Installing a patch set
o Upgrading
o Downgrading
o Performing any other operation which requires catalog or catproc to be run (Except when we create a DB in 8i / 9i)
o Installing Java (initjvm)
o Any other action which runs scripts which modify objects owned by SYS
then you should set the hidden init.ora parameter _SYSTEM_TRIG_ENABLED to FALSE before starting the instance under Oracle8i (or 9i) to perform the respective maintenance operation unless the steps you are following
advise otherwise.
September 10, 2005 - 12:48 pm UTC
that note seems rather comprehensive doesn't it? what light needs be shed?
it tells you what to do.
it tells you how to do it.
it tells you why you want to do it.
In fact the note says explicitly "does not need to be set upon database creation"
.... (Except when we create a DB in 8i / 9i) .....
How can I achieve this?
Rich, August 08, 2006 - 4:44 pm UTC
Hi Tom,
We have several FORMS applications that are using one database. In maintenance period some applications must be made unavailable. We embedded a call to dbms_application_info on each application FORMS to populate client_info. Unfortunately, as stated in this forum, the info is not available during logon. How can we prevent users using a specific application to connect to the database and let the others in?
August 09, 2006 - 10:05 am UTC
rather than trying to make it "by magic", why not have the application itself see if it is wise to be connected.
kill the process
sagi, September 04, 2006 - 1:17 am UTC
Hi Tom,
creating our own DBA role is not feasible .
We are trying to kill a dba logoned using a logon trigger , under certain circumstances. we use 9i.
we are trying to submit job that killes the process, however, we encounter some problems with the quotation marks.
dbms_job.submit (jobid,'begin execute immediate 'alter system kill session '12,13' '; end;',sysdate,null);
however, we cant seem to get our quotations marks right . is it possible ?
September 04, 2006 - 8:53 am UTC
this is utterly confounding to me.
If (someone has DBA)
then
well, they are a dba, what's up with having to KILL their sessions?!?!?!?!
end if;
use two quotes to get one quote - but really before you do that, ask yourself "what the HECK are we thinking here, something is really whacked in our internal process if we need to kill a DBA session in an automated fashion"
Seems you should sit down the guy with DBA and tell them "look, don't do this, if you do this again - you will be removed from your position".
kill dba
sagi, September 04, 2006 - 9:33 am UTC
Hi,
we have more then 1500 insatnces (development)
we need to make sure that ops$oracle is not entered from our nt domain.
if a user creates a local user names oracle , well, he can connect as a dba.
we want to avoid this by killing the session.
using password files is not feasible in our env.
we can pinpoint this sessions still, I can get this think to work.
should go something like this
declare
jobno number;
begin
dbms_job.submit(jobno,'declare
ident varchar2(100);
sid number;
serial number;
begin
sid:=70;
serial :=29007;
execute immediate ''alter system kill session '':2,:3'' using sid,serial; commit;
end;'
,sysdate,null);
end;
however , I cant get it to work
regards,
sagsag
September 04, 2006 - 10:21 am UTC
1500 develop instances! good gosh - i mean, ouch.
but - you need to sort of lock down security here, it should not be possible for users to do this unless you PERMIT them to do this.
you cannot bind to DDL by the way.
I STRONGLY SUGGEST, not I'll just outright state it:
fix the problem, not the sympton. It should not be possible for a user to create an oracle user in your domain and just start logging in - they have "umm, too many privileges". You really need to secure your system.
Why? I'll just do this:
a) create the user oracle
b) log in and "grant dba to me;" OR
c) "grant dba to rouge_account_that_didnt_exist identified by pw;"
and you can kill me if you like, but it is too late.
don't write this trigger, it won't fix a darn thing.
kill dba
A reader, September 04, 2006 - 10:37 am UTC
Hi Tom,
I wish I could.I'm only the DBA :) .
I need a way to kill using a tigger , we could do that using a trigger calling a procedure that kills. however, I want to eliminate the need for a procedure.
I have no other way to control `5000 developers , some even a bit smart.
September 04, 2006 - 2:24 pm UTC
what I'm trying to tell you is
YOUR TRIGGER WON'T WORK, I NEED .01 seconds of access time to grant me or someone else what I need and then I won't be ops$oracle.
You have a massive, as in huge, as in I cannot believe it - security issue here in your windows setup.
Why don't you just start creating privileged accounts all over the place for other remote system services and really playing havoc with they system (forget Oracle for a second)
And you do have a way to control 5,000 other developers. If you have a policy in place that says "if you abuse this, if you do this operation, if you create an account like this - the ramifications are: termination - got it?"
Sorry, this is an environmental issue here, the problem is very much going to the foundation of the way your network was setup.
How to display only the error message
A reader, March 08, 2007 - 10:41 am UTC
Hi Tom,
Instead of displaying the entire error stack like this
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to logon from this terminal
ORA-06512: at line 2
Is it possible to display only the message
"You are not allowed to logon from this terminal" to the user ? - (using raise_application_error or by some other trick, in a after log on trigger)
thanks
March 08, 2007 - 11:12 am UTC
nope
Robert, March 27, 2007 - 1:16 am UTC
Hi Tom,
You said: "very doubtful the MODULE info will be set right after a logon, given that this field is populated
BY the application *after* logging in." way back in this thread.
I have an issue where I need to be able to stop access to a database unless the user is logging in via the application's client (ie not SQLPlus or Toad etc). As it is easy to spoof sqlpusw.exe (copy sqlplusw.exe app.exe) I need to use the Module to identify the internal name of the executable.
How can I use an after login trigger to perform this check?
March 27, 2007 - 9:37 am UTC
you cannot
now, describe your architecture - are you application server based, what is your application coded in, ....
maybe we can suggest something such as a secure application role...
and don't forget - anyone can set the module to anything they want - your application won't be the only thing able to set the module to something you are looking for...
Robert, March 27, 2007 - 8:41 pm UTC
Hi Tom,
The application is client/ server and is written in Delphi. It's an off the shelf package for managing trade expenditure and is therefore a hot topic with our auditors (hence what I'm trying to do). The database is version 9.2.0.6.
I'm a bit confused about your comment re Module. Here is an example:
> SELECT username,
program,
module
FROM v$session
where username='SCOTT';
Username Program MODULE
---------- --------------- ------------
SCOTT sqlplusw.exe SQL*Plus
(Copy sqlplusw.exe to naughty.exe and use naughty.exe to start another session as scott)
> /
Username Program MODULE
---------- ---------------- -----------
SCOTT naughty.exe SQL*Plus
SCOTT sqlplusw.exe SQL*Plus
So even though we've spoofed the program, the module remains the same. I'm sure it possible to get around this also, but if we could use module at login to ensure the correct executable was being used, this would go a long way to solving our problem. Am I missing something (aside from my original question)?
March 28, 2007 - 11:31 am UTC
ops$tkyte%ORA9IR2> select module from v$session
2 where sid = (select sid from v$mystat where rownum=1);
MODULE
------------------------------------------------
SQL*Plus
ops$tkyte%ORA9IR2> exec dbms_application_info.set_module( 'Hello','World');
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> select module from v$session
2 where sid = (select sid from v$mystat where rownum=1);
MODULE
------------------------------------------------
Hello
but, as I've said - during the LOGIN PROCESS, the module isn't "there", you need to be logged in to set the module
chicken and egg problem.
logon not permitted but session is there
Daud, June 06, 2007 - 1:10 am UTC
I created a logon trigger to prevent a user from logging in from a machine other than a specified one. It works ok except that when a logon is not allowed, the session is not terminated. (See the review from Connor McDonald above). Is this a bug and is there a workaround?
June 06, 2007 - 1:36 pm UTC
I doubt it is a "bug", you are not logged in - they would not classify it as a bug.
and, as soon as the client "goes away", so will the session - the connection - from the client.
System triggers
Alvin, May 14, 2008 - 6:29 pm UTC
I'm trying to write a system triggers that monitors for column changes like 'ALTER TABLE TEST DROP/ADD/MODIFY (column)';
I am trying to get the 'newly modified' column on user_tab_columns but the 'after system triggers' can't find it in USER_TAB_COLUMNS. I'm certain that it should be one of the x$ tables.
Or is there a better way of doing this ?
My test case is as follows. NOTE: the select * from chist_delta table list everything in USER_TAB_COLUMNS;
------
- CODE
------
create table chist_delta
(table_name varchar2(30)
, column_name varchar2(30)
, ddl_date date
, sqltext varchar2(2000)
);
create table test (a number);
create or replace trigger chist_delta
after alter on schema
declare
type c_type is REF CURSOR return USER_TAB_COLUMNS%ROWTYPE;
c c_type;
r USER_TAB_COLUMNS%ROWTYPE;
v_is_alter boolean;
v_is_drop boolean;
v_column_exist number;
v_column_text varchar2(100);
v_is_in_config number;
v_tabname varchar2(30);
v_str varchar2(2000);
begin
-- Is it a table ?
if ora_dict_obj_type='TABLE' then
-- 6.2
v_is_in_config :=0;
select count(*)
into v_is_in_config
from user_tables
where table_name in ('TEST')
and table_name=ora_dict_obj_name;
-- is it in the config_list
if v_is_in_config >0 then
-- Checks table name length against 30 char limit
if length(r.table_name) >=25 then
v_tabname:=substr(r.table_name,1,24)||'_CHIST';
else
v_tabname:= r.table_name||'_CHIST';
end if;
-- loop at all of the column name to see which one was modified,added,dropped.
open c for select *
from user_tab_columns
where table_name = ora_dict_obj_name;
loop
fetch c into r ;
exit when c%NOTFOUND;
--v_is_alter := ;
--v_is_drop := ;
-- DEBUG !
insert into chist_delta (sqltext) values (r.column_name);
if ora_is_alter_column (r.column_name) then
-- Check if column exist if it does then 'alter table modify ' if not then 'alter table ADD '
select count(*)
into v_column_exist
from user_tab_columns
where table_name=v_tabname
and column_name =r.column_name;
if (v_column_exist >0) then
v_column_text:='modify ';
else
v_column_text:='add ';
end if;
v_str :='alter table '||v_tabname ||v_column_text||';' ;
insert into chist_delta (table_name, column_name, ddl_date, sqltext)
values (r.table_name, r.column_name,sysdate, v_str);
elsif ora_is_drop_column (r.column_name) then
v_str := 'alter table '||v_tabname ||' drop column '||r.column_name||';';
insert into chist_delta (table_name, column_name, ddl_date, sqltext)
values (r.table_name, r.column_name,sysdate, v_str);
end if;
end loop;
end if ; -- is config
end if;
close c;
end;
/
sho err;
alter table test add (x number);
select sqltext from CHIST_DELTA;
May 16, 2008 - 12:16 pm UTC
your trigger is firing during the processing of the DDL, the information may or may not be accessible.
I don't know why you need the column name - seems you want to monitor in general - ALTERS OF TABLES. So, we can capture that pretty easily...
ops$tkyte%ORA10GR2> create or replace trigger chist_delta
2 after alter on schema
3 declare
4 l_sql long;
5 n number;
6 l_sql_text ora_name_list_t;
7 l_name varchar2(30);
8 begin
9
10 if ora_dict_obj_type='TABLE'
11 then
12 n := ora_sql_txt(l_sql_text);
13 for i in 1 .. n
14 loop
15 l_sql := l_sql || l_sql_text(i);
16 exit when length(l_sql) > 4000;
17 end loop;
18 l_name := ora_dict_obj_name;
19 insert into log (table_name, ddl_date, sqltext ) values ( l_name, systimestamp, substr(l_sql,1,4000) );
20 end if;
21 end;
22
23 /
Trigger created.
ops$tkyte%ORA10GR2> sho err;
No errors.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table test add (x number);
Table altered.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from log;
TABLE_NAME
------------------------------
DDL_DATE
---------------------------------------------------------------------------
SQLTEXT
-------------------------------------------------------------------------------
TEST
16-MAY-08 12.14.42.967537 PM
alter table test add (x number)
System triggers part2
Alvin, May 15, 2008 - 5:51 pm UTC
I'll re-phrase my question. :)
Which metadata table does oracle update when an 'alter table add/drop/modify' DDL is issued ? I need to catch which columns were changed via the system trigger (see above for code).
Apparently not its not in the USER_TAB_COLUMNS.
May 19, 2008 - 1:16 pm UTC
you do not have access to this information.. I cannot, will not, tell you to touch a SYS owned table (that'll not happen).
You can capture the DDL
The DDL has everything you asked for really, it is right there.
DDL Replication
Alvin, May 18, 2008 - 5:52 pm UTC
Thanks for the reply above !
The trigger is half of the solution that I've come up with. One part is to store the old value of the row in the history table (table trigger solution)the "THIS" part is to have the DDL change applied to the history table when the base table structure has changed (system trigger solution). Both scripts are only applied to a list of tables.
This trigger was created to monitor ddl (alter table add/modify/drop) changes and ultimately capture what columns were added/modified/dropped. And propagating the changes to their respective 'twin' tables which contains the old data (UNDO row-by-row plus stamp of when/who changed them).
The solution we're after is somewhere in between auditing and streams. I know the DDL replication can be done through streams, I just don't know it writing both trigger would be much simpler than the auditing+streams configuration or it could turn out to be more laborious than AUDIT+STREAMS.
May 19, 2008 - 4:15 pm UTC
but if you capture the DDL
all you need to do is change the table name in the DDL
and then apply it? What else do you need...
alter any trigger is the key privilege
sandro, July 25, 2008 - 9:35 am UTC
i faced the same problem, about raise_application_error and after logon trigger.
No matter (in 11.1.0.6) of dba or imp_full_database role.
I executed some test and if i create a user with only create session and alter any trigger, RAISE_APPLICATION_ERROR in AFTER LOGON trigger does not prevent user from logging in
July 29, 2008 - 10:16 am UTC
got full example, give test case (and review what has been granted to public)
behaviour explained on metalink
sandro, July 29, 2008 - 4:53 am UTC
Metalink Note:265012.1
explains the behaviour
July 29, 2008 - 3:58 pm UTC
Sandro,
I hope you understand why I said
... got full example, give test case (and review what has been granted to public) ....
I say that because 90% or more of the time people say:
I did X
I saw Y
and then I type it all in myself and write back:
No, I did X - see above, and we see Z, not Y.
and they write back and say "oh, we actually did X-prime, not X..."
so, when I see a question anymore, my gut reaction is "show me your example from start to finish"
at least that way, I can cut and paste your example, assert that I see what you saw....
grant object privilege to other schema
yao, March 25, 2010 - 10:32 pm UTC
hi,tom
i am the comp's dba so i worded with exp & imp from a sid to another with one_by_one "imp sid=../.." and later i face a question that users should grant object privileges to others each other ,i want to do the work once time ,so i write the block,just e.g:
begin
for i in (select owner,table_name,grantee,privilege from dba_tab_privs) loop
execute immediate 'grant '||i.privilege||' on '||i.owner||'.'||i.table_name||' to '||i.grantee;
end loop;
end;
/
but it show error ,only the owner of the object can grant object prilileges to others !
so dear Tom ,how i can do this once time other than connect to every user to "grant ... " many times
remember me for your family and tks
March 26, 2010 - 3:20 pm UTC
you give no version, no context.
I'm a DBA, just created myself:
sys%ORA10GR2> drop user "&U" cascade;
old 1: drop user "&U" cascade
new 1: drop user "OPS$TKYTE" cascade
User dropped.
sys%ORA10GR2> create user "&U" identified by foobar;
old 1: create user "&U" identified by foobar
new 1: create user "OPS$TKYTE" identified by foobar
User created.
sys%ORA10GR2> grant connect, dba to "&U";
old 1: grant connect, dba to "&U"
new 1: grant connect, dba to "OPS$TKYTE"
Grant succeeded.
sys%ORA10GR2> alter user "&U" default tablespace users;
old 1: alter user "&U" default tablespace users
new 1: alter user "OPS$TKYTE" default tablespace users
User altered.
sys%ORA10GR2> grant select any dictionary to "&U";
old 1: grant select any dictionary to "&U"
new 1: grant select any dictionary to "OPS$TKYTE"
Grant succeeded.
sys%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> set echo off
ops$tkyte%ORA10GR2> grant select on scott.emp to a;
Grant succeeded.
So, a bit more "information" please.
is Alert Log Updated
sergy2, February 14, 2013 - 2:50 pm UTC
Hi tom,
is the raise_application_error (-2001) writes a line in the alert log file ? What is the condition to write in the alert log ? (On a development Environment, i haven't message in alert.log after trigger logon on database, but in the production environment i have a message in alert log ; my wishe is to have no message in alert log)
Thanks tom for your help
February 14, 2013 - 3:57 pm UTC
no, raise application error doesn't write to the alert log.
however, when an logon trigger fails *we* write to the alert log. That just happens by design.
if you are having a logon trigger fail with an unhandled exception - you WANT to be notified about it so you can fix it. It should not be happening. If it is - fix the cause of the failure. don't try to supress the messenger...
Logon Trigger and Set Roles
Russell, May 03, 2013 - 7:22 am UTC
Hi Tom,
Currently we have 1 main DB schema, and a number of reporting users created with individual schemas.
We are moving to an environment that is a shared database, with schemas created per application in the DB (using Oracle 10G). As such, the DBA group do not want to create individual reporting schemas, and have suggested using a logon trigger, that authenticates users based on their OS_USER value captured in sys_context('USERENV','OS_USER').
Currently the different users have different table permissions, which are granted through roles.
I have created triggers, secure application roles and authentication packages. The trigger works when the reporting user logs on. The trigger includes the call to set the roles, but does do anything when the trigger is fired.
The role is set, if I manually run the SET_ROLES procedure after a user logs on. I am trying to automatically set the roles in the trigger when the user logs on (Some of our users are trying to run a Crystal Report, and I am unaware of a method to run the set_roles procedure when they logon).
I have created the following objects successfully, and there are no compilation errors presented. There are also no errors displayed when the reporting user logs in.
Can you advise if there are any problems with creating Secure Application Roles through a Logon Trigger please?
If we cannot use the Logon trigger to set these roles, do you have any other methods we could try to do the same thing?
See the code sample below:
CREATE USER MAIN_DB
IDENTIFIED BY Testing2
ACCOUNT UNLOCK;
GRANT CONNECT, CREATE TABLE TO MAIN_DB;
CREATE USER REPORT_USER
IDENTIFIED BY Testing1
ACCOUNT UNLOCK;
GRANT CONNECT TO REPORT_USER;
GRANT CRYSTAL_USER TO REPORT_USER;
ALTER USER REPORT_USER DEFAULT ROLE NONE;
create table MAIN_DB.ALLOWED_ACCESS (USERID varchar2(30), PERMISSIONS VARCHAR2(60) );
GRANT SELECT ON MAIN_DB.ALLOWED_ACCESS TO REPORT_USER;
-- Sample user in authentication table
INSERT INTO MAIN_DB.ALLOWED_ACCESS VALUES ('TOM_KYTE','CRYSTAL_USER');
-- Table used for logging of when users connect
CREATE TABLE MAIN_DB.LOGGING_TEST (OS_USER VARCHAR2(20), PERMISSION VARCHAR2(20), S_DATE DATE);
GRANT INSERT, SELECT on MAIN_DB.LOGGING_TEST TO REPORT_USER;
-- Function to authenticate users. Checking their OS_USER value against entries in the table
CREATE OR REPLACE FUNCTION MySecurityCheck RETURN BOOLEAN
AS
l_count number;
BEGIN
select count(*) into l_count
from dual
where exists ( select NULL
from MAIN_DB.ALLOWED_ACCESS
where UPPER(userid) = (select UPPER(sys_context('USERENV','OS_USER')) from dual
)
);
if (l_count = 0)
then
return FALSE;
else
return TRUE;
end IF;
END;
GRANT EXECUTE ON MySecurityCheck TO PUBLIC;
-- Package used to set various permissions when user has been authenticated. Change the various schemas for relevant environment
CREATE OR REPLACE PACKAGE SET_ROLES
AUTHID CURRENT_USER
IS
PROCEDURE ALLOWED_ROLES ;
END;
CREATE OR REPLACE PACKAGE BODY SET_ROLES IS
PROCEDURE ALLOWED_ROLES
IS
v_ALLOWED_ROLE varchar2(20);
BEGIN
/* authentication check here */
if (MySecurityCheck = TRUE)
then
select PERMISSIONS into v_ALLOWED_ROLE
from MAIN_DB.ALLOWED_ACCESS
where UPPER(userid) = UPPER(sys_context('USERENV','OS_USER'));
if v_ALLOWED_ROLE is not NULL
then
dbms_session.set_role(v_ALLOWED_ROLE);
INSERT INTO MAIN_DB.LOGGING_TEST VALUES( UPPER(sys_context('USERENV','OS_USER')) ,v_ALLOWED_ROLE,SYSDATE);
commit;
dbms_session.set_role(v_ALLOWED_ROLE);
EXECUTE IMMEDIATE 'SET ROLE ' || v_ALLOWED_ROLE;
end if;
end if;
END;
END;
-- Create Secure Application Roles – which will use SET_ROLES package to grant relevant access
CREATE ROLE CRYSTAL_USER IDENTIFIED USING SET_ROLES;
-- Create LOGON trigger. Checks user is authenticated, and calls package to establish roles.
CREATE OR REPLACE TRIGGER ALLOWED_USERS AFTER LOGON
ON REPORT_USER.SCHEMA
DECLARE
l_count number;
v_PERMISSION varchar2(20);
begin
if (MySecurityCheck = TRUE)
then
MAIN_DB.SET_ROLES.ALLOWED_ROLES; -- REPLACE MAIN_DB SCHEMA with DATABASE APPLICATION SCHEMA
else
RAISE_APPLICATION_ERROR(-20001, 'Unknown user');
end if;
end;
Thanks.
Russell
May 06, 2013 - 7:00 pm UTC
because the DBA's don't want to create users - you'll just let everyone *share* a password?????? seriously? for real?
and you'll just query up os_user? and trust that (I can make my os user anything I want - I'll just create a network bridged virtual machine and create some users....)
this seems like a lot of work for no security to be in place..... for auditing, security, ease of implementation - well, for everything in the world - I'd be using honest to goodness USERS with roles granted to them. come up with a naming convention so they are easy to see - start their names with CR_ or something like that. Just because they current have "a schema per application" doesn't mean they cannot support using the database fully for anything else.
taking your example down to the barest minimum:
ops$tkyte%ORA11GR2> create user a identified by a;
User created.
ops$tkyte%ORA11GR2> grant create session to a;
Grant succeeded.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table log ( id number primary key, msg varchar2(80) ) organization index;
Table created.
ops$tkyte%ORA11GR2> create sequence s;
Sequence created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE OR REPLACE procedure SET_ROLES
2 authid current_user
3 as
4 pragma autonomous_transaction;
5 begin
6 INSERT INTO log VALUES( s.nextval, 'setting role ' || sys_context( 'userenv', 'session_user' ) );
7 commit;
8 dbms_session.set_role( 'test_role' );
9 INSERT INTO log VALUES( s.nextval, 'we set the role ' || sys_context( 'userenv', 'session_user' ) );
10 commit;
11 END;
12 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE ROLE test_role IDENTIFIED USING SET_ROLES;
Role created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> GRANT test_role TO a;
Grant succeeded.
ops$tkyte%ORA11GR2> alter user a default role none;
User altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> select * from session_roles;
no rows selected
a%ORA11GR2>
a%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE OR REPLACE TRIGGER ALLOWED_USERS AFTER LOGON
2 ON A.SCHEMA
3 begin
4 SET_ROLES;
5 end;
6 /
Trigger created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect a/a
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-06565: cannot execute SET ROLE from within stored procedure
ORA-06512: at "SYS.DBMS_SESSION", line 143
ORA-06512: at "OPS$TKYTE.SET_ROLES", line 8
ORA-06512: at line 2
Warning: You are no longer connected to ORACLE.
dbms_session.set_role cannot be invoked from a trigger (the error message is off a bit there...)
see support note: 106140.1 for many further reasons why this won't work (the trigger is always definers rights too, it would be firing with the wrong authentication schema).
In short - trigger <> working in this case.
My only suggestion is....
use actual users with their own private passwords and erase all of this code..
SERHAT, March 17, 2021 - 1:42 pm UTC
If you want to prohibit connection any user;
1. Build a customized DBA role
2. Exclude below system privilege and roles from your customized dba role
SYSTEM PRIVILEGE
ADMINISTER DATABASE TRIGGER
ROLES
DATAPUMP_IMP_FULL_DATABASE
IMP_FULL_DATABASE
I hope it helps you.
Serhat.