Recently, we had a requirement to create an installation of Siebel Tools that would run against the server database and be READ_ONLY with no ability to change the user that had WRITE privileges. The users of this machine knew the password for SADMIN, and also had the ability to create a new user that had READ/WRITE privileges.
Was meeting this requirement possible? Yes!
Here are the steps:
- Create in the Oracle database, an account READ_ONLY.
- Create a new role SSE_READONLY that was a copy of SSE_ROLE but only had select privileges. Note, you have to be careful and make sure that SSE_READONLY had access to ALL the tables that SSE_ROLE does.
- GRANT SSE_READONLY TO READ_ONLY
Here is a screenhot that shows you are able to log into Siebel Tools using READ_ONLY:
Here is a screenshot that shows you can view all objects:
Here is a screenshot that shows what happens if you attempt to update an object:
So, that is 50% of the task. Now, how do I prevent someone from logging in as a different user?
There are likely many ways of doing this, but I chose to use a database trigger:
CREATE OR REPLACE TRIGGER DENY_LOGIN
AFTER LOGON ON DATABASE
DECLARE
BEGIN
IF ( sys_context(‘USERENV’,’IP_ADDRESS’) <> ‘192.168.1.3’) and USER <> ‘READ_ONLY’
THEN
raise_application_error( -20001, ‘Connection not authorised on this workstation’ );
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
This is what happens if try to log in using SADMIN:
