Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- string sConnectionString = "Data Source=XE;User ID=sys;Password=system;DBA PRIVILEGE=sysdba";
- OracleConnection myConnection = new OracleConnection(sConnectionString);
- OracleCommand myCommand = new OracleCommand("spValidateDBA", myConnection);
- myCommand.CommandType = CommandType.StoredProcedure;
- myCommand.CommandText = "spValidateDBA";
- myCommand.Parameters.Add("UserId", OracleDbType.Varchar2, 50);
- myCommand.Parameters["UserId"].Value = txtUsrId.Text.ToString().ToUpper();
- myCommand.Parameters.Add("UserRole",OracleDbType.RefCursor, 50).Direction = ParameterDirection.Output;
- myCommand.Parameters.Add("UserIdOut", OracleDbType.Varchar2, 50).Direction = ParameterDirection.Output;
- var rolechk = false;
- string checkrole = "DBA";
- myConnection.Open();
- myCommand.ExecuteReader();
- // Create the OracleDataAdapter
- OracleDataAdapter da = new OracleDataAdapter(myCommand);
- DataTable dt = new DataTable();
- da.Fill(dt); // Trying to populate a DataTable with refcursor UserRole.
- if (myCommand.Parameters["UserIdOut"].Value.ToString().ToUpper() == txtUsrId.Text.ToString().ToUpper())
- {
- CustomMsgbox.Show("1", "DB Utilities Tool", "OK", "Cancel");
- foreach (DataRow dr in dt.Rows)
- {
- if (dr["UserRole"].ToString().ToUpper().Equals(checkrole)==true)//getting the error "Column 'UserRole' does not belong to table." here
- {
- CustomMsgbox.Show("tLogin Successful..!!t" + Environment.NewLine + "Welcome to DB Utilities Tool", "DB Utilities Tool", "OK", "Cancel");
- DBA dba = new DBA();
- dba.Show();
- this.Hide();
- rolechk = true;
- break;
- }
- }
- if (!rolechk)
- {
- CustomMsgbox.Show("Insufficient privileges", "DB Utilities Tool", "OK", "Cancel");
- myConnection.Close();
- }
- else
- CustomMsgbox.Show("Please enter correct User ID/Password", "DB Utilities Tool", "OK", "Cancel");
- }
- create or replace PROCEDURE spValidateDBA(
- UserId IN VARCHAR2,
- UserRole OUT SYS_REFCURSOR,
- UserIdOut OUT VARCHAR2)
- AS
- BEGIN
- select USERNAME into UserIdOut from DBA_USERS DU where DU.USERNAME=UserId;
- OPEN UserRole FOR
- select GRANTED_ROLE from DBA_USERS DU,DBA_ROLE_PRIVS DRP where DU.USERNAME=UserId AND DU.USERNAME=DRP.GRANTEE;
- END spValidateDBA;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement