Home » SQL & PL/SQL » SQL & PL/SQL » Restricting an user to access only stored procedures
Restricting an user to access only stored procedures [message #218919] Mon, 12 February 2007 01:35 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hello gurus,

I am using Oracle 9.
I dont have any knowledge on the dba activities.
Can anyone please help me to do the below following tasks?

I need to create an user with only access to the stored procedures. He should not have any modification access to the existing tables or procedures/packages.

I can able to create the user by the following statement:

create user <username> identified by <password>;

Can anyone please let me know how to restrict the access to only stored procedures or packages?

Thanks in advance,
prashas_d

[Updated on: Mon, 12 February 2007 01:41]

Report message to a moderator

Re: Restricting an user to access only stored procedures [message #218926 is a reply to message #218919] Mon, 12 February 2007 02:04 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
GRANT EXECUTE on stored procedures to that user. However, it will not be able to run any of those which use tables, views etc. because you didn't grant access to those objects. "Safe" way to make it use them is granting SELECT privilege - user will be able to read, but not write data.

Is that what you are looking for? If not, say so and we'll see what next.
Re: Restricting an user to access only stored procedures [message #218929 is a reply to message #218926] Mon, 12 February 2007 02:33 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Littlefoot,

Thanks for the reply.

I have just got the answer to my question from one of my friends and I have done the required task.

I have created an user and I gave execute permission to all the existing stored procedures using the below statement:

grant execute any procedure to scott;

Even though the user does not have any privileges to access any of the tables that are being used by the procedures, he still can execute the procedures and insert the records in those tables.

I just made that test now.

Thanks,
prashas_d
Re: Restricting an user to access only stored procedures [message #219168 is a reply to message #218929] Tue, 13 February 2007 08:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No no no nononononono

Execute Any Procedure means just that - if there is a procedure anywhere in the database (including all those REALLY powerful ones owned by SYS, then a user with Execute Any Procedure can run that procedure.

You really really don't want to grant this privilige lightly. You want to grant a list of specific execute privileges to a Role, and then grant this role to your Execute only user

@Littlefoot - I don't think you're right about GRANT EXECUTE not letting you run procedures that access tables you can't see. If you run this example here, which creates two users, one of which has a procedure that does an insert into a table only it can see, and the other user has EXECUTE privs on that procedure, then when the second user runs the first users procedure, a row is still inserted.
SQL> create user test_user_1 identified by test_user_1;

User created.

SQL> grant dba to test_user_1;

Grant succeeded.

SQL> conn test_user_1/test_user_1@fps_oratest
Connected.
SQL> create table test_table(col_1 varchar2(10));

Table created.

SQL> create or replace procedure ins_test as
  2  begin
  3    insert into test_table (col_1) values (to_char(sysdate,'hh24:mi:ss'));
  4    commit;
  5* end;
SQL> /

Procedure created.

SQL> conn john_test/john_test@fps_Oratest
Connected.
SQL> create user test_user_2 identified by test_user_2 ;

User created.

SQL> grant execute on test_user_1.ins_test to test_user_2;

Grant succeeded.

SQL> grant create session to test_user_2;

Grant succeeded.

SQL> conn test_user_2/test_user_2@fps_oratest
Connected.
SQL> begin
  2    test_user_1.ins_test;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> conn test_user_1/test_user_1@fps_oratest
Connected.
SQL> select * from test_table;

COL_1
----------
14:13:39
Re: Restricting an user to access only stored procedures [message #219174 is a reply to message #219168] Tue, 13 February 2007 08:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I totally agree with JRowbottom on this.
Granting execute any privilege might look like the easy way out, but there are few things more dangerous than a lazy dba..
Re: Restricting an user to access only stored procedures [message #219264 is a reply to message #219174] Tue, 13 February 2007 13:44 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, I was terribly wrong. Thank you, JRowbottom, for taking time and creating a test case.

This is what documentation says (an excerpt):
Quote:
  • If the procedure is a definer's-rights procedure, then it runs with the privileges of the procedure owner. The owner must have all the necessary object privileges for any referenced objects.
  • If the procedure is an invoker's-rights procedure, then it runs with your privileges (as the invoker). In this case, you also need privileges on all referenced objects; that is, all objects accessed by the procedure through external references that are resolved in your schema.

If I understood it correctly, it means that if your 'INS_TEST' procedure was created as
create procedure ins_test
AUTHID CURRENT_USER
is ...
(note AUTHID clause), this would not work: USER_1 should explicitly grant INSERT on 'test_table' to USER_2 and, moreover, table name should also be qualified with schema name:
insert into USER_1.TEST_TABLE values ...
in order for this procedure to work when ran connected as USER_2.

So, after taking some time (which I should have done much earlier, before posting my answer - or not posting it at all - but hey! It is Frank to blame because he didn't provide a link to that Java Ranch "polite answering" pages in time), I came to such a conclusion. Did I correctly interpret it, or am I (again) missing something? I would be grateful if you could put a comment on it.

I apologize if my incorrect thoughts caused problems to anyone.
Re: Restricting an user to access only stored procedures [message #219277 is a reply to message #219264] Tue, 13 February 2007 14:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Of course I will take all responsibility.
Mea Culpa
Re: Restricting an user to access only stored procedures [message #219323 is a reply to message #219277] Wed, 14 February 2007 00:51 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Not enough. You forgot the MAXIMA. ./fa/1606/0/
Re: Restricting an user to access only stored procedures [message #219324 is a reply to message #219323] Wed, 14 February 2007 00:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Anonymous wrote
You forgot the MAXIMA


You should know there is no natural order in a relational database!

[Updated on: Wed, 14 February 2007 00:55]

Report message to a moderator

Re: Restricting an user to access only stored procedures [message #219339 is a reply to message #219324] Wed, 14 February 2007 01:23 Go to previous message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/2133/0/
Previous Topic: FORALL and Bulk collect
Next Topic: How to stringify a generic set of columns in a ROWTYPE variable?
Goto Forum:
  


Current Time: Tue Dec 06 00:26:25 CST 2016

Total time taken to generate the page: 0.05548 seconds