This discussion is archived
4 Replies Latest reply: Aug 2, 2012 4:42 AM by Miroslav197 RSS

user privileges

Miroslav197 Newbie
Currently Being Moderated
I have created tablespace in Oracle Database 10.0.2.0.1 on windows platform. I need to create a role for users who should be able to select, insert and update tables. How to create role for that kind of users? Which privileges should that role have?

Thx in advance.
  • 1. Re: user privileges
    929328 Newbie
    Currently Being Moderated
    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 then 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;

    If you have any questions ask.
    Kirill Babeyev

    Edited by: Kirill.Babeyev on Aug 1, 2012 6:39 AM
  • 2. Re: user privileges
    Miroslav197 Newbie
    Currently Being Moderated
    Thx. Another question: is there possibility grant priviledge to roles?
  • 3. Re: user privileges
    ShankarViji Pro
    Currently Being Moderated
    Hi User,

    Yes, you can assgin Priviliges roles. Please read the thread below:

    <a href ="http://www.techonthenet.com/oracle/roles.php"> Priviliges</a>

    And, Priviliges assgined by role to user are not considered as direct grant in PL\SQL.
    When using PLSQL direct grants must be given to access the object inside PL\SQL.




    Thanks,
    Shankar
  • 4. Re: user privileges
    Miroslav197 Newbie
    Currently Being Moderated
    Pls, would you be kind and explain me little bit more:

    "And, Priviliges assgined by role to user are not considered as direct grant in PL\SQL.
    When using PLSQL direct grants must be given to access the object inside PL\SQL."

    Mostly, I'm using SQLPlus.

    Thx!

Legend

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