Ygthkb

Is the percentage symbol a constant?

How to align the top of the text with the top of a figure produced by tikz in minipage

Is it really OK to use "because of"?

How can I deduce the power of a capacitor from its datasheet?

Where does documentation like business and software requirement spec docs fit in an agile project?

If I tried and failed to start my own business, how do I apply for a job without job experience?

Does it take energy to move something in a circle?

How can I prevent an oracle who can see into the past from knowing everything that has happened?

How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?

Can me and my friend spend the summer in Canada (6 weeks) at 16 years old without an adult?

Rigorous justification for non-relativistic QM perturbation theory assumptions?

XOR-free sets: Maximum density?

Why did Ylvis use "go" instead of "say" in phrases like "Dog goes 'woof'"?

How to not let the Identify spell spoil everything?

Allow console draw poker game to output more hands

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

Word for something that's always reliable, but never the best?

Was there a pre-determined arrangement for the division of Germany in case it surrendered before any Soviet forces entered its territory?

Why does 0.-5 evaluate to -5?

Possible issue with my W4 and tax return

Is it legal to point a domain to someone else's ip (website)?

How can I handle players killing my NPC outside of combat?

Why is it that Bernie Sanders is always called a "socialist"?

"Starve to death" Vs. "Starve to the point of death"

Restrict Oracle DB Login based on SQL Client being used

Restrict Oracle database schema from login using Toad/PLSQLdifferent login credentials based on userExport Oracle Database User data from clientUse LDAP for name look up with Oracle Instant ClientSQL Server trace based on client TCP portHow to restrict table access to a applicationIs it ever good practice to use a distinct database account for each user of an application?SQL Server 2016 Security - Restrict users from viewing a single databaseHow to run Oracle Client 10g from non Administrator account?Granting Read access to Replica database (via data guard) but need to restrict the same login in production DatabaseOracle Database Client 12c Product Components

1

We have an application in our organization that is based on Oracle Forms 11g (on Oracle Fusion Middleware/WLS). Users have access to the application through accounts created at the database (i.e. they exist in dba_users). Each user is assigned a specific role based on their area of work and they use the user id to log on to the application. The user may have read/write access to certain functionality in the application and for this their roles have the following permissions-

EXECUTE ANY LIBRARY   

SELECT  ANY SEQUENCE   

EXECUTE ANY TYPE      

EXECUTE ANY PROCEDURE 

UPDATE  ANY TABLE      

SELECT  ANY TABLE      

DELETE  ANY TABLE      

EXECUTE ANY INDEXTYPE 

INSERT  ANY TABLE  

Now we have some users requesting for SQL Client access (i.e. TNS settings) so that they can connect to the DB and perform queries for their research. These business users are ofcourse knowledgeable in SQL but we want to restrict their access based on the type of client they use to log on to the database. Any client other than the application itself, should restrict them to "read only".

Is there a way to achieve this?

oracle security

share|improve this question

asked Feb 26 '15 at 23:52

AnnjawnAnnjawn

12019

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Not possible. The name of the client is provided by the client itself - so the client can pretend to be sqlplus or sqldeveloper or annjawan. You have to restrict this through the user account, it's the only safe way.
  
  – a_horse_with_no_name
  Feb 27 '15 at 0:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  But we know the Application client's name. Can we restrict it like if not <app_client> then... perhaps through a BEFORE LOGON trigger? We also know the Machine Name which will always be the Application server name if the user is using the Application, that can be used as well?
  
  – Annjawn
  Feb 27 '15 at 0:09
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I can connect with a different client pretending to be <app_client> without any problems. You can also change the value that shows up in machine in v$session (those are simply connection properties for a JDBC connection for example). You might be able to check the IP address in a logon trigger, but I'm not sure about that. The only (really: the only) safe way to restrict access is through privileges on the user account.
  
  – a_horse_with_no_name
  Feb 27 '15 at 0:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, I figured that is the only safe way but revoking access privilege will mean revoke privileges from the application too, which we don't want to do. I don't think the users are that smart to be changing their connection properties ;) but the question is will a BEFORE LOGON trigger with revoke command work?
  
  – Annjawn
  Feb 27 '15 at 0:28
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The only way to do this is to create separate read-only users. A given user cannot have two seperate sets of grants.
  
  – Philᵀᴹ
  Feb 27 '15 at 7:59
  

  
  
  
  

