This discussion is archived
3 Replies Latest reply: Nov 19, 2012 10:02 AM by user346369 RSS

tables created in sys account

Munish Newbie
Currently Being Moderated
Hi,
I created some tables in sys user account in oracle database 11g. (I was told to, though I am against creating anything under sys account).
Now when in developer (11.1.1.6.0) we call those tables (eg. SYS.tableA), the form will not compile. It says identified sys.tableA must be declared.

What am I missing? We cannot create public synonym for these tables (not allowed by company policies)

Thanks
Munish
  • 1. Re: tables created in sys account
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,

    To see tables in other users, you need privileges. The owner give those privileges to other users with the GRANT command.
    But, the more important is not that but the following.

    Never, never, NEVER create any object in the SYS/SYSTEM scheme.

    Francois
  • 2. Re: tables created in sys account
    ck Explorer
    Currently Being Moderated
    dont create any objects on sys user.

    But anyhow when you create in another user then

    you need to grant select/insert/update/delete to your log in user.

    Grant Privileges on Tables

    You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.



    Privilege
    Description
    Select
    Ability to query the table with a select statement.
    Insert
    Ability to add new rows to the table with the insert statement.
    Update
    Ability to update rows in the table with the update statement.
    Delete
    Ability to delete rows from the table with the delete statement.
    References
    Ability to create a constraint that refers to the table.
    Alter
    Ability to change the table definition with the alter table statement.
    Index
    Ability to create an index on the table with the create index statement.


    The syntax for granting privileges on a table is:
    grant privileges on object to user;
    For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:
    grant select, insert, update, delete on suppliers to smithj;
    You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
    grant all on suppliers to smithj;
    If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
    grant select on suppliers to public;

    http://www.techonthenet.com/oracle/grant_revoke.php
  • 3. Re: tables created in sys account
    user346369 Expert
    Currently Being Moderated
    In addition to at least granting SELECT on that table (no matter what userid owns it), in order for users to run the form, those users ALSO must be granted Select access.

    It is easier to grant access to a role, and grant that role to all the users who use that form.

Legend

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