privileges inbulit schemas have

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.    
                         
                     
                           
                       

                           
                           
LVL 17
k_murli_krishnaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
doronlaConnect With a Mentor Commented:
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
 
biraCommented:
0
 
k_murli_krishnaAuthor Commented:
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
 
k_murli_krishnaAuthor Commented:
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
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.