add a comment | 

1

We have an application in our organization that is based on Oracle Forms 11g (on Oracle Fusion Middleware/WLS). Users have access to the application through accounts created at the database (i.e. they exist in dba_users). Each user is assigned a specific role based on their area of work and they use the user id to log on to the application. The user may have read/write access to certain functionality in the application and for this their roles have the following permissions-

EXECUTE ANY LIBRARY   

SELECT  ANY SEQUENCE   

EXECUTE ANY TYPE      

EXECUTE ANY PROCEDURE 

UPDATE  ANY TABLE      

SELECT  ANY TABLE      

DELETE  ANY TABLE      

EXECUTE ANY INDEXTYPE 

INSERT  ANY TABLE  

Now we have some users requesting for SQL Client access (i.e. TNS settings) so that they can connect to the DB and perform queries for their research. These business users are ofcourse knowledgeable in SQL but we want to restrict their access based on the type of client they use to log on to the database. Any client other than the application itself, should restrict them to "read only".

Is there a way to achieve this?

oracle security

share|improve this question

asked Feb 26 '15 at 23:52

AnnjawnAnnjawn

12019

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Not possible. The name of the client is provided by the client itself - so the client can pretend to be sqlplus or sqldeveloper or annjawan. You have to restrict this through the user account, it's the only safe way.
  
  – a_horse_with_no_name
  Feb 27 '15 at 0:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  But we know the Application client's name. Can we restrict it like if not <app_client> then... perhaps through a BEFORE LOGON trigger? We also know the Machine Name which will always be the Application server name if the user is using the Application, that can be used as well?
  
  – Annjawn
  Feb 27 '15 at 0:09
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I can connect with a different client pretending to be <app_client> without any problems. You can also change the value that shows up in machine in v$session (those are simply connection properties for a JDBC connection for example). You might be able to check the IP address in a logon trigger, but I'm not sure about that. The only (really: the only) safe way to restrict access is through privileges on the user account.
  
  – a_horse_with_no_name
  Feb 27 '15 at 0:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, I figured that is the only safe way but revoking access privilege will mean revoke privileges from the application too, which we don't want to do. I don't think the users are that smart to be changing their connection properties ;) but the question is will a BEFORE LOGON trigger with revoke command work?
  
  – Annjawn
  Feb 27 '15 at 0:28
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The only way to do this is to create separate read-only users. A given user cannot have two seperate sets of grants.
  
  – Philᵀᴹ
  Feb 27 '15 at 7:59
  

  
  
  
  

add a comment | 

We have an application in our organization that is based on Oracle Forms 11g (on Oracle Fusion Middleware/WLS). Users have access to the application through accounts created at the database (i.e. they exist in dba_users). Each user is assigned a specific role based on their area of work and they use the user id to log on to the application. The user may have read/write access to certain functionality in the application and for this their roles have the following permissions-

EXECUTE ANY LIBRARY   

SELECT  ANY SEQUENCE   

EXECUTE ANY TYPE      

EXECUTE ANY PROCEDURE 

UPDATE  ANY TABLE      

SELECT  ANY TABLE      

DELETE  ANY TABLE      

EXECUTE ANY INDEXTYPE 

INSERT  ANY TABLE  

Now we have some users requesting for SQL Client access (i.e. TNS settings) so that they can connect to the DB and perform queries for their research. These business users are ofcourse knowledgeable in SQL but we want to restrict their access based on the type of client they use to log on to the database. Any client other than the application itself, should restrict them to "read only".

