OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » synonyms (Developer 6i, Databse 11G R2,window 7 ultimate)

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

synonyms [message #635068]

Thu, 19 March 2015 13:24

kilimanjaro
Messages: 151
Registered: May 2009
Location: Tanzania

Senior Member

Hello Buddies

Please assist

I have one user called CMX and he owns 1000+ tables.He is the main user of the system.

My question is, when I create additional user of the system I must either create table views or synonyms.
If its synonyms, is the a way of granting access to all CMX tables to additional user in a single line?

like 'create public synonyms for cmx all tables????'

Thank you in Advance.

Report message to a moderator

Re: synonyms [message #635071 is a reply to message #635068]

Thu, 19 March 2015 13:41

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

synonym <> grant
Do not confuse them.
Synonym allows to give another name to access an object but it does not allow you to access this object.
Grant allows to access the object.

So you have 3 questions.
1) How to create a a public synonym or private synonyms for all users (better solution) when you create a new object in the owner schema
2) How to grant access to owner objects when a new user comes
3) How to create synonyms on owner objects when a new user comes

2) is done using a role which contains the necessary privileges for all users; so when a new one comes you just have to grant him the role.

3) can be done with public synonyms but this is not a good solution because:
* You can't have several times the same application in different owner schema
* You may have synonym with same than another application which already exists or will come some day
* You will not be able to use Editions which allow online application upgrade
So how to do it with private synonyms? There is no direct way, you have to write a script which will automatically generate these synonyms (not a big work and you just have to do it once)

1) is done with the same trick than 3)

Report message to a moderator

Re: synonyms [message #635072 is a reply to message #635071]

Thu, 19 March 2015 13:56

kilimanjaro
Messages: 151
Registered: May 2009
Location: Tanzania

Senior Member

Thank you Michel

In my case, Assume the main user CMX has two tables. employee and salaries
so when new user KENGE comes what do I do? log in as CMX then
create public synonym for employee ;
create public synonym for salaries ;??

Thank you.

Report message to a moderator

Re: synonyms [message #635073 is a reply to message #635072]

Thu, 19 March 2015 14:13

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It depends on the options you choose among those I posted.
1/ Private or public synonyms
2/ Direct privilege or role

Report message to a moderator

Re: synonyms [message #635075 is a reply to message #635073]

Thu, 19 March 2015 14:18

kilimanjaro
Messages: 151
Registered: May 2009
Location: Tanzania

Senior Member

I go for role.Thank you

Report message to a moderator

Re: synonyms [message #635076 is a reply to message #635075]

Thu, 19 March 2015 14:33

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

-- do one time only:
CREATE PUBLIC SYNONYM employee FOR cmx.employee;
CREATE PUBLIC SYNONYM salaries FOR cmx.salaries;
CREATE ROLE cmx_access;
GRANT SELECT ON employee TO cmx_access;
GRANT SELECT ON salaries TO cmx_access;

-- do once for each new user:
GRANT cmx_access TO kenge;

Report message to a moderator

Re: synonyms [message #635085 is a reply to message #635076]

Fri, 20 March 2015 01:52

kilimanjaro
Messages: 151
Registered: May 2009
Location: Tanzania

Senior Member

Thank you all
Final piece please

Do I log in as system or the object owner?

eg CMX when granting kenge some previleges?

Report message to a moderator

Re: synonyms [message #635086 is a reply to message #635085]

Fri, 20 March 2015 01:55

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you go to role and pubic synonyms, you have to connect to user that can
1) grant object privilege on CMX objects, so himself or a DBA (like SYSTEM)
2) create public synonym, so CMX if it can or a DBA.

Report message to a moderator

Re: synonyms [message #635089 is a reply to message #635086]

Fri, 20 March 2015 02:12

kilimanjaro
Messages: 151
Registered: May 2009
Location: Tanzania

Senior Member

Thank you Michel .Understood.

Report message to a moderator

Re: synonyms [message #635098 is a reply to message #635076]

Fri, 20 March 2015 06:45

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

GRANT SELECT ON cmx.employee TO cmx_access;
GRANT SELECT ON cmx.salaries TO cmx_access;

Report message to a moderator

Re: synonyms [message #635125 is a reply to message #635098]

Sat, 21 March 2015 06:09

kilimanjaro
Messages: 151
Registered: May 2009
Location: Tanzania

Senior Member

Hello Michel,
Thank you for you help,though it's a bit confusing due to the results I got.These are the steps I followed.

Log in as system and GRANT CREATE SYNONYM TO CMX;
log in as CMX and CREATE ROLE cmx_access;
log in as kenge and CREATE PUBLIC SYNONYM employee FOR cmx.employee;
log in as CMX and GRANT SELECT ON employee TO cmx_access;
log in as CMX and GRANT cmx_access TO kenge;

Is this what I should do?

If so? what is the use of the role here?because Kenge can still delete and update table instead of only select.

Thank you.

Report message to a moderator

Re: synonyms [message #635126 is a reply to message #635125]

Sat, 21 March 2015 08:04

EdStevens
Messages: 1377
Registered: September 2013

Senior Member

kilimanjaro wrote on Sat, 21 March 2015 06:09

Hello Michel,
Thank you for you help,though it's a bit confusing due to the results I got.These are the steps I followed.

Log in as system and GRANT CREATE SYNONYM TO CMX;

No. That is a security hole. Creating synonyms is the job of the DBA.

Quote:

log in as CMX and CREATE ROLE cmx_access;

No. creating roles is the job of the dba

Quote:

log in as kenge and CREATE PUBLIC SYNONYM employee FOR cmx.employee;

No. creating synonyms is the job of the dba. Besides, you didn't even grant that privlige to kenge, you granted it to CMX.

Quote:

log in as CMX and GRANT SELECT ON employee TO cmx_access;

possibly, but better to be done by a DBA

Quote:

log in as CMX and GRANT cmx_access TO kenge;

No, that should be done by a dba

Quote:

Is this what I should do?

If so? what is the use of the role here?because Kenge can still delete and update table instead of only select.

We don't know what else you've granted to who by who.

No.
First you should have your own account, with the role DBA granted to it

- conn / as sysdba
- sql> create user estevens .....
- sql> grant dba to estevens

- conn estevens/pwd (all following actions taken by estevens, a DBA

sql> create role cmx_access;
sql> CREATE PUBLIC SYNONYM employee FOR cmx.employee;
sql> GRANT SELECT ON cmx.employee TO cmx_access;
sql> GRANT cmx_access TO kenge;

[EDITED by LF: fixed some invalid tags]

[Updated on: Sat, 21 March 2015 09:16] by Moderator

Report message to a moderator

Re: synonyms [message #635129 is a reply to message #635126]

Sat, 21 March 2015 12:36

kilimanjaro
Messages: 151
Registered: May 2009
Location: Tanzania

Senior Member

Thank you very much EdStevens. It has worked.

Report message to a moderator

Re: synonyms [message #635132 is a reply to message #635129]

Sun, 22 March 2015 07:37

EdStevens
Messages: 1377
Registered: September 2013

Senior Member

kilimanjaro wrote on Sat, 21 March 2015 12:36

Thank you very much EdStevens. It has worked.

Good.

Now, what did you learn from this? I'm much more interested in helping people increase their understanding of how things work and why we do what we do. I'm not interested in just handing someone code for them to just blindly execute to get past their immediate problem.

Report message to a moderator

Previous Topic:	REGEXP_REPLACE Help NEEDED....
Next Topic:	DBMS_JOB TO run every saturday starting next saturday

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 27 03:36:56 CDT 2026