Setting the file. One moment. 10
Setup DevOps Agent
33
AWS Deployment
RDS Oracle
Raw file
scripts/test_oracle_connection.py
Python·165 lines·6 KB
16
17
18def main():
19 verbose = "--verbose" in sys.argv
20 args = [a for a in sys.argv[1:] if a != "--verbose"]
21 if len(args) < 4:
22 print(
23 "Usage: python3 test_oracle_connection.py <endpoint> <port> <service_name> <username> [--verbose]"
24 )
25 print(" Set ORACLE_PASSWORD env var, or you will be prompted.")
26 print(
27 "Example: ORACLE_PASSWORD=<from-secrets-manager> python3 test_oracle_connection.py mydb.xxx.us-east-1.rds.amazonaws.com 1521 ORCL admin"
28 )
29 sys.exit(1)
30
31 endpoint = args[0]
32 port = args[1]
33 service_name = args[2]
34 username = args[3]
35 password = os.environ.get("ORACLE_PASSWORD") or getpass.getpass("Password: ")
36
37 dsn = f"{endpoint}:{port}/{service_name}"
38
39 print("=== Oracle Connection Test ===")
40 # SECURITY: connection metadata (endpoint, DSN, username) is hidden by default and shown
41 # only with --verbose, to avoid exposing system architecture in persisted logs. Do NOT use
42 # --verbose in production or CI/CD pipelines. The password is never printed.
43 if verbose:
44 print(f"Endpoint: {endpoint}")
45 print(f"Port: {port}")
46 print(f"Service Name: {service_name}")
47 print(f"Username: {username}")
48 print(f"DSN: {dsn}")
49 else:
50 print("(connection metadata hidden; pass --verbose to show endpoint/DSN/username)")
51 print()
52
53 try:
54 import oracledb
55 except ImportError:
56 print("FAIL: oracledb not installed")
57 print(" Fix: pip install oracledb")
58 sys.exit(1)
59
60 print(f"Driver: python-oracledb {oracledb.__version__}")
61 print(f"Mode: {'Thick' if not oracledb.is_thin_mode() else 'Thin'}")
62 print()
63
64 print("--- Connection Test ---")
65 start = time.time()
66 try:
67 conn = oracledb.connect(user=username, password=password, dsn=dsn)
68 elapsed = time.time() - start
69 print(f"PASS: Connected in {elapsed:.2f}s")
70 print(f" Database version: {conn.version}")
71 except oracledb.DatabaseError as e:
72 elapsed = time.time() - start
73 (error,) = e.args
74 print(f"FAIL: Connection failed after {elapsed:.2f}s")
75 print(f" ORA-{error.code}: {error.message}")
76 hints = {
77 12170: "TNS connect timeout — check security groups, VPC routing, endpoint",
78 12541: "No listener — check endpoint and port are correct",
79 1017: "Invalid username/password — check credentials",
80 12514: "Listener does not know of service — check service name",
81 12505: "Listener does not know of SID — try SERVICE_NAME instead of SID",
82 28000: "Account is locked — unlock the user in the database",
83 }
84 if error.code in hints:
85 print(f" Hint: {hints[error.code]}")
86 sys.exit(1)
87 print()
88
89 print("--- Query Test ---")
90 try:
91 cursor = conn.cursor()
92 cursor.execute(
93 "SELECT sysdate, SYS_CONTEXT('USERENV', 'DB_NAME'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual"
94 )
95 row = cursor.fetchone()
96 print("PASS: Query succeeded")
97 print(f" Server time: {row[0]}")
98 print(f" Database: {row[1]}")
99 print(f" Session user: {row[2]}")
100 except oracledb.DatabaseError as e:
101 (error,) = e.args
102 print(f"FAIL: Query failed — ORA-{error.code}: {error.message}")
103 print()
104
105 print("--- Encryption Status ---")
106 try:
107 cursor = conn.cursor()
108 cursor.execute("SELECT SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') FROM dual")
109 protocol = cursor.fetchone()[0]
110 print(f" Network protocol: {protocol}")
111 if protocol and protocol.lower() == "tcps":
112 print(" PASS: Connection is SSL/TLS encrypted (TCPS)")
113 elif protocol and protocol.lower() == "tcp":
114 print(" INFO: Connection is TCP (check if NNE is active below)")
115 else:
116 print(f" INFO: Protocol is '{protocol}'")
117
118 cursor.execute(
119 """
120 SELECT network_service_banner
121 FROM v$session_connect_info
122 WHERE sid = SYS_CONTEXT('USERENV', 'SID')
123 AND network_service_banner IS NOT NULL
124 """
125 )
126 banners = cursor.fetchall()
127 if banners:
128 for banner in banners:
129 print(f" Banner: {banner[0]}")
130 if "encryption" in str(banner[0]).lower() or "crypto" in str(banner[0]).lower():
131 print(" PASS: Encryption is active (NNE or SSL)")
132 else:
133 print(" INFO: No encryption banners found — connection may be unencrypted")
134 except oracledb.DatabaseError as e:
135 (error,) = e.args
136 print(f" WARN: Cannot check encryption — ORA-{error.code}: {error.message}")
137 print(" (This may require additional privileges)")
138 print()
139
140 print("--- Authentication Info ---")
141 try:
142 cursor = conn.cursor()
143 cursor.execute(
144 """
145 SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD'),
146 SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'),
147 SYS_CONTEXT('USERENV', 'HOST')
148 FROM dual
149 """
150 )
151 row = cursor.fetchone()
152 print(f" Auth method: {row[0]}")
153 print(f" Identity: {row[1]}")
154 print(f" Client host: {row[2]}")
155 except oracledb.DatabaseError as e:
156 (error,) = e.args
157 print(f" WARN: Cannot check auth info — ORA-{error.code}")
158 print()
159
160 conn.close()
161 print("=== All tests passed ===")
162
163
164if __name__ == "__main__":
165 main()