Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

New User in Oracle

Posted on 2011-02-15
8
Medium Priority
?
424 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 78

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 78

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
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!

 
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 78

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

782 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