OraFAQ Forum: Security » why i cant create synonym

Home » RDBMS Server » Security » why i cant create synonym

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

why i cant create synonym [message #296493]

Sun, 27 January 2008 23:40

mm_kanish05
Messages: 479
Registered: January 2007
Location: Chennai

Senior Member

Hi Friends,

Here I have user 'NVS' on his login i can access table like this query.

   Select * from accl.all_master

but cant all_master
it said table_view does not exist

For that i Want to create synonym for the accl.all_master
when i try the below statament

create synonym all_master for accl.all_master

im getting insufficient privileges.

What privilege i want to provide for the user 'NVS'
for create the synonym.

but all_master is an view

Thank in advance

wbr
Kanish

Report message to a moderator

Re: why i cant create synonym [message #296496 is a reply to message #296493]

Sun, 27 January 2008 23:53

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Are you sure?
1. did you grant select privileges on "all_master" to NVS schema.
did you able to see result after execute below query ?
select * from accl.all_master;

2.

Report message to a moderator

Re: why i cant create synonym [message #296498 is a reply to message #296493]

Mon, 28 January 2008 00:10

mm_kanish05
Messages: 479
Registered: January 2007
Location: Chennai

Senior Member

hi,

'NVS' scheme privilege are drived from the 'FINADMIN' role
so i was provided the privilege to accl the table all_privilege.

After that only i can access the table on his longin but using accl.all_master.

wbr
kanish

Report message to a moderator

Re: why i cant create synonym [message #296507 is a reply to message #296493]

Mon, 28 January 2008 00:42

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

What privilege i want to provide for the user 'NVS' for create the synonym.

CREATE SYNNONYM

Regards
Michel

Report message to a moderator

Re: why i cant create synonym [message #296514 is a reply to message #296493]

Mon, 28 January 2008 00:58

mm_kanish05
Messages: 479
Registered: January 2007
Location: Chennai

Senior Member

hi michel,

Read my 1st post.

wbr
kanish

Report message to a moderator

Re: why i cant create synonym [message #296522 is a reply to message #296514]

Mon, 28 January 2008 01:18

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, I read the first post and you didn't say anywhere that your user has the privilege to create a synonym. More you say it has not (read your error message).

Regards
Michel

Report message to a moderator

Re: why i cant create synonym [message #296528 is a reply to message #296522]

Mon, 28 January 2008 01:26

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

OP, micheal is correct,
I was missed this point.

Report message to a moderator

Re: why i cant create synonym [message #296532 is a reply to message #296493]

Mon, 28 January 2008 01:40

mm_kanish05
Messages: 479
Registered: January 2007
Location: Chennai

Senior Member

hi

Ok how to provide the synonym privilege to user.

wbr

kanish

Report message to a moderator

Re: why i cant create synonym [message #296533 is a reply to message #296532]

Mon, 28 January 2008 01:44

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Through explicit
SQL> grant CREATE SYNONYM to username;

Through role
SQL> grant CONNECT to username;

Report message to a moderator

Re: why i cant create synonym [message #296537 is a reply to message #296533]

Mon, 28 January 2008 02:17

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

grant CONNECT to username;

Not in 10g and up.

Regards
Michel

Report message to a moderator

Re: why i cant create synonym [message #296540 is a reply to message #296493]

Mon, 28 January 2008 02:25

mm_kanish05
Messages: 479
Registered: January 2007
Location: Chennai

Senior Member

hi

Thank Mohammad, Nice help

wbr.

Kanish

Report message to a moderator

Re: why i cant create synonym [message #296545 is a reply to message #296540]

Mon, 28 January 2008 02:31

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Nice to see that what I say is useless for you.

Regards
Michel

Report message to a moderator

Re: why i cant create synonym [message #296546 is a reply to message #296493]

Mon, 28 January 2008 02:38

mm_kanish05
Messages: 479
Registered: January 2007
Location: Chennai

Senior Member

hi

Mohammad/michel what is the meaning of statment.

    grant connect to username

and what are all grant to will be provide by the above statements

wbr
kanish

Report message to a moderator

Re: why i cant create synonym [message #296547 is a reply to message #296546]

Mon, 28 January 2008 02:46

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

OP,
Actually "connect" is role with many privileges.

SQL> grant connect to a;

Grant succeeded.

SQL> conn a/a@db1
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK

8 rows selected.

It is not recommended to grant "connect" role to directly database user always better to grant explicitly required privileges.

PS: In my previous post i was show two way to grant "create synonym" privileges but it is not recommended to grant "connect" role directly so i apologize for that.

[Updated on: Mon, 28 January 2008 02:46]

Report message to a moderator

Re: why i cant create synonym [message #296548 is a reply to message #296546]

Mon, 28 January 2008 02:47

Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

7.0 to 9.2:
ALTER SESSION
CREATE CLUSTER
CREATE DATABASE LINK
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE VIEW

10.1 and up
CREATE SESSION

Regards
Michel

Report message to a moderator

Re: why i cant create synonym [message #296550 is a reply to message #296493]

Mon, 28 January 2008 02:51

mm_kanish05
Messages: 479
Registered: January 2007
Location: Chennai

Senior Member

hi

Again thanks

wbr

kanish

Report message to a moderator

Previous Topic:	Query Builder 6i to 10g
Next Topic:	Descriptions of these Logins

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 19:35:00 CDT 2013

Total time taken to generate the page: 0.12479 seconds