Here is something I want to achieve in Oracle 10g/11g:
I have a schema APP_RW, I need to create a user JOHN and need to grant full access on the schema APP_RW, that means JOHN can create/drop objects in schema APP_RW, with insert/update/delete/execute any object with APP_RW.
That said, I know I can grant "CREATE ANY" Privilege to JOHN, but that will allow him to create objects in any schema. I want to restrict JOHN to only APP_RW.
Is there a way in oracle to do this?
schema level privileges is not possible in oracle..............
but you can make a script to grant privilege at object level SQL> set head off SQL> select 'grant select on '||OWNER||'.'||TABLE_NAME||' to JOHN;' as grant_select FROM dba_tables WHERE owner='SCOTT'; grant select on SCOTT.DEPT to JOHN; grant select on SCOTT.EMP to JOHN; grant select on SCOTT.SALGRADE to JOHN; grant select on SCOTT.BONUS to JOHN; SQL>
you can replace select on with insert on or update on or with delete on respectively