Database

How to hide primary user information from another user on Oracle

Sometimes you have to create user for your colleague/client that have privillege(s) to do select on primary user’s table. Unfortunately, your primary user’s password is not secure enough and you can’t change that password because many application connect to that database using that user, so you think you want to hide information about that primary users.

For example :

Your primary user/password is irwan/irwan (the password is easy to guess)
You want to create another user, let us say : hnawri, that can do query select to irwan.emp table.

Maybe, you usually do something like this

create user hnawri
identified by hn4wr1~!@
default tablespace TBS_DATA
temporary tablespace TEMP

grant connect to hnawri;
grant resource to hnawri;
grant create session to hnawri;

grant select on irwan.emp to hnawri;

So, when user hnawri to do query, he must execute this command

select * from irwan.emp

From that command, we can see that emp table is owned by user irwan.

What if your colleague/client trying to connect using user irwan and password irwan ?
If they do it, they can login into the system and access your primary user’s object. I think, you don’t want it to happend.

To avoid that situation. You may use this step

— Create interface user

create user interfaceuser
identified by h1d33nus3r!!!
default tablespace TBS_DATA
temporary tablespace TEMP;

grant connect to interfaceuser;
grant resource to
interfaceuser;
grant create session to
interfaceuser
grant select on irwan.emp to interfaceuser;
create synonym
interfaceuser.emp for irwan.emp;

— create user for your colleague/client

create user hnawri
identified by hn4wr1~!@
default tablespace TBS_DATA
temporary tablespace TEMP

grant connect to hnawri;
grant resource to hnawri;
grant create session to hnawri;
grant select on
interfaceuser.emp to hnawri;
create synonym hnawri.emp for
interfaceuser.emp;

If your colleague/client trying to connect using user interfaceuser, he/she must guess the interfaceuser‘s password correctly. I think it’s the not easy to guess password 🙂
And if your colleague/client check the synonym definition, he just see that his reference is interfaceuser (not the primary user irwan).

Advertisements

Discussion

No comments yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives

Twitter Updates

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

%d bloggers like this: