[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

create account

Posted on 2014-04-23
11
Medium Priority
?
269 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
[X]
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
11 Comments
 
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.
0
 
LVL 74

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 74

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

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

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 77

Accepted Solution

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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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 …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

656 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