Is there a way to achieve this?

oracle security

share|improve this question

asked Feb 26 '15 at 23:52

AnnjawnAnnjawn

12019

EXECUTE ANY LIBRARY   

SELECT  ANY SEQUENCE   

EXECUTE ANY TYPE      

EXECUTE ANY PROCEDURE 

UPDATE  ANY TABLE      

SELECT  ANY TABLE      

DELETE  ANY TABLE      

EXECUTE ANY INDEXTYPE 

INSERT  ANY TABLE  

share|improve this question

asked Feb 26 '15 at 23:52

AnnjawnAnnjawn

12019

share|improve this question

asked Feb 26 '15 at 23:52

AnnjawnAnnjawn

12019

asked Feb 26 '15 at 23:52

AnnjawnAnnjawn

12019

asked Feb 26 '15 at 23:52

AnnjawnAnnjawn

12019

1 Answer
1

active

oldest

votes

2

You can check it with a database trigger, for example:

CREATE ROLE ROLE_POWER_USER NOT IDENTIFIED;



CREATE OR REPLACE TRIGGER LOG_T_LOGON 

    AFTER LOGON ON DATABASE

DECLARE



    osUser VARCHAR2(30);

    machine VARCHAR2(100); 

    prog VARCHAR2(100)

    ip VARCHAR2(15);



BEGIN



    IF ora_login_user IS NULL THEN 

        RETURN;

    END IF;



    SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address

    INTO osUser, machine, prog, ip

    FROM V$SESSION 

    WHERE SID = SYS_CONTEXT('USERENV', 'SID');



    IF NOT DBMS_SESSION.IS_ROLE_ENABLED('ROLE_POWER_USER') THEN     

        IF LOWER(prog) <> 'your_application_name.exe' THEN

            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only the official client application');

        END IF;

    ELSE

        IF LOWER(prog) NOT IN ('sqlplus.exe', 'toad.exe') THEN

            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only SQL*Plus or TOAD for you private queries');

        END IF; 

    END IF;

    -- Successful login, continue as normal

END;

/

You can also check other conditions like IP-Address or the machine name.

SELECT privileges on tables and views you have to grant to the user or ROLE in the "classic" way. This trigger only prevents to logon to the database with certain tools.

Note, a user with System Privilege ADMINISTER DATABASE TRIGGER (for example DBA role, or SYS of course) never get the exception, i.e. they can logon to the database in any case and you cannot block this by the trigger.

Another note: This trigger is not 100% secure. For example you can simply make a copy of your local sqlplus.exe and name it your_application_name.exe. Then this trigger would allow to use it.

share|improve this answer

edited Feb 27 '15 at 12:08

answered Feb 27 '15 at 9:09

Wernfried DomscheitWernfried Domscheit

1,203611

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't need to make a copy of sqlplus.exe to connect using your_application_name.exe. In a JDBC client this is a simple configuration property (so is machine name or OS user)
  
  – a_horse_with_no_name
  Feb 27 '15 at 13:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @wernfried This trigger worked pretty nicely. We did some changes to it to include checking the user PROFILE and instead of checking the program we check the MACHINE from where the conncection is coming from. Our Application always connects from the App Server Machine id even when User logs on to the app from their PC and that server machine id will not change (ever). So that solves the program name problem.
  
  – Annjawn
  Mar 9 '15 at 19:52
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f93949%2frestrict-oracle-db-login-based-on-sql-client-being-used%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2

You can check it with a database trigger, for example:

CREATE ROLE ROLE_POWER_USER NOT IDENTIFIED;



CREATE OR REPLACE TRIGGER LOG_T_LOGON 

    AFTER LOGON ON DATABASE

DECLARE



    osUser VARCHAR2(30);

    machine VARCHAR2(100); 

    prog VARCHAR2(100)

    ip VARCHAR2(15);



