I needhelp

[tracy]

I not sure if i posted to the rite group, but hope somebody can help.

as i know, system is the superpower user in oracle database. He can
views everything, including role created by others DBA.

My problem is when i connect as oracle, i unable to view roles created
by dba_01. unless i conncet as dba_01.

Can somebody tell me , what's wrong?


thanq!!

[Bricklen]

Re: I needhelp

tracy wrote:

I not sure if i posted to the rite group, but hope somebody can help.
>
as i know, system is the superpower user in oracle database. He can
views everything, including role created by others DBA.
>
My problem is when i connect as oracle, i unable to view roles created
by dba_01. unless i conncet as dba_01.
>
Can somebody tell me , what's wrong?
>
>
thanq!!

SYS is the Supreme Being....

Try prefixing your queries for dba_01's objects with that username:
eg. select * from dba_01.table_na me;

(or grant dba privs etc to oracle user --generally not a good idea)

etc etc

[Mark C. Stock]

Re: I needhelp


"Bricklen" <Xbricklen@yaho o.comXwrote in message
news:XQGSb.3426 18$X%5.28732@pd 7tw2no...
| tracy wrote:
|
| I not sure if i posted to the rite group, but hope somebody can help.
| >
| as i know, system is the superpower user in oracle database. He can
| views everything, including role created by others DBA.
| >
| My problem is when i connect as oracle, i unable to view roles created
| by dba_01. unless i conncet as dba_01.
| >
| Can somebody tell me , what's wrong?
| >
| >
| thanq!!
| SYS is the Supreme Being....
|
| Try prefixing your queries for dba_01's objects with that username:
| eg. select * from dba_01.table_na me;
|
| (or grant dba privs etc to oracle user --generally not a good idea)
|
| etc etc

unless you are connecting as a user that has SELECT privilege on DBA_ROLES
(typically via a granted role, like the legacy DBA role) you will not be
able to access DBA-restricted data, like the list of roles in the DBA_ROLES
table

could you give more detail as to what statements and accounts you are using
to create and then attempt to view the roles?

-- mcs

[tracy]

Re: I needhelp

hi, mcs,

I have dba_1 created a role called TESTING. From the SQL, if a connect
as dba_1, i can see the role.

SQLCONN dba_1/dba_1
Connected.
SQLselect distinct role from role_tab_privs;

ROLE
------------------------------
TESTING
ITEM_ROLE

And dba_2 has created ITEM_ROLE
SQLconn dba_2/dba_2
Connected.
SQLselect distinct role from role_tab_privs;

ROLE
------------------------------
ITEM_ROLE

But if, i conncet as dba, I can't see the role TESTING as below:

SQLconn system/manager
Connected.
SQLselect distinct role from role_tab_privs;

ROLE
------------------------------
POSTGRAD_ROLE
TUTORMNGT_ROLE
p/s: these two roles created using sysem_id.


I curios, why when i log in using system, i can't see ITEM_ROLE and
TESTING.
and why dba_1 can see role created by dba_2(Item_role )but dba_2 can't
see role created by dba_1 (TESTING role)

DBA_1 AND DBA_2 were granted DBA priviledge.


Thanz.

[Mark C. Stock]

Re: I needhelp


"tracy" <tracykim10@yah oo.com.hkwrote in message
news:5c91cce9.0 402030109.2b333 fa1@posting.goo gle.com...
| hi, mcs,
|
| I have dba_1 created a role called TESTING. From the SQL, if a connect
| as dba_1, i can see the role.
|
| SQLCONN dba_1/dba_1
| Connected.
| SQLselect distinct role from role_tab_privs;
|
| ROLE
| ------------------------------
| TESTING
| ITEM_ROLE
|
| And dba_2 has created ITEM_ROLE
| SQLconn dba_2/dba_2
| Connected.
| SQLselect distinct role from role_tab_privs;
|
| ROLE
| ------------------------------
| ITEM_ROLE
|
| But if, i conncet as dba, I can't see the role TESTING as below:
|
| SQLconn system/manager
| Connected.
| SQLselect distinct role from role_tab_privs;
|
| ROLE
| ------------------------------
| POSTGRAD_ROLE
| TUTORMNGT_ROLE
| p/s: these two roles created using sysem_id.
|
|
| I curios, why when i log in using system, i can't see ITEM_ROLE and
| TESTING.
| and why dba_1 can see role created by dba_2(Item_role )but dba_2 can't
| see role created by dba_1 (TESTING role)
|
| DBA_1 AND DBA_2 were granted DBA priviledge.
|
|
| Thanz.

Why are you looking in ROLE_TAB_PRIVS? That view does not contain the list
of roles, it lists table privileges granted to roles, specifically the roles
which the current user has granted to it.

DBA_ROLES is where you need to look. Also, take some time to read thru the
Oracle Reference Manual's chapter on 'Static Data Dictionary Views'.

Make sure that when you choose a table or view for your query that you are
choosing the object that most directly defines the data you're looking for,
not a table that contains (some) references to the data. this is important
when accessing the data dictionary and when writing application code.

-- mcs