Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

create account

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
vangogpeter
Asked:
vangogpeter
  • 4
  • 4
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
vangogpeterAuthor Commented:
What is the difference?

v_grantee   VARCHAR2(30) := 'YOUR_USER';  
    v_owner     VARCHAR2(30) := 'YOUR_SCHEMA';
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
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
 
vangogpeterAuthor Commented:
I have 4 schemas

schema1,schema2,schema3,schema4

I am  confused with

your schema
your user
0
 
sdstuberCommented:
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
 
vangogpeterAuthor Commented:
if we have 100 schema to do we have to do everything  100 times
0
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
vangogpeterAuthor Commented:
This is not what I expected.

The new user should get access to all the view in the database.
0
 
slightwv (䄆 Netminder) Commented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now