BEGIN



    IF ora_login_user IS NULL THEN 

        RETURN;

    END IF;



    SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address

    INTO osUser, machine, prog, ip

    FROM V$SESSION 

    WHERE SID = SYS_CONTEXT('USERENV', 'SID');



    IF NOT DBMS_SESSION.IS_ROLE_ENABLED('ROLE_POWER_USER') THEN     

        IF LOWER(prog) <> 'your_application_name.exe' THEN

            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only the official client application');

        END IF;

    ELSE

        IF LOWER(prog) NOT IN ('sqlplus.exe', 'toad.exe') THEN

            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only SQL*Plus or TOAD for you private queries');

        END IF; 

    END IF;

    -- Successful login, continue as normal

END;

/

You can also check other conditions like IP-Address or the machine name.

SELECT privileges on tables and views you have to grant to the user or ROLE in the "classic" way. This trigger only prevents to logon to the database with certain tools.

Note, a user with System Privilege ADMINISTER DATABASE TRIGGER (for example DBA role, or SYS of course) never get the exception, i.e. they can logon to the database in any case and you cannot block this by the trigger.

Another note: This trigger is not 100% secure. For example you can simply make a copy of your local sqlplus.exe and name it your_application_name.exe. Then this trigger would allow to use it.

share|improve this answer

edited Feb 27 '15 at 12:08

answered Feb 27 '15 at 9:09

Wernfried DomscheitWernfried Domscheit

1,203611

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't need to make a copy of sqlplus.exe to connect using your_application_name.exe. In a JDBC client this is a simple configuration property (so is machine name or OS user)
  
  – a_horse_with_no_name
  Feb 27 '15 at 13:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @wernfried This trigger worked pretty nicely. We did some changes to it to include checking the user PROFILE and instead of checking the program we check the MACHINE from where the conncection is coming from. Our Application always connects from the App Server Machine id even when User logs on to the app from their PC and that server machine id will not change (ever). So that solves the program name problem.
  
  – Annjawn
  Mar 9 '15 at 19:52
  

  
  
  
  

add a comment | 

2

You can check it with a database trigger, for example:

CREATE ROLE ROLE_POWER_USER NOT IDENTIFIED;



CREATE OR REPLACE TRIGGER LOG_T_LOGON 

    AFTER LOGON ON DATABASE

DECLARE



    osUser VARCHAR2(30);

    machine VARCHAR2(100); 

    prog VARCHAR2(100)

    ip VARCHAR2(15);



BEGIN



    IF ora_login_user IS NULL THEN 

        RETURN;

    END IF;



    SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address

    INTO osUser, machine, prog, ip

    FROM V$SESSION 

    WHERE SID = SYS_CONTEXT('USERENV', 'SID');



    IF NOT DBMS_SESSION.IS_ROLE_ENABLED('ROLE_POWER_USER') THEN     

        IF LOWER(prog) <> 'your_application_name.exe' THEN

            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only the official client application');

        END IF;

    ELSE

        IF LOWER(prog) NOT IN ('sqlplus.exe', 'toad.exe') THEN

            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only SQL*Plus or TOAD for you private queries');

        END IF; 

    END IF;

    -- Successful login, continue as normal

END;

/

You can also check other conditions like IP-Address or the machine name.

SELECT privileges on tables and views you have to grant to the user or ROLE in the "classic" way. This trigger only prevents to logon to the database with certain tools.

Note, a user with System Privilege ADMINISTER DATABASE TRIGGER (for example DBA role, or SYS of course) never get the exception, i.e. they can logon to the database in any case and you cannot block this by the trigger.

Another note: This trigger is not 100% secure. For example you can simply make a copy of your local sqlplus.exe and name it your_application_name.exe. Then this trigger would allow to use it.

share|improve this answer

edited Feb 27 '15 at 12:08

answered Feb 27 '15 at 9:09

Wernfried DomscheitWernfried Domscheit

