This discussion is archived
9 Replies Latest reply: Feb 21, 2013 7:55 AM by TurlochO'Tierney RSS

SQLDeveloper - change proposal

user571349 Newbie
Currently Being Moderated
Hello Everyone,

for the past couple of years my colleagues and I have been using SQLDeloper. As a DBA I normally I use TOAD and SQLPlus to perform my work. SQLDeveloper seems a fine tool for my developent teams, however some functions in SQLDeveloper always put me off. The same also applies to TOAD as well. The main problem is running scripts.
When I use SQLPLUS I often use the parameter prompt &1 and &&1, it seems that both SQLDeveloper and TOAD fail to graps that concept, so if I want to run the same script I use in SQLPLUS where I only put my parameter value once, SQLDeveloper will ask me every time the &1 and &&1 is in the script.
Can this be enhanced?

The next subject is security. It so happens regular users are now downloading SQLDeveloper and use it. Because there is no installation required anyone can copy this software and run with their own account having update privileges bypassing the application. I will not mention runnig full scans on large tables, boy, everybody loves those, why doesn't every human being understand what an optimizer is and how to make use of it?
So far there is no way I can force stopping them (although Windows 7 will not allow putting stuff into C:\Program FIles), as I would have to enable a trigger rejecting the SQLDeveloper.exe as a tool, it could be complex, I've tried it, wasted a lot of time. I would have to reject all users or write a complex procedure so Developers could use it, but not users, exceptions, exceptions, etc. I think this is a serious flaw. Oracle should put some effort into this subject. Access to downloading SQLDeveloper should be restricted.
In order to use SQLPLUS they have to ask admins to install it, as normally they don't have admin rights, the same should apply to tools like SQLDeveloper. And we don't install SQPLUS by default. They however do get SQL*NET and use a tool called GOLDEN which is a READ-ONLY tool and no updates in general are possible.

I hope I raised some interesting points.

Thank you for any comments.
Regards.
Richard

