-1

I want to grant select privilege to a particular user in Oracle. Its possible in mysql with following query

GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED      
BY 'password';

Is there any way we can achieve this in Oracle ?

chetan
  • 1,385
  • 4
  • 15
  • 31

2 Answers2

2

What MySQL calls a "database" is more similar to what Oracle calls a "schema". Oracle does not have the ability to grant privileges on all objects in a schema to a particular user, you have to grant the privileges on each object individually. You can use a bit of dynamic SQL to simplify the initial grant

BEGIN
  FOR x IN (SELECT *
              FROM dba_tables
             WHERE owner = <<name of schema>>)
  LOOP
    EXECUTE IMMEDIATE 'GRANT select, insert, delete ON ' || 
                         x.owner || '.' || x.table_name ||
                         ' TO <<username>>';
  END LOOP;
END;

Every time you create a new object in the schema, though, you'll need to either grant the same privileges on the new object to the user or you'll need to re-run the PL/SQL block above. If you really want to, a DDL trigger could submit a job that would grant the privileges automatically when new objects are created but that is not particularly recommended.

From a managability perspective, you would generally create a role that would be assigned all these privileges and then assign that role to whatever set of users actually need those DML privileges. That allows you to easily assign the same role to everyone in the organization that needs the same set of privileges rather than managing privileges on n objects for m users.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 1
    You might want to look at [using roles](http://docs.oracle.com/cd/E11882_01/network.112/e16543/authorization.htm) to manage privileges, and then granting the appropriate role to the user, to simplify future changes. (That's aimed at @chetan, not Justin, obviously!) – Alex Poole Aug 29 '12 at 15:05
0

Yes it is with a GRANT statement : GRANT in Oracle documentation

You must first create a user with CREATE USER.

Bgi
  • 2,513
  • 13
  • 12
  • 1
    You don't *have* to have a separate `create user` statement; if you `grant` with the `identified by` clause then the user will be created if it doesn't already exist. That's [mentioned in the documentation](http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9013.htm#sthref8966) you linked to. But you can't do a global grant for all objects in a schema, as Justin pointed out; closest is perhaps granting a role which has all the required privileges. – Alex Poole Aug 29 '12 at 14:50