1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Grant Select and DML

Discussion in 'Server Administration and Options' started by Arju, Oct 10, 2008.

  1. Arju

    Arju Forum Expert

    Likes Received:
    Trophy Points:
    Which privileges I can give for a user who can only do DML and queries of his schema. The answer is only create session and quota on a tablespace.

    Let's see how we can do it.
    1)Create a new user named least_priv and make his password l.

    Code (Text):
    SQL> grant create session to least_priv identified by l;

    Grant succeeded.
    2)Grant some quota suppose 1M on tablespace users to user least_priv.
    Code (Text):

    SQL> alter user least_priv quota 1M on users;

    User altered.
    3)Create some objects in least_priv's schema.
    Code (Text):
    SQL> create table least_priv.test(a number, b number);

    Table created.
    4)Now connect as least_priv user and do operation on his table.
    He can do every work with the table in his schema, even he can drop it.
    Code (Text):
    SQL> conn least_priv/l
    SQL> insert into test values(1,2);

    1 row created.

    SQL> delete from test;

    1 row deleted.

    SQL> alter table test add c varchar2(10);

    Table altered.

    SQL> drop table test;

    Table dropped.