Home » RDBMS Server » Security » GRANT ANY OBJECT PRIVILEGE (11g, 11.2.0.1 windows server 2008)
GRANT ANY OBJECT PRIVILEGE [message #597027] Mon, 30 September 2013 04:51 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear All,

I am having one confusion about the privileges.
Previously, In testing database, user was having DBA roles now we restrict it from DBA roles.
Now, we want the user to give "SELECT" privileges on object to PUBLIC. But through below command, i can see number of rows with "DEBUG", "EXECUTE" command to PUBLIC are appearing.


SELECT GRANTOR,  TABLE_NAME,PRIVILEGE, grantee  FROM dba_TAB_PRIVS
WHERE GRANTEE='PUBLIC'
and privilege <>'SELECT'


User is having "GRANT ANY OBJECT PRIVILEGE" privilege. Do this privilege is having the problem?
Kindly assist me to know about it.

Regards,
Ishika
icon4.gif  Re: GRANT ANY OBJECT PRIVILEGE [message #597033 is a reply to message #597027] Mon, 30 September 2013 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i can see number of rows with "DEBUG", "EXECUTE" command to PUBLIC are appearing.


This is expected... except for the DEBUG privilege someone granted it to PUBLIC.

Quote:
User is having "GRANT ANY OBJECT PRIVILEGE" privilege. Do this privilege is having the problem?


Of course! It can grant any privilege on any object to any one.



Re: GRANT ANY OBJECT PRIVILEGE [message #597121 is a reply to message #597033] Tue, 01 October 2013 04:21 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

Our requirement is to provide user to give access (SELECT ON TABLE, EXECUTE ON PROCEDURE) to its object to other schema other than PUBLIC.

Do I need to specify each object with GRANT SELECT ON <object_name> to user or is there any other privilge which can be provided to user (before removing GRANT ANY OBJECT PRIVILEGE ) ?

Regards,
Ishika
icon5.gif  Re: GRANT ANY OBJECT PRIVILEGE [message #597124 is a reply to message #597121] Tue, 01 October 2013 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't understand the question.
if you need SELECT on a table, yes, of course, grant SELECT on this table.
Same thing for a procedure.

Re: GRANT ANY OBJECT PRIVILEGE [message #597236 is a reply to message #597124] Tue, 01 October 2013 20:00 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Mr. Michel,

I mean to say that -

I want to provide grant "select on objects" to users. Users should able to give privileges on objects to other schemas as "select on object" and "execute on procedure". As a DBA, i don't know what are all objects on which they want to give select privileges to other schemas.

Our main requirement is to give privileges to users so that they can give only "SELECT ON OBJECTS" & "EXECUTE ON PROCEDURE/PACKAGES/FUNCTION" to other schemas. schemas should not able to modified others schemas table structure or procedure/package/function.

Regards,
Ishika
Re: GRANT ANY OBJECT PRIVILEGE [message #597238 is a reply to message #597236] Tue, 01 October 2013 20:27 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
any schema owner/user can issue GRANT against any of their own objects.
Re: GRANT ANY OBJECT PRIVILEGE [message #597239 is a reply to message #597238] Tue, 01 October 2013 20:57 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Mr. BlackSwan,

If I remove "GRANT ANY OBJECT PRIVILEGE" from the schema's users then???

Users can do anything on its objects but other schema's users can only select. How to restrict it??

Please guide me on the same.

Regards,
Ishika
Re: GRANT ANY OBJECT PRIVILEGE [message #597240 is a reply to message #597239] Tue, 01 October 2013 21:01 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
what stops YOU from doing your own testing & command validation?????????
[oracle@localhost ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 1 18:59:38 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant select on emp to hr;   

Grant succeeded.

SQL> connect hr/hr
Connected.
SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

SQL> 



with Oracle everything is forbidden; except that which is explicitly GRANTed.

[Updated on: Tue, 01 October 2013 21:09]

Report message to a moderator

icon8.gif  Re: GRANT ANY OBJECT PRIVILEGE [message #597247 is a reply to message #597239] Wed, 02 October 2013 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You should REALLY read Database Concepts, at least the first chapter.
You REALLY lack the basics.

Re: GRANT ANY OBJECT PRIVILEGE [message #597328 is a reply to message #597247] Thu, 03 October 2013 04:50 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Mr. Michel,

There are some confusion in my mind, want to clarify it. I have created one table under user SCOTT and provided grant "select on table" to DIP. Now, when i fetch data from DIP, it allowed. Even it allowed to alter the table (which one should be restricted). Please assist me about the privilege -

Hope, this time I tried to put my view clearly in front of you.

Below are the steps -

SQL> SHO USER
USER is "SCOTT"
SQL> CREATE TABLE EMP_TST AS SELECT * FROM EMP;

Table created.

SQL> GRANT SELECT ON EMP_TST TO DIP;

Grant succeeded.

SQL> CONN DIP@TEST1
Enter password:
Connected.
SQL> select * from scott.emp_tst;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800         30         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> update scott.emp_tst set comm=300 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.




Privileges provided to DIP is as below -

SQL> select * from dba_sys_privs where grantee='DIP' order by 2;

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DIP                            ALTER ANY CLUSTER                        NO
DIP                            ALTER ANY DIMENSION                      NO
DIP                            ALTER ANY EVALUATION CONTEXT             NO
DIP                            ALTER ANY INDEX                          NO
DIP                            ALTER ANY INDEXTYPE                      NO
DIP                            ALTER ANY LIBRARY                        NO
DIP                            ALTER ANY MATERIALIZED VIEW              NO
DIP                            ALTER ANY OPERATOR                       NO
DIP                            ALTER ANY PROCEDURE                      NO
DIP                            ALTER ANY ROLE                           NO
DIP                            ALTER ANY RULE                           NO
DIP                            ALTER ANY RULE SET                       NO
DIP                            ALTER ANY SEQUENCE                       NO
DIP                            ALTER ANY TABLE                          NO
DIP                            ALTER ANY TRIGGER                        NO
DIP                            ALTER ANY TYPE                           NO
DIP                            ALTER SYSTEM                             NO
DIP                            ALTER USER                               NO
DIP                            ANALYZE ANY                              NO
DIP                            AUDIT ANY                                NO
DIP                            BACKUP ANY TABLE                         NO
DIP                            COMMENT ANY TABLE                        NO
DIP                            CREATE ANY CLUSTER                       NO
DIP                            CREATE ANY CONTEXT                       NO
DIP                            CREATE ANY DIMENSION                     NO
DIP                            CREATE ANY DIRECTORY                     NO
DIP                            CREATE ANY INDEX                         NO
DIP                            CREATE ANY INDEXTYPE                     NO
DIP                            CREATE ANY JOB                           NO
DIP                            CREATE ANY LIBRARY                       NO
DIP                            CREATE ANY MATERIALIZED VIEW             NO
DIP                            CREATE ANY PROCEDURE                     NO
DIP                            CREATE ANY RULE                          NO
DIP                            CREATE ANY RULE SET                      NO
DIP                            CREATE ANY SEQUENCE                      NO
DIP                            CREATE ANY SQL PROFILE                   NO
DIP                            CREATE ANY SYNONYM                       NO
DIP                            CREATE ANY TABLE                         NO
DIP                            CREATE ANY TRIGGER                       NO
DIP                            CREATE ANY TYPE                          NO
DIP                            CREATE ANY VIEW                          NO
DIP                            CREATE CLUSTER                           NO
DIP                            CREATE DATABASE LINK                     NO
DIP                            CREATE DIMENSION                         NO
DIP                            CREATE EXTERNAL JOB                      NO
DIP                            CREATE INDEXTYPE                         NO
DIP                            CREATE JOB                               NO
DIP                            CREATE MATERIALIZED VIEW                 NO
DIP                            CREATE PROCEDURE                         NO
DIP                            CREATE PUBLIC DATABASE LINK              NO
DIP                            CREATE PUBLIC SYNONYM                    NO
DIP                            CREATE ROLE                              NO
DIP                            CREATE RULE                              NO
DIP                            CREATE RULE SET                          NO
DIP                            CREATE SEQUENCE                          NO
DIP                            CREATE SESSION                           NO
DIP                            CREATE SYNONYM                           NO
DIP                            CREATE TABLE                             NO
DIP                            CREATE TRIGGER                           NO
DIP                            CREATE TYPE                              NO
DIP                            CREATE USER                              NO
DIP                            CREATE VIEW                              NO
DIP                            DEBUG ANY PROCEDURE                      NO
DIP                            DEBUG CONNECT SESSION                    NO
DIP                            DELETE ANY TABLE                         NO
DIP                            DROP ANY CLUSTER                         NO
DIP                            DROP ANY CONTEXT                         NO
DIP                            DROP ANY DIMENSION                       NO
DIP                            DROP ANY INDEX                           NO
DIP                            DROP ANY INDEXTYPE                       NO
DIP                            DROP ANY MATERIALIZED VIEW               NO
DIP                            DROP ANY OPERATOR                        NO
DIP                            DROP ANY OUTLINE                         NO
DIP                            DROP ANY PROCEDURE                       NO
DIP                            DROP ANY ROLE                            NO
DIP                            DROP ANY RULE SET                        NO
DIP                            DROP ANY SEQUENCE                        NO
DIP                            DROP ANY SQL PROFILE                     NO
DIP                            DROP ANY SYNONYM                         NO
DIP                            DROP ANY TABLE                           NO
DIP                            DROP ANY TRIGGER                         NO
DIP                            DROP ANY TYPE                            NO
DIP                            DROP ANY VIEW                            NO
DIP                            DROP PUBLIC SYNONYM                      NO
DIP                            ENQUEUE ANY QUEUE                        NO
DIP                            EXECUTE ANY EVALUATION CONTEXT           NO
DIP                            EXECUTE ANY INDEXTYPE                    NO
DIP                            EXECUTE ANY LIBRARY                      NO
DIP                            EXECUTE ANY PROCEDURE                    NO
DIP                            EXECUTE ANY PROGRAM                      NO
DIP                            EXECUTE ANY RULE                         NO
DIP                            EXECUTE ANY RULE SET                     NO
DIP                            EXECUTE ANY TYPE                         NO
DIP                            EXPORT FULL DATABASE                     NO
DIP                            FORCE ANY TRANSACTION                    NO
DIP                            FORCE TRANSACTION                        NO
DIP                            GLOBAL QUERY REWRITE                     NO
DIP                            IMPORT FULL DATABASE                     NO
DIP                            INSERT ANY TABLE                         NO
DIP                            MANAGE ANY QUEUE                         NO
DIP                            MERGE ANY VIEW                           NO
DIP                            ON COMMIT REFRESH                        NO
DIP                            QUERY REWRITE                            NO
DIP                            READ ANY FILE GROUP                      NO
DIP                            RESTRICTED SESSION                       NO
DIP                            SELECT ANY DICTIONARY                    NO
DIP                            SELECT ANY SEQUENCE                      NO
DIP                            SELECT ANY TABLE                         NO
DIP                            SELECT ANY TRANSACTION                   NO
DIP                            UNDER ANY TYPE                           NO
DIP                            UNDER ANY VIEW                           NO
DIP                            UNLIMITED TABLESPACE                     NO
DIP                            UPDATE ANY TABLE                         NO

113 rows selected.



Privilege "Alter any table" is provided to dip. It means it can do alter for it's own schema rite???

Regards,
Ishika

[Updated on: Thu, 03 October 2013 04:53]

Report message to a moderator

Re: GRANT ANY OBJECT PRIVILEGE [message #597330 is a reply to message #597328] Thu, 03 October 2013 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Even it allowed to alter the table


Not if DIP is ONLY granted SELECT.

Quote:
Privilege "Alter any table" is provided to dip. It means it can do alter for it's own schema rite???


No, to ANY schema.
Please DO READ the documentation; this is basic principles.

Re: GRANT ANY OBJECT PRIVILEGE [message #597331 is a reply to message #597328] Thu, 03 October 2013 05:12 Go to previous messageGo to next message
Littlefoot
Messages: 19678
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
DIP is user that already exists. It possesses some privileges that - as you said - shouldn't. Who granted them and why, I don't know, but it seems that you don't know it either.

So, if you want DIP to have only one privilege, which is SELECT FROM SCOTT.EMP_TST, you'll have to REVOKE all unnecessary privileges from DIP so that it would have only that one privilege (i.e. select from scott.emp_tst).
Re: GRANT ANY OBJECT PRIVILEGE [message #597334 is a reply to message #597331] Thu, 03 October 2013 05:27 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Mr Littlefoot,

Yes, DIP is already existing user. I have granted all the privileges to check the functionalists.
I need to provide privilege as "GRANT SELECT ANT TABLE" to SCOTT. And "ALTER TABLE" TO DIP instead of "ALTER ANY TABLE"
so that DIP can alter its own schema's object rather than others schema's ?

I am correct Mr Michel?

Regards,
Ishika
Re: GRANT ANY OBJECT PRIVILEGE [message #597337 is a reply to message #597334] Thu, 03 October 2013 05:37 Go to previous messageGo to next message
Littlefoot
Messages: 19678
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Not really. Owner doesn't need any privileges to alter his own tables.

I'm creating a brand new users and allowing him just to connect and create tables:
SQL> create user brisime identified by brisime default tablespace user
  2  quota unlimited on users;

User created.

SQL> grant create session, create table to brisime;

Grant succeeded.


SQL> connect brisime/brisime@ora10
Connected.

SQL> create table test (col number(2));

Table created.

SQL> alter table test modify col number(10);

Table altered.

SQL>
See? No ALTER TABLE privilege at all.
Re: GRANT ANY OBJECT PRIVILEGE [message #597338 is a reply to message #597334] Thu, 03 October 2013 05:38 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Mr. Michel / littlefoot,

I revoked "ALTER ANY TABLE", "UPDATE ANY TABLE" from DIP. Even after doing that, DIP can able to update the scott's table (which is provided "SELECT ON TABLE_NAME TO DIP).

SQL> CONN sys@test1 as sysdba
Enter password:
Connected.
SQL> revoke UPDATE ANY TABLE from dip;

Revoke succeeded.

SQL> conn scott@test1
Enter password:
Connected.
SQL> drop table emp2;

Table dropped.

SQL> create table emp3 as select * from emp;

Table created.

SQL> grant select on emp3 to dip;

Grant succeeded.

SQL> conn dip@test1
Enter password:
Connected.
SQL> select * from scott.emp3;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800       5000         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> update scott.emp3 set comm=5000 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dba_sys_privs where grantee='DIP' order by 2;

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DIP                            ALTER ANY CLUSTER                        NO
DIP                            ALTER ANY DIMENSION                      NO
DIP                            ALTER ANY EVALUATION CONTEXT             NO
DIP                            ALTER ANY INDEX                          NO
DIP                            ALTER ANY INDEXTYPE                      NO
DIP                            ALTER ANY LIBRARY                        NO
DIP                            ALTER ANY MATERIALIZED VIEW              NO
DIP                            ALTER ANY OPERATOR                       NO
DIP                            ALTER ANY PROCEDURE                      NO
DIP                            ALTER ANY ROLE                           NO
DIP                            ALTER ANY RULE                           NO
DIP                            ALTER ANY RULE SET                       NO
DIP                            ALTER ANY SEQUENCE                       NO
DIP                            ALTER ANY TRIGGER                        NO
DIP                            ALTER ANY TYPE                           NO
DIP                            ALTER SYSTEM                             NO
DIP                            ALTER USER                               NO
DIP                            ANALYZE ANY                              NO
DIP                            AUDIT ANY                                NO
DIP                            BACKUP ANY TABLE                         NO
DIP                            COMMENT ANY TABLE                        NO
DIP                            CREATE ANY CLUSTER                       NO
DIP                            CREATE ANY CONTEXT                       NO
DIP                            CREATE ANY DIMENSION                     NO
DIP                            CREATE ANY DIRECTORY                     NO
DIP                            CREATE ANY INDEX                         NO
DIP                            CREATE ANY INDEXTYPE                     NO
DIP                            CREATE ANY JOB                           NO
DIP                            CREATE ANY LIBRARY                       NO
DIP                            CREATE ANY MATERIALIZED VIEW             NO
DIP                            CREATE ANY PROCEDURE                     NO
DIP                            CREATE ANY RULE                          NO
DIP                            CREATE ANY RULE SET                      NO
DIP                            CREATE ANY SEQUENCE                      NO
DIP                            CREATE ANY SQL PROFILE                   NO
DIP                            CREATE ANY SYNONYM                       NO
DIP                            CREATE ANY TABLE                         NO
DIP                            CREATE ANY TRIGGER                       NO
DIP                            CREATE ANY TYPE                          NO
DIP                            CREATE ANY VIEW                          NO
DIP                            CREATE CLUSTER                           NO
DIP                            CREATE DATABASE LINK                     NO
DIP                            CREATE DIMENSION                         NO
DIP                            CREATE EXTERNAL JOB                      NO
DIP                            CREATE INDEXTYPE                         NO
DIP                            CREATE JOB                               NO
DIP                            CREATE MATERIALIZED VIEW                 NO
DIP                            CREATE PROCEDURE                         NO
DIP                            CREATE PUBLIC DATABASE LINK              NO
DIP                            CREATE PUBLIC SYNONYM                    NO
DIP                            CREATE ROLE                              NO
DIP                            CREATE RULE                              NO
DIP                            CREATE RULE SET                          NO
DIP                            CREATE SEQUENCE                          NO
DIP                            CREATE SESSION                           NO
DIP                            CREATE SYNONYM                           NO
DIP                            CREATE TABLE                             NO
DIP                            CREATE TRIGGER                           NO
DIP                            CREATE TYPE                              NO
DIP                            CREATE USER                              NO
DIP                            CREATE VIEW                              NO
DIP                            DEBUG ANY PROCEDURE                      NO
DIP                            DEBUG CONNECT SESSION                    NO
DIP                            DELETE ANY TABLE                         NO
DIP                            DROP ANY CLUSTER                         NO
DIP                            DROP ANY CONTEXT                         NO
DIP                            DROP ANY DIMENSION                       NO
DIP                            DROP ANY INDEX                           NO
DIP                            DROP ANY INDEXTYPE                       NO
DIP                            DROP ANY MATERIALIZED VIEW               NO
DIP                            DROP ANY OPERATOR                        NO
DIP                            DROP ANY OUTLINE                         NO
DIP                            DROP ANY PROCEDURE                       NO
DIP                            DROP ANY ROLE                            NO
DIP                            DROP ANY RULE SET                        NO
DIP                            DROP ANY SEQUENCE                        NO
DIP                            DROP ANY SQL PROFILE                     NO
DIP                            DROP ANY SYNONYM                         NO
DIP                            DROP ANY TABLE                           NO
DIP                            DROP ANY TRIGGER                         NO
DIP                            DROP ANY TYPE                            NO
DIP                            DROP ANY VIEW                            NO
DIP                            DROP PUBLIC SYNONYM                      NO
DIP                            ENQUEUE ANY QUEUE                        NO
DIP                            EXECUTE ANY EVALUATION CONTEXT           NO
DIP                            EXECUTE ANY INDEXTYPE                    NO
DIP                            EXECUTE ANY LIBRARY                      NO
DIP                            EXECUTE ANY PROCEDURE                    NO
DIP                            EXECUTE ANY PROGRAM                      NO
DIP                            EXECUTE ANY RULE                         NO
DIP                            EXECUTE ANY RULE SET                     NO
DIP                            EXECUTE ANY TYPE                         NO
DIP                            EXPORT FULL DATABASE                     NO
DIP                            FORCE ANY TRANSACTION                    NO
DIP                            FORCE TRANSACTION                        NO
DIP                            GLOBAL QUERY REWRITE                     NO
DIP                            IMPORT FULL DATABASE                     NO
DIP                            INSERT ANY TABLE                         NO
DIP                            MANAGE ANY QUEUE                         NO
DIP                            MERGE ANY VIEW                           NO
DIP                            ON COMMIT REFRESH                        NO
DIP                            QUERY REWRITE                            NO
DIP                            READ ANY FILE GROUP                      NO
DIP                            RESTRICTED SESSION                       NO
DIP                            SELECT ANY DICTIONARY                    NO
DIP                            SELECT ANY SEQUENCE                      NO
DIP                            SELECT ANY TABLE                         NO
DIP                            SELECT ANY TRANSACTION                   NO
DIP                            UNDER ANY TYPE                           NO
DIP                            UNDER ANY VIEW                           NO
DIP                            UNLIMITED TABLESPACE                     NO

111 rows selected.

SQL>



Please assist me as I am going through the privileges part is oracle documents.

Regards,
Ishika
icon10.gif  Re: GRANT ANY OBJECT PRIVILEGE [message #597341 is a reply to message #597338] Thu, 03 October 2013 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, will wait you have read the documentation and try to find why happens what you see so you can explain us.

Hint: create a new user and grant it the DIP's privilege one by one until you find the one that allows what you see.

[Updated on: Thu, 03 October 2013 06:15]

Report message to a moderator

Re: GRANT ANY OBJECT PRIVILEGE [message #597342 is a reply to message #597341] Thu, 03 October 2013 06:21 Go to previous message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel / Littlefoot,

You both are correct. My understanding was wrong. As said by Littlefoot, many un-necessary privileges was given to DIP.
After creating new user and assigning the privileges one by one gaves me hints on it.

Below are the steps
SQL> create user usr1 identified by usr1 default tablespace users;

User created.

SQL> grant connect to usr1;

Grant succeeded.

SQL> grant CREATE TABLE to usr1;

Grant succeeded.

SQL> conn scott@test1
Enter password:
Connected.
SQL> grant select on emp3 to usr1
  2  /

Grant succeeded.

SQL> conn sys@test1 as sysdba
Enter password:
Connected.
SQL> alter user usr1
  2  quota unlimited on users;

User altered.

SQL> sho user
USER is "SYS"
SQL> conn scott@test1
Enter password:
Connected.
SQL> grant UPDATE ON EMP3 to usr1;

Grant succeeded.

****************  USR1  ***************************

SQL> insert into usr_tb1 values (1);
insert into usr_tb1 values (1)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


SQL> insert into usr_tb1 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from usr_tb1;

        SR
----------
         1

SQL> update usr_tb1 set sr=2;

1 row updated.

SQL> select * from scott.emp3;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800       5000         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> update scott.emp3 set comm=50 where empno=7369;
update scott.emp3 set comm=50 where empno=7369
             *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> update scott.emp3 set comm=50 where empno=7369;

1 row updated.



Now, myself check each and every privileges and revert.
Thanks a lot to you all.

Regards,
Ishika
Previous Topic: VPD function for column masking with inner-select
Next Topic: DBMS_REDACT package not available in oracle 11g
Goto Forum:
  


Current Time: Mon Oct 20 21:12:45 CDT 2014

Total time taken to generate the page: 0.11802 seconds