This content has been marked as final. Show 7 replies
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:
There are SEVERAL errors in that code.
GRANT select to "scott" on "hr.employees";
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:
3. You have the DDL components in the wrong order, hence it is invalid. The ON clause comes BEFORE the target schema.
See the SQL Language doc for the GRANT statement
GRANT select to on hr.employees to scott;
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.
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