Solved

privileges inbulit schemas have

Posted on 2001-07-22
4
999 Views
Last Modified: 2008-02-26
soctt/tiger is a default schema of oracle. Are privileges granted to it individually, via a role or automatically as part of the installation process. Same is my question on any of the following schemas. Please indicate which of them have DBA privileges.

SYS, SYSTEM, OUTLN, DBSNMP, AURORA$ORB$UNAUTHENTICATED,ORDSYS,ORDPLUGINS,MDSYS, CTXSYS, ADAMS,JONES, CLARK and BLAKE.

How to know all the privileges assigned to the above schemas. Can we grant/revoke to/from individually or as as a role. Please be elaborate so that i can grade immediately. Can you tell me the difference between a schema and a user.    
                         
                     
                           
                       

                           
                           
0
Comment
Question by:k_murli_krishna
  • 2
4 Comments
 
LVL 6

Expert Comment

by:bira
Comment Utility
0
 
LVL 3

Accepted Solution

by:
doronla earned 30 total points
Comment Utility
Hello sir,

First, user and schema are basically the same thing. When you talk about the password,profile,privileges, etc. you talk about a "user". When you reference tables and other objects, this is a schema. Every user that has the CONNECT role has the privilege to create tables, indexes and other basic objects in his schema. Another term in this conversation is the "OWNER" which is, again, another name for the same thing. This sentence summarize all: "The user scott, which tiger is its password,is the owner of the objects in his schema, such as the table emp".
To access a table in another schema you need to write: schema.table . if the table is in your own schema, table name is enough.

Every user have unlimited privileges to objeects in its one schema. Scott has no priv at all! no role granted at all! All objects are in his schema.

about privs, you can easily check with these dictionary tables:
DBA_ROLE_PRIVS (for roles granted to users)
ROLE_ROLE_PRIVS (roles granted to other roles)
DBA_TAB_PRIVS (table privileges granted to users or roles)
and much more but these are the main tables.

In a default DB installed, SYS and SYSTEM and DBAs.

Good Luck!
Doronla
0
 
LVL 17

Author Comment

by:k_murli_krishna
Comment Utility
Dear doronia,
             Thanx. Please clarify my following doubts. I will give you A grade and the 30 points. You deserve it. The doubt is we are having DBA schemas here like system. Onsite in US they have normal users but they cannot reveal anymore details. If i grant connect,resource to schema then drop table is included. If i grant individually, then granting create table leads to alter and drop as well. I am not able to grant or revoke drop table. I am not able to grant create view,index etc. Please clarify.

K. Murli Krishna
0
 
LVL 17

Author Comment

by:k_murli_krishna
Comment Utility
Dear doronla,
             I am going about accepting answers and grading them. We right now have given connect,resource. Is connect enough to run DDL, DML and TCL and grant/revoke them for all objects i.e. table etc. Please clarify on both of my comments.

with regards

( K. Murli Krishna )
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Value of 0's not appearing. 9 49
Passing list of object to Oracle Database Procedure 3 41
PL/SQL LOOP CURSOR 3 40
null value 15 58
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now