Solved

New User in Oracle

Posted on 2011-02-15
8
420 Views
Last Modified: 2012-06-27
We have a oracle database and i need to set up a user with username 'acct_Dani' and password 'gT54rlo' and make it so this account only has view rights (in other words cant use update, replace or delete)

What is the sql code that i need to use to run this.

Also i know i can use

select * from all_users
-- view all users

Select * from SESSION_PRIVS
-- view all possible privelidges

but how do i view all priveledges for a specfic user
0
Comment
Question by:Brant Snow
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34899620
from a SQL prompt:
create user acct_Dani identified by gT54rlo default tablespace users temporary tablespace temp;

grant create session to acct_Dani;

For the select only, I would create a role and grant select to the individual objects then grant that role to acct_Dani.

the only other way is granting 'select any' and that opens the database up because the user can select system tables, source code, etc...

>>but how do i view all priveledges for a specfic user

This is pretty hard to actually do in Oracle.  There are several views you need because you can have privileges, roles all granted to one another.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34899684
More on permissions:

There is DBA_TAB_PRIVS that will show explicit grants to tables, DBA_ROLE_PRIVS to show roles granted to a user,  DBA_SYS_PRIVS to show system privs (create, drop, etc...) granted to a user.

Then there's ROLE_TAB_PRIVS, ROLE_SYS_PRIVS to show what is granted to a role.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 34900265
Oracle does not give us a way to explicitly limit an account so it "only has view rights (in other words can't use update, replace or delete)".

But, when you create a new account, it will only include those priviliges (like: insert, update and delete) for tables that the account owns.  If you do not create any tables in that new user account (schema) then the new account will not be able to do those actions in tables that belong to other users.  Also, that new account will only be able to read (select) from tables in other schemas if those schema owners explicitly grant this new owner permission to select from their tables.

0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 35

Expert Comment

by:johnsone
ID: 34900320
While most DBAs would never do this, be aware of privileges granted to PUBLIC.  If the insert/update/delete privilege is granted to PUBLIC, then every user has the privilege.  Some third party applications where the security is enforced at the application level and not the database level grant all privileges to PUBLIC.  It is a bad security design, but it is out there.  People have also been known to grant things to PUBLIC just to make a problem go away, rather than figure it out.
0
 
LVL 4

Author Comment

by:Brant Snow
ID: 34900371
slightwv you say the best would be to create a role and assign it select privledges correct, how do i do that.

and if i want to add a user to say myDatabase so you wrote

create user acct_Dani identified by gT54rlo default tablespace users temporary tablespace temp;

grant create session to acct_Dani;

i understand that except do i need to insert the name of the table within the {tablespace}?

How could i create a role and assign it to the user?
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 175 total points
ID: 34900572
To create a role:

CREATE ROLE READ_ONLY_ROLE;    -- You can use any name you want.

To assign it to a user:

GRANT READ_ONLY_ROLE TO <user>;

You grant privileges to the role the same way you grant them to a user.

GRANT SELECT ON <table> TO READ_ONLY_ROLE;
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 75 total points
ID: 34901170
"do i need to insert the name of the table within the {tablespace}?"

No.  When you create a user in Oracle you do not provide a table name.  You should specify two tablespace names as suggested: a default one (in case the user ever does create a table); and a temporary one (that Oracle will use if/when this user executes queries that require temporary space on disk for sorts, etc.).

0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 34902506
>>be aware of privileges granted to PUBLIC.

Thanks.  I keep forgetting about PUBLIC.

>>slightwv you say the best would be to create a role and assign it select privledges correct, how do i do that.

johnsone provided that for me in http:#a34900572.

You need to grant select on every table/view necessary.  You can have SQL generate SQL as a shortcut.

sql script:
--------------------------------
set pages 0
set feedback off
set lines 1000
set trimspool on
spool myscript.sql
select 'grant select on SOME_SCHEMA.' || table_name || ' to READ_ONLY_ROLE;' from dba_tables where owner='SOME_SCHEMA';
spool off

@myscript
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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

632 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