Edited by: user571349 on 20-Feb-2013 01:11
  • 1. Re: SQLDeveloper - change proposal
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    often use the parameter prompt &1 and &&1, it seems that both SQLDeveloper fail to graps that concept
    Can you start a new thread and submit an example showing the bad behavior? We should be mirroring the SQL*Plus feature, if not, it's a bug. There was a bug we just fixed in this area for the next release but it was concerning updates to a & not being honored vs unnecessary prompting.
    So far there is no way I can force stopping them
    I don't understand. You want to control access to the database by restricting access to database client tools? This should be controlled via database security. Don't give the folks database accounts. If you must for them to use 'official' applications, then use triggers to disconnect sessions where they are using applications outside your approved list. If they don't honor your rules, then freeze their db access and/or contact your HR department.

    We could make it harder to access SQL Developer, but instead I prefer to make it easier to access. As soon as you throw up a roadblock, a smart person will figure out how to put some software on a USB, or they'll write their own java or .NET app to get in, or, or, or.
    a READ-ONLY tool and no updates in general are possible.
    You should control this via database privileges, not access to 'read only' tools.

    I understand this is a more of a subjective, and opinion filled debate, however I strongly believe that maintaining database security and performance via accessing database tools vs controlling it via database access, privileges, and resource groups (that could fix your Cartesian join issue btw) is the absolutely wrong way to go.
  • 2. Re: SQLDeveloper - change proposal
    rp0428 Guru
    Currently Being Moderated
    >
    It so happens regular users are now downloading SQLDeveloper and use it. Because there is no installation required anyone can copy this software and run with their own account having update privileges bypassing the application.
    >
    Yes - the world is changing. Tools that used to be proprietary and expensive and now inexpensive or even open-source. Anyone has access to them.

    You didn't mention C, C++, Java or the many other 'tools' that can be, and have been, used to access the DB and which do not require installation.

    Any security issues you have in your organization exist totally independently of the tool being used to access the DB. The tool does not convey priviliges to users; that is controlled strictly by the way your DB is installed and configured. If you believe you have a security issue with your users accessing your DB outside your app you should IMMEDIATELY report this to your management.
    >
    I will not mention runnig full scans on large tables, boy, everybody loves those, why doesn't every human being understand what an optimizer is and how to make use of it?
    >
    What does that have to do with Sql Developer?
    >
    So far there is no way I can force stopping them (although Windows 7 will not allow putting stuff into C:\Program FIles), as I would have to enable a trigger rejecting the SQLDeveloper.exe as a tool, it could be complex, I've tried it, wasted a lot of time. I would have to reject all users or write a complex procedure so Developers could use it, but not users, exceptions, exceptions, etc. I think this is a serious flaw. Oracle should put some effort into this subject.
    >
    Oracle HAS put 'some effort into this subject; a LOT of effort. You have several options for managing resource use with Oracle DBs. A LOGON trigger is one option, user and resource profiles are options, proper use of ROLES and GRANTS provide even more options.

    If you want users to ONLY connect thru your app then you can have your app control the connection environment and ROLE assignments that those users can use. And if those user accounts are not connecting via your app you can easily restrict access using a simple LOGON trigger. That trigger doesn't need to reject a specific tool; it can ALLOW only your app.
    >
    Access to downloading SQLDeveloper should be restricted.
    >
    That won't solve any security issue that exists in your database. Ask your users not to use sql developer and if they use it disable their database accounts.
    >
    In order to use SQLPLUS they have to ask admins to install it, as normally they don't have admin rights, the same should apply to tools like SQLDeveloper. And we don't install SQPLUS by default. They however do get SQL*NET and use a tool called GOLDEN which is a READ-ONLY tool and no updates in general are possible.
    >
    That is an issue for your desktop security group to deal with. Depending on your OS there are ways to prevent the use of resources that are needed to run the applications.

    The only relevant issue you have raised is related to the security of your database and connections.

    I suggest you create a new thread in the Database - General Questions forum
    General Questions

    There are plenty of experts in that forum that can help you with your security issue. If you search that forum (and the SQL and PL/SQL forum) you will find that we have answered, and reanswered, many questions similar to yours over the years.

    When you post provide your 4 digit Oracle version and the full specifics of your issue including your current configuration (shared server/dedicated server), types of user access that are needed, what resources you would like to control and what client toolsl are being used.

    You will get prompt feedback about your issue.
  • 3. Re: SQLDeveloper - change proposal
    user571349 Newbie
    Currently Being Moderated
    Hi,

    let me respond to the fist problem with the &1 parameter substitution.

    I am using the latest version I believe which is 3.2.20.09 BUild 0887.

    Here is my script in the .SQL file which I open in SQLDeveloper:
    column owner format a18
    column name format a31
    column ts format a15
    column type format a12
    column t format 999999999
    column Kbytes format 999,999,999
    column nbrext format 9999999
    column max_extents format 9999999999
    
    set lines 118
    set pause off
    set verify off
    
    break on report
    compute sum of Kbytes on report
    column alloc  format a10
    column extman format a10
    column contents format a10
    
    select tablespace_name tspace,
           allocation_type alloc,
           extent_management extman,
           contents,
           initial_extent/1024 "Initial KB",
           next_extent/1024 "Next KB",
           min_extents,
           status
    from
     dba_tablespaces
    where tablespace_name like upper('%&1')
    order by 2
    /
    
    column tablespace_name format a30
    column file_name       format a40
    
    select tablespace_name, file_name  from dba_data_files
    where tablespace_name like upper('%&&1');
    
    select s.owner owner,
         s.segment_name name,
         s.tablespace_name ts,
         s.segment_type type,
    --     s.max_extents max_extents,
         sum(s.bytes/1024) Kbytes,
         sum(s.extents) nbrext
    from dba_segments s
    where s.tablespace_name like upper('%&&1')
    group by
         s.owner ,
         s.segment_name ,
         s.tablespace_name ,
         s.segment_type,
         s.max_extents
    order by 6 desc
    /
    The first occurance is &1, next &&1. When I run this as a script (F5) it asks for the value twice, the posion where the &&1 is highlighted - no mistake. I don't know if I can submit a screenshot here.
    This might be a case for another thread, if so let me know, I'll open a "bug fix" thread.


    As for the second subject I agree and that is how we normally provide access to the database. We have users assigned to database roles and they use Forms to login. However, there is no way of preventing the user from using exactly the same login in SQLPLUS or any other client tool. Normally they would not need SQLPLUS nor SQL*NET if they use Forms via a web frontend. We would not need any Oracle client, however some users have been granted permissions to use SQL by using the tool called Golden, that unfortunately requires at least SQL*NET. They use a special database role which only allows them to use "SELECT". But that does not mean they cannot use the "update" login and run updates. That is why we only give those tools to "power" users.
    We have advised our user to remove SQLDeveloper, as he was breaching his terms of using his computer by installing software without the consent of our IT department. For now, now harm done, one user was warned. But should I be spending vast hours searching for users abusing the system?

    I spotted that user on an occasional search which proves he managed to bypass our Proxy settings, managed to download SQLDeveloper and didn't have to run an installer in order to use it. I spoke to our admin and he has to go through extreme lengths to block "downloads" and at some point the Oracle.com web page has been added as a trusted site. In any other case someone can simply go home, download SQLDeveloper, stick it onto a CD or USB stick and copy it onto an office computer and use it without our knowledge.
    This in many cases even with Windows software would not be possible, because the majority of software needs admin privileges to be installed. That still is the case with Oracle Client software.
    +(sorry for the bold)+

    It seems now that we need to login into support.oracle.com to download the software - fair enough, the question is can someone without a license create such an account to download software from Oracle?
    I agree software should be easily installed - that always was Oracle-'s approach (apart from an Oracle Forms aIS on a UNIX box), but 99% of people using computers accept that in order to install software you need admin rights, both in private and in an office.

    Our user had this comment : "yes, I downloaded the software, since it was not blocked I assumed it was safe to use, doesn't need a license so I could use it" - so how do you respond to that?
    One user was warned, now I need to write a scripts and job to monitor such activity, because who knows...

    Maybe the solution is simple. Change your SQLDeveloper so that when it is launched for the first time it requires "admin" rights and then create some sort of a config file (or registry setting), that would allow us to grant privileges for users to run the software. Once privileges have to be revoked (user leaves the company), we remove the control file from the computer (or registry setting).

    I hope this is a worthy discussion :-)
    Best regards,
    Richard




    thanks for taking your time.
  • 4. Re: SQLDeveloper - change proposal
    user571349 Newbie
    Currently Being Moderated
    "I will not mention runnig full scans on large tables, boy, everybody loves those, why doesn't every human being understand what an optimizer is and how to make use of it?"
     
    What does that have to do with Sql Developer?
    We have given access to some users (not all) to use SQL for more complex analysis, for them we have provided a role that only allows SELECT.
    The tool they use requires a license, our acceptance via installation.
    This way we have 20 users running full scans, not 500.
     
    Oracle HAS put 'some effort into this subject; a LOT of effort. You have several options for managing resource use with Oracle DBs. 
    A LOGON trigger is one option, user and resource profiles are options, proper use of ROLES and GRANTS provide even more options.
    Yes, I've seens that and tried it, but I spent so much time on exceptions I decided it would make my security policies much more complex and hard to maintain in the long run.
    As an example I have a user who renamed his executable 'TOAD.EXE' into 'TOAD NEW.exe', so I was chasing my own tail.

    Regards,
    Richard
  • 5. Re: SQLDeveloper - change proposal
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    Please split out the ampersand issue separate from the security one.
  • 6. Re: SQLDeveloper - change proposal
    Gary Graham Expert
    Currently Being Moderated
    Try something similar to the following alternative technique to resolve your parameter substitution issue:
    set pause off
    set verify off
    accept namepfx char prompt 'Enter a name prefix: '
    select * from emp where ename like '&namepfx%';
    select * from emp where ename like '&namepfx.C%';
    select * from emp where ename like '&namepfx.M%';
    with these results on the classic SCOTT schema using Run Script (F5) for namepfx=S
         EMPNO ENAME      JOB              MGR HIREDATE                      SAL       COMM     DEPTNO                     
    ---------- ---------- --------- ---------- ---------------------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80 12.00.00 AM         800                    20                                  
          7788 SCOTT      ANALYST         7566 19-APR-87 12.00.00 AM        3000                    20                                  
    
         EMPNO ENAME      JOB              MGR HIREDATE                      SAL       COMM     DEPTNO                          
    ---------- ---------- --------- ---------- ---------------------- ---------- ---------- ----------
          7788 SCOTT      ANALYST         7566 19-APR-87 12.00.00 AM        3000                    20                                  
    
         EMPNO ENAME      JOB              MGR HIREDATE                      SAL       COMM     DEPTNO                         
    ---------- ---------- --------- ---------- ---------------------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80 12.00.00 AM         800                    20                                  
    Regards,
    Gary
  • 7. Re: SQLDeveloper - change proposal
    rp0428 Guru
    Currently Being Moderated
    >
    We have given access to some users (not all) to use SQL for more complex analysis, for them we have provided a role that only allows SELECT.
    The tool they use requires a license, our acceptance via installation.
    This way we have 20 users running full scans, not 500.
    >
    That still won't prevent those users from doing cartesian joins on those tables/views they can query; that requires a resource limitation in the database.

    It is, and always has been, YOUR responsibility for controlling what software your users use. As I said above
    >
    That is an issue for your desktop security group to deal with. Depending on your OS there are ways to prevent the use of resources that are needed to run the applications.
    >
    I have contracted for many large international firms as well as smaller localized ones. All of them have policies in place that state clearly what software can be downloaded or installed or used on the PCs that they provide to their users.

    They also all strictly enforce those policies. And none of them rely on happenstance to discover violations. They all have software installed on those PCs that performs automatic virus scans and they all have software that scans for inappropriate software or files on a regular basis.

    That catches all 'innocent' violations of the policy and those violations can be dealt with as the company sees fit.

    Sql Developer, like most software, has a very visible footprint. It is trivial to find the files (jars, etc) that it needs to run. If that is a security or operational concern for your company I suggest you have them research such scanning software and evaluate it.

    So while I understand the issue you are talking about I do not share your opinion that the tool makers should implement installation or other restrictions.
  • 8. Re: SQLDeveloper - change proposal
    Aleksander Stopar Newbie
    Currently Being Moderated
    Richard,

    we had similar security issue with our users. We solved it by using password protected roles, which are not enabled by default. When user is using the approved application, the role is enabled in the application (application "knows" the password). The users don't know the password, so they can not enable the role and thus have no access to tables with any tool. How do you store the password is your choice (have it hardcoded in the application or available (encrypted) in the configuration file or read it from somewhere else or computed on the fly... whatever)

    Furhter division (whithin the application) on who can do what is done by using application contexts.

    Hope this helps
  • 9. Re: SQLDeveloper - change proposal
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi Richard

    & and &&

    are treated correctly to my knowledge. I ran your script you get prompted twice (as expected)

    Regarding Column:
    There is a bug where returning no rows in the query following column is not treated correctly.

    I notices you are using break and compute:
    'Column' support in SQLDeveloper has been relatively recently extended for column .. format,
    from a quick check, break and compute did not get implemented.

    (
    i.e. the following did not work: (put together based on a documentation break/compute example)
    break on DEPARTMENT_ID page nodup on job_id skip 1 nodup
    break
    COMPUTE SUM OF SALARY ON DEPARTMENT_ID
    SELECT 1 DEPARTMENT_ID, 1 JOB_ID, 'Flintstone' "LAST_NAME", 100 "SALARY"
    FROM scott.emp
    ORDER BY DEPARTMENT_ID, JOB_ID;
    )

    Create (or add to existing) entries in "Feature Requests, Extensions and General Collateral" for more SQLPLUS emulation functionality.

    -Turloch
    SQLDeveloper Team

    Note:
    &1 can be an argument reference i.e. first argument in calling a script as well as a substitution variable &1

    I see your SQLPLUS script is regarding Database Administration:
    Note that SQLDeveloper has some basic aids for Dababase Administration:

    1/Reports:
    Data Dictionary Reports -> Database Administration -> Storage ...
    (You can use existing reports and make your own reports)

    and DBA viewer
    2/View -> DBA
    (add your database connection to the view)
    (select your connection and log in)->storage->...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points