Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Gyan.

Asked: March 03, 2017 - 4:45 am UTC

Last updated: March 04, 2017 - 1:47 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

i have create the one test user with default tablespace users,
now i need to access to test user to access the other schema such as hr, and test user able to create the table in hr schema as well,
how to solve this issue??

and Connor said...

To actually connect, a user needs the 'create session' privilege. So you you might do something like:

create user DEMO identified by DEMO default tablespace users;
grant create session to DEMO;

Now you will be able to do:

connect demo/demo

That gives you *no* access to other schemas. To do that, you need specific grants.

The HR schema might give you particular grants on particular objects, eg

grant insert, select on HR.EMPLOYEES to DEMO

or you might need that level of access on *any* schema (which would be a rare requirement unless you were a DBA), eg

grant select any table to DEMO

I'd strongly suggest you take a read of the basic concepts here

https://docs.oracle.com/database/121/CNCPT/cmntopc.htm#GUID-4D6D2B67-1B65-476D-852A-976E9D153EEC


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Gyan Tamang, March 03, 2017 - 8:06 am UTC

thanx McDonald, but my query is,
can DEMO user create the table at HR schema?? is it possible??
Connor McDonald
March 04, 2017 - 1:47 am UTC

You would need "create any table" for that

create table in other schema

Rajeshwaran, Jeyabal, March 03, 2017 - 11:58 am UTC 

.....
can DEMO user create the table at HR schema?? is it possible?? 
......


demo@ORA12C>
demo@ORA12C> create table hr.t(x int) ;
create table hr.t(x int)
*
ERROR at line 1:
ORA-01031: insufficient privileges


demo@ORA12C> create table scott.t(x int) ;
create table scott.t(x int)
*
ERROR at line 1:
ORA-01031: insufficient privileges


demo@ORA12C> conn rajesh/oracle@ora12c
Connected.
rajesh@ORA12C> grant create any table to demo;

Grant succeeded.

rajesh@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> create table hr.t(x int) ;

Table created.

demo@ORA12C> create table scott.t(x int) ;

Table created.

demo@ORA12C>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.