Link to home
Start Free TrialLog in
Avatar of Brant Snow
Brant Snow

asked on

New User in Oracle

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

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.
Avatar of Brant Snow

ASKER

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?
SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial