This discussion is archived
6 Replies Latest reply: Jan 16, 2013 1:41 AM by just a DBA RSS

APEX_PUBLIC_USER privileges

just a DBA Newbie
Currently Being Moderated
Question about APEX security.

When new workspace is created, a tablespace and a user is created in database. This is done by APEX_PUBLIC_USER.
How APEX_PUBLIC_USER with limited number of privileges gain DBA access ?
We have traced session and seen "CREATE USER", "CREATE TABLESPACE" and other statements, not procedure calls.
  • 1. Re: APEX_PUBLIC_USER privileges
    fac586 Guru
    Currently Being Moderated
    >

    Welcome to the forum: please read the FAQ and forum sticky threads (if you haven't done so already), and update your profile with a real handle instead of "user12881866 ".
    Question about APEX security.

    When new workspace is created, a tablespace and a user is created in database. This is done by APEX_PUBLIC_USER.
    How APEX_PUBLIC_USER with limited number of privileges gain DBA access ?
    We have traced session and seen "CREATE USER", "CREATE TABLESPACE" and other statements, not procedure calls.
    See +{message:id=1244957}+ for a very concise explanation of what's going on.

    Search for other threads discussing DBMS_SYS_SQL for more on this.

    -----

    When you have a problem you'll get a faster, more effective response by including as much relevant information as possible upfront. This should include:

    <li>Full APEX version
    <li>Full DB/version/edition/host OS
    <li>Web server architecture (EPG, OHS or APEX listener/host OS)
    <li>Browser(s) and version(s) used
    <li>Theme
    <li>Template(s)
    <li>Region/item type(s) (making particular distinction as to whether a "report" is a standard report, an interactive report, or in fact an "updateable report" (i.e. a tabular form)

    With APEX we're also fortunate to have a great resource in apex.oracle.com where we can reproduce and share problems. Reproducing things there is the best way to troubleshoot most issues, especially those relating to layout and visual formatting. If you expect a detailed answer then it's appropriate for you to take on a significant part of the effort by getting as far as possible with an example of the problem on apex.oracle.com before asking for assistance with specific issues, which we can then see at first hand.
  • 2. Re: APEX_PUBLIC_USER privileges
    just a DBA Newbie
    Currently Being Moderated
    Thank you for answer.
    APEX version we are using in latest (4.2), Oracle 11.2.
    Package DBMS_SYS_SQL is owned by SYS and no user has access to it:

    select * from dba_tab_privs where table_name='DBMS_SYS_SQL';

    no rows selected.
  • 3. Re: APEX_PUBLIC_USER privileges
    riedelme Expert
    Currently Being Moderated
    user12881866 wrote:
    Question about APEX security.

    When new workspace is created, a tablespace and a user is created in database. This is done by APEX_PUBLIC_USER.
    How APEX_PUBLIC_USER with limited number of privileges gain DBA access ?
    We have traced session and seen "CREATE USER", "CREATE TABLESPACE" and other statements, not procedure calls.
    APEX_PUBLIC_USER was designed for internal use by Apex and is not used by people to log in. It will probably be best to leave it alone and use other schemas for your work.

    We use the workspace schemas Apex creates when workspaces are created as well as developer schemas with needed access for individuals who should not have admin privileges

    Edited by: riedelme on Jan 15, 2013 5:43 AM
  • 4. Re: APEX_PUBLIC_USER privileges
    just a DBA Newbie
    Currently Being Moderated
    APEX_PUBLIC_USER was designed for internal use by Apex and is not used by people to log in. It will probably be best to leave it alone and use other schemas for your work.

    We use the workspace schemas Apex creates when workspaces are created as well as developer schemas with needed access for individuals who should not have admin privileges
    The question is: how APEX_PUBLIC_USER session gains DBA access when creating apex workspace if none of database users has access to SYS.DBMS_SYS_SQL ?
  • 5. Re: APEX_PUBLIC_USER privileges
    Mihael Pro
    Currently Being Moderated
    The question is: how APEX_PUBLIC_USER session gains DBA access when creating apex workspace if none of database users has access to SYS.DBMS_SYS_SQL ?
    When you install APEX, user APEX_xxx is created, where xxx is APEX version. This user has DBA privileges like create/alter user, create tablespace etc. Also this user owns a lot of packages that are granted to PUBLIC.
  • 6. Re: APEX_PUBLIC_USER privileges
    just a DBA Newbie
    Currently Being Moderated
    >
    When you install APEX, user APEX_xxx is created, where xxx is APEX version. This user has DBA privileges like create/alter user, create tablespace etc. Also this user owns a lot of packages that are granted to PUBLIC.
    It would be a security hole if any could create users through public procedures.

Legend

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