Re: Can I Assign A Synonym To A Role?

From: Y <yunus000_at_england.com>
Date: Wed, 01 Sep 1999 15:34:44 +0100
Message-ID: <37CD3984.23C1E18D@england.com>

simonpeterbeck_at_my-deja.com wrote:
>
> Hi!
>
> I have created a table, MYUSER.MYTABLE, and a
> role, MYROLE.
>
> I would like to create a synonym for
> MYUSER.MYTABLE that would be available to any
> user granted the role MYROLE.
>
> I tried CREATE SYNONYM MYROLE.MYTABLESYN FOR
> MYUSER.MYTABLE but this failed with the
> error "ORA-01917:user or role '' does not exist".
>
> I would like to avoid creating a public synonym
> (i.e. CREATE PUBLIC SYNONYM MYTABLESYN FOR
> MYUSER.MYTABLE).
>
> Is what I want to do possible?
>
> Thanks,
> Simon
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

If you are trying to restrict access to your table to users with MYROLE - then you need
to grant priviledges on that table (see manual for grant syntax) to MYROLE only.

This 'grant' will allow anyone with MYROLE to access the MYTABLE via 'MYUSER.MYTABLE' syntax. All users that need to access MYUSER.MYTABLE will need to create a synonym on their own schemas.

Maybe you could create SYNONYM in the MYUSER schema and use the GRANT statement on the synonym in the same way as the above.

Any DBA's out there with a better explanation/suggestion?

--

---

"Don't be suckered in by the comments - debug only the code." To reply by e-mail remove '000' from my address

---

Received on Wed Sep 01 1999 - 09:34:44 CDT