This discussion is archived
7 Replies Latest reply: Jun 7, 2013 4:09 PM by dsurber RSS

Grant permission through dynamic parameters entered by user through web app

1008023 Newbie
Currently Being Moderated
This is my code.

f1=request.getParameter("URL");
out.println("parameter f1 ===>"+f1);//user name
f2=request.getParameter("URL1");
out.println("parameter f2 ===>"+f2);//table name
f3=request.getParameter("URL2");

out.println("parameter f3 ===>"+f3);//privilege name
sql="GRANT f3 to \"" + f1 + "\""+"on \""+f2+"\"";

st= con.createStatement();
st.execute(sql);
out.println("grant succeeded");

it is giving error that invalid SQL query.please help in writing this code.Any other method for giving dynamic SQL query for granting permission.
  • 1. Re: Grant permission through dynamic parameters entered by user through web app
    Tolls Journeyer
    Currently Being Moderated
    You need to stick your code inside {*code*} tags {*code*} (ignore the '*') as I suspect the forum is mucking up your text.

    Also, print out what the actual SQL you are trying to execute is.
  • 2. Re: Grant permission through dynamic parameters entered by user through web app
    Joe Weinstein Expert
    Currently Being Moderated
    Your SQL is broken because it starts out with "GRANT f3 to ..."
    I expect you want

    sql="GRANT " + f3 + " to " + f1 + " on " + f3;
  • 3. Re: Grant permission through dynamic parameters entered by user through web app
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!
    >
    Any other method for giving dynamic SQL query for granting permission.
    >
    You should NOT be using dynamic SQL for issuing grants. Security is something that should be taken seriously and grants should ONLY be given to users that need the permission. The necessary grants should be created and reviewed BEFORE they are executed.

    Best practices are to create scripts containing your DDL and place those scripts in a version control system.

    The scripts can then be executed in sql*plus, sql developer or another tool and the results reviewed to ensure that they executed properly.

    If dynamic SQL is needed you:

    1. create a sql statement manually and test it to make sure it works properly
    2. create the code to assemble similar statements and VIEW the output DDL to make sure that it is valid
    3. add exception handling and security handling to the code so that is can only be used for the intended operations and is not subject to SQL injection.
    4. manually execute the DDL produced by the code to make sure there are no syntax errors.

    Clearly you did not even test your SQL before trying to write code to produce it or you would have known your syntax is invalid.
    >
    sql="GRANT f3 to \"" + f1 + "\""+"on \""f2"\"";
    >
    . . .
    >
    it is giving error that invalid SQL query.
    >
    Of course it is. That code might try to produce the equivalent of:
    GRANT select to "scott" on "hr.employees";
    There are SEVERAL errors in that code.

    1. You are enclosing the SCHEMA in double-quotes. That means the actual user name will be treated as case-sensitive. So if someone provides 'scott' it will be considered lower-case. There is NO user "scott" in Oracle unless you created that user yourself and used double-qoutes to preserve the case.

    ALL of the schemas created by Oracle, and most users, are UPPER case. So your code will not find any name if the user supplies a LOWER case or mixed-case value.

    2. You are enclosing the target schema and object name in double quotes. There are two things wrong. The same case issue applies again. And the string "hr.employees" will be treated as ONE value. The proper way to quote such a value is:
    "HR"."EMPLOYEES"
    3. You have the DDL components in the wrong order, hence it is invalid. The ON clause comes BEFORE the target schema.
    GRANT select to on hr.employees to scott;
    See the SQL Language doc for the GRANT statement
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9013.htm

    All of the issues you have demonstrate why you should NOT be using dynamic SQL to do DDL. You don't understand the syntax so you can't write code to implement that syntax.

    The syntax is much more complex than the siimple code you are trying to use.

    Grant statements often need to include "SCHEMA.OBJECT" syntax and your code makes no provision for that.

    DDL needs to be tightly controlled and doing it in code can create huge, gaping security holes.

    Abandon your method and use prepared scripts for the DDL commands you need to execute.
  • 4. Re: Grant permission through dynamic parameters entered by user through web app
    1008023 Newbie
    Currently Being Moderated
    thanku.....but In which language should i write the script for this DDL statement.
  • 5. Re: Grant permission through dynamic parameters entered by user through web app
    rp0428 Guru
    Currently Being Moderated
    >
    thanku.....but In which language should i write the script for this DDL statement.
    >
    Umm - you should use English.

    But, as said above, you first need to write (manually) something that uses the correct syntax and works properly.

    Don't try to automate something until you can first do it manually.
  • 6. Re: Grant permission through dynamic parameters entered by user through web app
    1008023 Newbie
    Currently Being Moderated
    well now it is showing the error that USER OR ROLE DOES NOT EXISTS.i m writing the statement as:
    grant insert on table_name to user_name;

    i have first created user in database by create user statement and it is showing the user name that i have created in the DBA_USERS table when i issue the command:

    select * from dba_users where username="my user name";
    but why grant statement fails for the same user.
    i have also studied the case-sensitivity issues and tried to grant permission by typing username in lowercase as well as upper case.but it still not working
  • 7. Re: Grant permission through dynamic parameters entered by user through web app
    dsurber Explorer
    Currently Being Moderated
    You really do not want to write this code. As rp said in another thread, this is a career ending move. If you do this be sure to have another job already lined up.

    Douglas

Legend

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