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]

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_name;

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

etc etc

[Mark C. Stock]

"Bricklen" <Xb*******@yahoo.comX> wrote in message
news:XQGSb.342618$X%5.28732@pd7tw2no...
| 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_name;
|
| (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]

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.

SQL> CONN dba_1/dba_1
Connected.
SQL> select distinct role from role_tab_privs;

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

And dba_2 has created ITEM_ROLE
SQL> conn dba_2/dba_2
Connected.
SQL> select distinct role from role_tab_privs;

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

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

SQL> conn system/manager
Connected.
SQL> select 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]

"tracy" <tr********@yahoo.com.hk> wrote in message
news:5c**************************@posting.google.c om...
| 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.
|
| SQL> CONN dba_1/dba_1
| Connected.
| SQL> select distinct role from role_tab_privs;
|
| ROLE
| ------------------------------
| TESTING
| ITEM_ROLE
|
| And dba_2 has created ITEM_ROLE
| SQL> conn dba_2/dba_2
| Connected.
| SQL> select distinct role from role_tab_privs;
|
| ROLE
| ------------------------------
| ITEM_ROLE
|
| But if, i conncet as dba, I can't see the role TESTING as below:
|
| SQL> conn system/manager
| Connected.
| SQL> select 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