OraFAQ Forum: Security » Update role

Home » RDBMS Server » Security » Update role

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update role [message #115095]

Thu, 07 April 2005 08:06

ntnboi
Messages: 17
Registered: April 2005
Location: Italy - Rome

Junior Member

Hi,
I have follow question.

USER A
USER B

I want that user A can select all tables of user B

for make this:

1. - create a rule ROLE_SEL_USER_A;

2. - add role ROLE_SEL_USER_A to user A;

3. - execute follow script

set pages 0
set linesize 120
spool creatre_grant_select_user_A.sql
SELECT 'GRANT SELECT ON B.'||OBJECT_NAME|| ' TO ROLE_SEL_USER_A;'
FROM DBA_OBJECTS where owner='B' and object_type='TABLE';
spool off

4. - launch creatre_grant_select_user_A.sql by user B.

User A now can select all tables of user B, but if user B create a new table, user A do not select this new table.
I make steps 3,4 every time user B create a new table? Or is possible create a role outoupdating?

Thank you.
Best regards.

Antonio.

Report message to a moderator

Re: Update role [message #115102 is a reply to message #115095]

Thu, 07 April 2005 08:30

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Only method i know is to make use on an ON SCHEMA trigger.

Thomas Kyte has a given a perfect example.
http://asktom.oracle.com/pls/ask/f?p=4950:8:1127903949901447524::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:646423863863

fixed the typo in URL.

[Updated on: Thu, 07 April 2005 08:33]

Report message to a moderator

Re: Update role [message #115811 is a reply to message #115102]

Thu, 14 April 2005 09:48

ntnboi
Messages: 17
Registered: April 2005
Location: Italy - Rome

Junior Member

Hi, I have 8.0.6 oracle RDBMS version, the SCHEMA trigger work since 8.1.6. Sad

Sad

Thank you.

Regards.
Antonio

Report message to a moderator

Re: Update role [message #115814 is a reply to message #115811]

Thu, 14 April 2005 09:52

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I have no other idea.
anyhow first you need to upgrade to atleast oracle 9i.

Report message to a moderator

Previous Topic:	creating role with password
Next Topic:	NOT using sqlplus in ora8 database??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 18:35:08 CDT 2025