GRANT a user is able to view dba_user table?

I used grant to a user like this
Use as SYS this:

CREATE USER ABC
  IDENTIFIED BY initinit
  DEFAULT TABLESPACE some tablespace
  ACCOUNT UNLOCK;

  GRANT UNLIMITED TABLESPACE TO ABC;
 

  GRANT CREATE SESSION   TO ABCABC;
  GRANT CREATE TRIGGER   TO ABC;
  GRANT CREATE SEQUENCE  TO ABC;
  GRANT CREATE TYPE      TO ABC;
  GRANT CREATE PROCEDURE TO ABC;
  GRANT CREATE CLUSTER   TO ABC;
  GRANT CREATE OPERATOR  TO ABC;
  GRANT CREATE INDEXTYPE TO ABC;
  GRANT CREATE TABLE     TO ABC;

how is to let the user ABC able to view the system table dba_user?
LVL 15
fsze88Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
GRANT SELECT ON DBA_USER TO ABC;
0
 
fsze88Author Commented:
is any user can view sys, system user on their (ABC) schema?
0
 
fsze88Author Commented:
SQL> select username, account_status from dba_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SYS                            OPEN
SYSTEM                         OPEN
DBSNMP                         OPEN
KOONHEY                        OPEN
SCOTT                          OPEN
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not by default, you have to grant the permissions
0
 
fsze88Author Commented:
so, for each user/schema means one of database of one oracle server is it?
one oracle server can having several databases(user/schema) not only one right?
0
 
slightwv (䄆 Netminder) Commented:
It sounds like you are coming to Oracle from a SQL Server background.  I attended a SQL Server class a couple years ago and the biggest problem I had was the naming convention differences between the 2.  Particularly what a 'database' was.

>>one oracle server can having several databases(user/schema) not only one right?

Yes.

In a nutshell Oracle goes like this (I may be slightly off on 1 or 2.  It's been a while since I've had to define all this):
Instance - Made up of Server processes, Memory segments (SGA)
Database - All the datafiles, parameter files, archived redo logs if you have them
User - An account that has connect authority to the instance
Schema (loosely defined) - a user that owns objects ( tables, types, etc... ) in the instance

So,
In a single database you can have multiple schemas supporting multiple applications and they never know about each other unless they are explicitly given permission to see each other.

If this doesn't answer your question, please post back.
0
 
fsze88Author Commented:

In a single database you can have multiple schemas supporting multiple applications and they never know about each other unless they are explicitly given permission to see each other.

you mean one oracle server have single database and separate by schemas for different data structure include table, view, procedure .......? is it?

BUT, MSSQL is having different is : ONE MSSQL server can having different (several) database on one machine (computer).

0
 
slightwv (䄆 Netminder) Commented:
You're getting there.   In a nutshell, yes.

I'm not sure how you define "Oracle server".  Again, things in the MSSQL world are named VERY different.

To continue from above:
It's all in how you design it and what you need it to do.  You can run multiple instances/databases on a single machine to support multiple apps or  depending on the apps and their design, you can support all them in 1 database ( I'm not sure I'd go into production with this unless the business model suppports it ).  For example:  You probably don't want the payroll data and some homegrown app in different schemas in the same database.

To complicate things further, you can have multiple instances ( see defination above ) on multiple machines talk to the same database (again, see definition ).  The old way was called Parallel Server and the new name is Real Application Clusters (RAC).

An Oracle instance has a certain amount of overhead involved so on smaller machines it may make sense to combine multiple apps into a single database with multiple schemas.  Espically in a development environment.  I've even done this in poduction for a series of smaller homegrown apps.  I gave each application administrator access to their schemas (Oracle schemas not MSSQL schemas) and I controlled the database overall.  What they did to the tables/views/procs was up to them ( until they wrote bad code and it impacted performance on the other apps ).  They could only destroy their own tables and not tables in other schemas.

0
 
Mark GeerlingsDatabase AdministratorCommented:
Yes, the meaning of the word "database" is very different in SQL Server than in Oracle!  In Oracle, the word "database" means something like: "complete system", since in Oracle, one database includes all of the datafiles (including the SYSTEM and TEMP tablespaces, etc.) plus all of the users (or schemas) and all of their tables, views, procedures, etc.  Each Oracle user (or schema) see only its own tables, views, procedures etc. unless a different owner grants permission on his/her object to another user/schema.

So, to get back to your original question, to allow a "normal" user to see the contents of  the "dba_users" view, you need to log in as SYS (the owner of dba_users) then:
grant select on dba_users to [some_other_user]

This same kind of grant can be given by any Oracle user to any other Oracle user (or role) for any object owned by the user doing the grant.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.