 | |
are the ALL_* views usually viewable by all users?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
are the ALL_* views usually viewable by all users? -
06-02-2008
, 12:41 PM
#2
| |||||
| |||||
|
|
I'm currently querying the USER_* views to do introspection on a user's schema. *But, in order to handle aliases, I need to use DBMS_UTILITY.NAME_RESOLVE and the ALL_* views. Will I enounter any unexpected issues where a random user won't be able to execute *DBMS_UTILITY.NAME_RESOLVE |
Quote:
|
or access the ALL_* views? |
connected user can access.
Quote:
|
If so, what's the role they should have to access these? |
execute DBMS_UTILITY.NAME_RESOLVE so no special privileges need be
granted:
SQL> create user blerp identified by blorp;
User created.
SQL> grant create session to blerp;
Grant succeeded.
SQL> grant select on test to blerp;
Grant succeeded.
SQL> connect blerp/blorp@###########
Connected.
SQL> declare
2 sch varchar2(40);
3 p1 varchar2(40);
4 p2 varchar2(40);
5 dblink varchar2(40);
6 p1t number;
7 objnum number;
8 begin
9 dbms_utility.name_resolve('BING.TEST',2,sch, p1,p2, dblink,
p1t, objnum);
10 dbms_output.put_line(sch||' '||p1||' '||p2||' '||
dblink||' '||p1t||' '||objnum);
11 end;
12 /
BING TEST 2 68806
PL/SQL procedure successfully completed.
SQL>
Quote:
|
Will that be a common case? |
Quote:
|
Many TIA, Mark -- Mark Harrison Pixar Animation Studios |
David Fitzjarrell
#3
| |||||
| |||||
|
|
I'm currently querying the USER_* views to do introspection on a user's schema. *But, in order to handle aliases, I need to use DBMS_UTILITY.NAME_RESOLVE and the ALL_* views. Will I enounter any unexpected issues where a random user won't be able to execute *DBMS_UTILITY.NAME_RESOLVE |
Quote:
|
or access the ALL_* views? |
connected user can access.
Quote:
|
If so, what's the role they should have to access these? |
execute DBMS_UTILITY.NAME_RESOLVE so no special privileges need be
granted:
SQL> create user blerp identified by blorp;
User created.
SQL> grant create session to blerp;
Grant succeeded.
SQL> grant select on test to blerp;
Grant succeeded.
SQL> connect blerp/blorp@###########
Connected.
SQL> declare
2 sch varchar2(40);
3 p1 varchar2(40);
4 p2 varchar2(40);
5 dblink varchar2(40);
6 p1t number;
7 objnum number;
8 begin
9 dbms_utility.name_resolve('BING.TEST',2,sch, p1,p2, dblink,
p1t, objnum);
10 dbms_output.put_line(sch||' '||p1||' '||p2||' '||
dblink||' '||p1t||' '||objnum);
11 end;
12 /
BING TEST 2 68806
PL/SQL procedure successfully completed.
SQL>
Quote:
|
Will that be a common case? |
Quote:
|
Many TIA, Mark -- Mark Harrison Pixar Animation Studios |
David Fitzjarrell
#4
| |||||
| |||||
|
|
I'm currently querying the USER_* views to do introspection on a user's schema. *But, in order to handle aliases, I need to use DBMS_UTILITY.NAME_RESOLVE and the ALL_* views. Will I enounter any unexpected issues where a random user won't be able to execute *DBMS_UTILITY.NAME_RESOLVE |
Quote:
|
or access the ALL_* views? |
connected user can access.
Quote:
|
If so, what's the role they should have to access these? |
execute DBMS_UTILITY.NAME_RESOLVE so no special privileges need be
granted:
SQL> create user blerp identified by blorp;
User created.
SQL> grant create session to blerp;
Grant succeeded.
SQL> grant select on test to blerp;
Grant succeeded.
SQL> connect blerp/blorp@###########
Connected.
SQL> declare
2 sch varchar2(40);
3 p1 varchar2(40);
4 p2 varchar2(40);
5 dblink varchar2(40);
6 p1t number;
7 objnum number;
8 begin
9 dbms_utility.name_resolve('BING.TEST',2,sch, p1,p2, dblink,
p1t, objnum);
10 dbms_output.put_line(sch||' '||p1||' '||p2||' '||
dblink||' '||p1t||' '||objnum);
11 end;
12 /
BING TEST 2 68806
PL/SQL procedure successfully completed.
SQL>
Quote:
|
Will that be a common case? |
Quote:
|
Many TIA, Mark -- Mark Harrison Pixar Animation Studios |
David Fitzjarrell
#5
| |||||
| |||||
|
|
I'm currently querying the USER_* views to do introspection on a user's schema. *But, in order to handle aliases, I need to use DBMS_UTILITY.NAME_RESOLVE and the ALL_* views. Will I enounter any unexpected issues where a random user won't be able to execute *DBMS_UTILITY.NAME_RESOLVE |
Quote:
|
or access the ALL_* views? |
connected user can access.
Quote:
|
If so, what's the role they should have to access these? |
execute DBMS_UTILITY.NAME_RESOLVE so no special privileges need be
granted:
SQL> create user blerp identified by blorp;
User created.
SQL> grant create session to blerp;
Grant succeeded.
SQL> grant select on test to blerp;
Grant succeeded.
SQL> connect blerp/blorp@###########
Connected.
SQL> declare
2 sch varchar2(40);
3 p1 varchar2(40);
4 p2 varchar2(40);
5 dblink varchar2(40);
6 p1t number;
7 objnum number;
8 begin
9 dbms_utility.name_resolve('BING.TEST',2,sch, p1,p2, dblink,
p1t, objnum);
10 dbms_output.put_line(sch||' '||p1||' '||p2||' '||
dblink||' '||p1t||' '||objnum);
11 end;
12 /
BING TEST 2 68806
PL/SQL procedure successfully completed.
SQL>
Quote:
|
Will that be a common case? |
Quote:
|
Many TIA, Mark -- Mark Harrison Pixar Animation Studios |
David Fitzjarrell
#6
| |||
| |||
|
|
Comments embedded. On Jun 2, 12:41*pm, m... (AT) pixar (DOT) com wrote: I'm currently querying the USER_* views to do introspection on a user's schema. *But, in order to handle aliases, I need to use DBMS_UTILITY.NAME_RESOLVE and the ALL_* views. Will I enounter any unexpected issues where a random user won't be able to execute *DBMS_UTILITY.NAME_RESOLVE I haven't found any, so no. *or access the ALL_* views? No, as the ALL_* views report all of the oibjects the currently connected user can access. If so, what's the role they should have to access these? Even a freshly created user with only CREATE SESSION privilege can execute DBMS_UTILITY.NAME_RESOLVE so no special privileges need be granted: SQL> create *user blerp identified by blorp; User created. SQL> grant create session to blerp; Grant succeeded. SQL> grant select on test to blerp; Grant succeeded. SQL> connect blerp/blorp@########### Connected. SQL> declare * 2 * * *sch * *varchar2(40); * 3 * * *p1 * * varchar2(40); * 4 * * *p2 * * varchar2(40); * 5 * * *dblink varchar2(40); * 6 * * *p1t number; * 7 * * *objnum number; * 8 *begin * 9 * * dbms_utility.name_resolve('BING.TEST',2,sch, p1,p2, dblink, p1t, objnum); *10 * * * * *dbms_output.put_line(sch||' * '||p1||' '||p2||''|| dblink||' '||p1t||' '||objnum); *11 *end; *12 */ BING * TEST * 2 68806 PL/SQL procedure successfully completed. SQL Will that be a common case? It should be common that you shouldn't have issues, in my opinion. Many TIA, Mark -- Mark Harrison Pixar Animation Studios David Fitzjarrell |
execute on dbms_utility to public via catproc. If you site has
removed some of the public grants you may need to restore the
privilege or provide the privilege via a role.
Also as mentioned the ALL_ views are sensitive to the querying user's
privilege set. If you are going to code any of the queries into
stored code you could be changing the result set unless you use
current user authorization.
HTH -- Mark D Powell --
#7
| |||
| |||
|
|
Comments embedded. On Jun 2, 12:41*pm, m... (AT) pixar (DOT) com wrote: I'm currently querying the USER_* views to do introspection on a user's schema. *But, in order to handle aliases, I need to use DBMS_UTILITY.NAME_RESOLVE and the ALL_* views. Will I enounter any unexpected issues where a random user won't be able to execute *DBMS_UTILITY.NAME_RESOLVE I haven't found any, so no. *or access the ALL_* views? No, as the ALL_* views report all of the oibjects the currently connected user can access. If so, what's the role they should have to access these? Even a freshly created user with only CREATE SESSION privilege can execute DBMS_UTILITY.NAME_RESOLVE so no special privileges need be granted: SQL> create *user blerp identified by blorp; User created. SQL> grant create session to blerp; Grant succeeded. SQL> grant select on test to blerp; Grant succeeded. SQL> connect blerp/blorp@########### Connected. SQL> declare * 2 * * *sch * *varchar2(40); * 3 * * *p1 * * varchar2(40); * 4 * * *p2 * * varchar2(40); * 5 * * *dblink varchar2(40); * 6 * * *p1t number; * 7 * * *objnum number; * 8 *begin * 9 * * dbms_utility.name_resolve('BING.TEST',2,sch, p1,p2, dblink, p1t, objnum); *10 * * * * *dbms_output.put_line(sch||' * '||p1||' '||p2||''|| dblink||' '||p1t||' '||objnum); *11 *end; *12 */ BING * TEST * 2 68806 PL/SQL procedure successfully completed. SQL Will that be a common case? It should be common that you shouldn't have issues, in my opinion. Many TIA, Mark -- Mark Harrison Pixar Animation Studios David Fitzjarrell |
execute on dbms_utility to public via catproc. If you site has
removed some of the public grants you may need to restore the
privilege or provide the privilege via a role.
Also as mentioned the ALL_ views are sensitive to the querying user's
privilege set. If you are going to code any of the queries into
stored code you could be changing the result set unless you use
current user authorization.
HTH -- Mark D Powell --
#8
| |||
| |||
|
|
Comments embedded. On Jun 2, 12:41*pm, m... (AT) pixar (DOT) com wrote: I'm currently querying the USER_* views to do introspection on a user's schema. *But, in order to handle aliases, I need to use DBMS_UTILITY.NAME_RESOLVE and the ALL_* views. Will I enounter any unexpected issues where a random user won't be able to execute *DBMS_UTILITY.NAME_RESOLVE I haven't found any, so no. *or access the ALL_* views? No, as the ALL_* views report all of the oibjects the currently connected user can access. If so, what's the role they should have to access these? Even a freshly created user with only CREATE SESSION privilege can execute DBMS_UTILITY.NAME_RESOLVE so no special privileges need be granted: SQL> create *user blerp identified by blorp; User created. SQL> grant create session to blerp; Grant succeeded. SQL> grant select on test to blerp; Grant succeeded. SQL> connect blerp/blorp@########### Connected. SQL> declare * 2 * * *sch * *varchar2(40); * 3 * * *p1 * * varchar2(40); * 4 * * *p2 * * varchar2(40); * 5 * * *dblink varchar2(40); * 6 * * *p1t number; * 7 * * *objnum number; * 8 *begin * 9 * * dbms_utility.name_resolve('BING.TEST',2,sch, p1,p2, dblink, p1t, objnum); *10 * * * * *dbms_output.put_line(sch||' * '||p1||' '||p2||''|| dblink||' '||p1t||' '||objnum); *11 *end; *12 */ BING * TEST * 2 68806 PL/SQL procedure successfully completed. SQL Will that be a common case? It should be common that you shouldn't have issues, in my opinion. Many TIA, Mark -- Mark Harrison Pixar Animation Studios David Fitzjarrell |
execute on dbms_utility to public via catproc. If you site has
removed some of the public grants you may need to restore the
privilege or provide the privilege via a role.
Also as mentioned the ALL_ views are sensitive to the querying user's
privilege set. If you are going to code any of the queries into
stored code you could be changing the result set unless you use
current user authorization.
HTH -- Mark D Powell --
#9
| |||
| |||
|
|
Comments embedded. On Jun 2, 12:41*pm, m... (AT) pixar (DOT) com wrote: I'm currently querying the USER_* views to do introspection on a user's schema. *But, in order to handle aliases, I need to use DBMS_UTILITY.NAME_RESOLVE and the ALL_* views. Will I enounter any unexpected issues where a random user won't be able to execute *DBMS_UTILITY.NAME_RESOLVE I haven't found any, so no. *or access the ALL_* views? No, as the ALL_* views report all of the oibjects the currently connected user can access. If so, what's the role they should have to access these? Even a freshly created user with only CREATE SESSION privilege can execute DBMS_UTILITY.NAME_RESOLVE so no special privileges need be granted: SQL> create *user blerp identified by blorp; User created. SQL> grant create session to blerp; Grant succeeded. SQL> grant select on test to blerp; Grant succeeded. SQL> connect blerp/blorp@########### Connected. SQL> declare * 2 * * *sch * *varchar2(40); * 3 * * *p1 * * varchar2(40); * 4 * * *p2 * * varchar2(40); * 5 * * *dblink varchar2(40); * 6 * * *p1t number; * 7 * * *objnum number; * 8 *begin * 9 * * dbms_utility.name_resolve('BING.TEST',2,sch, p1,p2, dblink, p1t, objnum); *10 * * * * *dbms_output.put_line(sch||' * '||p1||' '||p2||''|| dblink||' '||p1t||' '||objnum); *11 *end; *12 */ BING * TEST * 2 68806 PL/SQL procedure successfully completed. SQL Will that be a common case? It should be common that you shouldn't have issues, in my opinion. Many TIA, Mark -- Mark Harrison Pixar Animation Studios David Fitzjarrell |
execute on dbms_utility to public via catproc. If you site has
removed some of the public grants you may need to restore the
privilege or provide the privilege via a role.
Also as mentioned the ALL_ views are sensitive to the querying user's
privilege set. If you are going to code any of the queries into
stored code you could be changing the result set unless you use
current user authorization.
HTH -- Mark D Powell --
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.