Solved

user creation

Posted on 2014-04-23
14
403 Views
Last Modified: 2014-05-18
I want to create user ( brand new)
so that the user should not have access to objects belong to sys and system

How we will create such a user.
0
Comment
Question by:vangogpeter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
14 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 40018281
create user someusername identified by somepassword;
grant create session to someusername;

Now if you want the user to be able to create objects in their own schema:
grant resource to someusername;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40018284
Note:  Any user will have access to ANYTHING that has been granted to the PUBLIC role.

So if access to SYS and SYSTEM objects were granted to PUBLIC, then there really isn't a way to keep users out.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40018286
Should also add the following if they are going to be creating objects:
create user someusername identified by somepassword
    default tablespace some_table_space_name temporary tablespace your_temp_tablespace_name;

This sets up the tablespaces they use by default.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:vangogpeter
ID: 40018305
I have in my database 100 users.
I want to create a 101th user which cannot access to sys and system.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40018314
>>I want to create a 101th user which cannot access to sys and system.

OK, just run the create user statement for the 101st user.
0
 

Author Comment

by:vangogpeter
ID: 40018336
This 101th user should not access sys and system .
Which command I use.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40018341
>>This 101th user should not access sys and system .

I gave it to you already.

If you don't grant access to SYS and SYSTEM for any user you create, they don't have access.

With the one exception I noted:  ALL users have access to ANYTHING that has been granted to the PUBLIC role.

You cannot restrict a user from seeing PUBLIC items.  You need to revoke access for those objects from PUBLIC but that is a different question.
0
 
LVL 23

Assisted Solution

by:David
David earned 250 total points
ID: 40018712
There are likely to be outliers on this, but as a general rule, Oracle user accounts CANNOT access objects (tables for this discussion) that belong to SYS, SYSTEM, or any other schema in the database.

The exceptions are as above.
1.  The object's owner has granted an OBJECT PRIVILEGE to PUBLIC.
2.  The object's owner has granted an OBJECT PRIVILEGE to the new user.
3.  A privileged user (DBA) has granted a SYSTEM PRIVILEGE to the new user or PUBLIC.

Examples:
1.  A user account may initially view information about objects it owns using SYS.USER_OBJECTS.
2.  A user account may initially view information about objects it owns, plus those to which a privilege has been granted, using SYS.ALL_OBJECTS.
3.  A user account may NOT view other SYS or SYSTEM objects unless it has been granted permission to do so (SELECT ANY TABLE).

I hope this helps, somehow....
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40020291
Can you explain your selected answer here?

It appears you just picked the last post.  There is nothing in that post that shows you how to create a user which is the question you asked.

If you found in some of the information in that post helpful, you should award it some points but not ALL points.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40058807
I would suggest a split:

accept: http:#a40018281
assist: http:#a40018712
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question