Set current-schema and granting 
Author Message
 Set current-schema and granting
Hi,

when using the non-documented 'alter session set current_schema=...' how can
I grant rights on the objects I create in an other users schema? Do I need
special system rights other than DBA?

Regards
Matthias

Sent via Deja.com http://www.***.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 Set current-schema and granting

You need to have the other user grant you rights
to those objects with ADMIN option.

You will find the same is true if you connect as
a DBA and do:
    create table scott.new_table;
    grant select on scott.new_table to amundsen;

The effect of:
    alter session set current_schema=scott;

only changes the route through name resolution,
so that you don't have to specify SCOTT in the
commands, it doesn't give you any 'special access'
to that user's privileges.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html

Quote:

>Hi,

>when using the non-documented 'alter session set current_schema=...' how
can
>I grant rights on the objects I create in an other users schema? Do I need
>special system rights other than DBA?

>Regards
>Matthias

>Sent via Deja.com http://www.deja.com/
>Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 Set current-schema and granting

Quote:

> Hi,

> when using the non-documented 'alter session set current_schema=...' how can
> I grant rights on the objects I create in an other users schema? Do I need
> special system rights other than DBA?

> Regards
> Matthias

> Sent via Deja.com http://www.deja.com/
> Before you buy.

You can't....

also its now documented (from 8i onwards and thus supported)...

To do grants on other people's stuff take a look at:

http://www.oracledba.co.uk/tips/grants.htm
--
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"



Wed, 18 Jun 1902 08:00:00 GMT
 Set current-schema and granting
Thank you both
Matthias

Sent via Deja.com http://www.deja.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. set current schema

2. "set current schema" and commit

3. Grants to Public Role ON INFORMATION SCHEMA

4. automatically granting access to schema objects???

5. Granting select on other users schema

6. NEWBIE: granting permission to entire schema (Oracle8)

7. Grant privilege in Schema Builder

8. can GRANT give schema level access?

9. Granting SELECT to all tables in a schema

10. Granting privileges to objects in a schema

11. Can I grant access to another user's schema objects

12. What's the CURRENT schema ?


 
Powered by phpBB® Forum Software