Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

New User in Oracle

Posted on 2011-02-15
8
Medium Priority
?
422 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 700 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 300 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 1000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

705 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