Create another
I was trying to create a new role that will have all the privileges for the PUBLIC role and then remove all privileges from the PUBLIC role. This is done for security purposes.
This is problem. I could not provide SYS./1005bd30_LnkdConstant and others with the same format to my new role.
Example:
SYS./10076b23_OraCustomDatumClosur SYS./100c1606_StandardMidiFileRead ORDSYS./1013c29d_PlanarImageServerPro,,.
Do I really need these or can my new "public" role do without them?
Any help would be much appreciated.
Let me think quickly. The problem you are having is that object names are case sensitive. A quick fix is to enclose the object names in double quotes, for example.
GRANT EXECUTE ON SYS."/1005bd30_LnkdConstant" TO mynewpublicrole;
You will indicate that you "could not provide [EXECUTE ON] SYS./1005bd30_LnkdConstant" for the role.
I suppose this means that when you ran the GRANT statement, Oracle threw an exception, most likely
ORA-00903: invalid table name
Binding the object name in double quotes (as shown in the example) should fix this problem.
There is no way to answer the question of whether your new role needs the EXECUTE privilege on these objects or not. Well, you don't necessarily need a role. The question is whether they need the user or not (whether provided directly or provided indirectly through roles). This can be determined through rigorous testing.
Some other comments.
If you intend to create a new role and provide this role to all users, I do not see the security being changed or improved. So, I'm going to assume that it isn't.
It would seem that you are trying to apply the principle of "least privilege". I applaud this effort.
One of the more common patterns I see is for the application to connect to the database as the owner of the schema objects. This means that the application has all sorts of privileges that it probably doesn't need, for example. DROP TABLE, ALTER PROCEDURE, etc.
We use a template that the user "OWNER", who owns the schema objects, must have, and a separate user, "APP", who has the specific privileges required for the "OWNER" objects, and synonyms for the "OWNER" objects. (Synonyms allow you to refer to an OWNER. Object without qualification with OWNER.) Needless to say, we do not grant PUBLIC privileges, we grant roles where appropriate.
I mention this because this is the pattern we use to implement the principle of "least privilege".
For other security issues, I recommend that you review the Oracle Security Checklist whitepaper:
Some other possible exceptions that can occur when executing a GRANT statement:
ORA-01031: insufficient privileges
or
ORA-04042: procedure, function, package, or package body does not exist.
In either case, make sure you connect to the database as SYS (SYSDBA) in order to grant privileges. We almost always grant privileges as the owner of the object and not as another user as GRANTEE. I almost never use "WITH GRANT OPTION" on object privileges. This is a simpler model and avoids any potential problem with dependency trees.
a source to share