Cant connect as normal user
903508Dec 3 2011 — edited Dec 5 2011Hi, 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!