| GRANT ANY OBJECT PRIVILEGE [message #597027] |
Mon, 30 September 2013 04:51  |
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
|
|
|
|
|
|
| Re: GRANT ANY OBJECT PRIVILEGE [message #597121 is a reply to message #597033] |
Tue, 01 October 2013 04:21  |
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
|
|
|
|
|
|
| Re: GRANT ANY OBJECT PRIVILEGE [message #597236 is a reply to message #597124] |
Tue, 01 October 2013 20:00 |
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 #597239 is a reply to message #597238] |
Tue, 01 October 2013 20:57 |
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 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
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 |
|
|
|
|
|
| Re: GRANT ANY OBJECT PRIVILEGE [message #597328 is a reply to message #597247] |
Thu, 03 October 2013 04:50 |
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 #597334 is a reply to message #597331] |
Thu, 03 October 2013 05:27 |
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 #597338 is a reply to message #597334] |
Thu, 03 October 2013 05:38 |
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
|
|
|
|
 Re: GRANT ANY OBJECT PRIVILEGE [message #597341 is a reply to message #597338] |
Thu, 03 October 2013 06:14 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
