Home » RDBMS Server » Security » Package body,triggers are not appearing on "Connect" user (11g, 11.2.0.1, window server )
Package body,triggers are not appearing on "Connect" user [message #599100] Tue, 22 October 2013 04:17 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear All,

I need to create a user who has have only view privilges.

Below are my steps-
***************  To create new user and assigned to users TB ************************
create user genisys
identified by genisys
default tablespace users


***************   Grant connect session to user *******************************
grant connect to genisys


grant execute any procedure to genisys
grant select any table to genisys
grant SELECT ANY SEQUENCE to genisys
grant execute ANY type to genisys



I know that "EXECUTE ANY PROGRAM" / "EXECUTE ANY PROCEDURE" will allow user to do changes if user has privilges other than "CONNECT" role. Now, problem is, package body / triggers are not appearing under "GENISYS" user.

Kindly Assist me to sort-out the problem.

Regards,
Ishika
Re: Package body,triggers are not appearing on "Connect" user [message #599104 is a reply to message #599100] Tue, 22 October 2013 04:30 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Forget about predefined roles (such as CONNECT or RESOURCE which were rather popular a few versions back). Nowadays, you are supposed to grant privileges you are interested to (you can include them into your own role, though).

As of packages etc. "not appearing" - why would they? User GENISYS doesn't own them. You could create synonyms for these objects in GENISYS schema.
icon4.gif  Re: Package body,triggers are not appearing on "Connect" user [message #599105 is a reply to message #599100] Tue, 22 October 2013 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No user show have any ANY privileges. Remove that now.

Explain in details what you need exactly.

Re: Package body,triggers are not appearing on "Connect" user [message #599113 is a reply to message #599105] Tue, 22 October 2013 05:04 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

User should have privileges to select all the objects from all the schemas but restricted to edit them (Tables/procedures/packages/body/view/triggers etc).

Regards,
Ishika
Re: Package body,triggers are not appearing on "Connect" user [message #599118 is a reply to message #599113] Tue, 22 October 2013 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
User should have privileges to select all the objects from all the schemas but restricted to edit them


No this should never happen.
What is the functional needs for this?

Why do you mean EXACTLY by SELECT? You don't select a procedure.

Re: Package body,triggers are not appearing on "Connect" user [message #599122 is a reply to message #599118] Tue, 22 October 2013 05:51 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

Need to create a user who would have access to all the objects (only to view purpose) from the entire Schemas.

Assist me to enhance the requirement.

Regards,
Ishika
Re: Package body,triggers are not appearing on "Connect" user [message #599123 is a reply to message #599122] Tue, 22 October 2013 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Need you answer my questions.

Re: Package body,triggers are not appearing on "Connect" user [message #599127 is a reply to message #599123] Tue, 22 October 2013 06:12 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

What is the functional needs for this?
>> developer, doesn't have access to this DB but they want to check the procedure/table/packages which found Invalid.

Why do you mean EXACTLY by SELECT? You don't select a procedure.
>> Developer can query data from the tables and its respective views / can see the packages and its body / triggers.

Here, SELECT means, specified user can query.

This is what our requirement.
icon4.gif  Re: Package body,triggers are not appearing on "Connect" user [message #599133 is a reply to message #599127] Tue, 22 October 2013 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
developer, doesn't have access to this DB but they want to check the procedure/table/packages which found Invalid


If it is a development database you can grant them full access to the owner schema.
If it is a production database, they should have anyway no access to it. Objects should not become invalid unless illegal accesses has be made. If this appears, a new installation of the application should be made otherwise it should never be safe.

Re: Package body,triggers are not appearing on "Connect" user [message #599134 is a reply to message #599127] Tue, 22 October 2013 07:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
ishika_20 wrote on Tue, 22 October 2013 12:12
Dear Michel,

What is the functional needs for this?
>> developer, doesn't have access to this DB but they want to check the procedure/table/packages which found Invalid.

Why do you mean EXACTLY by SELECT? You don't select a procedure.
>> Developer can query data from the tables and its respective views / can see the packages and its body / triggers.

Here, SELECT means, specified user can query.

This is what our requirement.
Perhaps you could grant SELECT ANY TABLE as you have done, with SELECT ANY DICTIONARY and EXECUTE ON DBMS_METADATA to give access to the source code? I haven't tried this.
It is a weird requirement.
Re: Package body,triggers are not appearing on "Connect" user [message #599137 is a reply to message #599134] Tue, 22 October 2013 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
you could grant SELECT ANY TABLE

I'd not like anyone can get my bank balance or my medical records.

Re: Package body,triggers are not appearing on "Connect" user [message #599205 is a reply to message #599137] Wed, 23 October 2013 00:24 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear John,

I have given below accesses to the user but still he can't see package body/triggers. Though able to view package specification.

***************   Grant connect session to user *******************************
grant connect to genisys

grant execute any procedure to genisys
grant select any table to genisys
grant SELECT ANY SEQUENCE to genisys
grant execute ANY type to genisys
grant execute ANY program to genisys
grant execute on dbms_metadata to genisys
grant select any dictionary to genisys



When I give provide DBA privileges then user can able to view all the package body along with its respective specifications.

Dear Michel,

Requirement is like that, developer can see the object over testing database through provided user name.

Regards,
Ishika

[Updated on: Wed, 23 October 2013 00:26]

Report message to a moderator

icon13.gif  Re: Package body,triggers are not appearing on "Connect" user [message #599220 is a reply to message #599205] Wed, 23 October 2013 01:23 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If it is a testing database, just give them the password of the owner account.
What is the problem then?
Note that the privileges you want (or need to give to your user are more powerful than just give the owner password.
Note that you cannot give the privileges you want to give and still make the user a read only one.
So your "requirements" are impossible to fulfil and the best solution is the one I mention.

Previous Topic: Oracle database vault query
Next Topic: User of DBA role
Goto Forum:
  


Current Time: Thu Mar 28 14:23:59 CDT 2024