?
Solved

GRANT  a user is able to view dba_user table?

Posted on 2007-07-26
9
Medium Priority
?
3,885 Views
Last Modified: 2008-01-09
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?
0
Comment
Question by:fsze88
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 19573085
GRANT SELECT ON DBA_USER TO ABC;
0
 
LVL 15

Author Comment

by:fsze88
ID: 19573732
is any user can view sys, system user on their (ABC) schema?
0
 
LVL 15

Author Comment

by:fsze88
ID: 19573736
SQL> select username, account_status from dba_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SYS                            OPEN
SYSTEM                         OPEN
DBSNMP                         OPEN
KOONHEY                        OPEN
SCOTT                          OPEN
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 19573742
not by default, you have to grant the permissions
0
 
LVL 15

Author Comment

by:fsze88
ID: 19573792
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 total points
ID: 19574170
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
 
LVL 15

Author Comment

by:fsze88
ID: 19574949

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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 total points
ID: 19575854
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 400 total points
ID: 19577717
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

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 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