Solved

create account

Posted on 2014-04-23
11
258 Views
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.
0
Comment
Question by:vangogpeter
  • 4
  • 4
  • 3
11 Comments
 
LVL 76

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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40018364
Try this...


create user YOUR_USER;
grant select any dictionary to YOUR_USER;

DECLARE
    v_grantee   VARCHAR2(30) := 'YOUR_USER';  
    v_owner     VARCHAR2(30) := 'YOUR_SCHEMA';
BEGIN
    FOR x
        IN (SELECT    'grant '
                   || CASE
                          WHEN object_type IN ('TABLE', 'VIEW')
                          THEN
                              'select,insert,update,delete'
                          WHEN object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'TYPE')
                          THEN
                              'execute'
                          WHEN object_type = 'SEQUENCE'
                          THEN
                              'select'
                      END
                   || ' on "'
                   || owner
                   || '"."'
                   || object_name
                   || '" to "'
                   || v_grantee
                   || '"'
                       grant_sql
              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'))))
    LOOP
        BEGIN
           EXECUTE IMMEDIATE x.grant_sql;
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line(SQLERRM);
                DBMS_OUTPUT.put_line(x.grant_sql);
        END;
    END LOOP;
END;
0
 

Author Comment

by:vangogpeter
ID: 40018500
What is the difference?

v_grantee   VARCHAR2(30) := 'YOUR_USER';  
    v_owner     VARCHAR2(30) := 'YOUR_SCHEMA';
0
 
LVL 73

Expert Comment

by:sdstuber
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)
0
 

Author Comment

by:vangogpeter
ID: 40018525
I have 4 schemas

schema1,schema2,schema3,schema4

I am  confused with

your schema
your user
0
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.

 
LVL 73

Expert Comment

by:sdstuber
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.
0
 

Author Comment

by:vangogpeter
ID: 40018559
if we have 100 schema to do we have to do everything  100 times
0
 
LVL 76

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.
0
 
LVL 73

Expert Comment

by:sdstuber
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...

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

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

end loop;
end;
0
 

Author Comment

by:vangogpeter
ID: 40020209
This is not what I expected.

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

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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'.
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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 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

18 Experts available now in Live!

Get 1:1 Help Now