1,203611

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't need to make a copy of sqlplus.exe to connect using your_application_name.exe. In a JDBC client this is a simple configuration property (so is machine name or OS user)
  
  – a_horse_with_no_name
  Feb 27 '15 at 13:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @wernfried This trigger worked pretty nicely. We did some changes to it to include checking the user PROFILE and instead of checking the program we check the MACHINE from where the conncection is coming from. Our Application always connects from the App Server Machine id even when User logs on to the app from their PC and that server machine id will not change (ever). So that solves the program name problem.
  
  – Annjawn
  Mar 9 '15 at 19:52
  

  
  
  
  

add a comment | 

You can check it with a database trigger, for example:

CREATE ROLE ROLE_POWER_USER NOT IDENTIFIED;



CREATE OR REPLACE TRIGGER LOG_T_LOGON 

    AFTER LOGON ON DATABASE

DECLARE



    osUser VARCHAR2(30);

    machine VARCHAR2(100); 

    prog VARCHAR2(100)

    ip VARCHAR2(15);



BEGIN



    IF ora_login_user IS NULL THEN 

        RETURN;

    END IF;



    SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address

    INTO osUser, machine, prog, ip

    FROM V$SESSION 

    WHERE SID = SYS_CONTEXT('USERENV', 'SID');



    IF NOT DBMS_SESSION.IS_ROLE_ENABLED('ROLE_POWER_USER') THEN     

        IF LOWER(prog) <> 'your_application_name.exe' THEN

            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only the official client application');

        END IF;

    ELSE

        IF LOWER(prog) NOT IN ('sqlplus.exe', 'toad.exe') THEN

            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only SQL*Plus or TOAD for you private queries');

        END IF; 

    END IF;

    -- Successful login, continue as normal

END;

/

You can also check other conditions like IP-Address or the machine name.

SELECT privileges on tables and views you have to grant to the user or ROLE in the "classic" way. This trigger only prevents to logon to the database with certain tools.

Note, a user with System Privilege ADMINISTER DATABASE TRIGGER (for example DBA role, or SYS of course) never get the exception, i.e. they can logon to the database in any case and you cannot block this by the trigger.

Another note: This trigger is not 100% secure. For example you can simply make a copy of your local sqlplus.exe and name it your_application_name.exe. Then this trigger would allow to use it.

share|improve this answer

edited Feb 27 '15 at 12:08

answered Feb 27 '15 at 9:09

Wernfried DomscheitWernfried Domscheit

1,203611

CREATE ROLE ROLE_POWER_USER NOT IDENTIFIED;



CREATE OR REPLACE TRIGGER LOG_T_LOGON 

    AFTER LOGON ON DATABASE

DECLARE



    osUser VARCHAR2(30);

    machine VARCHAR2(100); 

    prog VARCHAR2(100)

    ip VARCHAR2(15);



BEGIN



    IF ora_login_user IS NULL THEN 

        RETURN;

    END IF;



    SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address

    INTO osUser, machine, prog, ip

    FROM V$SESSION 

    WHERE SID = SYS_CONTEXT('USERENV', 'SID');



    IF NOT DBMS_SESSION.IS_ROLE_ENABLED('ROLE_POWER_USER') THEN     

        IF LOWER(prog) <> 'your_application_name.exe' THEN

            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only the official client application');

        END IF;

    ELSE

        IF LOWER(prog) NOT IN ('sqlplus.exe', 'toad.exe') THEN

            RAISE_APPLICATION_ERROR(-20000, 'Logon denied: You must use only SQL*Plus or TOAD for you private queries');

        END IF; 

    END IF;

    -- Successful login, continue as normal

END;

/

share|improve this answer

edited Feb 27 '15 at 12:08

answered Feb 27 '15 at 9:09

Wernfried DomscheitWernfried Domscheit

1,203611

answered Feb 27 '15 at 9:09

Wernfried DomscheitWernfried Domscheit

1,203611

answered Feb 27 '15 at 9:09

Wernfried DomscheitWernfried Domscheit

1,203611