This forum is currently locked. You can't register or post questions at this time. (read more)

Need your input here:

All posts relating to Oracle database administration.

Moderator: Tim...

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Need your input here:

Postby royalyogi » Fri Jul 26, 2013 6:15 pm

Application user got below error while running job. He said this job was run successfully previously.
But only today got below error. He complained about privileges issue.

Applicaton user end Error:

create table: create table SNRK.C$_0MA_CNTRT with the HB_STAGING schema (SKHB)
Error is : 1031 : 42000 : java.sql.SQLException: ORA-01031: privilèges insuffisants



yesterday I was revoked the dba role from this user but still he had create session privileges on his schema.
But today my mate have granted DBA Role again and also granted insert,update,delete,drop,create any table to user.
Now apllication user said issue has resolved.

My though: DBA Role is not problem for above error and DBA role didn't resolve the issue since DBA role don't have create privileges as below.
Issue has resolved becuase my mate has given "Grant create,drop,delete,insert******* any table to user". I know Grant create**** any table to user'
is not good practice for DBA. :o

DBA Role have below privileges:
SQL> SELECT distinct(PRIVILEGE) FROM DBA_TAB_PRIVS WHERE GRANTEE = 'DBA';
PRIVILEGE
----------------------------------------
EXECUTE
FLASHBACK
ALTER
ON COMMIT REFRESH
DEBUG
UPDATE
DELETE
QUERY REWRITE
SELECT
INSERT

10 rows selected.

Need your input here:
Could you please let me know DBA Role has resolved the issue or privilège insert,update,delete,drop,create any table.... has resolved issue?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Need your input here:

Postby Tim... » Sat Jul 27, 2013 7:52 am

Hi.

1) If you need to grant a privilege containing the word "ANY" to a user to make your application work you have failed. This means people can screw up the data dictionary and kill your whole database. It also means they can to any form of privilege escalation they like.
2) If you need to grant DBA privilege to any user to make your application work you have failed. Same problems as above.

Regarding your question, if the code is a stored procedure, function or package the DBA role is unlikely to be used. Privileges granted via a role are not usable by code created with owner rights. They are only used by code created with invoker rights and anonymous blocks. This is more than likely why adding the DBA role did not "fix" your problem.

If you have a requirement in your application to do something with this level of privilege, what you should do is.

1) Create a user with the necessary privileges to perform the task, making sure the only people who have access to that user are the DBAs.
2) Create PL/SQL APIs to perform all the necessary tasks, making sure they have coded the APIs carefully to prevent SQL Injection or privilege escalation.
3) Grant execute on those APIs to the application users that need them.

This way, you control which tasks the users can do.

If you worked for me and granted application users the DBA role or privileges with "ANY" in then, you would be asked to leave the building and you would never work for me again. It really is that wrong!

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: Need your input here:

Postby royalyogi » Sat Jul 27, 2013 12:11 pm

Thanks Tim fo your feedback. what can I do since my senior mate given those privileges :o

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: Need your input here:

Postby royalyogi » Sat Jul 27, 2013 12:13 pm

Tim one more question here:
How can we know when role,privileges has granted to user?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Need your input here:

Postby Tim... » Sat Jul 27, 2013 12:52 pm

Hi.

The simplest way is to use this script to create the DDL for that user.

http://www.oracle-base.com/dba/script.p ... er_ddl.sql

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: Need your input here:

Postby royalyogi » Sat Jul 27, 2013 1:43 pm

Above link is not showing when(Date & Time)we have graned perticuler privileges to user. It's only shows whatever privileges user have.

I need, When(Date,time ) have we granted Role or privileges to user. (if audit is disabled on the db). I know it is very difficult to search in google also.Only u can help here.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Need your input here:

Postby Tim... » Sat Jul 27, 2013 2:16 pm

Hi.

Sorry. I misread your post.

If you have the appropriate level of auditing enabled, you can check the audit trail for the time the DDL was executed. If not, then there is no simple solution.

If you have a lot of time on your hands, and access to all the relevant redo logs, you could trawl though the redo logs using log miner.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: Need your input here:

Postby royalyogi » Sat Jul 27, 2013 2:41 pm

Thanks Tim.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Need your input here:

Postby Tim... » Sat Jul 27, 2013 4:08 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 2 guests

cron