Home » RDBMS Server » Security » Update role
Update role [message #115095] Thu, 07 April 2005 08:06 Go to next message
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.
Re: Update role [message #115102 is a reply to message #115095] Thu, 07 April 2005 08:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous messageGo to next message
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

Thank you.

Regards.
Antonio
Re: Update role [message #115814 is a reply to message #115811] Thu, 14 April 2005 09:52 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I have no other idea.
anyhow first you need to upgrade to atleast oracle 9i.
Previous Topic: creating role with password
Next Topic: NOT using sqlplus in ora8 database??
Goto Forum:
  


Current Time: Thu Mar 28 04:44:50 CDT 2024