[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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