Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Cant connect as normal user

903508Dec 3 2011 — edited Dec 5 2011
Hi, Im learning Oracle and Enterprise manager, Im have to set up a profile and role, then create a user who uses the permissions set out in the profile and role, I have granted the connect role, but unless I also include the sysdba role I can not connect, I understand for security this is not ideal! How can I go about fixing this?

below is my sql scripts to illustrate what Im doing

CREATE USER "DBSEC_ADMIN" PROFILE "DBSEC_ADMIN_PROF" IDENTIFIED BY "*******" DEFAULT TABLESPACE "ASSIGNMENT" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK
GRANT "CONNECT" TO "DBSEC_ADMIN"
GRANT "DBSEC_ADMIN_ROLE" TO "DBSEC_ADMIN"

CREATE ROLE "DBSEC_ADMIN_ROLE" NOT IDENTIFIED
GRANT ALTER ANY TABLE TO "DBSEC_ADMIN_ROLE"
GRANT SELECT ANY TABLE TO "DBSEC_ADMIN_ROLE"

CREATE PROFILE "DBSEC_ADMIN_PROF" LIMIT CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
CONNECT_TIME 480
IDLE_TIME 60
SESSIONS_PER_USER 5
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
PRIVATE_SGA DEFAULT
COMPOSITE_LIMIT DEFAULT
PASSWORD_LIFE_TIME 30
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_LOCK_TIME DEFAULT
FAILED_LOGIN_ATTEMPTS DEFAULT
PASSWORD_VERIFY_FUNCTION DEFAULT

I am unable to connect unless I add the sysdba system privellage!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 2 2012
Added on Dec 3 2011
23 comments
6,391 views