9 Replies Latest reply on Apr 28, 2006 3:06 PM by BarryG

    Priveleges to create procedures/functions in schemas


      I have a default schema associated with my user account. Can permissions be given for my user account to create functions and procedures in another schema without giving that user priveleges to create in ANY schema.

      Our default schema for HTMLDB is not the schema associated with my user account. I want to be able to create my functions inside that schema, but our DBA's havent been able to find out how to give the privelege without opening up all schemas to that account.

      hope this made sense,

      Cliff Moon
        • 1. Re: Priveleges to create procedures/functions in schemas

          Doesn't entirely make sense to me. Keep in mind that HTML DB user accounts (assuming that's what you mean by user accounts) have nothing to do with the database. They have no privileges/roles, nothing like that. At the same time, there are database schemas (users) in your database. You can grant them whatever privileges you like. Those schemas and their privileges have no relationship to your application user accounts and they only relate to your HTML DB workspaces and applications insofar as there is a list of schemas that a workspace can access and there is one schema (at a time) that a given application can "parse as". Finally, the default schema associated with an HTML DB user account is just a hint that sets an LOV to that value when multiple schemas might be available, e.g., in the SQL Workshop.

          • 2. Re: Priveleges to create procedures/functions in schemas
            Hey Scott,

            The user account is in the DB where the schemas I want to access exist. I'm trying (using jdev) to access that particular schema (XXUTPA, the schema I map my apps to), but until I'm given access to the schema, I can only create functions and procedures in the schema which my user account has access to. I've asked the DBA's if I can be granted access to create functions and procedures in XXUTPA, but the response I've been given is that they can give me access, but it's an all-or-none proposition. My account would then have access to create functions and procedures (and a lot more) in all the schemas. As I'm not involved in that area, is it possible to just grant create function and create procedures to a specfic DB user account on a specific schema without opening pandora's box? If this is the case, what would be the commands to do this?

            Thanks for the response

            -- cliff moon
            • 3. Re: Priveleges to create procedures/functions in schemas
              The concept of HTML DB account versus FLOWS account versus accounts (schemas) in a workspace is a tough concept for people new to HTML DB. Are there any good graphics/pictorials which show how these different accounts interact/function?

              We are looking at this internally to determine how we will setup access to HTML DB applications. I am going to create an MS PowerPoint to explain the account roles if there is none available.
              • 4. Re: Priveleges to create procedures/functions in schemas

                I still don't get it. What is this user account you're talking about? If you can connect to the database in jdev (?) or SQL*Plus, won't you be connected as XXUTPA or whatever schema you connect to? Then you can create procedures if that schema has the privilege to do so. If you login to an HTML DB workspace and use the SQL Workshop, you'll be able to perform operations in the database as the schema you select in the LOV (or the single value there if there is only one schema assigned to the workspace). If the schema XXUTPA is mapped to the workspace, you'll be able to use that schema's privileges to do whatever it allows. Again, the HTML DB user account that you use to login to HTML DB has nothing to do with the database.

                • 5. Re: Priveleges to create procedures/functions in schemas
                  Hey Scott,

                  Sorry for the confusion about this. This issue has more to do with our DBA's figuring out how to all access to a DB account (cliff) in this case to have access to more than 1 schema. The cliff account (a DB account) (an I'm just filling in what I'm being told by the DBA's) has access to the schema cliff. The schema that I use for HTMLDB is XXUTPA. I want for the DB account cliff to have access to the XXUTPA schema. I'm being told by our DBA's that in order to grant access to the DB account CLIFF to have access to create functions and procedures in the schema XXUTPA, they will in essence have to give CLIFF access to all schemas (an all or nothing action). Is it possible the CLIFF account to have access to my CLIFF schema as well as the ability to create functions and procedures in the XXUTPA schema. If it is possible, can you outline the commands to enter to do this, so I can pass it on to our DBA's. At present, I'm creating all my functions and procedures in jdev in my CLIFF schema, and then cutting and pasting them into the HTMLDB create function box to get them into the XXUTPA schema.

                  -- cliff
                  • 6. Re: Priveleges to create procedures/functions in schemas

                    The DBAs are giving you the straight scoop. You can grant object privileges to CLIFF from the XXUTPA schema, e.g., 'grant select on sometable to cliff;', but you cannot allow cliff to create procedures in xxutpa without granting a system privilege that would be inappropriately powerful.

                    I'm curious why you just don't connect as XXUTPA to do the work initially, instead of doing it twice.

                    • 7. Re: Priveleges to create procedures/functions in schemas
                      Hey Scott,

                      Thanks for staying with me on that one. I'll inform the DBA's to look no further, and that their assumptions were correct. I have a DB account for developing, but the schema associated with it wasnt XXUTPA. I'll get our DBA's to create another account that we can use for development that is associated with it that I can connect with. Once again, thanx for all the help.

                      • 8. Re: Priveleges to create procedures/functions in schemas
                        Okay Cliff, no problem.

                        Now, Michael, I don't know of any prepared docs specifically about this but fwiw, I'll try to recap how it works.

                        1. HTML DB uses a public account to create (or reclaim) a distinct database session to service each page request. The connection is configured with the modplsql DAD and the database user (schema) that owns the session is HTMLDB_PUBLIC_USER. (The exception to this is when you configure a DAD for basic authentication.)

                        2. The public packages (like wwv_flow) and procedures (like f) invoked through each HTTP request are owned by schema FLOWS_xxxxxx. Packages like wwv_flow use definers rights. This means, among other things, that they can execute any other packages owned by the FLOWS_xxxxxx schema, including the highly privileged, non-public packages that execute user code.

                        3. The more privileged non-public packages do all the real work of rendering pages and processing POSTed pages. During these phases, your application code is executed (your report region queries, your DML operations, your page processes, validations, condition evaluation, your API calls, everything). All of this code is "parsed as" the database user (schema) assigned to your application. (Only one schema is assigned to a given application, although the assigned schema can be changed using the builder whenever you like.) The HTML DB engine can execute all of your application code as the "parse as" schema because it has SYS privileges to do so.

                        4. Any of your code that HTML DB executes dynamically runs with the security privileges of your application schema. These privileges must have been granted explicitly and not through roles. So if your report query does 'select * from emp' it's necessary for emp (or a synonym for it) to exist in your application schema and for that schema to have select privilege on emp.

                        5. The SQL Workshop works the same way, except things happen there at a workspace level, not at an application level. A workspace has one or more database schemas mapped to it. This means only that a conscious decision has been made (by an admin) to allow each workspace to access specific schemas. The list of schemas mapped to a given workspace appears in LOVs in various places, such as the SQL Command Processor. Selecting a schema from this LOV allows you to perform operations in that schema. You can perform operations in any of the other mapped schemas by selecting them from the LOV in turn.


                        Note: so far we've said nothing about who the authenticated user is using your application (or the SQL Workshop application), because it has absolutely no bearing on anything so far.


                        6. HTML DB allows developers to specify a plan to be used by the engine at the start of every page request to perform the chores of authentication, initial session registration and session management. This plan is called an authentication scheme. HTML DB provides standard schemes that are used by most developers, but developers can also design and build custom authentication schemes over which developers have complete control.

                        7. During the execution of the authentication scheme for a page view (show) or page processing (accept) request, it is common for the scheme to cause a branch/redirect to a login page if it determines that no valid session yet exists. The operation of the login page results in the user being challenged for credentials and for those credentials to be verified. If they check out, related housekeeping tasks are performed such as recording the session ID in a table and session cookie creation. And a token is established to be used to identify the authenticated user for the duration of the HTML DB session. This value is stored in APP_USER and can be queried by developer-owned code and HTML DB-owned code as required.

                        8. The credentials verification step is where user accounts come into play. It doesn't matter to HTML DB whether your application uses custom tables, an LDAP directory, an SSO infrastructure, or database accounts to verify credentials -- the verification takes place, usually once per HTML DB session, and that's that. The authentication scheme determines the exact method used.

                        9. One example of an application that uses its own custom tables to hold account information (usernames/passwords) is HTML DB itself. You get the first account created for you during product installation and then you create administrator and developer accounts as you create multiple workspaces for developers at the site. These accounts are just rows in tables, a username, a password, an email address, the ID of the workspace, basic stuff like that. They are not database user accounts (schemas). And with these accounts, you can authenticate to HTML DB and use the Builder, the SQL Workshop, and the administration functions. Just remember, the database knows nothing of these accounts (they are like Oracle Applications user accounts).

                        10. These HTML DB user accounts exist primarily to allow developers to use HTML DB. But they can also be used to allow end users to authenticate to applications created using HTML DB. That relieves each developer of having to "reinvent the wheel" and set up account repository tables and to have to write APIs to store/manage passwords, the work we did for HTML DB itself. Your application can simply use the built-in HTML DB authentication scheme which uses the account repository for credentials verification. It's not the only way for your application to verify credentials. In fact it's best suited for experimental applications, small workgroup applications, prototypes, apps on that scale. Applications that are slated for actual production deployment should be fitted with enterprise-level identity management solutions.

                        11. Finally, HTML DB provides a very, very basic group-membership model that allows developer accounts (not database schemas) to be assigned to arbitrarily organized named groups. There is a supporting API for queries against these groups and an admin UI to create/maintain these groups. The same caveats given for using developer accounts for production applications apply to this facility.



                        Database accounts: HTML DB does not use these accounts, their roles, or their privileges except to dynamically execute application code using these schemas as the "parsing schema".

                        HTML DB user accounts: No relation to database schemas (*). They exist in custom tables owned by the HTML DB product. Accounts can be created and used by application developers as an out-of-the-box credentials verification method for authentication.

                        *Exception: The "default schema" associated with an HTML DB user account is the name of a schema used to prime an LOV when the user sees a list-of-schemas LOV in places like the SQL Workshop.


                        • 9. Re: Priveleges to create procedures/functions in schemas
                          The info in this last post by Scott was great, exactly what I needed to explain to various people around here. I hope that this info is still accurate for the current version of Application Express.

                          The question of "which account does what?" comes up pretty frequently. This is the kind of stuff that would be perfect for the Wiki or FAQ or both.