Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Access to Oracle Server 7.x or 8.x
On Wed, 16 Sep 1998 15:11:52 -0700, "George Dimopoulos"
<george_at_hivnet.ubc.ca> wrote:
>How does one restrict access to Oracle Instances?
>
>say users can only access Oracle Instance/db via the application
>and that is all.
>
>no sqlplus, no browsers, no odbc
>
>any ideas, thoughts would be greatly appreciated.
Grant only "CREATE SESSION" system privilege to the "restricted" users. No "CONNECT" role, no "RESOURCE" role, no nothing. That way user will be able to connect to database with any tool, but will be unable to do or see (allmost) anything on the database.
Now grant all privileges, needed to run your aplication to roles and lock this roles with passwords. Grant those roles to the "restricted" users as non-default roles and don't let them know the passwords for enabling the roles.
In your application make a module which will enable all required roles by providing passwords (use "SET ROLE appl_role IDENTIFIED BY appl_role_pwd;" commands) imidiately after user's successfull login.
That way user will be able to perform any action on your database only through your application. How you will manage the roles security (storing and changing the passwords for roles) is up to you and your application, but you'd better not store them in a readable format in registry, ini files and simmilar.
>please send to george_at_hivnet.ubc.ca
>and I will post the answers, comments
>
>thx in advance
HTH,
--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)