create account

Posted on 2014-04-23
Medium Priority
Last Modified: 2014-04-24
How will I create a User accounts needs access to all  schema objects for a  particular schema as well as with access to all system views.
Question by:vangogpeter
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40018357
Same create user as before in your previous question.

What they can access is all in the grants.

The difficulty here is ALL SYS and SYSTEM objects and only ONE specific schema's objects.

If you wanted them to be able to query anything in the entire database:
grant select any table to someusername;

Remember: Then can then select from ANY table in ANY schema.

Is this what you are after?

If you want to restrict selecting from SOME schemas then you need to get a little creative.
LVL 74

Expert Comment

ID: 40018364
Try this...

create user YOUR_USER;
grant select any dictionary to YOUR_USER;

    v_grantee   VARCHAR2(30) := 'YOUR_USER';  
    v_owner     VARCHAR2(30) := 'YOUR_SCHEMA';
    FOR x
        IN (SELECT    'grant '
                   || CASE
                          WHEN object_type IN ('TABLE', 'VIEW')
                          WHEN object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'TYPE')
                          WHEN object_type = 'SEQUENCE'
                   || ' on "'
                   || owner
                   || '"."'
                   || object_name
                   || '" to "'
                   || v_grantee
                   || '"'
              FROM dba_objects
             WHERE     owner = v_owner
                   AND object_type IN
                           ('TABLE', 'VIEW', 'FUNCTION', 'PROCEDURE', 'PACKAGE', 'SEQUENCE', 'TYPE')
                   AND (   object_type != 'TABLE'
                        OR (    object_type = 'TABLE'
                            AND object_name NOT IN (SELECT table_name
                                                      FROM dba_tables
                                                     WHERE iot_type = 'IOT_OVERFLOW'))))
           EXECUTE IMMEDIATE x.grant_sql;
            WHEN OTHERS

Author Comment

ID: 40018500
What is the difference?

v_grantee   VARCHAR2(30) := 'YOUR_USER';  
    v_owner     VARCHAR2(30) := 'YOUR_SCHEMA';
Technology Partners: 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!

LVL 74

Expert Comment

ID: 40018515
look at the variable names

one is the owner  of the schema your user needs access to  (YOUR_SCHEMA)

the other is the user that is receiving the grants (YOUR_USER)

Author Comment

ID: 40018525
I have 4 schemas


I am  confused with

your schema
your user
LVL 74

Expert Comment

ID: 40018538
that doesn't match what you originally asked for.

a User accounts needs access to all  schema objects for a  particular schema

So, one user needs grants to objects for one schema.

If you want to grant privileges to a user for multiple schemas that fine, but it's a different task.

Easily handled though...

Simply run the block multiple times, each time changing the schema but the user would remain the same.

Author Comment

ID: 40018559
if we have 100 schema to do we have to do everything  100 times
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40018580
Do you have one 'master' schema that you want all 100 users to be able to query the tables in?

If so then:
create role master_select_role;
grant select on some_table to master_select_role;
grant select on some_other_table to master_select_role;
...--for the rest of masters tables, you can script this

Then for all 100 users (this can be scripts as well):
grant master_select_role to user1;
grant master_select_role to user2;
grant master_select_role to user100;

You can create a DDL trigger on the master schema to automatically grant select on new tables to the master_role but I wouldn't suggest it.
LVL 74

Expert Comment

ID: 40018616
if you have 100 schemas then put the whole block inside a loop and iterate across a variable from a query and simply remove the line

v_owner     VARCHAR2(30) := 'YOUR_SCHEMA';

so, as an outline it would look something like this...

for v_owner in (select ......) loop

      .... put my block here with the assignment line removed....

end loop;

Author Comment

ID: 40020209
This is not what I expected.

The new user should get access to all the view in the database.
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 40020226
What database product/system/OS behaves like that?

If you create a new Unix/Windows user does it have access to ALL files by default?

You only get access to what you've been granted.

It's the same with Oracle.

A new user doesn't even get to connect to the database until you have granted 'create session'.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

765 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