Creating synonyms and grants across multiple users based on access levels

You should never be creating objects in the SYS schema.

The objects will only be created in other user accounts, Not in SYS schema.

This line of code:

WHERE owner = 'SYS'

This bullet point:

1. SYS (System Admin/owner account )

This line of requirement:

Logging in as SYS -  Need to create a package with procedures for each of the below

That doesn't look like user schemas to me.

You grant privileges to a role the same way you grant them to a user.  Then grant the role to the user (this is only done once).

For creating synonyms, either query so that you only get the ones that don't exist, or just create them and ignore the already exists error with an EXCEPTION clause.

I agree with Johnsone, that this doesn't look good at all:
"Logging in as SYS -  Need to create a package with procedures for each ..."

You should have an administrator account that you create once as SYS, and give it the DBA role and/or any other privileges it needs, then you login as this user, not as SYS, to create (and own) whatever custom procedures you want to create.

One option for synonyms is to create public synonyms, that any/all users can use.  I've used public synonyms in Oracle databases for over 25 years.  I like that option.  But some people now advise against public synonyms.  These don't create a security problem.  But, you do have to make sure then that any objects you want to create synonyms for don't match existing public synonyms that all Oracle databases have by default for objects in Oracle's data dictionary.

Also, trying to grant DML access in a procedure may be a challenge, since PL\SQL usually ignores privileges granted via a role, so a procedure may not be able to grant DML access if the procedure owner is not also the table owner.  If the procedure owner and table owner are the same, it may work.

Agreed. I was misleading with the way I raised the question.

I've an administrator account having a few tables (example My_Owner.Table1, My_Owner.Table2).  These tables reside in the my_owner schema and as of now are accessible to folks with the admin account only.

The business users (they'll have  a "connect account"  ) will need to access these tables (My_Owner.Table1, My_Owner.Table2) and will be allowed to perform queries and dml only against  those 2 tables, but no DDL operations will be permitted.

How do I refactor the below code into  a stored procedure which'll grant these users having the connect account, to access those tables in  the my_owner schema?  

SELECT    'GRANT '
                   || CASE
				WHEN object_type IN ('VIEW')
                          THEN
                              'select'
                          WHEN object_type IN ('TABLE')
                          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 "'
                   || ETL_USER
                   || '"'
                       grant_sql
              FROM all_objects
             WHERE owner = 'SYS'
                   AND object_type IN
                           ('TABLE', 'VIEW', 'FUNCTION', 'PROCEDURE', 'PACKAGE', 'SEQUENCE', 'TYPE')

Select allOpen in new window

A couple of questions:

1. What if new tables are created in owner account. Would we create public synonyms for each new table, explicitly each table?
2. In terms of sequence of events:

a. Create the synonyms first for the owner account.
b. Create a role for user accounts
c. Run grants script.

Is that correct?

Appreciate your suggestions on this..

Creating the public synonym and grants for a new table would be done as part of the process of creating a new table.

You need to create the role.  Then run the grants.

Creating the synonyms and granting the role to the user can be done at any time, the role and the grants to the role are not dependent on them.

As a general rule, you should not be granting all those privileges to users.  They should only be granted the privileges they require to do their job.  The principle of least (or minimal) privilege.  For example, does a user need anything besides a SELECT privilege on a lookup table?  Are they really going to be deleting records from production tables?  If you are subject to any kind of audits, these will be caught and you'll have some explaining to do.

Thanks for your suggestions. That was helpful