Home » SQL & PL/SQL » SQL & PL/SQL » Grant References to Role ? (Oracle 10g)
Grant References to Role ? [message #393712] Tue, 24 March 2009 02:39 Go to next message
rushimails
Messages: 24
Registered: November 2008
Location: Mumbai
Junior Member
Hi

Is it possible to give GRANT REFERENCES to a Role and then associate this Roles to sub Schemas.

e.g I have schemas APPS , MMS, ADMIN
I have certain/ALL tables in ADMIN Schema which have referential constraints in MMS Schema.
e.g Company Master (CompCD) in ADMIN Schema as Foreign Key in MMS schema for say Item Master.

So every time I make Table in ADMIN I have to give
GRANT REFERENCES ON << TABLE NAME>> TO MMS;

Instead can I make a Role give grant REFERENCES to this ROLE and associate this role to MMS.
So everytime I dont have to grant REFERENCES on all Objects.

Rgds
Rushi
Re: Grant References to Role ? [message #393718 is a reply to message #393712] Tue, 24 March 2009 03:05 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Why duplicate post ??

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Grant References to Role ? [message #393720 is a reply to message #393718] Tue, 24 March 2009 03:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
gentlebabu wrote on Tue, 24 March 2009 09:05

Why duplicate post ??

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Please indicate why the original post does not follow the guidelines.
Re: Grant References to Role ? [message #393723 is a reply to message #393720] Tue, 24 March 2009 03:13 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


>>Instead can I make a Role give grant REFERENCES to this ROLE and associate this role to MMS.


Yes It's possible Using Role. Have you tried?

babu
Re: Grant References to Role ? [message #393729 is a reply to message #393723] Tue, 24 March 2009 03:21 Go to previous messageGo to next message
rushimails
Messages: 24
Registered: November 2008
Location: Mumbai
Junior Member
Yes I did but with following error :

SQL> CONNECT SYSTEM/xxxxx@xxx
Connected.

SQL> CREATE ROLE ROLE_ADMIN_REF2ALL;
Role created.

SQL> GRANT REFERENCES TO ROLE_ADMIN_REF2ALL;
GRANT REFERENCES TO ROLE_ADMIN_REF2ALL
      *
ERROR at line 1:
ORA-01919: role 'REFERENCES' does not exist


Re: Grant References to Role ? [message #393730 is a reply to message #393729] Tue, 24 March 2009 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you should check the syntax how to use REFERENCES privilege. For your information, it is not a system privilege but an object one.

Anyway, you cannot grant REFERENCES privilege to a role.

Regards
Michel
Re: Grant References to Role ? [message #393732 is a reply to message #393729] Tue, 24 March 2009 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> grant references on t to r;
grant references on t to r
*
ERROR at line 1:
ORA-01931: cannot grant REFERENCES to a role

ORA-01931: cannot grant %s to a role
 *Cause:  UNLIMITED TABLESPACE, REFERENCES, INDEX, SYSDBA or SYSOPER
          privilege cannot be granted to a role.
 *Action: Grant privilege directly to the user.

Regards
Michel
Re: Grant References to Role ? [message #393733 is a reply to message #393729] Tue, 24 March 2009 03:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would be because it can't be done.

From the documentation

Quote:
REFERENCES


Create a constraint that refers to the table. You cannot grant this privilege to a role.


[Drat - Michel got there first]

[Updated on: Tue, 24 March 2009 03:33]

Report message to a moderator

Re: Grant References to Role ? [message #393748 is a reply to message #393733] Tue, 24 March 2009 04:55 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


I was thinking it's possible.

SQL> drop table parent;

Table dropped.

 SQL> create table parent (a int primary key);

Table created.
 

SQL> create table child (a int,b int, constraint fk foreign key (a) references parent (a));

Table created.

SQL> show user
USER is "BABU"
SQL>
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>

SQL> drop role ref;

Role dropped.

SQL>
SQL>
SQL> create role ref;

Role created.

 
SQL> grant references on babu.child to ref;

Grant succeeded.

SQL> grant ref to ram;

Grant succeeded.

Re: Grant References to Role ? [message #393761 is a reply to message #393748] Tue, 24 March 2009 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle version?

- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.


Regards
Michel

[Updated on: Tue, 24 March 2009 05:21]

Report message to a moderator

Re: Grant References to Role ? [message #393766 is a reply to message #393761] Tue, 24 March 2009 05:25 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Thanks Mr Michel. I got the details.

Thank you in advance.

Babu
Re: Grant References to Role ? [message #393770 is a reply to message #393766] Tue, 24 March 2009 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anyway what was your Oracle version (4 decimals)?

Regards
Michel

Re: Grant References to Role ? [message #393773 is a reply to message #393748] Tue, 24 March 2009 05:35 Go to previous messageGo to next message
rushimails
Messages: 24
Registered: November 2008
Location: Mumbai
Junior Member
To Single Object we definately can ,
I was wondering somehting to the User/Schema itslef without Identifying individual Tables/Objects in the User/Schema.
Re: Grant References to Role ? [message #393774 is a reply to message #393773] Tue, 24 March 2009 05:37 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

SQL> select * from v$version where rownum =1 ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product


Babu
Re: Grant References to Role ? [message #393778 is a reply to message #393773] Tue, 24 March 2009 05:47 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
To Single Object we definately can ,

I disagree, see my previous example.

In addition, to show you that what seems to "work" with SYS does not:
SYS> grant references on michel.t to r;

Grant succeeded.

SYS> revoke references on michel.t from r;
revoke references on michel.t from r
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

SYS> select * from dba_tab_privs where table_name='T' and owner='MICHEL';

no rows selected

Refer to what I said about SYS.
Quote:
I was wondering somehting to the User/Schema itslef without Identifying individual Tables/Objects in the User/Schema.

No.

Regards
Michel
Previous Topic: how to get the column value of previous row to current row? (merged 3)
Next Topic: merge multiple varchar2 rows into one row of type CLOB
Goto Forum:
  


Current Time: Mon Dec 05 11:15:40 CST 2016

Total time taken to generate the page: 0.06650 seconds