Home » SQL & PL/SQL » SQL & PL/SQL » Grant select on other_user1.table_name to other_user2
Grant select on other_user1.table_name to other_user2 [message #356149] Wed, 29 October 2008 14:44 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
I have 4 users A, B, C, and D

A has obscene privileges and is accessible to almost no one, but has several packages and functions which B has execute privileges on.

One of these functions needs to be able to run something like the following:

execute immediate 'grant select, update, insert, delete on C.table_name to D';

But fails due to: ORA-01031: insufficient privileges

I've already granted (amongst a host of other privileges) select any table, update any table, delete any table and insert any table.

Can someone point me to the necessary privilege?

[Updated on: Wed, 29 October 2008 14:46]

Report message to a moderator

Re: Grant select on other_user1.table_name to other_user2 [message #356152 is a reply to message #356149] Wed, 29 October 2008 15:02 Go to previous messageGo to next message
rodolpho
Messages: 6
Registered: August 2008
Location: Rio de Janeiro, Brazil
Junior Member
Hi TMcallister,

Wich user are function owner?
Wich user are execute the function?

Regards,
Rodolpho
Re: Grant select on other_user1.table_name to other_user2 [message #356153 is a reply to message #356152] Wed, 29 October 2008 15:03 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
rodolpho wrote on Wed, 29 October 2008 14:02

Wich user are function owner?


A

rodolpho wrote on Wed, 29 October 2008 14:02
Wich user are execute the function?


B
Re: Grant select on other_user1.table_name to other_user2 [message #356154 is a reply to message #356149] Wed, 29 October 2008 15:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With code instead of what you think there is you likely have more accurate and appropriate answers.

Post a full test case we can reproduce.

Regards
Michel
Re: Grant select on other_user1.table_name to other_user2 [message #356156 is a reply to message #356149] Wed, 29 October 2008 15:16 Go to previous messageGo to next message
rodolpho
Messages: 6
Registered: August 2008
Location: Rio de Janeiro, Brazil
Junior Member
Hi TMcallister,

When you execute a PL/SQL block, the grants to execute isn't by the caller user. The block run using grants by owner user.
You sad that your user A is almost grants no one, so, check the rights user A to execute your function. If you desire that your function running by grants from user caller, you must include the AUTHID [USER_NAME] in your function, like this:
CREATE OR REPLACE FUNCTION teste (
  2     p_A IN VARCHAR2,
  3     p_B IN VARCHAR2 DEFAULT ',' )
  4  RETURN VARCHAR2
       Look this!!!!!
  5     AUTHID CURRENT_USER AS
  6     
 11  BEGIN
        null;
 20  END;
 21  / 


Regards,
Rodolpho
Re: Grant select on other_user1.table_name to other_user2 [message #356157 is a reply to message #356149] Wed, 29 October 2008 15:19 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
WITH GRANT OPTION is something you should look at (I think, based on the pseudo example). Like Michel said, real code helps much more than translating code to a "simpler" form.
Re: Grant select on other_user1.table_name to other_user2 [message #356160 is a reply to message #356149] Wed, 29 October 2008 15:26 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you, perhaps, grant certain privileges to ROLES (and then granted roles to users)? If so, these privileges will not apply in PL/SQL code (as you've mentioned packages, procedures and EXECUTE IMMEDIATE which is used in PL/SQL).

So, granting privileges directly to users might help if the above is correct.
Re: Grant select on other_user1.table_name to other_user2 [message #356161 is a reply to message #356149] Wed, 29 October 2008 15:30 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
It's WITH ADMIN OPTION (as opposed to grant).

Right now I just have SQLPlus window open to A and am just trying to:
grant update on c.table_name to d;

No special functions or procedures or anything, although that will be the final setup (so please don't supply a role that will do this).

I'll look at getting some specific test code, but if anyone can get this working with any 3 arbitrary users I'd be interested in the privileges granted.
Re: Grant select on other_user1.table_name to other_user2 [message #356162 is a reply to message #356149] Wed, 29 October 2008 15:34 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
No I have not used roles, in fact I've used parsed through the privileges grant to several roles (via the ROLE_SYS_PRIVS table) granted to the SYSTEM user, since the SYSTEM user can perform this SQL just fine.
Re: Grant select on other_user1.table_name to other_user2 [message #356171 is a reply to message #356149] Wed, 29 October 2008 16:47 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
For reference the privilege needed is: grant any object privilege.

Grant grant any object privilege to A;
Re: Grant select on other_user1.table_name to other_user2 [message #356265 is a reply to message #356171] Thu, 30 October 2008 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not a reference, this is a silly option.
You could also grant all privileges to your user and why not always use SYS?

Regards
Michel
Re: Grant select on other_user1.table_name to other_user2 [message #356640 is a reply to message #356149] Fri, 31 October 2008 12:25 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
I was under the impression, and so is my DBA/manager, that this is fine since no one has direct access to this user, only execute on a package whose procedures are vetted before being added.

If there is a better/more limiting grant, or a better setup, I'd be glad to hear it!

[Updated on: Fri, 31 October 2008 12:26]

Report message to a moderator

Re: Grant select on other_user1.table_name to other_user2 [message #356644 is a reply to message #356640] Fri, 31 October 2008 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I already requested post a full test case reproducing what you want to do.

Regards
Michel
Re: Grant select on other_user1.table_name to other_user2 [message #356658 is a reply to message #356149] Fri, 31 October 2008 15:37 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Edit: just noticed I switched the order of the users from previous descriptions. D is now the privileged user with package/procedures, C is now the one that executes the procedures and has a public face, A and B hold the data proper.

/sigh.

My environment varies from 10.1 to 11, this was done on 10.1

Create your own tablespace.

Create the A, B, C, D users via the following:
CREATE USER a IDENTIFIED BY a DEFAULT TABLESPACE <TABLESPACE> TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT CREATE SESSION TO a;
GRANT CREATE TABLE TO a;
GRANT CREATE VIEW TO a;
GRANT CREATE SYNONYM TO a;
GRANT CREATE SEQUENCE TO a;
GRANT CREATE DATABASE LINK TO a;
ALTER USER a QUOTA UNLIMITED ON <TABLESPACE>;


On the D user only add the following:
GRANT CREATE PROCEDURE TO d;
GRANT CREATE ANY SYNONYM TO d;
GRANT GRANT ANY OBJECT PRIVILEGE TO D; --the privilege in question


On A run the following:
CREATE TABLE test_tab AS SELECT 'A' test_col  FROM DUAL;


On B run the following:
CREATE TABLE test_tab AS SELECT 'B' test_col  FROM DUAL;


On D run the following:
CREATE OR REPLACE PROCEDURE update_synonym (schema_name VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE    'create or replace synonym C.test_tab for '
                     || schema_name
                     || '.test_tab';

   EXECUTE IMMEDIATE    'grant delete, insert, select, update on '
                     || schema_name
                     || '.test_tab to C';
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20000,
                                  'Unknown Exception Raised: '
                               || SQLCODE
                               || ' '
                               || SQLERRM
                              );
END update_synonym;

GRANT EXECUTE ON update_synonym TO c;


On the C user execute the following:
exec d.update_synonym('A');


You should be able to select, insert, delete, and update this table as though it existed in the C schema.

On the C user execute the following:
exec d.update_synonym('B');


You should be able to select, insert, delete, and update this table as though it existed in the C schema.

The obvious benefit to this arrangement are:
1) Updates/rebuilds can be made/done to a large database without taking the database down.
2) If a critical error is discovered in the current data, the last build can be made immediately available.

Comments/criticisms of this process are welcome!

It should be noted that A, B, and D will NOT be publicly available. Ideally I would want the procedure to be executed from a fifth user (E), with only C being publicly available, but our DBA said that wasn't necessary. Also I have very little understanding/control over the processes that make A and B, so making changes to privileges there isn't really an option. Also D already has all the individual privileges granted by the IMP/EXP_FULL_DATABASE roles so as to perform datapumps in PL/SQL API, so it's privileges are already so godly that they it's access is going to be heavily regulated (I don't even have direct access in our loading environment, let alone production).

[Updated on: Fri, 31 October 2008 16:20]

Report message to a moderator

Re: Grant select on other_user1.table_name to other_user2 [message #357482 is a reply to message #356149] Wed, 05 November 2008 10:11 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
I don't mean to be a nag; I'm not justified to your time.

But asking for a test case, then not responding after I give one is somewhat annoying. Is the test case deficient in someway?
Re: Grant select on other_user1.table_name to other_user2 [message #357489 is a reply to message #357482] Wed, 05 November 2008 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are an average of 220 messages per day.
I read between 120 to 170 of them.
I answer an average of 30 per day.

Sometimes I miss some.
Sometimes I give up on some.
Sometimes I think it's worth I think more and take more time on some (but then forget it).

There are many reasons for me to not answer (especially after having asked many times for an information).

Now I will switch to another question after posting new questions:
- Who is D? What is its function? Is it a DBA?
- What is the purpose of this function? Why the object owner does not do these tasks? Why is this D that does this for other schemas?

Regards
Michel
Re: Grant select on other_user1.table_name to other_user2 [message #357502 is a reply to message #357489] Wed, 05 November 2008 12:21 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Michel Cadot wrote on Wed, 05 November 2008 09:44

- Who is D? What is its function? Is it a DBA?
- What is the purpose of this function? Why the object owner does not do these tasks? Why is this D that does this for other schemas?



D has not been granted to DBA role. Most of what it does it does through packages/procedures which makes privileges granted via roles moot. However it has been granted many of the individual privileges of DBA, IMP_FULL_DATABASE, EXP_FULL_DATABASE. In the sense of who has access it is very limited as it is *very* privileged and in this sense it may be considered DBA.

D's function is to facilitate the automation of the dropping, creating and population (via datapump) of several steps in a multistep monthly production process, from raw data from various sources in multiple formats to a consolidated industry standard database, to client deliveries, and finally an upgraded post-process internal-only version.

I'd rather have these privileges gather in one spot, than scattered around. The privileges to do a datapump are definitely not trivial.
Re: Grant select on other_user1.table_name to other_user2 [message #357518 is a reply to message #357502] Wed, 05 November 2008 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So if you exactly what is the purpose of D and knows how much privileged it is, then it is OK. My answer was about an anonymous D, a common user.
It appears you know what you do, I'd just recommend to lock the account when it is not used (and even expire the password and change it each month).

Regards
Michel
Re: Grant select on other_user1.table_name to other_user2 [message #357760 is a reply to message #357502] Thu, 06 November 2008 08:25 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The minimum needed is for user a and user b to grant the required privileges (select, insert, update, delete) on table test_tab directly (not through a role) to user d with GRANT (not admin) option, as previously suggested by joy_division, and demonstrated below.

SCOTT@orcl_11g> -- create user a:
SCOTT@orcl_11g> CREATE USER a IDENTIFIED BY a DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK;

User created.

SCOTT@orcl_11g> GRANT CREATE SESSION TO a;

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE TABLE TO a;

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE VIEW TO a;

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE SYNONYM TO a;

Grant succeeded.

SCOTT@orcl_11g> ALTER USER a QUOTA UNLIMITED ON users;

User altered.

SCOTT@orcl_11g> -- create user b:
SCOTT@orcl_11g> CREATE USER b IDENTIFIED BY b DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK;

User created.

SCOTT@orcl_11g> GRANT CREATE SESSION TO b;

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE TABLE TO b;

Grant succeeded.

SCOTT@orcl_11g> ALTER USER b QUOTA UNLIMITED ON users;

User altered.

SCOTT@orcl_11g> -- create user d:
SCOTT@orcl_11g> CREATE USER d IDENTIFIED BY d DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK;

User created.

SCOTT@orcl_11g> GRANT CREATE SESSION TO d;

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE PROCEDURE TO d;

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE ANY SYNONYM TO d;

Grant succeeded.

SCOTT@orcl_11g> ALTER USER d QUOTA UNLIMITED ON users;

User altered.

SCOTT@orcl_11g> -- create user c:
SCOTT@orcl_11g> CREATE USER c IDENTIFIED BY c DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK;

User created.

SCOTT@orcl_11g> GRANT CREATE SESSION TO c;

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE TABLE TO c;

Grant succeeded.

SCOTT@orcl_11g> ALTER USER c QUOTA UNLIMITED ON users;

User altered.

SCOTT@orcl_11g> -- as user a:
SCOTT@orcl_11g> CONNECT a/a
Connected.
A@orcl_11g>
A@orcl_11g> CREATE TABLE test_tab AS SELECT 'A' test_col FROM DUAL;

Table created.

A@orcl_11g> GRANT DELETE, INSERT, SELECT, UPDATE ON test_tab TO d WITH GRANT OPTION;

Grant succeeded.

A@orcl_11g> -- as user b:
A@orcl_11g> CONNECT b/b
Connected.
B@orcl_11g>
B@orcl_11g> CREATE TABLE test_tab AS SELECT 'B' test_col FROM DUAL;

Table created.

B@orcl_11g> GRANT DELETE, INSERT, SELECT, UPDATE ON test_tab TO d WITH GRANT OPTION;

Grant succeeded.

B@orcl_11g> -- as user d:
B@orcl_11g> CONNECT d/d
Connected.
D@orcl_11g>
D@orcl_11g> CREATE OR REPLACE PROCEDURE update_synonym (schema_name VARCHAR2)
2 IS
3 BEGIN
4 EXECUTE IMMEDIATE 'create or replace synonym C.test_tab for '
5 || schema_name
6 || '.test_tab';
7
8 EXECUTE IMMEDIATE 'grant delete, insert, select, update on '
9 || schema_name
10 || '.test_tab to C';
11 END update_synonym;
12 /

Procedure created.

D@orcl_11g> GRANT EXECUTE ON update_synonym TO c;

Grant succeeded.

D@orcl_11g> -- as user c:
D@orcl_11g> CONNECT c/c
Connected.
C@orcl_11g>
C@orcl_11g> exec d.update_synonym('A');

PL/SQL procedure successfully completed.

C@orcl_11g> select * from test_tab;

T
-
A

1 row selected.

C@orcl_11g> insert into test_tab values ('C');

1 row created.

C@orcl_11g> update test_tab set test_col = 'C';

2 rows updated.

C@orcl_11g> delete from test_tab;

2 rows deleted.

C@orcl_11g> exec d.update_synonym('B');

PL/SQL procedure successfully completed.

C@orcl_11g> select * from test_tab;

T
-
B

1 row selected.

C@orcl_11g> insert into test_tab values ('C');

1 row created.

C@orcl_11g> update test_tab set test_col = 'C';

2 rows updated.

C@orcl_11g> delete from test_tab;

2 rows deleted.

C@orcl_11g>

[Updated on: Thu, 06 November 2008 08:26]

Report message to a moderator

Previous Topic: PL/SQL Package compilation
Next Topic: Automate Store Proc Testing
Goto Forum:
  


Current Time: Sat Dec 10 16:37:59 CST 2016

Total time taken to generate the page: 0.04564 seconds