Home » RDBMS Server » Security » why i cant create synonym
why i cant create synonym [message #296493] Sun, 27 January 2008 23:40 Go to next message
mm_kanish05
Messages: 481
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
Re: why i cant create synonym [message #296496 is a reply to message #296493] Sun, 27 January 2008 23:53 Go to previous messageGo to next message
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.
Re: why i cant create synonym [message #296498 is a reply to message #296493] Mon, 28 January 2008 00:10 Go to previous messageGo to next message
mm_kanish05
Messages: 481
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
Re: why i cant create synonym [message #296507 is a reply to message #296493] Mon, 28 January 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
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
Re: why i cant create synonym [message #296514 is a reply to message #296493] Mon, 28 January 2008 00:58 Go to previous messageGo to next message
mm_kanish05
Messages: 481
Registered: January 2007
Location: Chennai
Senior Member

hi michel,

Read my 1st post.

wbr
kanish
Re: why i cant create synonym [message #296522 is a reply to message #296514] Mon, 28 January 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
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
Re: why i cant create synonym [message #296528 is a reply to message #296522] Mon, 28 January 2008 01:26 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

OP, micheal is correct,
I was missed this point.
Re: why i cant create synonym [message #296532 is a reply to message #296493] Mon, 28 January 2008 01:40 Go to previous messageGo to next message
mm_kanish05
Messages: 481
Registered: January 2007
Location: Chennai
Senior Member

hi

Ok how to provide the synonym privilege to user.

wbr

kanish
Re: why i cant create synonym [message #296533 is a reply to message #296532] Mon, 28 January 2008 01:44 Go to previous messageGo to next message
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;

Re: why i cant create synonym [message #296537 is a reply to message #296533] Mon, 28 January 2008 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
grant CONNECT to username;

Not in 10g and up.

Regards
Michel
Re: why i cant create synonym [message #296540 is a reply to message #296493] Mon, 28 January 2008 02:25 Go to previous messageGo to next message
mm_kanish05
Messages: 481
Registered: January 2007
Location: Chennai
Senior Member

hi

Thank Mohammad, Nice help

wbr.

Kanish
Re: why i cant create synonym [message #296545 is a reply to message #296540] Mon, 28 January 2008 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice to see that what I say is useless for you.

Regards
Michel
Re: why i cant create synonym [message #296546 is a reply to message #296493] Mon, 28 January 2008 02:38 Go to previous messageGo to next message
mm_kanish05
Messages: 481
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
Re: why i cant create synonym [message #296547 is a reply to message #296546] Mon, 28 January 2008 02:46 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
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
Re: why i cant create synonym [message #296550 is a reply to message #296493] Mon, 28 January 2008 02:51 Go to previous message
mm_kanish05
Messages: 481
Registered: January 2007
Location: Chennai
Senior Member

hi

Again thanks

wbr

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


Current Time: Sat Oct 25 07:15:55 CDT 2014

Total time taken to generate the page: 0.09434 seconds