Problem solve Get help with specific problems with your technologies, process and projects.

Granting access to SQL different from access on Forms and Reports

I would like to know how I can give access to users on SQL different from their access on FORMS and REPORTS?

I'm working under two assumptions for this answer:
1) That you are asking how to provide different privileges/access when a user logs in via SQL*Plus vs. if they log in to an application written using Forms or Reports.
2) Users are granted access privileges via roles and not by individually granted privileges.

Working with those two assumptions you can use the PRODUCT_USER_PROFILE table (created using the pupbld.sql script located in the ORACLE_HOME\sqlplus\admin directory) to limit what role a user accesses when using SQL*Plus.

The following information is taken from the Oracle Application Developers Guide - Fundamentals document found on TechNet at http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/appdev.901/a88876/adgsec02.htm#1011731.

"DBAs can use PRODUCT_USER_PROFILE to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. SQL*Plus, not Oracle, enforces this security. DBAs can even restrict access to the GRANT, REVOKE, and SET ROLE commands in order to control users' ability to change their database privileges.

The PRODUCT_USER_PROFILE table enables you to list roles which you do not want users to activate with an application. You can also explicitly disable use of various commands, such as SET ROLE. For example, you could create an entry in the PRODUCT_USER_PROFILE table to:

Disallow use of the CLERK and MANAGER roles with SQL*Plus
Disallow use of SET ROLE with SQL*Plus

Suppose user Jane connects to the database using SQL*Plus. Jane has the CLERK, MANAGER, and ANALYST roles. As a result of the above entry in PRODUCT_USER_PROFILE, Jane is only able to exercise her ANALYST role with SQL*Plus. Also, when Jane attempts to issue a SET ROLE statement, she is explicitly prevented from doing so because of the entry in the PRODUCT_USER_PROFILE table prohibiting use of SET ROLE.

Use of the PRODUCT_USER_PROFILE table does not completely guarantee security, for multiple reasons. In the above example, while SET ROLE is disallowed with SQL*Plus, if Jane had other privileges granted to her directly, she could exercise these using SQL*Plus. "

I think that will do the trick for you if I made the correct assumptions!

For More Information

  • Dozens more answers to tough Oracle questions from Karen Morton are available here.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.